MySQL窗口函數(shù)用法總結(jié)

1.聚合函數(shù)

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

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

COUNT( 列 ) OVER( ... ) ：求總數(shù)

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

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

2.排名函數(shù)

RANK() OVER(...) ：如果排名相同，編號(hào)跳過(guò)，比如 1、2、2、4

DENSE_RANK() OVER(...) ：編號(hào)不跳過(guò)，比如 1、2、2、3

ROW_NUMBER() OVER(...) ：如果排名相同，編號(hào)不會(huì)重復(fù)，比如 1、2、3、4

NTILE( n ) OVER(...) ：均勻地劃分n個(gè)組，返回對(duì)應(yīng)組號(hào)。

3.跨行取值函數(shù)

LAG( 列, 偏移, 默認(rèn)值 ) OVER(...) ：上 n 行，可以只寫(xiě) LAG(列)，默認(rèn)取上一行

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

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

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

4.分布函數(shù)

PERCENT_RANK() OVER(ORDER BY 列) ：計(jì)算 小于 當(dāng)前值的行數(shù)占 總行數(shù) - 1 的比例。

CUME_DIST() OVER(ORDER BY 列) ：計(jì)算 小于等于 當(dāng)前值的行數(shù)占總行數(shù)的比例。

5.排序分組關(guān)鍵字

OVER( PARTITION BY 列 ) ：分組

OVER( ORDER BY  列 ) ：排序，注意：如果配合聚合函數(shù)使用會(huì)產(chǎn)生截?cái)嘈Ч瑢?dǎo)致只能聚合到當(dāng)前行及之前的數(shù)據(jù)。可結(jié)合移動(dòng)關(guān)鍵字解除。

6.窗口移動(dòng)

移動(dòng)關(guān)鍵字：

ROWS ：以 行號(hào) 為單位移動(dòng)窗口。

RANGE ：以 字段值 為單位移動(dòng)窗口，必須結(jié)合排序關(guān)鍵字，以 ORDER BY 的列值為移動(dòng)基準(zhǔn)。

范圍關(guān)鍵字：

UNBOUNDED PRECEDING ：首行

n PRECEDING ：前 n 行

CURRENT ROW ：當(dāng)前行

n FOLLOWING ：后 n 行

UNBOUNDED FOLLOWING ：末行

演示：

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

# 當(dāng)前天 到 下一天
# RANGE 以值為單位
# 如果當(dāng)前日期是 2024-5-1，那么所有等于 2024-5-1 和 2024-5-2 的數(shù)據(jù)都會(huì)被聚合。
# RANGE 只會(huì)統(tǒng)計(jì)當(dāng)前天 和 +1 天的記錄，如果沒(méi)有 2024-5-2 ，不會(huì)接著去找 2024-5-3。
COUNT( mydate )OVER( ORDER BY mydate RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING )

# 上一天 到 當(dāng)前天
COUNT( myinter )OVER( ORDER BY mydate RANGE ROWS 1 PRECEDING )