【PostgreSQL 17】11 窗口函数

直接这么写会报错，缺少GROUP BY

SELECTemployee_id,first_name,last_name,salary,AVG(salary)
FROM employees
;

添加OVER()

SELECTemployee_id,first_name,last_name,salary,AVG(salary) OVER()
FROM employees
;

定义

window_function (expression, ...) OVER (PARTITION BY ...ORDER BY ...frame_clause
)

分区选项 PARTITION BY

用于定义分区，作用类似于 GROUP BY。

SELECTemployee_id,first_name,last_name,salary,department_id,AVG(salary) OVER(PARTITION BY department_id)
FROMemployees
;

排序选项　ORDER BY

指定分区内的排序方式

SELECTemployee_id,first_name,last_name,salary,department_id,RANK() OVER(PARTITION BY department_id ORDER BY salary DESC)
FROMemployees
;

窗口选项　frame_clause

在当前分区内指定一个计算窗口。
指定了之后，分析函数不再基于分区计算，而是基于窗口内的数据进行计算。

例题：1321. 餐馆营业额变化增长
计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值。

-- 外层查询：获取最终结果，包含日期、7天总营业额和平均营业额
SELECTDISTINCT visited_on,  -- 去重后的访问日期amount,               -- 子查询计算的7天累计营业额-- 计算7天平均营业额并保留2位小数ROUND(amount::NUMERIC / 7, 2) AS average_amount
FROM (-- 子查询：计算每个日期往前7天（含当天）的累计营业额SELECTvisited_on,-- 使用窗口函数计算滚动总和SUM(amount) OVER (ORDER BY visited_on  -- 按日期排序-- 定义窗口范围：当前行及之前6天（共7天）RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW) AS amountFROM Customer  -- 从顾客消费表获取数据
) t  -- 子查询别名
-- 过滤条件：只保留有完整7天数据的日期
-- 即当前日期减去6天后的日期必须存在于表中
WHERE visited_on - INTERVAL '6 days' IN (SELECT visited_on FROM Customer)
ORDER BY visited_on;  -- 按日期升序排列结果

参考资料

[1] 不剪发的Tony老师【PostgreSQL开发指南】第31~32节