行番号を抽出
行番号 ... 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
：