MySQL技巧 | 窗口函数

由于MySQL基本篇内容过多，故将窗口函数的相关内容独立出来。本篇内容大多作于2024年4月。

窗口函数（OLAP函数）主体

其语法为：

SELECT <OLAP_FUNCTION> OVER ([PARTITION BY column] [ORDER BY column]) column_alias FROM table;

PARTITION BY 分组后的结果被称为“窗口”，其与GROUP BY 不同，不会改变行数。

专业窗口函数

包含ROW_NUMBER()、RANK()和DENSE_RANK()等。

ROW_NUMBER()会为每个分组内的行分配一个唯一的编号，这个编号是根据ORDER BY中指定的排序顺序来决定的。

RANK()与ROW_NUMBER()类似，RANK()也会根据ORDER BY中的排序顺序来为每个分组内的行分配一个编号。不同之处在于，如果两行的排序值相同，RANK()会给这两行分配相同的编号。接下来的编号会跳过之前重复的数量，比如如果有两行并列排在第一位，那么下一个编号就是3。

DENSE_RANK()的工作方式类似于RANK()，但是它不会跳过编号。也就是说，如果有两行并列排在第一位，那么下一个编号仍然是2。

案例：ROW_NUMBER() 的使用场景

https://leetcode.cn/problems/restaurant-growth/?envType=study-plan-v2&envId=sql-free-50

SQL

with distinct_date as (
  select 
    visited_on, 
    sum(amount) curr_amount 
  from 
    Customer 
  group by 
    visited_on 
  order by 
    visited_on
), 
add_prev_amount as (
  select 
    visited_on, 
    sum(curr_amount) over(
      rows between 6 preceding 
      and current row
    ) amount, 
    row_number() over() row_num 
  from 
    distinct_date
) 
select 
  visited_on, 
  amount, 
  round(amount / 7, 2) average_amount 
from 
  add_prev_amount 
where 
  row_num > 6;

案例：RANK() 的使用场景

https://leetcode.cn/problems/delete-duplicate-emails/?envType=study-plan-v2&envId=sql-free-50

SQL

delete from 
  Person 
where 
  id in (
    select 
      id 
    from 
      (
        select 
          *, 
          rank() over (
            partition by email 
            order by 
              id
          ) rnk 
        from 
          Person
      ) sub 
    where 
      rnk > 1
  );

案例：DENSE_RANK() 的使用场景

https://leetcode.cn/problems/department-top-three-salaries/?envType=study-plan-v2&envId=sql-free-50

SQL

select 
  dep.name Department, 
  sub.name Employee, 
  sub.salary Salary 
from 
  (
    select 
      name, 
      salary, 
      departmentId, 
      dense_rank() over (
        partition by departmentId 
        order by 
          salary desc
      ) rnk 
    from 
      Employee
  ) sub 
  left join Department dep on sub.departmentId = dep.id 
where 
  sub.rnk <= 3;

聚合函数与窗口函数

场景A

显然窗口函数也可以使用聚合函数。此时，聚合函数获得了新的特性：能够在每一行的上下文中进行计算，而不是像普通聚合函数那样，对整个查询结果或分组进行单一的汇总计算（实际上这里是ORDER BY后面省略了默认表达式ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）。

例如，我们有一张销售数据表sales，包含两个字段：date（日期）和amount（销售额）。现在，我们想计算到每一天为止的累计销售额。这里，我们就可以使用聚合函数SUM作为窗口函数来实现。

SQL

SELECT 
  date, 
  SUM(amount) OVER (
    ORDER BY 
      date ASC
  ) as cumulative_sales 
FROM 
  sales;

这样，对于表中的每一行，cumulative_sales列都会显示到当前行为止的累计销售额，而每行的数据还保留了原来的独立性。

场景B

当然，在窗口函数中使用聚合函数时也未必一定需要实现上述特性，因为聚合函数在窗口函数中也可以实现这样的功能：达成GROUP BY中聚合函数的效果，且保证行数不变（注意这里的OVER()不能包含ORDER BY，否则又变成了场景A）。

场景C

由于窗口函数的运行顺序在GROUP BY之后，因此可以嵌套基于GROUP BY聚合的结果。比如现在有一张简单的表sales：

id	region	sales
1	East	100
2	West	200
3	East	150
4	West	300

需求是计算每个区域的总销售额，并且将总销售额最高区域的相应值添加到一个新列中，便于后续处理，则方法可以是：

SQL

SELECT 
    region, 
    SUM(sales) AS total_sales,
    MAX(SUM(sales)) OVER() AS max_sales  
FROM 
    sales  
GROUP BY 
    region;

结果为：

region	total_sales	max_sales
East	250	500
West	500	500

当然，也有需要将专业窗口函数和GROUP BY连用的情况，使用形式如RANK() OVER(ORDER BY COUNT(*)) column_alias FROM table GROUP BY column等，和聚合函数的写法稍有差异。

案例：SUM() 在窗口函数中的使用场景（对应场景A）

https://leetcode.cn/problems/last-person-to-fit-in-the-bus/description/?envType=study-plan-v2&envId=sql-free-50

SQL

select 
  person_name 
from 
  (
    select 
      *, 
      sum(weight) over (
        order by 
          turn
      ) as weightSum 
    from 
      Queue
  ) sub 
where 
  weightSum <= 1000 
order by 
  weightSum desc 
limit 
  1;

案例：COUNT()在窗口函数中的使用场景（对应场景B）

https://leetcode.cn/problems/primary-department-for-each-employee/?envType=study-plan-v2&envId=sql-free-50

SQL

select 
  employee_id, 
  department_id 
from 
  (
    select 
      *, 
      count(*) over(partition by employee_id) as cnt 
    from 
      Employee
  ) sub 
where 
  cnt = 1 
  or primary_flag = 'Y';

案例：窗口函数嵌套GROUP BY聚合的使用场景（对应场景C）

https://www.nowcoder.com/practice/6765b4a4f260455bae513a60b6eed0af

SQL

with
    preprocess as (
        select
            uid,
            date (in_time) in_time,
            date (out_time) out_time
        from
            tb_user_log
    ),
    get_date as (
        select
            uid,
            min(in_time) first_log,
            max(out_time) last_log,
            max(max(out_time)) over () curr
        from
            preprocess
        group by
            uid
    ),
    add_label as (
        select
            uid,
            case
                when datediff (curr, last_log) < 7
                and datediff (curr, first_log) > 6 then '忠实用户'
                when datediff (curr, first_log) < 7 then '新晋用户'
                when datediff (curr, last_log) between 7 and 29 then '沉睡用户'
                else '流失用户'
            end user_grade
        from
            get_date
    )
select
    user_grade,
    round(count(*) / sum(count(*)) over (), 2) ratio
from
    add_label
group by
    user_grade
order by
    ratio desc;

ROWS BETWEEN

在ORDER BY后面使用ROWS BETWEEN (AND)后缀可以控制窗口函数的范围（注意必须基于ORDER BY使用）。ROWS BETWEEN附加的关键词包括UNBOUNDED（不受控的）、PRECEDING（在……之前）和FOLLOWING（在……之后）。综合示例见https://blog.csdn.net/qq_42374697/article/details/115109386。

比如，在一个video_like表中，现在希望针对video_type分组，统计date_time窗口范围为近一周的like_cnt合计情况，则可以写成（这里用了ROWS BETWEEN一端为当前行时的简写）：

SQL

select 
  video_type, 
  date_time, 
  sum(like_cnt) over(
    partition by video_type 
    order by 
      date_time rows 6 preceding
  ) sum_like_cnt_7d 
from 
  video_like 
order by 
  video_type, 
  date_time;

案例：ROWS BETWEEN 的使用场景

见上述“案例：ROW_NUMBER() 的使用场景”。

LAG() · LEAD()

使用LAG()或者LEAD()可以在对应列向上或向下查找值。其常用语法为：

LAG(expr, offset, default_value) OVER ()

LEAD(expr, offset, default_value) OVER ()

第一个参数为列名或内置函数；第二个参数为向上/向下查找的行数，默认为1；第三个参数为无法取得时的赋值，默认为空值。

窗口函数的执行位置

窗口函数的执行处于结果集生成之后，结果集排序之前。

在MySQL里，子句具体的执行顺序是这样的：

FROM：选择并组合数据表。
WHERE：过滤来自数据表的行。
GROUP BY：把行分到不同的组。
HAVING：过滤分组后的结果。
SELECT：选择所需的列。
WINDOW FUNCTIONS：在结果集上计算窗口函数。
ORDER BY：排序结果集。
LIMIT：限制返回的行数。

因此，HAVING也是无法筛选窗口函数计算结果的。