* 連番テーブルと相関サブクエリ [#b2576269]
** 前提 [#x3146192]
以下のように名前と年齢からなるusersテーブルがあるとする。これを年齢順に5位までのランキング算出する。
SELECT * FROM users ;
id | name | age
----+--------+-----
1 | taro | 20
2 | jiro | 15
3 | saburo | 10
4 | hanako | 18
5 | ichiro | 15
6 | ken | 25
** 方法 [#g610f5e1]
SELECT x.rank
, x.arr[1] AS age
, x.arr[2] AS name
FROM (
SELECT num::int + 1 AS rank
, (
SELECT ARRAY[age::text
, name]
FROM users
ORDER BY age DESC LIMIT 1 OFFSET num
) AS arr
FROM generate_series(0,4) num
) x;
rank | age | name
------+-----+--------
1 | 25 | ken
2 | 20 | taro
3 | 18 | hanako
4 | 15 | jiro
5 | 15 | ichiro
- generate_series() で0から4までの連番テーブルを作る。
- usersテーブルをageでORDER BYし、その際、連番の番号を使ってOFFSETを指定する。
- そのままではusersのnameとageを一緒に取り出せないので、ARRAYを使って一旦配列にまとめる。これをインラインテーブルxとし、ここからageとnameを展開する。
