PostgreSQL/SQL/ランキング/連番テーブルと相関サブクエリのバックアップ(No.1) - yanor.net/wiki

バックアップ一覧
差分を表示
現在との差分を表示
ソースを表示
PostgreSQL/SQL/ランキング/連番テーブルと相関サブクエリへ行く。
- 1 (2015-09-17 (木) 08:44:48)

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

前提

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

generate_series() で0から4までの連番テーブルを作る。
usersテーブルをageでORDER BYし、その際、連番の番号を使ってOFFSETを指定する。
そのままではusersのnameとageを一緒に取り出せないので、ARRAYを使って一旦配列にまとめる。これをインラインテーブルxとし、ここからageとnameを展開する。