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 不是必须的。省略后,整个结果集就是一个窗口;加入后,每个分区独立计算。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

理解窗口函数

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通