窗口函数 聚合函数

1. 聚合函数#

聚合函数 Aggregate Functions 用于对一组数据进行计算, 它们会将多行数据“聚合成”一个单一的结果, 常用于 SQL 查询 如 GROUP BY 搭配使用:

1.1. 统计数据#

# 统计每天的销售额
SELECT order_date, SUM(total_price) AS daily_revenue
FROM orders
GROUP BY order_date;

• 第一步 扫描表 FROM, 数据库先把整个 grades 表读一遍, 拿到所有原始数据
• 第二步 分组 GROUP BY, 数据库根据 student_id 把数据拆成几组
  • student_id = 1 的组: (1, Math, 85), (1, English, 90)
  • student_id = 2 的组: (2, Math, 78), (2, English, 92)
• 第三步 每组上应用聚合函数:
  • 组1：(85 + 90) / 2 = 87.5
  • 组2：(78 + 92) / 2 = 85
• 最终输出 student_id = 1, avg_score = 87.5; student_id = 2, avg_score = 85

如果查询语句没有加 WHERE 条件, 数据库通常会全表扫描, 因为它必须读取表中所有的行才能正确地分组和聚合, 哪怕你 student_id 上有索引, 它也必须读出所有行的 score 值

本质上 SQL 的执行顺序差不多就是:

FROM → WHERE → GROUP BY → 聚合函数（AVG, SUM 等）→ HAVING → SELECT → ORDER BY

1.2. 计算平均值#

#计算每个学生所有科目的平均分
SELECT student_id, AVG(score) AS avg_score
FROM grades
GROUP BY student_id;

1.3. 计算最大值#

# 查出每个部门里工资最高的员工薪资
SELECT department, MAX(salary) AS max_salary
FROM employees
GROUP BY department;

2. 窗口函数#

窗口函数也是一种聚合函数的增强版, 但它不压缩多行成一行, 而是为每一行保留原始结构, 并计算“周围的一组数据”的统计值:

函数名() OVER (
    [PARTITION BY 列名]  -- 可选，分组
    [ORDER BY 列名]      -- 可选，排序
    [ROWS 或 RANGE 子句] -- 可选，定义窗口范围
)

2.1. 排名#

# 每行都有 rank 字段，表示这位学生在 Math 科目中的排名
SELECT student_id, score,
       RANK() OVER (ORDER BY score DESC) AS rank
FROM grades
WHERE subject = 'Math';

注意: 虽然写了 RANK() OVER (ORDER BY score DESC), 这只是窗口函数内部使用排序, 结果集本身的输出顺序还是无序的（除非你显式加上 ORDER BY）

• 第一步 FROM + WHERE 过滤筛选 WHERE subject = 'Math' 的记录
• 第二步 窗口函数准备 OVER(), 这一步, 数据库不会排序筛选出的记录, 而是在内存中为窗口函数排序一份数据副本（根据 score DESC）, 准备做排名
• 第三步 对每行应用 RANK() 排名函数, 数据库逐行执行这个函数, 这时候每一行都保留原始数据, 并加上一列新的 rank 值
• 第四步 结果集准备好, 但顺序可能不是你想要的, 除非你加 ORDER BY

如果想让结果集按分数顺序排序:

SELECT student_id, score,
       RANK() OVER (ORDER BY score DESC) AS rank
FROM grades
WHERE subject = 'Math'
ORDER BY score DESC;

2.2. 使用窗口函数计算累计总和#

使用聚合函数 SUM()

SELECT 产品, SUM(销售额) AS 总销售额
FROM 销售表
GROUP BY 产品;

使用窗口函数 SUM() OVER()

SELECT 日期, 产品, 销售额,
       SUM(销售额) OVER(PARTITION BY 产品) AS 产品总销售额
FROM 销售表;

使用窗口函数计算累计总和

SELECT 日期, 产品, 销售额,
       SUM(销售额) OVER(PARTITION BY 产品 ORDER BY 日期) AS 累计销售额
FROM 销售表;

2.3. 执行过程#

SELECT 日期, 产品, 销售额,
       SUM(销售额) OVER(PARTITION BY 产品 ORDER BY 日期) AS 累计销售额
FROM 销售表;

1. 数据扫描阶段 首先, 数据库引擎扫描⁠销售表中的所有行：

2. 分区处理阶段 根据⁠PARTITION BY 产品子句, 数据库将数据分成不同的分区：

产品 A 的分区：

产品 B 的分区：

3. 分区内排序阶段

由于有⁠ORDER BY 日期子句，数据库会在每个分区内按日期排序。在本例中，数据已经是按日期排序的，所以排序后的结果与原始分区相同。但这一步在逻辑上是必要的，因为它会影响窗口函数的计算方式。

4. 窗口函数计算阶段 - 累计求和

由于添加了⁠ORDER BY 日期，⁠SUM()函数不再计算整个分区的总和，而是计算累计总和。

对于每个分区内的每一行，数据库会计算当前行及之前所有行的销售额总和：

产品 A 的分区计算过程：

​ 1. 第一行（2025-01-01）：累计销售额 = 100

​ 2. 第二行（2025-01-02）：累计销售额 = 100 + 150 = 250

​ 3. 第三行（2025-01-03）：累计销售额 = 100 + 150 + 200 = 450

产品 B 的分区计算过程：

​ 1. 第一行（2025-01-01）：累计销售额 = 50

​ 2. 第二行（2025-01-02）：累计销售额 = 50 + 75 = 125

​ 3. 第三行（2025-01-03）：累计销售额 = 50 + 75 + 125 = 250

5. 结果组装阶段

数据库将原始行数据与计算得到的累计销售额结果组合起来：

6. 返回最终结果

最终返回的结果集：

3. 窗口函数 vs 聚合函数#

聚合函数：

• 把多行数据聚合成一行输出一个结果（比如 SUM、AVG、COUNT 等）
• 通常与 GROUP BY 配合使用, 结果是每个组一个值
• 输出行数通常会减少

窗口函数：

• 在每行数据上基于一个“窗口”（由 OVER 子句定义的范围）计算结果
• 不会减少行数，每行都会保留并附带计算结果
• 常用于需要保留原始数据细节同时进行分析的场景

假设我们有一个 sales 表，记录销售员的销售情况：

salesperson | sale_date  | amount
------------|------------|--------
Alice      | 2025-01-01 | 500
Alice      | 2025-01-02 | 300
Bob        | 2025-01-01 | 400
Bob        | 2025-01-03 | 600
Charlie    | 2025-01-02 | 700
Charlie    | 2025-01-04 | 200

3.1. 计算总销售额并排名#

聚合函数方案

SELECT salesperson, SUM(amount) AS total_sales
FROM sales
GROUP BY salesperson
ORDER BY total_sales DESC; -- 需额外处理排名

salesperson | total_sales
------------|-------------
Bob        | 1000
Charlie    | 900
Alice      | 800

• 聚合函数将多行聚合成一行, 适合只需要汇总结果的场景, 但无法直接生成排名, 需要额外子查询或手工处理

窗口函数方案

SELECT DISTINCT salesperson, 
       SUM(amount) OVER (PARTITION BY salesperson) AS total_sales,
       RANK() OVER (ORDER BY SUM(amount) DESC) AS sales_rank
FROM sales;

salesperson | total_sales | sales_rank
------------|-------------|------------
Bob        | 1000        | 1
Charlie    | 900         | 2
Alice      | 800         | 3

• 窗口函数直接计算总和并排名, DISTINCT 避免重复行, 如果需要保留每笔销售记录, 只需去掉 DISTINCT, 更灵活

3.2. 截止到某一天的累计销售额排名#

需求: 计算截止到 2025 年 1 月 2 日的累计销售额，并排名

SELECT salesperson, SUM(amount) AS cumulative_sales
FROM sales
WHERE sale_date <= '2025-01-02'
GROUP BY salesperson
ORDER BY cumulative_sales DESC;

salesperson | cumulative_sales
------------|------------------
Alice      | 800
Charlie    | 700
Bob        | 400

• 聚合函数简洁地汇总截止日期的数据，Alice (500+300)、Charlie (700)、Bob (400), 但排名需额外处理

3.3. 按日期显示每天的累计销售额排名#

SELECT salesperson, sale_date,
       SUM(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) AS cumulative_sales,
       RANK() OVER (PARTITION BY sale_date ORDER BY SUM(amount) DESC) AS daily_rank
FROM sales
WHERE sale_date <= '2025-01-02'
ORDER BY sale_date, daily_rank;

salesperson | sale_date  | cumulative_sales | daily_rank
------------|------------|------------------|------------
Alice      | 2025-01-01 | 500              | 1
Bob        | 2025-01-01 | 400              | 2
Alice      | 2025-01-02 | 800              | 1
Charlie    | 2025-01-02 | 700              | 2
Bob        | 2025-01-02 | 400              | 3

• 窗口函数轻松实现累计值和每天的排名, 保留每行数据, 逻辑清晰