具体应用场景

我的目的和需求是在做日行情数据时，想了解每个报告期末的总市值和市净率PB值。但是无法直接在数据库中令tradedate=报告期，原因是报告期当天不一定是交易日。因此需要把数据进行补齐。分析了choice、wind和聚源的数据，发现只有wind已经有了补齐的逻辑，具体是：取离报告期最近的交易日的数据。

因此，分析下来，我发现我的需求如下。

（1）新建一列ENDDATE，令其是每个报告期的最后一天，具体来说就是3/31、6/30、9/30和12/31。

（2）新建一列TRADEDATENEW，记录报告期最后一个交易日的日期。

（3）新建一列总市值ZSZNEW和PBNEW，令其取整个报告期最后一个交易日的数值。

最核心的是（3），是我最想要的数值。

SQL代码操作

具体到SQL代码，上面的（2）、（3）可以用first_value()函数轻松解决。

首先简单说下first_value()函数，字面上看也很明确，就是取第一个记录值。一般操作中语法如下：

first_value(取值列) over (partition by 分组列 order by 排序列)

具体来说：逻辑就是将数据按照“分组列”进行分组，再在组内利用“排序列”进行排序，然后取每个组“取值列”的第一个值。

下面将其应用到我的案例中，以总市值为例。

我们先规定原始数据库中：TRADEDATE列是交易日，ZSZ列是总市值，SECCODE是证券代码。

那么我新建的（1）ENDDATE列就应该是：

ADD_MONTH(TRUNC(TRADEDATE,'Q'),3)-1

新建的（2）TRADEDATENEW列就应该是：

first_value(TRADEDATE) over (partition by SECCODE,ADD_MONTH(TRUNC(TRADEDATE,'Q'),3)-1 order by TRADEDATE DESC)

新建的（2）ZSZNEW列就应该是：

first_value(ZSZ) over (partition by SECCODE,ADD_MONTH(TRUNC(TRADEDATE,'Q'),3)-1 order by TRADEDATE)

再解释下：（以2019年第二季度为例，该季度最后一天是2019/6/30，但当天是周日，最接近的一个交易日是2019/6/28）

（1）ENDDATE列：

先用TRUNC函数，将TRADEDATE函数截取到季度，其实也就是TRADEDATE所在季度的第一天；接着再用ADD_MONTH函数，将TRUNC函数的结果加上3个月，也就是变到下一个季度的第一天；最后再减掉1天，取到了下一季度第一天的前一天，也就是所在季度的最后一天。

如TRADEDATE为2019/6/1日时，TRUNC(TRADEDATE,'Q')得到2019/4/1，ADD_MONTH(TRUNC(TRADEDATE,'Q'),3)得到2019/7/1，ADD_MONTH(TRUNC(TRADEDATE,'Q'),3)-1就是2019/6/30。

（下面（2）、（3）解释中，用ENDDATE代替ADD_MONTH(TRUNC(TRADEDATE,'Q'),3)-1）

（2）TRADEDATENEW列：

将数据按照证券代码和ENDDATE分组，分组之后在组内根据TRADEDATE倒序排序（从大到小）。也就是每个公司的每个报告期都是一组，在这一组里面按照交易日从大到小排列。最后取最上面那一行的TRADEDATE的值，也就是最接近ENDDATE的交易日了。

（3）ZSZNEW列：

与（2）前面都一样，只不过最后取值取的是首行的ZSZ，也就是最接近报告期末的交易日的总市值。

前面说到的都是first_value()函数，last_value()与之类似，取的是最后一行的数值。但是last_value()有一个需要注意的地方。

last_value()默认统计范围是 rows between unbounded preceding and current row，也就是取当前行数据与当前行之前的数据的比较。个人理解是它类似于一个累计的过程，比如分组排序（正序排序）之后交易日分别是2019/6/26、2019/6/27和2019/6/28，那么生成的TRADEDATENEW列值分别为2019/6/26、2019/6/27和2019/6/28，也就是第一行2019/6/26与它和它之前行的数据比较取最后那一行的值，第二行2019/6/27与它和它之前行的数据比较取最后那一行的值，第三行2019/6/28与它和它之前行的数据比较取最后那一行的值.

那么，如果我们希望像first_value（）那样直接在每行数据中显示最后的那个数据，就需要在order by 条件的后面加上语句：rows between unbounded preceding and unbounded following。

以上就是我的学习整理啦~个人感觉这个函数还蛮实用，作为个人积累与复习，也希望有幸帮助到其他人。