我們來看一個(gè)實(shí)際的問題： 有一張人員薪水表，這張表包含 人員編號(hào)赖捌，人員所在部門原叮，薪水。現(xiàn)在需要選出每個(gè)部門薪水排名前10的員工信息巡蘸》芰ィ總結(jié)一下就是求每個(gè)分組的前N名信息。
有一個(gè)思路是 按照部門進(jìn)行分組悦荒，在組內(nèi)按照薪水倒序唯欣，然后取出每組的前10條。
ROW_NUMBER
postgres 提供一個(gè)計(jì)算行號(hào)的函數(shù) ROW_NUMBER(), 這個(gè)函數(shù)可以計(jì)算出當(dāng)前行在分組中的位置,如果在分組內(nèi)按照某個(gè)字段排序搬味，那么 ROW_NUMBER() 計(jì)算的行號(hào)就代表著這個(gè)規(guī)則中的排名境氢。需要特別注意的是，ROW_NUMBER() 必須使用窗口函數(shù)的語法才能夠正常使用碰纬，也就是這個(gè)函數(shù)后面必須接 OVER() 子句萍聊。
postgres 中內(nèi)建了一些窗口函數(shù)，除了這些函數(shù)外悦析，任何內(nèi)建的或用戶定義的普通聚集函數(shù)（但有序集或假想集聚集除外）都可以作為窗口函數(shù)寿桨。僅當(dāng)調(diào)用跟著OVER子句時(shí)，聚集函數(shù)才會(huì)作為窗口函數(shù)强戴；否則它們作為常規(guī)的聚集亭螟。
內(nèi)建窗口函數(shù)
| 函數(shù) | 返回類型 | 描述 |
|---|---|---|
row_number() |
bigint |
當(dāng)前行在其分區(qū)中的行號(hào)，從1計(jì) |
rank() |
bigint |
帶間隙的當(dāng)前行排名骑歹； 與該行的第一個(gè)同等行的row_number相同 |
dense_rank() |
bigint |
不帶間隙的當(dāng)前行排名预烙； 這個(gè)函數(shù)計(jì)數(shù)同等組 |
percent_rank() |
double precision |
當(dāng)前行的相對(duì)排名： (rank- 1) / (總行數(shù) - 1) |
cume_dist() |
double precision |
當(dāng)前行的相對(duì)排名： (當(dāng)前行前面的行數(shù) 或 與當(dāng)前行同等的行的行數(shù))/(總行數(shù)) |
ntile(*num_buckets* integer) |
integer |
從1到參數(shù)值的整數(shù)范圍，盡可能等分分區(qū) |
lag(*value* anyelement [, *offset* integer [, *default* anyelement ]]) |
和*value*的類型相同 |
返回*value*道媚， 它在分區(qū)內(nèi)當(dāng)前行的之前*offset*個(gè)位置的行上計(jì)算扁掸；如果沒有這樣的行，返回*default*替代最域。 (作為*value*必須是相同類型)谴分。 *offset*和*default*都是根據(jù)當(dāng)前行計(jì)算的結(jié)果。如果忽略它們羡宙，則*offset*默認(rèn)是1狸剃，*default*默認(rèn)是空值 |
lead(*value* anyelement [, *offset* integer [, *default* anyelement ]]) |
和*value*類型相同 |
返回*value*，它在分區(qū)內(nèi)當(dāng)前行的之后*offset*個(gè)位置的行上計(jì)算狗热；如果沒有這樣的行钞馁，返回*default*替代虑省。(作為*value*必須是相同類型)。*offset*和*default*都是根據(jù)當(dāng)前行計(jì)算的結(jié)果僧凰。如果忽略它們探颈，則*offset*默認(rèn)是1，*default*默認(rèn)是空值 |
first_value(*value* any) |
same type as *value* |
返回在窗口幀中第一行上計(jì)算的*value*
|
last_value(*value* any) |
和*value*類型相同 |
返回在窗口幀中最后一行上計(jì)算的*value*
|
nth_value(*value* any, *nth* integer) |
和*value*類型相同 |
返回在窗口幀中第*nth*行（行從1計(jì)數(shù)）上計(jì)算的*value*训措；沒有這樣的行則返回空值 |
OVER()
一個(gè)窗口函數(shù)在一系列與當(dāng)前行有某種關(guān)聯(lián)的表行上執(zhí)行一種計(jì)算伪节。這與一個(gè)聚集函數(shù)所完成的計(jì)算有可比之處。但是與通常的聚集函數(shù)不同的是绩鸣，使用窗口函數(shù)并不會(huì)導(dǎo)致行被分組成為一個(gè)單獨(dú)的輸出行--行保留它們獨(dú)立的標(biāo)識(shí)怀大。在這些現(xiàn)象背后，窗口函數(shù)可以訪問的不僅僅是查詢結(jié)果的當(dāng)前行呀闻。
下面使用窗口函數(shù) ROW_NUMBER() 來獲取每個(gè)部門薪水排名前十的用戶信息
SELECT * FROM
(SELECT depname, empno, salary, ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC) as row_index )
WHERE(row_index <= 10) FROM empsalary
使用 Rails 可以這樣寫
user_group = User.select("id,depname, empno, salary, ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC) as row_index").to_sql
User.select("agg.*").joins("RIGHT JOIN (#{user_group}) as agg ON agg.id = user.id").where("agg.row_index <= 10")
其中 ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC) as row_index 的 PARTITION BY depname 意思是按照 depname 進(jìn)行分區(qū)化借， ORDER BY salary DESC 在當(dāng)前分區(qū)內(nèi)按照薪水倒序， 然后 ROW_NUMBER() 計(jì)算出當(dāng)前行在所在分區(qū)的行號(hào)然后作為 row_index 列顯示捡多。值得注意的是最后的輸出結(jié)果是前十名蓖康，但并不是按照薪水的高低排序的如果想要有序的數(shù)據(jù)還需要在最后使用 ORDER BY salary DESC 進(jìn)行排序
更多有關(guān)窗口函數(shù)的介紹請(qǐng)看手冊(cè) http://www.postgres.cn/docs/9.6/tutorial-window.html http://www.postgres.cn/docs/9.6/functions-window.html
