Edited at

#PostgreSQL Window関数で行番号や順位を抽出

More than 3 years have passed since last update.


行番号を抽出


行番号 ... ROW_NUMBER()


  • 抽出順に1から始まる行番号を付ける。


sql

SELECT ROW_NUMBER() OVER (), *

FROM tables;


  • レコードの作成日時createdが古いものから順に番号をつける。


sql

SELECT ROW_NUMBER() OVER (ORDER BY created ASC), *

FROM tables;


各ブログのコメントの内もっとも古いもの1件を抽出などのような場合に用いた。


  • PARTITION BYを用いて同じblog_idの中で何番目とする。


sql

SELECT a.*

FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY blog_id ORDER BY created ASC) AS no, *
FROM comments
) AS a
WHERE a.no = 1


順位を抽出


同率で番号を飛ばす順位 ... RANK()


  • ポイントの高いものから順に順位をつける。

  • 同ポイントは順位が同じ、同率で番号を飛ばす。

  • ページ切り替えを考慮する場合、ORDER BYにユーザ番号なども加え抽出順が変わらないようにする。OVER()内に加えると条件が変わり順位が変わる。


sql

SELECT RANK() OVER (ORDER BY point DESC), *

FROM points
ORDER BY point DESC, user_id ASC


結果

rank  user_id  point

1 9004 337
2 222 312
3 4 295
3 56 295
5 10120 286



比率 ... PERCENT_RANK()


  • ポイントの高いものから順に比率を算出し1位=100%、最下位=0%とする。

  • ページ切り替えを考慮する場合、ORDER BYにユーザ番号なども加え抽出順が変わらないようにする。OVER()内に加えると条件が変わり比率が変わる。


sql

SELECT (1 - PERCENT_RANK() OVER (ORDER BY point DESC)) * 100, *

FROM points
ORDER BY point DESC, user_id ASC


結果

?column?  user_id  point

100 102 149
99.9957131221331 8 146
99.991426244266 107 49



参考