mysql8 窗口函数学习

SUM (column) OVER (PARTITION BY column) as alias : 全量行数聚合sum

SELECT 
		`subject`, 
    SUM(score)
FROM
    stu_score
GROUP BY 
    `subject`; 
		
			
SELECT 
    `subject`, 
    score,
    SUM(score) OVER (PARTITION BY `subject`) total_score
FROM
    stu_score
ORDER BY `subject` desc; 

数学 15
语文 12

---

语文 4 12
语文 5 12
语文 3 12
数学 5 15
数学 5 15
数学 5 15

LAG ( column, number ) / LEAD ( column, number )

分组后获取当前行的前/后n行

SELECT
    `subject`, 
    score, 
    LAG(score, 1) OVER (
        PARTITION BY `subject`
        ORDER BY score
    ) aaa
FROM 
    stu_score; 

或

SELECT
    `subject`, 
    score, 
    LEAD(score, 1) OVER (
        PARTITION BY `subject`
        ORDER BY score
    ) aaa
FROM 
    stu_score; 

SELECT
    `subject`, 
    score, 
    NTH_VALUE(score, 2) OVER (
        PARTITION BY `subject`
        ORDER BY score
    ) aaa
FROM 
    stu_score; 

NTILE(number) 按组数分行

SELECT
    `subject`, 
    score, 
    NTILE(2) OVER (
        ORDER BY score
    ) group_id
FROM 
    stu_score; 

RANK(column) : 按指定字段排名

SELECT
    score,
    RANK() OVER (
        ORDER BY score
    ) rank_score
FROM
    stu_score; 

PERCENT_RANK ( column) :按指定字段排名, 获取排名占比

SELECT
    score,
    PERCENT_RANK() OVER (
        ORDER BY score
    ) rank_score
FROM
    stu_score; 

CUME_DIST() OVER ( order by column ) as alias

SELECT
		`subject`,
    score,
		ROW_NUMBER() OVER (ORDER BY `subject` asc ,score desc) row_num,
    CUME_DIST() OVER (
        ORDER BY `subject` asc ,score desc
    ) cume_score
FROM
    stu_score; 

subject ,score, row_num, cume_score
数学 5 1 0.5
数学 5 2 0.5
数学 5 3 0.5 3 / 6
语文 5 4 0.6666666666666666 4 / 6
语文 4 5 0.8333333333333334 5 / 6
语文 3 6 1 6 / 6