PostgreSQL / 系统课时
理解窗口函数
理解窗口函数
本课解决“怎样保留每条明细行,同时附加分组统计值”的问题。完成后,你可以理解窗口函数与 GROUP BY 的本质区别,并计算分区总额、占比和组内排名。
学习目标
- 理解窗口函数不会折叠明细行
- 使用
OVER和PARTITION BY定义计算窗口 - 计算分组总额、占比与组内排名
窗口函数与分组聚合
GROUP BY 会把多行压缩成每组一行,适合生成汇总表。窗口函数在结果集的相关行上计算,但每条明细仍然保留,适合订单列表、排行榜和累计统计。
窗口函数的基本形式是 函数() OVER (...)。PARTITION BY 决定分区,ORDER BY 决定分区内顺序。
主示例
下面保留每条销售记录,同时计算地区总额、该记录在地区中的金额占比,以及地区内金额排名。
sql
WITH sales(id, region, salesperson, amount) AS (
VALUES
(1, 'east', 'alice', 100.00::numeric),
(2, 'east', 'bob', 150.00::numeric),
(3, 'east', 'alice', 80.00::numeric),
(4, 'west', 'carol', 200.00::numeric),
(5, 'west', 'dave', 120.00::numeric)
)
SELECT
id,
region,
salesperson,
amount,
SUM(amount) OVER (PARTITION BY region) AS region_total,
ROUND(
amount * 100 / SUM(amount) OVER (PARTITION BY region),
2
) AS region_pct,
ROW_NUMBER() OVER (
PARTITION BY region
ORDER BY amount DESC, id
) AS region_rank
FROM sales
ORDER BY region, id;预期结果
text
id | region | salesperson | amount | region_total | region_pct | region_rank
----+--------+-------------+--------+--------------+------------+-------------
1 | east | alice | 100.00 | 330.00 | 30.30 | 2
2 | east | bob | 150.00 | 330.00 | 45.45 | 1
3 | east | alice | 80.00 | 330.00 | 24.24 | 3
4 | west | carol | 200.00 | 320.00 | 62.50 | 1
5 | west | dave | 120.00 | 320.00 | 37.50 | 2地区总额会重复出现在该地区的每条明细行中,这是窗口函数“保留明细”的直接体现。ROW_NUMBER 即使金额相同也会给出不同序号,示例用 id 作为并列时的稳定排序条件。
常见边界
窗口函数通常不能直接写在 WHERE 中,因为过滤发生在窗口计算之前。需要按排名筛选时,可先在子查询或公共表表达式中计算排名,再在外层查询过滤。
PARTITION BY 不是必须的。省略后,整个结果集就是一个窗口;加入后,每个分区独立计算。
按自己的节奏,确认真正理解。