学习目标

学完本章后，你将能够：

• 理解开窗函数的基本概念和作用
• 掌握 PARTITION BY 和 ORDER BY 子句在开窗函数中的用法
• 使用 SUM、AVG 等聚合函数结合 OVER 子句进行数据分析
• 应用 ROW_NUMBER、RANK、DENSE_RANK 函数进行数据排序和排名
• 通过 LAG、LEAD 函数分析数据前后关系和变化趋势

开窗函数（Window Functions）是 SQL 中强大的高级功能，能够在保留原始行的同时进行分组计算。开窗函数能够解决许多复杂的数据分析问题，如排名、累计计算、移动平均等。

9.1 开窗函数概述

开窗函数可以对结果集的一个子集（称为"窗口"）进行计算，不需要使用 GROUP BY 子句对结果进行分组聚合。这样就可以在保留所有行的同时，为每一行计算一个聚合值或排序值。

开窗函数与普通聚合函数的区别

普通的聚合函数（如 SUM、AVG、MAX）与 GROUP BY 一起使用时，会将多行合并为一行。而开窗函数应用于一组行（窗口），但不会减少结果集中的行数。

以下面的例子说明：

sales 表：

month	product	sales
2025-01	A	100
2025-01	B	200
2025-02	A	150
2025-02	B	250

使用普通聚合函数：

▼
sql
复制代码
SELECT month, SUM(sales) AS total_sales
FROM sales
GROUP BY month;

结果：

month	total_sales
2025-01	300
2025-02	400

使用开窗函数：

▼
sql
复制代码
SELECT month, product, sales, 
       SUM(sales) OVER(PARTITION BY month) AS month_total
FROM sales;

结果：

month	product	sales	month_total
2025-01	A	100	300
2025-01	B	200	300
2025-02	A	150	400
2025-02	B	250	400

使用开窗函数时，原始行会保留，同时每行都有一个月度总销售额。

开窗函数的基本语法

开窗函数的基本语法如下：

▼
sql
复制代码
窗口函数 OVER (
    [PARTITION BY 分区列]
    [ORDER BY 排序列]
    [窗口范围]
)

其中：

• 窗口函数：可以是聚合函数（SUM, AVG, COUNT, MIN, MAX）或排序函数（ROW_NUMBER, RANK, DENSE_RANK）等。
• PARTITION BY：可选，指定分组列，类似于 GROUP BY，但不合并行。
• ORDER BY：可选，指定排序方式，影响窗口范围和某些函数的计算结果。
• 窗口范围：可选，定义当前行的窗口范围，如 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

常见的开窗函数类型

1. 聚合函数：SUM, AVG, COUNT, MIN, MAX 等与 OVER 子句结合。
2. 排序函数：ROW_NUMBER, RANK, DENSE_RANK, NTILE 等。
3. 偏移函数：LAG, LEAD 等，用于访问当前行前后的数据。
4. 统计函数：FIRST_VALUE, LAST_VALUE, NTH_VALUE 等。

9.2 PARTITION BY 与 ORDER BY

PARTITION BY 和 ORDER BY 是开窗函数中两个重要的子句，它们共同定义了窗口的形状和计算方式。

PARTITION BY 子句

PARTITION BY 子句将数据分成多个组（分区），开窗函数在每个分区内独立计算。如果省略 PARTITION BY，整个结果集被视为一个分区。

例如，计算每个产品在每月的销售占比：

▼
sql
复制代码
SELECT month, product, sales,
       sales / SUM(sales) OVER(PARTITION BY month) * 100 AS percentage
FROM sales;

结果：

month	product	sales	percentage
2025-01	A	100	33.33
2025-01	B	200	66.67
2025-02	A	150	37.50
2025-02	B	250	62.50

ORDER BY 子句

ORDER BY 子句在开窗函数中可以指定行的处理顺序，这样便于进行累计计算和执行排序函数。

例如，计算累计销售额：

▼
sql
复制代码
SELECT month, product, sales,
       SUM(sales) OVER(ORDER BY month) AS running_total
FROM sales;

结果：

month	product	sales	running_total
2025-01	A	100	300
2025-01	B	200	300
2025-02	A	150	700
2025-02	B	250	700

组合 PARTITION BY 和 ORDER BY

当 PARTITION BY 和 ORDER BY 一起使用时，ORDER BY 在每个分区内独立排序。

例如，计算每个产品的月度累计销售额：

▼
sql
复制代码
SELECT month, product, sales,
       SUM(sales) OVER(PARTITION BY product ORDER BY month) AS product_running_total
FROM sales;

结果：

month	product	sales	product_running_total
2025-01	A	100	100
2025-02	A	150	250
2025-01	B	200	200
2025-02	B	250	450

9.3 SUM OVER 函数

SUM OVER 是最常用的开窗函数之一，它可以在保留原始行的同时计算总和。

基本用法

▼
sql
复制代码
SUM(表达式) OVER ([PARTITION BY 分区列] [ORDER BY 排序列] [窗口范围])

无分区和排序的 SUM OVER

最简单的形式是不带 PARTITION BY 和 ORDER BY 的 SUM OVER，这样可以计算整个结果集的总和：

▼
sql
复制代码
SELECT month, product, sales,
       SUM(sales) OVER() AS total_sales
FROM sales;

结果：

month	product	sales	total_sales
2025-01	A	100	700
2025-01	B	200	700
2025-02	A	150	700
2025-02	B	250	700

带分区的 SUM OVER

添加 PARTITION BY 后，SUM 函数会分别计算每个分区的总和：

▼
sql
复制代码
SELECT month, product, sales,
       SUM(sales) OVER(PARTITION BY product) AS product_total
FROM sales;

结果：

month	product	sales	product_total
2025-01	A	100	250
2025-02	A	150	250
2025-01	B	200	450
2025-02	B	250	450

实际应用

SUM OVER 函数在数据分析中非常常见，例如：

1. 计算占比：计算每个项目占总体的百分比。
2. 分组占比：计算每个项目在其组内的占比。
3. 比较数据：将个体数据与总体或组内总计进行比较。

例如，在网站中，我们可以计算每个课程在其类别中的销售占比：

▼
sql
复制代码
SELECT c.category, c.course_name, o.total_sales,
       o.total_sales / SUM(o.total_sales) OVER(PARTITION BY c.category) * 100 AS category_percentage
FROM courses c
JOIN (
    SELECT course_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY course_id
) o ON c.course_id = o.course_id;

9.4 SUM OVER ORDER BY 函数

在 SUM() OVER() 窗口函数中引入 ORDER BY 子句后，计算逻辑会从单纯的分组求和转变为动态累计求和（也称为运行总和），分析随时间变化的连续累计趋势时，就可以使用 SUM OVER ORDER BY 函数，例如跟踪销售额或用户增长的累计曲线。

基本语法

▼
sql
复制代码
SUM(表达式) OVER (ORDER BY 排序列 [窗口范围])

默认的窗口范围

如果不指定窗口范围，默认范围是从分区开始到当前行：

▼
sql
复制代码
SELECT month, sales,
       SUM(sales) OVER(ORDER BY month) AS running_total
FROM monthly_sales;

假设我们有以下 monthly_sales 表的数据：

month	sales
2025-01	120000
2025-02	145000
2025-03	135000
2025-04	160000
2025-05	175000

查询结果将是：

month	sales	running_total
2025-01	120000	120000
2025-02	145000	265000
2025-03	135000	400000
2025-04	160000	560000
2025-05	175000	735000

这个语法等同于：

▼
sql
复制代码
SELECT month, sales,
       SUM(sales) OVER(ORDER BY month 
                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM monthly_sales;

自定义窗口范围

可以自定义窗口范围，例如，计算3个月移动总和：

▼
sql
复制代码
SELECT month, sales,
       SUM(sales) OVER(ORDER BY month 
                       ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_3month_sum
FROM monthly_sales;

使用相同的数据，这个3个月移动总和的结果为：

month	sales	moving_3month_sum
2025-01	120000	120000
2025-02	145000	265000
2025-03	135000	400000
2025-04	160000	440000
2025-05	175000	470000

注：

• 2025-01：只有当月自己的销售额
• 2025-02：1月和2月的销售额总和
• 2025-03：1月、2月和3月的销售额总和
• 2025-04：2月、3月和4月的销售额总和
• 2025-05：3月、4月和5月的销售额总和

结合 PARTITION BY 和 ORDER BY

当 PARTITION BY 和 ORDER BY 一起使用时，累计计算在每个分区内独立进行：

▼
sql
复制代码
SELECT year, month, product, sales,
       SUM(sales) OVER(PARTITION BY year, product ORDER BY month) AS ytd_sales
FROM sales;

假设我们有一个更详细的 sales 表：

year	month	product	sales
2025	1	AI 零代码应用生成平台教程	45000
2025	2	AI 零代码应用生成平台教程	52000
2025	3	AI 零代码应用生成平台教程	48000
2025	1	OJ 在线判题项目教程	38000
2025	2	OJ 在线判题项目教程	41000
2025	3	OJ 在线判题项目教程	39500
2026	1	AI 零代码应用生成平台教程	55000
2026	2	AI 零代码应用生成平台教程	59000
2026	1	OJ 在线判题项目教程	42000
2026	2	OJ 在线判题项目教程	44000

查询结果为：

year	month	product	sales	ytd_sales
2025	1	AI 零代码应用生成平台教程	45000	45000
2025	2	AI 零代码应用生成平台教程	52000	97000
2025	3	AI 零代码应用生成平台教程	48000	145000
2025	1	OJ 在线判题项目教程	38000	38000
2025	2	OJ 在线判题项目教程	41000	79000
2025	3	OJ 在线判题项目教程	39500	118500
2026	1	AI 零代码应用生成平台教程	55000	55000
2026	2	AI 零代码应用生成平台教程	59000	114000
2026	1	OJ 在线判题项目教程	42000	42000
2026	2	OJ 在线判题项目教程	44000	86000

9.5 ROW_NUMBER 函数

ROW_NUMBER 函数为结果集中的每一行分配一个唯一的序号，从 1 开始，根据指定的排序顺序递增。

基本语法

▼
sql
复制代码
ROW_NUMBER() OVER(ORDER BY 排序列)

基本用法

▼
sql
复制代码
SELECT product, SUM(sales) AS total_sales,
       ROW_NUMBER() OVER(ORDER BY SUM(sales) DESC) AS sales_rank
FROM sales
GROUP BY product;

结果：

product	total_sales	sales_rank
AI 零代码应用生成平台教程	259000	1
OJ 在线判题项目教程	204500	2

9.6 RANK 函数

RANK 函数类似于 ROW_NUMBER，但对于相同的值，它们共享同一个排名，并且排名可能会跳跃。

基本语法

▼
sql
复制代码
RANK() OVER(ORDER BY 排序列)

RANK 与 DENSE_RANK 的区别

SQL 提供了两种排名函数：RANK 和 DENSE_RANK。它们的区别在于处理并列排名后的序号：

• RANK：相同值获得相同排名，但会导致排名跳跃（如 1, 2, 2, 4）。
• DENSE_RANK：相同值获得相同排名，但不会跳跃（如 1, 2, 2, 3）。

例如：

▼
sql
复制代码
SELECT student, score,
       ROW_NUMBER() OVER(ORDER BY score DESC) AS row_num,
       RANK() OVER(ORDER BY score DESC) AS rank,
       DENSE_RANK() OVER(ORDER BY score DESC) AS dense_rank
FROM student_scores;

结果：

student	score	row_num	rank	dense_rank
鱼皮	95	1	1	1
代码鸭	95	2	1	1
算法达人	90	3	3	2
面试鸭	85	4	4	3
老鱼	85	5	4	3

实际应用

RANK 和 DENSE_RANK 一般用在以下场景：

1. 竞争排名：如学生成绩排名，运动比赛排名等。
2. 销售或性能指标排名：根据销售额、转化率等指标进行排名。
3. 数据分析：分析前几名或后几名的数据点。

例如，我们可以查看不同难度级别下题目的得分排名：

▼
sql
复制代码
SELECT 
    difficulty,
    question_id,
    avg_score,
    RANK() OVER(PARTITION BY difficulty ORDER BY avg_score DESC) AS difficulty_rank
FROM (
    SELECT 
        q.difficulty,
        q.question_id,
        AVG(a.score) AS avg_score
    FROM questions q
    JOIN answers a ON q.question_id = a.question_id
    GROUP BY q.difficulty, q.question_id
) scores;

9.7 LAG 与 LEAD 函数

LAG 和 LEAD 函数可以访问当前行前面或后面的行，便于分析数据变化趋势。

基本语法

▼
sql
复制代码
LAG(表达式 [, 偏移量 [, 默认值]]) OVER(ORDER BY 排序列)
LEAD(表达式 [, 偏移量 [, 默认值]]) OVER(ORDER BY 排序列)

• 表达式：需要获取的列值。
• 偏移量：可选，默认为 1，表示前一行或后一行。
• 默认值：可选，当没有前一行或后一行时返回的值，默认为 NULL。

基本示例

计算月度销售额的环比增长：

▼
sql
复制代码
SELECT 
    month,
    sales,
    LAG(sales) OVER(ORDER BY month) AS prev_month_sales,
    (sales - LAG(sales) OVER(ORDER BY month)) / LAG(sales) OVER(ORDER BY month) * 100 AS growth_rate
FROM monthly_sales;

结果：

month	sales	prev_month_sales	growth_rate
2025-01	120000	NULL	NULL
2025-02	145000	120000	20.83
2025-03	135000	145000	-6.90
2025-04	160000	135000	18.52
2025-05	175000	160000	9.38

练习题

练习 1：基本开窗函数应用

网站记录了每天各个课程的浏览量，存储在 course_views 表中，包含 date, course_id, views 等字段。请编写一个查询，计算每门课程的累计浏览量以及该课程在当天的浏览量占所有课程的百分比。

答案：

▼
sql
复制代码
SELECT 
    date, 
    course_id, 
    views,
    SUM(views) OVER(PARTITION BY course_id ORDER BY date) AS cumulative_views,
    views * 100.0 / SUM(views) OVER(PARTITION BY date) AS daily_percentage
FROM course_views;

练习 2：排名函数应用

假设 student_scores 表记录了学生的考试分数，包含 student_id, course_id, score 等字段。编写一个查询，为每门课程的学生分数进行排名（处理并列情况），并显示每个学生在各自课程中的百分位排名（例如，前5%）。

答案：

▼
sql
复制代码
SELECT 
    student_id, 
    course_id, 
    score,
    RANK() OVER(PARTITION BY course_id ORDER BY score DESC) AS course_rank,
    PERCENT_RANK() OVER(PARTITION BY course_id ORDER BY score DESC) * 100 AS percentile
FROM student_scores;

练习 3：LAG/LEAD 函数应用

有一个 user_logins 表记录用户登录信息，包含 user_id, login_time 等字段。编写一个查询，计算每个用户的连续登录会话之间的时间间隔（以小时为单位），会话定义为间隔小于24小时的连续登录。

答案：

▼
sql
复制代码
SELECT 
    user_id, 
    login_time,
    TIMESTAMPDIFF(
        HOUR,
        LAG(login_time) OVER(PARTITION BY user_id ORDER BY login_time),
        login_time
    ) AS hours_since_last_login,
    CASE 
        WHEN TIMESTAMPDIFF(
            HOUR,
            LAG(login_time) OVER(PARTITION BY user_id ORDER BY login_time),
            login_time
        ) < 24 THEN 'Same Session'
        ELSE 'New Session'
    END AS session_status
FROM user_logins;