前言

我在写sql语句的时候，遇到主表副表一对多的关系，需要主表join副表，并对副表的记录汇总处理等操作。之前处理这样的操作，我都是group by主表的字段，然后聚合函数处理副表的字段。但如果查询比较复杂，不仅需要副表join的多行数据，又需要对副表聚合查询，group by就不好用了。并且group by需要好多字段，否则会不满足mysql的语法要求this is incompatible with sql_mode=only_full_group_by。因此我学习了mysql的窗口函数，来简化这些操作。

简介

窗口函数（Window Functions）允许你在查询结果的行上执行计算，而不必对数据进行分组（GROUP BY）。

常见使用场景

  • 数据分析，如排名、排序、分组统计、计算、前后值比较等
  • 对某些分组场景简化SQL，提升效率
  • 常用于子查询，将一些复杂条件简化

需求MySQL版本 >= 8

语法结构

1
2
3
4
5
function_name(expression) OVER (
    [PARTITION BY column_name(s)]    -- 分区
    [ORDER BY column_name(s)]        -- 排序
    [frame_clause]                   -- 窗口帧
)
  • PARTITION BY：将结果集划分为不同的分区（类似于 GROUP BY，但不影响行数）。
  • ORDER BY：指定窗口内数据的排序方式（如排名、累计和等场景需要排序）。
  • frame_clause：定义窗口的范围（例如，当前行、前 N 行、后续 N 行等）。

常用案例

排名函数

  • ROW_NUMBER()：为分区内的每一行分配唯一的序号。
  • RANK()：根据排序列的值分配排名，值相同时排名并列，后续排名会跳过相应位置。
  • DENSE_RANK()：根据排序列的值分配排名，值相同时排名并列，但后续排名不会跳过。

示例：

1
2
3
4
5
6
7
SELECT
    name,
    score,
    ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS rn,   -- 按班级分区，按成绩倒序行号
    RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rank_val,   -- 按班级分区排名
    DENSE_RANK() OVER (PARTITION BY class ORDER BY score DESC) AS dense_rank   -- 按班级分区密集排名
FROM students;

聚合函数

  • SUM()：计算分区内的总和（累加）。
  • AVG()：计算分区内的平均值。
  • MIN() 和 MAX()：计算分区内的最小值和最大值。
  • COUNT()：计算分区内行数。

示例：

1
2
3
4
5
SELECT
    name,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS total_dept_salary   -- 部门工资总和
FROM employees;

取值函数

  • FIRST_VALUE()：取分区中排序后的第一个值。
  • LAST_VALUE()：取分区中排序后的最后一个值。

示例：

1
2
3
4
5
6
7
8
9
10
SELECT
    name,
    sale_date,
    amount,
    FIRST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_sale   -- 每个客户的第一笔销售额
FROM sales;