【MySQL】窗口函数

发布于:2025-04-21 ⋅ 阅读:(109) ⋅ 点赞:(0)

一、前言

二、概述

窗口函数指一些需要借助OVER关键字使用的特殊函数,它们用来实现对查询后数据的实时分组处理。这里的窗口,指的是函数生效的的范围。

窗口函数原则上只能在 SELECT 子句中使用

窗口函数可以分为三种:序号(排名)函数、聚合函数和前后头尾函数。列出部分参考:

序号函数 聚合函数 前后头尾函数
row_number() max() lag()
rank() min() lead()
dense_rank() sum() first_value()
count() last_vague()
avg()

over关键字:用来创建指定规则的窗口,格式为:

SELECT 
    字段1,
    字段2,
    窗口函数() OVER (
        [PARTITION BY 分组字段]
        [ORDER BY 排序字段 [ASC|DESC]]
        [ROWS BETWEEN 起始位置 AND 结束位置]
    ) AS 别名
FROM 表名;

PARTITION BY :分组窗口的方式

ORDER BY:窗口排序规则

rows:函数计算范围,可用于移动平均

PARTITION BYGROUP BY 的区别:

  1. GROUP BY对要查询的数据进行分组,并仅为每个组返回**一行(聚合后)**的数据
  2. PARTITION BY只能在OVER子句中使用,用来对查询结果分组,每组数据视为一个独立的窗口。即使使用聚合函数,也会保留窗口中的每一行数据

三、窗口命名

可以使用WINDOW关键字,将相同的窗口规则命名为rule,格式为WINDOW 名称 AS (规则1 规则2 规则3)

窗口别名需要定义在窗口函数执行之前,即select执行之前。

SELECT category_id,sku_price,
       ROW_NUMBER() OVER rule AS 排名,
       RANK() OVER rule AS 名次
FROM commodity
WINDOW rule AS (PARTITION BY category_id ORDER BY sku_price);

四、窗口函数

4.1 序号函数

对于序号函数,如ROW_NUMBER()、RANK()等,OVER子句中的ORDER BY主要用于在窗口内对数据进行排序

ROW_NUMBER():能够从1开始为窗口内的每一行数据生成一个序号,序号连续且不重复

// 使用商品类别分组数据,每组数据按照价格从低到高排序,并为其添加序号
SELECT category_id,sku_name,sku_price,
       ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sku_price) AS 排名
FROM commodity;

RANK()DENSE_RANK()允许并列排序,区别在于DENSE_RANK()并列排序后序号仍连续,而RANK()序号会出现间断

4.2 聚合函数

当窗口函数为聚合函数,如MAX()、AVG()、COUNT()等时,OVER中的ORDER BY不仅能为窗口内的数据排序;还能限定窗口内函数每次生效的范围——从第一行开始到当前行结束 。

在仅有PARTITION BY 的窗口中使用聚合函数,函数会对每个组的所有数据进行聚合运算,所以同一组下每行计算的聚合结果相同。

在既有PARTITION BY 又有ORDER BY的窗口中使用聚合函数,函数会按照顺序对第一行到当前行的所有数据进行聚合计算。所以同一组下每行计算的聚合结果不同,是累计的关系。

4.3 前后函数

LAG():用于获取前一行的数据。格式为LAG(字段名, 查阅的行数, 默认值)

查阅的行数:默认为1,如果前一行没有数据,LAG会记录为NULL

默认值:默认为NULL

// 获取排在当前员工前2位的员工薪资,若没有符合该员工的数据,就标记结果为'未找到'
SELECT department,name,salary,
       LAG(salary,2,'未找到') OVER (PARTITION BY department ORDER BY salary) AS 上一个人的薪水
FROM employees;

LEAD():与LAG函数效果相反,用来获取下一行的的内容,用法与LAG函数相同

4.4 头尾函数

FIRST_VALUE():获取每个窗口中的第一行数据

// 从订单表中查询出了账户id、支付金额、第一次消费金额
SELECT account_id, total_price,
       FIRST_VALUE(total_price) OVER (PARTITION BY account_id ORDER BY order_time) AS 第一次消费金额
FROM orders
WHERE status = true;

LAST_VALUE():用来获取窗口计算范围内的最后一行数据

  1. 如果窗口没有使用ORDER BY排序,返回的是当前窗口的最后一行数据;
  2. 如果窗口使用ORDER BY排序,返回的是当前行的内容。

错误示例:窗口使用ORDER BY排序,返回的是当前行的内容。

// 找出 最后一个 上巴士且不超过重量限制的乘客
select last_value(person_name) over (order by turn) as person_name
from (
    select sum(weight) over(order by turn) as total_weight,person_name,turn
    from queue
) as p
where total_weight<=1000
| person_name |
| ----------- |
| Alice       |
| Alex        |
| John Cena   |

五、移动平均

5.1 rows

移动平均是一种用于平滑时间序列数据的统计方法,这里的时间序列是指按照时间顺序排列的数据集合。它能够减少极大、极小等异常值对数据造成的误差,以此平滑平均数曲线。
窗口滑动范围:

当前行 之前的行 之后的行 无界限 表示从前面的起点 表示到后面的终点
current row preceding following unbounded unbounded preceding unbounded following

常见的范围定义:

// 从第一行到当前行
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

// 当前行前3行到后一行
ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING

// 当前行到最后一行
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

使用实例:

// 计算近3个月的移动平均
SELECT
    month,
    sales,
    AVG(sales) OVER (
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM monthly_sales;

尝试练习:https://leetcode.cn/problems/restaurant-growth/solutions/1047332/jiang-jie-bing-gai-jin-ping-lun-qu-da-la-34xv/?envType=study-plan-v2&envId=sql-free-50

5.2 range

range是窗口函数中用于定义逻辑范围的子句,根据字段的实际值来确定窗口的边界。
它可以用来处理缺失值,比如计算近7天的累计值。range格式为:

窗口函数() OVER (
    ORDER BY 排序字段
    RANGE BETWEEN 下限 AND 上限
)

常见范围定义:

// 当前时间的前7天到当前时间
RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW

// 当前值 -100 到当前值 +200
RANGE BETWEEN 100 PRECEDING AND 200 FOLLOWING

// 从最小值到当前行值
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

使用示例:

// 计算近7天的累计销售额
SELECT
    date,
    amount,
    SUM(amount) OVER (
        ORDER BY date 
        RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
    ) AS rolling_7d_sum
FROM daily_sales;

range 与 rows:
在这里插入图片描述


网站公告

今日签到

点亮在社区的每一天
去签到