原来除了 Modern C++、Modern CMake，我们还有 Modern SQL，真是佩服这种文艺复兴式的 branding。

窗口函数（Window Function）就是一个例子，它由 SQL:2003 引入，可以用来筛选结果集中与当前行存在指定关联的行。相比子查询，效率更高，用起来也更方便。

例如我们有一张去年全年每日收入的表 revenues，想根据这张表查一张报表，显示每季度总收入及其环比增长，就可以用窗口函数：

created_at revenue 2019-01-01 123.45 2019-01-02 456.78 2019-01-03 420.00 ... ...

SELECT
    date_part('quarter', created_at) AS quarter,
    sum(revenue) AS revenue,
    (sum(revenue) /
        lag(sum(revenue)) OVER ()) - 1 AS percentage
FROM revenues
GROUP BY 1
ORDER BY 1

得到的结果类似这样：

quarter revenue percentage 1 4530.50 2 4565.64 0.008 3 4933.01 0.080 4 4731.75 -0.041

SQL 中的 lag(sum(revenue)) OVER () 就是对窗口函数的调用了，其中 lag 函数就表示「上一条记录（季度）」。

窗口函数调用的特征是关键词 OVER

• OVER 前的部分为窗口函数调用本身，用来指定针对窗口中内容的操作。既可以用 lag 这样专门的窗口函数，也可以用 sum 这种普通的聚合函数。

• OVER 后的部分即为对窗口的定义，既可以是直接在括号里写出，也可以用稍后统一定义的窗口名，比如上面的 SQL 也可以写成：

  SELECT
      date_part('quarter', created_at) AS quarter,
      sum(revenue) AS revenue,
      (sum(revenue) /
          lag(sum(revenue)) OVER w) - 1 AS percentage
  FROM revenues
  GROUP BY 1
  WINDOW w AS ()
  ORDER BY 1
  

窗口的定义

「窗口定义」中的窗口其实英文叫 Frame，即窗框。「窗口函数」中的窗口则是 Window，即窗户。没什么特别含义，应该就是叫着顺口、想着形象而已。

下面的例子里，我们用 PostgreSQL 的聚合函数 array_agg 列出窗口中有哪几行。

括号中留空表示窗口中为结果中的所有行：

SELECT
    i,
    array_agg(i) OVER ()
FROM generate_series(0, 5) AS s(i)
ORDER BY 1

i array_agg 0 0,1,2,3,4,5 1 0,1,2,3,4,5 2 0,1,2,3,4,5 3 0,1,2,3,4,5 4 0,1,2,3,4,5 5 0,1,2,3,4,5

我们可以看到每一行的对应窗口里，都包含了所有其它行。

括号中还可以使用 PARTITION BY 指定分组的条件：

SELECT
    i,
    array_agg(i) OVER (
        PARTITION BY i % 2
    )
FROM generate_series(0, 5) AS s(i)
ORDER BY 1;

i array_agg 0 0,2,4 1 1,3,5 2 0,2,4 3 1,3,5 4 0,2,4 5 1,3,5

我们可以看到每一行的对应窗口里，都包含了与它 i % 2 值相同的行。

可以用 ROWS BETWEEN A AND B 来指定窗口中包含哪些行，例如：

SELECT
    i,
    array_agg(i) OVER (
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 
    )
FROM generate_series(0, 5) AS s(i)
ORDER BY 1;

i array_agg 0 0,1,2,3,4,5 1 1,2,3,4,5 2 2,3,4,5 3 3,4,5 4 4,5 5 5

这里其实直接读 SQL 就明白了，是要求窗口从当前行开始，一直到最后一条记录结束。

窗口定义里还可以用 ORDER BY 来排序，不过一旦排序，默认的范围就变成了 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRERNT ROW（从开头到当前行），如果不是想要的范围就需要显式指定。

SELECT
    i,
    array_agg(i) OVER (
        ORDER BY i DESC
    )
FROM generate_series(0, 5) AS s(i)
ORDER BY 1;

i array_agg 0 5,4,3,2,1,0 1 5,4,3,2,1 2 5,4,3,2 3 5,4,3 4 5,4 5 5

常见窗口函数

• lead 同一分组中，在当前行之后数的第 N 行
• lag 同一分组中，在当前行之前的第 N 行
• row_number 当前分组中的行号，从 1 开始
• rank 当前分组中的排名，如果存在相同名次，会是 1、1、3、4……
• dense_rank 当前分组中的排名，如果存在相同名次，会是 1、1、2、3……

SQL