关于Window function中ORDER BY子句结合BETWEEN运算符的疑问

你的代码中ORDER BY的实际作用 #

你在ARRAY_AGG窗口函数里写的ORDER BY day BETWEEN '2023-01-02' AND '2023-01-03'，完全不是过滤行——它是把布尔表达式的结果作为排序键来排序窗口内的行：

• 当day在指定范围内时，表达式返回true，SQL会将其转为数值1；不在范围内时返回false，转为数值0。
• 窗口函数默认按从小到大排序，所以0（对应2023-01-01）的行排在最前面，1（对应2023-01-02、01-03）的行排在后面。

同时，窗口函数在指定ORDER BY但未显式声明窗口框架时，默认采用RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW的框架规则：

• RANGE会把所有排序键值与当前行相同的行都纳入窗口范围。
• 所以对于排序键为1的行（2023-01-02和01-03），窗口会包含所有排序键≤1的行（也就是全部三行）；排序键为0的行只包含自己。

结果逐行对应逻辑 #

为什么和预期不符 #

你混淆了窗口排序和行过滤的逻辑：想要限制聚合的行范围，不能把过滤条件放在ORDER BY子句里，而是要用正确的过滤方式。

比如，如果你想让每一行的ss都只包含指定日期范围内的spent值，可以用FILTER子句：

WITH samp AS (
    SELECT '2023-01-01' AS day, 1 AS spent UNION ALL
    SELECT '2023-01-02' AS day, 2 AS spent UNION ALL 
    SELECT '2023-01-03' AS day, 3 AS spent
) 
SELECT day, 
       spent 
     , ARRAY_AGG(spent) FILTER (WHERE day BETWEEN '2023-01-02' AND '2023-01-03') OVER() ss
FROM samp 
ORDER BY day

执行后所有行的ss都会是[2,3]。

如果是想要实现滑动窗口（仅聚合当前行及之前的指定日期数据），则需要显式定义窗口框架（不同SQL方言语法略有差异，以你的使用环境为准）。

内容的提问来源于stack exchange，提问作者simon