MySQL窗口函数

东湾
483 阅读4分钟

1. 窗口函数适用场景：

对分组统计结果中的每一条记录进行计算的场景下, 使用窗口函数更好, 注意, 是每一条；因为MySQL的普通聚合函数的结果(如 group by)是每一组只有一条记录。通常用于产品的累计销量统计、分类排名、同比/环比分析等。

2. 格式：

<窗口函数> OVER (PARTITION BY <用于分组的列名>
                ORDER BY <用于排序的列名>
                frame clause)
  • PARTITION BY选项用于定义分区，其作用类似于查询语句中的 GROUP BY 子句。如果我们指定了分区选项,窗口函数将会分别针对每个分区单独进行分析
  • ORDER BY 选项用于指定分区内数据的排序方式，作用类似于查询语句中的ORDER BY子句
  • frame clause 选项用于指定一个移动的分析窗口，窗口总是位于分区的范围之内，是分区的一个子集。在指定了分析窗口之后，窗口函数不再基于分区进行分析，而是基于窗口内的数据进行分析。窗口选项可以用于实现各种复杂的分析功能，例如计算累计到当前日期为止的销量总和,每个月及其前后各 N个月的平均销量等。
    • 指定窗口大小的具体选项如下:
    • ( ROWS | RANGE ) frame start
( ROWS | RANGE ) BETWEEN frame_start AND frame_end

其中，ROWS 表示以数据行为单位计算窗口的偏移量，RANGE 表示以数值(例如 10 天内、五天内)为单位计算窗口的偏移量。
frame start 选项用于定义窗口的起始位置，可以指定以下内容之一:
1.UNBOUNDED PRECEDING，表示窗口从分区的第一行开始。
2.N PRECEDING，表示窗口从当前行之前的第 N 行开始· CURRENT ROW，表示窗口从当前行开始。

frame end 选项用于定义窗口的结束位置，可以指定以下内容之一:
1.CURRENT ROW，表示窗口到当前行结束。
2.M FOLLOWING，表示窗口到当前行之后的第 M 行结束。
3.UNBOUNDED FOLLOWING，表示窗口到分区的最后一行结束。

例如，以下窗口选项:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW//表示分析窗口从当前分区的第一行开始，直到当前行结束。

除使用 ROWS 关键字以数据行为单位指定窗口的偏移量外，我们也可以使用 RANGE 关键字以数值为单位指定窗口的偏移量。
例如，以下语句用于查找短期之内 (5天)累计转账超过100万元的账号:
SUM(amount) OVER (
    PARTITION BY from_user
    ORDER BY log_ts
    RANGE INTERVAL '5' DAY PRECEDING
    )AS total amount
range取特定日期区间说明
range interval 7-1 day preceding最近7天的值
range between interval 1 day preceding and interval 1 day following前后一天和当天的值

3. <窗口函数>可以放以下三种函数：

1） 排名窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
2） 取值窗口函数。
3） 聚合函数，如sum, avg, count, max, min等

3.1 排名窗口函数

ROW_NUMBER函数 可以为分区中的每行数据分配一个序列号，序列号从1开始。
RANK 函数返回当前行在分区中的名次。如果存在名次相同的数据，后续的排名将会产生跳跃。
DENSE_RANK函数 返回当前行在分区中的名次。即使存在名次相同的数据，后续的排名也是连续值，不会产生跳跃。
PERCENT RANK函数 以百分比的形式返回当前行在分区中的名次。如果存在名次相同的数据，后续的排名将会产生跳跃。
CUME_DIST函数计算当前行在分区内的累积分布。
NTILE(N)函数 将分区内的数据分为 N 等份，并返回当前行所在的分片位置

排名窗口函数不支持动态的窗口大小选项，而是以整个分区作为分析的窗口。

eg1:
    select emp_name，dept_id,salary,
        ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) as 'rn',
        rank() OVER (PARTITION BY dept_id ORDER BY salary DESC) as 'rk',
        dense_rank() OVER (PARTITION BY dept_id ORDER BY salary DESC) as 'dr',
        percent_rank() OVER (PARTITION BY dept_id ORDER BY salary DESC) as 'pr'
    FROM employee;
    
eg1 == eg2：
    select emp_name，dept_id,salary,
        ROW_NUMBER() OVER AA as 'rn',
        rank() OVER AA as 'rk',
        dense_rank() OVER AA as 'dr',
        percent_rank() OVER AA as 'pr'
    FROM employee
    WINDOW AA AS (PARTITION BY dept_id ORDER BY salary DESC);
3.2 取值窗口函数

LAG(列名、偏移量) 函数 可以返回窗口内当前行之前的第N行数据。
LEAD(列名、偏移量)函数 可以返回窗口内当前行之后的第 N行数据。
FIRST_VALUE(列名)函数 可以返回窗口内第一行数据。
LAST_VALUE(列名)函数 可以返回窗口内最后一行数据
NTH_VALUE(列名、偏移量) 函数 可以返回窗口内第N行数据。

LAGO和 LEAD 函数不支持动态的窗口大小，它们以整个分区作为分析的窗口。

例子请看 blog.csdn.net/liangmengbk…
blog.csdn.net/ANobility/a…

avatar
文章
阅读
粉丝