MySQL實戰三 窗口函數實現及查詢案例
- 2019 年 10 月 6 日
- 筆記
MySQL學習倉庫Up-Up-MySQL，這是一個學習MySQL從入門實戰到理論完善，再到精通的一個倉庫，後面會把MySQL的學習資料上傳上去！歡迎大家star與fork起來！
倉庫地址：
https://github.com/Light-City/Up-Up-MySQL
也可以點擊閱讀原文！
今天上手第三彈，窗口函數實現及查詢案例。
窗口分析函數在做數據分析時十分常用，但是mysql5.7卻不支援。
下面一起來實現一下：
1.生成組內連續但不唯一的數字。類似DENSE_RANK()函數。
按各科成績進行排序，並顯示排名， Score 重複時合併名次空缺
set @cid=0; set @rank=0; set @score=0; select @rank:=IF(@cid=sc.CId,IF(@score=sc.score,@rank,@rank+1),1) '排名',@cid:=sc.CId '課程編號' ,sc.SId,@score:=sc.score score from SC sc order by cid,score desc; +--------+--------------+-----+-------+ | 排名 | 課程編號 | SId | score | +--------+--------------+-----+-------+ | 1 | 01 | 01 | 80.0 | | 1 | 01 | 03 | 80.0 | | 2 | 01 | 05 | 76.0 | | 3 | 01 | 02 | 70.0 | | 4 | 01 | 04 | 50.0 | | 5 | 01 | 06 | 31.0 | | 1 | 02 | 01 | 90.0 | | 2 | 02 | 07 | 89.0 | | 3 | 02 | 05 | 87.0 | | 4 | 02 | 03 | 80.0 | | 5 | 02 | 02 | 60.0 | | 6 | 02 | 04 | 30.0 | | 1 | 03 | 01 | 99.0 | | 2 | 03 | 07 | 98.0 | | 3 | 03 | 03 | 80.0 | | 3 | 03 | 02 | 80.0 | | 4 | 03 | 06 | 34.0 | | 5 | 03 | 04 | 20.0 | +--------+--------------+-----+-------+ 18 rows in set (0.00 sec)
查詢學生的總成績，並進行排名，總分重複時不保留名次空缺
set @total=0; select @total:=IF(@total=s.total,@total,@total+1) '排名',s.SId,s.total from (select sc.SId,sum(sc.score) total from SC sc group by sc.SId) as s order by total desc; +--------+-----+-------+ | 排名 | SId | total | +--------+-----+-------+ | 1 | 01 | 269.0 | | 2 | 03 | 240.0 | | 3 | 02 | 210.0 | | 4 | 07 | 187.0 | | 5 | 05 | 163.0 | | 6 | 04 | 100.0 | | 7 | 06 | 65.0 | +--------+-----+-------+ 7 rows in set (0.00 sec)
2.生成組內連續且唯一的數字。類似ROW_NUMBER()函數
首先初始化變數
set @cid=0; set @rank=0;
使用IF
select @rank:=IF(@cid=sc.CId,@rank+1,1) '排名',@cid:=sc.CId '課程編號' ,sc.SId,sc.score from SC sc order by cid,sc.score desc;
使用case when end
select @rank:=(case @cid when sc.CId THEN @rank+1 else 1 end) '排名',@cid:=sc.CId '課程編號' ,sc.SId,sc.score from SC sc order by cid,sc.score desc;
查詢結果：
+--------+--------------+-----+-------+ | 排名 | 課程編號 | SId | score | +--------+--------------+-----+-------+ | 1 | 01 | 01 | 80.0 | | 2 | 01 | 03 | 80.0 | | 3 | 01 | 05 | 76.0 | | 4 | 01 | 02 | 70.0 | | 5 | 01 | 04 | 50.0 | | 6 | 01 | 06 | 31.0 | | 1 | 02 | 01 | 90.0 | | 2 | 02 | 07 | 89.0 | | 3 | 02 | 05 | 87.0 | | 4 | 02 | 03 | 80.0 | | 5 | 02 | 02 | 60.0 | | 6 | 02 | 04 | 30.0 | | 1 | 03 | 01 | 99.0 | | 2 | 03 | 07 | 98.0 | | 3 | 03 | 03 | 80.0 | | 4 | 03 | 02 | 80.0 | | 5 | 03 | 06 | 34.0 | | 6 | 03 | 04 | 20.0 | +--------+--------------+-----+-------+ 18 rows in set (0.00 sec)
注意：select中排名必須在課程編號前面，否則結果不正確！
3.生成組內既不連續也不唯一的數字。類似RANK()函數。
按各科成績進行排序，並顯示排名， Score 重複時保留名次空缺
select @enum:=IF(@cid=sc.CId,@enum+1,1) enum,@rank:=IF(@cid=sc.CId,IF(@score=sc.score,@rank,@enum),1) '排名',@cid:=sc.CId '課程編號' ,sc.SId,@score:=sc.score score from SC sc order by cid,score desc;
查詢結果：
mysql> select @enum:=IF(@cid=sc.CId,@enum+1,1) enum,@rank:=IF(@cid=sc.CId,IF(@score=sc.score,@rank,@enum),1) '排名',@cid:=sc.CId '課程編號' ,sc.SId,@score:=sc.score score from SC sc order by cid,score desc; +------+--------+--------------+-----+-------+ | enum | 排名 | 課程編號 | SId | score | +------+--------+--------------+-----+-------+ | 1 | 1 | 01 | 01 | 80.0 | | 2 | 1 | 01 | 03 | 80.0 | | 3 | 3 | 01 | 05 | 76.0 | | 4 | 4 | 01 | 02 | 70.0 | | 5 | 5 | 01 | 04 | 50.0 | | 6 | 6 | 01 | 06 | 31.0 | | 1 | 1 | 02 | 01 | 90.0 | | 2 | 2 | 02 | 07 | 89.0 | | 3 | 3 | 02 | 05 | 87.0 | | 4 | 4 | 02 | 03 | 80.0 | | 5 | 5 | 02 | 02 | 60.0 | | 6 | 6 | 02 | 04 | 30.0 | | 1 | 1 | 03 | 01 | 99.0 | | 2 | 2 | 03 | 07 | 98.0 | | 3 | 3 | 03 | 03 | 80.0 | | 4 | 3 | 03 | 02 | 80.0 | | 5 | 5 | 03 | 06 | 34.0 | | 6 | 6 | 03 | 04 | 20.0 | +------+--------+--------------+-----+-------+ 18 rows in set (0.00 sec)
使用自連接：
mysql> select sc1.CId,sc1.SId,sc1.score,count(sc2.score)+1 rank from SC sc1 left join SC sc2 on sc1.CId=sc2.CId and sc1.score<sc2.score group by sc1.CId,sc1.SId,sc1.score order by sc1.CId,rank asc; +-----+-----+-------+------+ | CId | SId | score | rank | +-----+-----+-------+------+ | 01 | 01 | 80.0 | 1 | | 01 | 03 | 80.0 | 1 | | 01 | 05 | 76.0 | 3 | | 01 | 02 | 70.0 | 4 | | 01 | 04 | 50.0 | 5 | | 01 | 06 | 31.0 | 6 | | 02 | 01 | 90.0 | 1 | | 02 | 07 | 89.0 | 2 | | 02 | 05 | 87.0 | 3 | | 02 | 03 | 80.0 | 4 | | 02 | 02 | 60.0 | 5 | | 02 | 04 | 30.0 | 6 | | 03 | 01 | 99.0 | 1 | | 03 | 07 | 98.0 | 2 | | 03 | 02 | 80.0 | 3 | | 03 | 03 | 80.0 | 3 | | 03 | 06 | 34.0 | 5 | | 03 | 04 | 20.0 | 6 | +-----+-----+-------+------+ 18 rows in set (0.00 sec)
不按各科成績進行排序，並顯示排名， Score 重複時保留名次空缺
select sc1.CId,sc1.SId,sc1.score,(select count(1) from SC sc2 where sc2.score>sc1.score)+1 as rank from SC sc1 order by rank; +-----+-----+-------+------+ | CId | SId | score | rank | +-----+-----+-------+------+ | 03 | 01 | 99.0 | 1 | | 03 | 07 | 98.0 | 2 | | 02 | 01 | 90.0 | 3 | | 02 | 07 | 89.0 | 4 | | 02 | 05 | 87.0 | 5 | | 01 | 01 | 80.0 | 6 | | 02 | 03 | 80.0 | 6 | | 03 | 03 | 80.0 | 6 | | 03 | 02 | 80.0 | 6 | | 01 | 03 | 80.0 | 6 | | 01 | 05 | 76.0 | 11 | | 01 | 02 | 70.0 | 12 | | 02 | 02 | 60.0 | 13 | | 01 | 04 | 50.0 | 14 | | 03 | 06 | 34.0 | 15 | | 01 | 06 | 31.0 | 16 | | 02 | 04 | 30.0 | 17 | | 03 | 04 | 20.0 | 18 | +-----+-----+-------+------+ 18 rows in set (0.00 sec)
