窗口函数根据一组相关的行(它的窗口)为每一行计算一个值,而不会像 GROUP BY 那样把行合并。写法是 function() OVER (PARTITION BY ... ORDER BY ...)。PARTITION BY 把行分成若干组,ORDER BY 设定每组内部的顺序。
每一行都留在结果中,并带上它所在区域的总额:north 是 1000,south 是 500。于是每位销售的占比只需一个表达式(ana 占 north 的 50%)。任何聚合函数(sum、count、avg、min、max)加上 OVER 就成了窗口函数。空的 OVER () 把整个结果当作一个窗口。
用 ROW_NUMBER、RANK 和 DENSE_RANK 编号
这三个排名函数只在行的 ORDER BY 值并列时才有区别:
ben 和 cy 都是 85 分。row_number 仍然给他们 2 和 3;除非 ORDER BY 能区分并列,否则谁得到 2 是不确定的。rank 给两人都是 2,dee 跳到 4。dense_rank 给两人都是 2,接着是 3。
| 函数 | 并列时 | 并列后是否跳号 |
|---|---|---|
row_number() | 编号不同 | 否 |
rank() | 编号相同 | 是 |
dense_rank() | 编号相同 | 否 |
每组取前 N 行
窗口函数在 WHERE 之后计算,所以不能直接按它筛选。先在子查询或 CTE 中给行编号,再在外层筛选:
结果是每个区域业绩最好的两位销售。如果要保留并列第二的所有行,就用 rank() 代替 row_number()。如果每组只取第一行,DISTINCT ON 写起来更短。
计算累计求和或移动平均
当 OVER 中有 ORDER BY 时,聚合函数会在一个窗口框架上计算。默认的框架从分区的第一行到当前行,这正好得到累计求和。用 ROWS BETWEEN 子句可以设置不同的框架,比如当前行和它前面的两行:
到第 5 天累计总数是 130,第 5 天的三日平均值是 (30 + 20 + 50) / 3 = 33.3。
默认框架是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。它包含和当前行 ORDER BY 值相同的所有行,所以同一天的两行会得到相同的累计值。写成 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,就会一行一行地累加。
和上一行比较:LAG 和 LEAD
lag(col) 读取窗口顺序中当前行前一行的值,lead(col) 读取后一行的值。超出边界时两者都返回 NULL,除非你在第三个参数中传入默认值。
第 1 天没有前一行,所以 previous 和 change 都是 NULL。WINDOW 子句按名字定义一次窗口,供多个函数共用。
first_value 在默认框架下能正常工作,last_value 却不行:默认框架到当前行为止,所以它返回的是当前行的值。加上 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING,让框架覆盖整个分区。其他只能用作窗口函数的有 ntile(n)、percent_rank()、cume_dist() 和 nth_value();PostgreSQL 窗口函数参考列出了全部函数。
筛选聚合函数统计哪些行
FILTER (WHERE ...) 也能用在窗口聚合上,在每一行上得到按条件计算的总数:
SELECT day,
count(*) FILTER (WHERE status = 'error') OVER (ORDER BY day) AS errors_so_far
FROM requests;
常见问题
GROUP BY 和 PARTITION BY 有什么区别?
GROUP BY 每组返回一行。OVER 中的 PARTITION BY 保留每一行,并为每一行给出在它所在组上计算的值,这样就能把某一行的金额和它所在组的总额并排显示。
WHERE 中能用窗口函数吗?
不能。窗口函数在 WHERE、GROUP BY 和 HAVING 之后执行,所以只能用在选择列表和 ORDER BY 中。在子查询或 CTE 中算出这个值,再在外层查询中筛选。
为什么 LAST_VALUE 返回的是当前行的值?
当 OVER 中有 ORDER BY 时,默认的窗口框架到当前行(及其并列行)为止,所以框架中的最后一行就是当前行。加上 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING,让框架覆盖整个分区。
窗口框架中 ROWS 和 RANGE 有什么区别?
ROWS 按物理行计数:ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 是当前行和它前面的两行。RANGE 按 ORDER BY 的值来算,所以值相等的行(同级行)总是一起出现在框架中。