連番テーブルと相関サブクエリ

前提

以下のように名前と年齢からなる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

方法

 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を展開する。