前言

partition by與group by都是對表中的某維度進(jìn)行分組。不同的是partition by返回的是分組后的每一條記錄，不改變表中數(shù)據(jù)行數(shù)，后續(xù)可以做排序、topN等操作；而 group by返回的是分組的聚合值，例如max、sum、avg等值。`

一、窗口函數(shù)

1.基本語法：

<窗口函數(shù)> over ( partition by<用于分組的列名> order by <用于排序的列名> desc) as "rank_col"

執(zhí)行順序為：
1、根據(jù) <用于分組的列名> 進(jìn)行分組操作（partition by），得到分組結(jié)果（中間表）；
2、對結(jié)果的每個分組進(jìn)行組內(nèi)（desc降序）排序：order by <用于排序的列名>（中間表）；
3、將窗口函數(shù)用于上述結(jié)果的每個分組（over）：增加組內(nèi)排序序號列"rank_col"。窗口函數(shù)包括rank()，dense_rank()，row_number()等。

以上過程生成了一個分組、組內(nèi)排序、增加組內(nèi)排序序號列的結(jié)果。

rank()函數(shù)：如果有并列名次的行，會占用下一個名次的位置。比如正常排名是1，2，3，4，但是現(xiàn)在前3名是并列的名次，所以結(jié)果是：1，1，1，4.
dense_rank()函數(shù)：如果有并列的名次，它不會占用下一個名次的位置，比如比如正常排名是1，2，3，4，但是現(xiàn)在前3名是并列的名次，所以結(jié)果是：1，1，1，2.
row_number()函數(shù)：不考慮并列的情況，比如前3名是并列的名次，排名是正常的1，2，3，4.

2.示例

[LC185]. 部門工資前三高的所有員工

公司的主管們感興趣的是公司每個部門中誰賺的錢最多。一個部門的 高收入者 是指一個員工的工資在該部門的 不同 工資中 排名前三 。編寫解決方案，找出每個部門中 收入高的員工

輸出格式要求如下：

分析：
題目要求是找出 每個部門中 排名前三的員工（partition by 部門），且相同收入水平并列、不占用后續(xù)排序位置（dense_rank()）。
寫sql前，最好把過程先想清楚，把每個中間子表想清楚，把重要的中間子表可以查出來看看，最后再完善代碼，且不要上來就搞代碼。思路如下：

1、先把最核心的計算寫出來

分組以及組內(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

到這里，核心的計算算是完成了，實現(xiàn)了 每個部門中排名前三，且相同收入水平并列、不占用后續(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

輸出正確，測試通過。

4、sql優(yōu)化

分組以及組內(nèi)排序后，直接join，節(jié)省一個中間子表

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

輸出正確，測試通過。

到此這篇關(guān)于SQL窗口函數(shù)之partition by的使用的文章就介紹到這了,更多相關(guān)SQL partition by內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論