MySQL窗口函数用法总结

1.聚合函数

SUM( 列 ) OVER( ... ) ：求总和

AVG( 列 ) OVER( ... ) ：求平均

COUNT( 列 ) OVER( ... ) ：求总数

MIN( 列 ) OVER( ... ) ：取最小值

MAX( 列 ) OVER( ... ) ：取最大值

 

2.排名函数

RANK() OVER(...) ：如果排名相同，编号跳过，比如 1、2、2、5

DENSE_RANK() OVER(...) ：编号不跳过，比如 1、2、2、4

ROW_NUMBER() OVER(...) ：如果排名相同，编号不会重复，比如 1、2、3、4

NTILE( n ) OVER(...) ：均匀地划分n个组，返回对应组号。

 

3.跨行取值函数

LAG( 列, 偏移, 默认值 ) OVER(...) ：上 n 行，可以只写 LAG(列)，默认取上一行

LEAD( 列, 偏移, 默认值 ) OVER(...) ：下 n 行，同上

FIRST_VALUE( 列 ) OVER(...) ：首行

LAST_VALUE( 列 ) OVER(...) ：尾行

 

4.分布函数

PERCENT_RANK() OVER(ORDER BY 列) ：计算 小于 当前值的行数占 总行数 - 1 的比例。

CUME_DIST() OVER(ORDER BY 列) ：计算 小于等于 当前值的行数占总行数的比例。

 

5.排序分组关键字

OVER( PARTITION BY 列 ) ：分组

OVER( ORDER BY  列 ) ：排序，注意：如果配合聚合函数使用会产生截断效果，导致只能聚合到当前行及之前的数据。可结合移动关键字解除。

 

6.窗口移动

移动关键字：

ROWS ：以 行号 为单位移动窗口。

RANGE ：以 字段值 为单位移动窗口，必须结合排序关键字，以 ORDER BY 的列值为移动基准。

范围关键字：

UNBOUNDED PRECEDING ：首行

n PRECEDING ：前 n 行

CURRENT ROW ：当前行

n FOLLOWING ：后 n 行

UNBOUNDED FOLLOWING ：末行

演示：

# 上一行 到 当前行
OVER( ROWS 1 PRECEDING )
# 首行 到 当前行
OVER( ROWS UNBOUNDED PRECEDING )
# 当前行 到 下一行
OVER( ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING )
# 上一行 到 末行
OVER( ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING )

# 当前天 到 下一天
# RANGE 以值为单位
# 如果当前日期是 2024-5-1，那么所有等于 2024-5-1 和 2024-5-2 的数据都会被聚合。
# RANGE 只会统计当前天 和 +1 天的记录，如果没有 2024-5-2 ，不会接着去找 2024-5-3。
COUNT( mydate )OVER( ORDER BY mydate RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING )

# 上一天 到 当前天
COUNT( myinter )OVER( ORDER BY mydate RANGE ROWS 1 PRECEDING )