SQL窗口函數(shù)之partition by的使用
前言
partition by與group by都是對(duì)表中的某維度進(jìn)行分組。不同的是partition by返回的是分組后的每一條記錄,不改變表中數(shù)據(jù)行數(shù),后續(xù)可以做排序、topN等操作;而 group by返回的是分組的聚合值,例如max、sum、avg等值。`
一、窗口函數(shù)
1.基本語(yǔ)法:
<窗口函數(shù)> over ( partition by<用于分組的列名> order by <用于排序的列名> desc) as "rank_col"
執(zhí)行順序?yàn)椋?br />1、根據(jù) <用于分組的列名> 進(jìn)行分組操作(partition by),得到分組結(jié)果(中間表);
2、對(duì)結(jié)果的每個(gè)分組進(jìn)行組內(nèi)(desc降序)排序:order by <用于排序的列名>(中間表);
3、將窗口函數(shù)用于上述結(jié)果的每個(gè)分組(over):增加組內(nèi)排序序號(hào)列"rank_col"。窗口函數(shù)包括rank(),dense_rank(),row_number()等。
以上過(guò)程生成了一個(gè)分組、組內(nèi)排序、增加組內(nèi)排序序號(hào)列的結(jié)果。
rank()函數(shù):如果有并列名次的行,會(huì)占用下一個(gè)名次的位置。比如正常排名是1,2,3,4,但是現(xiàn)在前3名是并列的名次,所以結(jié)果是:1,1,1,4.
dense_rank()函數(shù):如果有并列的名次,它不會(huì)占用下一個(gè)名次的位置,比如比如正常排名是1,2,3,4,但是現(xiàn)在前3名是并列的名次,所以結(jié)果是:1,1,1,2.
row_number()函數(shù):不考慮并列的情況,比如前3名是并列的名次,排名是正常的1,2,3,4.
2.示例
[LC185]. 部門工資前三高的所有員工
公司的主管們感興趣的是公司每個(gè)部門中誰(shuí)賺的錢最多。一個(gè)部門的 高收入者 是指一個(gè)員工的工資在該部門的 不同 工資中 排名前三 。編寫解決方案,找出每個(gè)部門中 收入高的員工
輸出格式要求如下:
分析:
題目要求是找出 每個(gè)部門中 排名前三的員工(partition by 部門),且相同收入水平并列、不占用后續(xù)排序位置(dense_rank())。
寫sql前,最好把過(guò)程先想清楚,把每個(gè)中間子表想清楚,把重要的中間子表可以查出來(lái)看看,最后再完善代碼,且不要上來(lái)就搞代碼。思路如下:
1、先把最核心的計(jì)算寫出來(lái)
分組以及組內(nèi)排序:
select *, dense_rank() over(partition by departmentId order by salary desc) as rank_col from Employee
按分組排序輸出了,且增加了排序列rank_col,但是沒有限制前三。
2、從上面的結(jié)果中,取每組的前三
把上面的結(jié)果當(dāng)作子表查詢
select * from( select *, dense_rank() over(partition by departmentId order by salary desc) as rank_col from Employee ) a where a.rank_col <=3
到這里,核心的計(jì)算算是完成了,實(shí)現(xiàn)了 每個(gè)部門中排名前三,且相同收入水平并列、不占用后續(xù)排序位置的要求。下一步,要按照規(guī)定格式輸出。
3、按要求格式輸出
繼續(xù)把上面的結(jié)果當(dāng)作子表查詢
select d.name Department, b.name Employee,b.salary Salary from (select * from( select *, dense_rank() over(partition by departmentId order by salary desc) as rank_col from Employee ) a where a.rank_col <=3) b left join Department d on b.departmentId = d.id
輸出正確,測(cè)試通過(guò)。
4、sql優(yōu)化
分組以及組內(nèi)排序后,直接join,節(jié)省一個(gè)中間子表
select d.name Department, a.name Employee,a.salary Salary from (select *, dense_rank() over(partition by departmentId order by salary desc) as "rank" from Employee ) a left join Department d on a.departmentId = d.id where a.rank <4
輸出正確,測(cè)試通過(guò)。
到此這篇關(guān)于SQL窗口函數(shù)之partition by的使用的文章就介紹到這了,更多相關(guān)SQL partition by內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
- MySQL窗口函數(shù) over(partition by)的用法
- MySQL8.0實(shí)現(xiàn)窗口函數(shù)計(jì)算同比環(huán)比
- SQL中去除重復(fù)數(shù)據(jù)的幾種方法匯總(窗口函數(shù)對(duì)數(shù)據(jù)去重)
- MySQL窗口函數(shù)OVER()用法及說(shuō)明
- SQL實(shí)現(xiàn)篩選出連續(xù)3天登錄用戶與窗口函數(shù)的示例代碼
- SQL窗口函數(shù)之聚合窗口函數(shù)的使用(count,max,min,sum)
- MySQL8.0窗口函數(shù)入門實(shí)踐及總結(jié)
- SQL中的窗口函數(shù)簡(jiǎn)介
相關(guān)文章
數(shù)據(jù)庫(kù)性能優(yōu)化三:程序操作優(yōu)化提升性能
程序訪問(wèn)優(yōu)化也可以認(rèn)為是訪問(wèn)SQL語(yǔ)句的優(yōu)化,一個(gè)好的SQL語(yǔ)句是可以減少非常多的程序性能的,下面列出常用錯(cuò)誤習(xí)慣,并且提出相應(yīng)的解決方案2013-01-01sqlserver 多表查詢不同數(shù)據(jù)庫(kù)服務(wù)器上的表
sqlserver中多表查詢不同數(shù)據(jù)庫(kù)服務(wù)器上的表的實(shí)現(xiàn)方法,需要的朋友可以參考下2012-04-04使用NotePad++錄制宏功能如何快速將sql搜索條件加上前后單引號(hào)
這篇文章給大家介紹使用NotePad++錄制宏功能如何快速將sql搜索條件加上前后單引號(hào),對(duì)notepad 引號(hào)問(wèn)題感興趣的朋友可以參考下本篇文章2015-10-10查看本地sql server數(shù)據(jù)庫(kù)的ip地址的方法指南
程序連線SQL數(shù)據(jù)庫(kù),需要SQL Server實(shí)例的名稱或網(wǎng)絡(luò)地址,故本文給大家介紹了查看本地sql server數(shù)據(jù)庫(kù)的ip地址的方法指南,文中通過(guò)圖文介紹的非常詳細(xì),需要的朋友可以參考下2024-06-06解析SQL Server聚焦移除(Bookmark Lookup、RID Lookup、Key Lookup)
本文主要講解索引性能優(yōu)化,著重對(duì)Bookmark Lookup、RID Lookup、Key Lookup三者進(jìn)行移除的實(shí)現(xiàn)進(jìn)行解析,以此來(lái)提高查詢性能。希望對(duì)大家有所幫助2016-12-12SQLServer錯(cuò)誤:15023(創(chuàng)建對(duì)于用戶失?。數(shù)據(jù)庫(kù)恢復(fù)到其他服務(wù)器
最近在遷移SQLServer數(shù)據(jù)庫(kù)2019時(shí),新建用戶成功后,做用戶映射失敗, SQLServer 報(bào)錯(cuò):用戶、組或角色'XXX' 在當(dāng)前數(shù)據(jù)庫(kù)中已存在. 錯(cuò)誤:15023.下面講一下解決的方法2025-03-03Spark臨時(shí)表tempView的注冊(cè)/使用/注銷/注意事項(xiàng)(推薦)
transformation是根據(jù)原有RDD創(chuàng)建一個(gè)新的RDD,而action則把RDD操作后的結(jié)果返回給driver,這篇文章主要介紹了Spark臨時(shí)表tempView的注冊(cè)/使用/注銷/注意事項(xiàng)的相關(guān)資料,需要的朋友可以參考下2022-10-10在 SQLSERVER 中快速有條件刪除海量數(shù)據(jù)
最近有個(gè)朋友問(wèn)我,他說(shuō)他在SQLSERVER刪除幾百萬(wàn)到幾千萬(wàn)數(shù)據(jù)是顯的很慢,幫他分析了一下,提了一些以下意見,或許對(duì)很多人有用。2008-10-10