窗口函数与 CTE
中等 约 40 分钟
你将构建什么
一组使用窗口函数进行排名、累计求和、滞后/超前比较的分析 SQL 查询,以及用 CTE 编写可读的多步查询。
学习目标
- 使用 ROW_NUMBER、RANK 和 DENSE_RANK
- 用窗口框架计算累计总和
- 用 LAG 和 LEAD 比较行
- 用 CTE 构建可读的查询
前置要求
- GROUP BY 和聚合
- JOIN 基础
- ORDER BY
步骤
- 1
建立销售数据
创建一个 sales 表,包含用于排名和趋势分析的月度数据。
代码CREATE TABLE sales ( id SERIAL PRIMARY KEY, region TEXT NOT NULL, month TEXT NOT NULL, amount INTEGER NOT NULL ); INSERT INTO sales (region, month, amount) VALUES ('North', '2024-01', 10000), ('North', '2024-02', 12000), ('North', '2024-03', 9000), ('South', '2024-01', 8000), ('South', '2024-02', 15000), ('South', '2024-03', 11000), ('East', '2024-01', 7000), ('East', '2024-02', 9000), ('East', '2024-03', 13000);预期结果
三个区域、三个月共九行销售数据。
- 2
用 ROW_NUMBER 和 RANK 排名
使用 ROW_NUMBER() 和 RANK() 配合 PARTITION BY,按销售额对每个区域内的月份排名。
代码SELECT region, month, amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn, RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rnk FROM sales ORDER BY region, rn;预期结果
每个区域的月份按金额降序编号 1-3;并列时共享排名。
- 3
用窗口框架计算累计总和
使用窗口框架子句计算每个区域随时间累积的销售额。
代码SELECT region, month, amount, SUM(amount) OVER ( PARTITION BY region ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM sales ORDER BY region, month;预期结果
running_total 在每个区域内逐月累加。
- 4
用 LAG 计算环比
使用 LAG() 将每个月的销售额与上月比较,计算百分比变化。
代码SELECT region, month, amount, LAG(amount) OVER (PARTITION BY region ORDER BY month) AS prev_month, ROUND( (amount - LAG(amount) OVER (PARTITION BY region ORDER BY month)) * 100.0 / LAG(amount) OVER (PARTITION BY region ORDER BY month), 1 ) AS pct_change FROM sales ORDER BY region, month;预期结果
每行显示上月金额和百分比变化。
- 5
用 CTE 查找每个区域的最佳表现
使用 CTE(WITH 子句)先对行排名,再过滤出每个区域的最佳表现。CTE 让复杂查询更易读。
代码WITH ranked AS ( SELECT region, month, amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn FROM sales ) SELECT region, month AS best_month, amount AS best_amount FROM ranked WHERE rn = 1 ORDER BY region;预期结果
返回每个区域销售额最高的月份。
自己动手试试
起始代码
-- Rank months within each region
SELECT region, month, amount,
??? OVER (PARTITION BY region ORDER BY amount DESC) AS rn
FROM sales;解决方案代码
WITH ranked AS (
SELECT region, month, amount,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
FROM sales
)
SELECT * FROM ranked WHERE rn = 1;你学到了什么
你掌握了窗口函数和 CTE —— 分析 SQL 的核心工具。窗口函数在不折叠行的情况下跨行计算,CTE 让复杂查询更易读、更易维护。
相关资源
相关术语
新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。
解读错误信息
用大白话解释常见错误——什么意思、为什么发生、如何修复。