mysql窗口函数

窗口函数

SUM() 统计

SELECT 
	*, 
	SUM(duration) OVER() AS exp1, 
	## 统计全部数据合计值
	SUM(duration) OVER(PARTITION BY user_name) AS exp2, 
	## 分组求和，不同数据相同分组会展示相同的累积值
	SUM(duration) OVER(ORDER BY `date` ASC) AS exp3, 
	## 全部数据累积和，根据排序统计当前的数据+前面的数据和
	SUM(duration) OVER(PARTITION BY user_name ORDER BY `date` ASC) AS exp4
	## 分组数据累积和，相同分组排序不同数据会统计当前数据+前面数据和
FROM 
	video_play;

count() 计数

SELECT 
	*, 
	COUNT(duration) OVER() AS exp1,
	## 总数
	COUNT(duration) OVER(PARTITION BY video_type) AS exp2,
	## 分组计数，不同组数量
	COUNT(duration) OVER(ORDER BY duration) AS exp3, 
	## 全部数据累积数量
	COUNT(duration) OVER(PARTITION BY video_type ORDER BY duration ASC) AS exp4
    ## 排序后分组累积数量
FROM 
	video_play;

AVG() 平均数

SELECT 
	*, 
	AVG(duration) OVER() AS exp1, 
	AVG(duration) OVER(PARTITION BY user_name) AS exp2, 
	AVG(duration) OVER(ORDER BY `date` ASC) AS exp3, 
	AVG(duration) OVER(PARTITION BY user_name ORDER BY `date` ASC) AS exp4 
FROM 
	video_play;

ROW_NUMBER() 行号，唯一值

SELECT 
	*, 
	ROW_NUMBER() OVER() AS exp1, 
	## id号
	ROW_NUMBER() OVER(PARTITION BY user_name) AS exp2,
	ROW_NUMBER() OVER(ORDER BY `date` ASC) AS exp3, 
	ROW_NUMBER() OVER(PARTITION BY user_name ORDER BY `date` ASC) AS exp4 
	## 分组后排序展示行号，不存在相同的行号
FROM 
	video_play;
	
## 举例 每个用户根据duration升序，相同的根据date倒叙，找到前两名
WITH user_video_play_index AS (
	SELECT
		user_name,
		video_type,
		duration,
		ROW_NUMBER() OVER(PARTITION BY user_name ORDER BY duration DESC, date ASC) AS row_num
	FROM 
		video_play
)

SELECT 
	user_name,
	video_type,
	duration,
	row_num
FROM
	user_video_play_index
WHERE
	row_num <= 2

RANK() 和DENSE_RANK()存在并列排名

SELECT 
	*, 
	RANK() OVER() AS exp1, 
	RANK() OVER(PARTITION BY user_name) AS exp2, 
	RANK() OVER(ORDER BY duration ASC) AS exp3, 
	RANK() OVER(PARTITION BY user_name ORDER BY duration ASC) AS exp4,
	## RANK()格式为1224，会跳过排名
	DENSE_RANK() OVER() AS exp5, 
	DENSE_RANK() OVER(PARTITION BY user_name) AS exp6, 
	DENSE_RANK() OVER(ORDER BY duration ASC) AS exp7, 
	DENSE_RANK() OVER(PARTITION BY user_name ORDER BY duration ASC) AS exp8
	## DENSE_RANK格式为1223
FROM 
	video_play

FIRST_VALUE()和LAST_VALUE() 排序后的第一位和最后一位

SELECT 
	*, 
	FIRST_VALUE(duration) OVER() AS exp1, 
	FIRST_VALUE(duration) OVER(PARTITION BY user_name) AS exp2, 
	FIRST_VALUE(duration) OVER(ORDER BY duration ASC) AS exp3, 
	FIRST_VALUE(duration) OVER(PARTITION BY user_name ORDER BY duration ASC) AS exp4,
	## first_value取得始终都是首位值
	LAST_VALUE(duration) OVER() AS exp5,
	LAST_VALUE(duration) OVER(PARTITION BY user_name) AS exp6, 
	LAST_VALUE(duration) OVER(ORDER BY duration ASC) AS exp7, 
	LAST_VALUE(duration) OVER(PARTITION BY user_name ORDER BY duration ASC) AS exp8 
	## LAST_VALUE会随着窗口大小的增加变化，基本上是当前行的值
FROM 
	video_play

LAG()和LEAD() 前后推行取值

SELECT 
	*, 
	LAG(duration, 2, 0) OVER() AS exp1, 
	LAG(duration, 2, 0) OVER(PARTITION BY user_name) AS exp2, 
	LAG(duration, 2, 0) OVER(ORDER BY duration ASC) AS exp3, 
	LAG(duration, 2, 0) OVER(PARTITION BY user_name ORDER BY duration ASC) AS exp4,
	## 取前两行数据，前两行不存在值取默认值0，可以用于获取昨日数据
	LEAD(duration, 2, 0) OVER() AS exp5,
	LEAD(duration, 2, 0) OVER(PARTITION BY user_name) AS exp6, 
	LEAD(duration, 2, 0) OVER(ORDER BY duration ASC) AS exp7, 
	LEAD(duration, 2, 0) OVER(PARTITION BY user_name ORDER BY duration ASC) AS exp8 
FROM 
	video_play

NTILE() 数据均分

SELECT 
	*, 
	NTILE(2) OVER() AS exp1, 
	NTILE(2) OVER(PARTITION BY user_name) AS exp2, 
	NTILE(2) OVER(ORDER BY duration ASC) AS exp3, 
	NTILE(2) OVER(PARTITION BY user_name ORDER BY duration ASC) AS exp4
	## 将分组排序后的数据进行均分，两个为一组，112233排
FROM 
	video_play

others 定义窗口框架

ROWS 指定窗口函数范围

SELECT
    id,
    value,
    SUM(value) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_values
    ## 计算范围控制在当前行的前1后1，排序后的行数
FROM
    your_table;

RANGE 按照日期指定范围（最常使用）

SELECT
    date_column,
    value,
    SUM(value) OVER (ORDER BY date_column RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND INTERVAL 3 DAY FOLLOWING) AS sum_values
    ## 根据date_column的前七后三天范围聚合
    ## DAY修改为method以月维度取范围
FROM
    your_table;

GROUPS

SELECT
    id,
    value,
    SUM(value) OVER (PARTITION BY category ORDER BY id GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_values
    ## 分组后的前1后1行  mysql中无
FROM
    your_table;