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

前提

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

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS

Last-modified: 2015-09-17 (木) 08:45:54