[SQL小技巧] 分組之後 組內再排序

語言: CN / TW / HK

theme: cyanosis

前言

利用午休時間寫寫關於SQL的一些小技巧,希望正好能被需要的同學看到並使用。

有一次逛StackOverflow時看到一個妹子提出來的問題,如下圖,

Image

沒人解答,看起來也不難,心想是時候輪到我裝X了,答得好得到妹子的青睞後,說不定還能認識一下,以後跟人家學學英語啥的。

於是我迅速得點了一下谷歌瀏覽器上的翻譯外掛,翻譯了一下問題:

大概意思就是說,她想對id相同的值分組,再在每組內額外用一列來標記組內的內容。

比如,圖中兩行id為10000的資料,第一行標記為10000-1,第二列標記為10000-2。

解決

sql SELECT id, concat(id,'_',row_number() over ( PARTITION BY id)) extra FROM table

image-20210713121135334

其中的 row_number() over ( PARTITION BY id)就是本文的重點內容了,就是這麼簡簡單單的一個小語句就能做到先對某個欄位進行分組,然後在組內進行一一標記的作用。

更進一步

PARTITION BY column1,column2,……就是表示依據哪幾列進行分組。與常用的group by的語法一致。

同時,它還能通過order by的語法來實現組內再排序。

image-20210713121619182

如圖所示,pid = 1的組內有七條資料。我想知道其中 id 為196的資料如果按照以id的大小做正序排序,在pid=1的組內它排第幾怎麼算呢?(很明顯答案應該是排第3)。用 row_number() over PARTITION BY column_name ORDER BY column_name實現:

sql SELECT id, pid, row_number() over ( PARTITION BY pid) pid_rank, concat(pid,'_',row_number() over ( PARTITION BY pid)) result FROM lock_test_order

image-20210713122328398

結果正確。

剛剛示例中的row_number()意思就是某個分組內的第幾行的意思。此外我們還可以在 over 前更換其他函式,在分組上使用其他功能。

比如:

sql count(pid) over(partition by pid) total_count

計算組內行資料總數。

說明

本教程是基於MySQL的資料庫,其他資料庫有的不支援該語法。

其他參考連結:https://www.sqlshack.com/sql-partition-by-clause-overview/