【PostgreSQL 17】11 窗口函数

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

SELECT
	employee_id,
	first_name,
	last_name,
	salary,
	AVG(salary)
FROM employees
;

添加OVER()

SELECT
	employee_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。

SELECT
	employee_id,
	first_name,
	last_name,
	salary,
	department_id,
	AVG(salary) OVER(PARTITION BY department_id)
FROM
	employees
;

排序选项　ORDER BY

指定分区内的排序方式

SELECT
	employee_id,
	first_name,
	last_name,
	salary,
	department_id,
	RANK() OVER(PARTITION BY department_id ORDER BY salary DESC)
FROM
	employees
;

窗口选项　frame_clause

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

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

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

聚合窗口函数

SELECT
	*,
	AVG(amount) OVER(),
	SUM(amount) OVER(),
	COUNT(*) OVER(),
	MIN(amount) OVER()
FROM
	sales_monthly
;

SELECT
	*,
	AVG(amount) OVER(PARTITION BY product),
	SUM(amount) OVER(PARTITION BY product),
	COUNT(*) OVER(PARTITION BY product),
	MIN(amount) OVER(PARTITION BY product)
FROM
	sales_monthly
;

SELECT
	*,
	AVG(amount) OVER(PARTITION BY product ORDER BY ym),
	SUM(amount) OVER(PARTITION BY product ORDER BY ym),
	COUNT(*) OVER(PARTITION BY product ORDER BY ym),
	MIN(amount) OVER(PARTITION BY product ORDER BY ym)
FROM
	sales_monthly
;

加了ORDER会有默认值 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

移动平均值

SELECT
	*,
	AVG(amount) OVER(
		PARTITION BY product 
		ORDER BY ym 
		ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
	)
FROM
	sales_monthly
;

排名窗口函数

用于对数据进行分组排名。

参考资料

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