経緯

業務で使用しているシステム(PostgresSQL) で、以下のような抽出をしたかったが断念したことがあり、方法を調べました。

やりたいこと

個人コード	資料ID
0001	11111111
0002	22222222
0001	33333333
0002	44444444
0001	55555555

上記のようなデータについて、
下記の通りに個人ごとに資料を並べたい。

個人コード	資料1	資料2	資料3
0001	11111111	33333333	55555555
0002	22222222	44444444	(null)

やったこと

以下のサイトを使うと、ブラウザ上でデータベース環境やSQLの使用可。 sqlfiddle.com

データを用意。

CREATE TABLE Siryo
 (kojin_cd  CHAR(4) NOT NULL,
 siryo_no    VARCHAR(10) NOT NULL,
 PRIMARY KEY (siryo_no));

BEGIN TRANSACTION;
INSERT INTO Siryo VALUES ('0001', '11111111');
INSERT INTO Siryo VALUES ('0002', '22222222');
INSERT INTO Siryo VALUES ('0001', '33333333');
INSERT INTO Siryo VALUES ('0002', '44444444');
INSERT INTO Siryo VALUES ('0001', '55555555');
COMMIT;

このサイトを参考にし、以下のSQLを作ってみた。 dev.classmethod.jp

select
     tmp.kojin_cd
    ,max(case tmp.seq when 1 then tmp.siryo_no else null end) as 資料1
    ,max(case tmp.seq when 2 then tmp.siryo_no else null end) as 資料2
    ,max(case tmp.seq when 3 then tmp.siryo_no else null end) as 資料3
from
(
    select
         kojin_cd
        ,siryo_no
        ,row_number() over (partition by kojin_cd) as seq
    from
       Siryo
) tmp
group by
    tmp.kojin_cd
;

すると、以下のように抽出できた。

個人コード	資料1	資料2	資料3
0001	11111111	33333333	55555555
0002	22222222	44444444	(null)

処理の流れ

まず、サブクエリ部分で、個人ごとに持っている資料に連番を振っている。

    select
         kojin_cd
        ,siryo_no
        ,row_number() over (partition by kojin_cd) as seq
    from
       Siryo

個人コード	資料ID	seq
0001	11111111	1
0001	33333333	2
0001	55555555	3
0002	22222222	1
0002	44444444	2

そして、上記のサブクエリの抽出結果を使用し、個人コードでグループ化する。連番が各個人コードごとで、資料に1から順に振られていることを利用し、caseで連番をそれぞれ取り出し、横並びにしている。

調べたこと

row_number() とは？　

=> ウィンドウ関数の1つ。抽出順に1から始まる行番号を付ける。

ウィンドウ関数とは?

ウィンドウ関数は現在の行に何らかとも関係するテーブル行の一纏まり全般の計算を行うhttps://www.postgresql.jp/document/9.1/html/tutorial-window.html より引用) 　

=> よくわからん

「何らかの条件で並んでいるデータに対して、特定の範囲のデータのみ参照できるようにする機能が用意されており、これをWindowと呼んでいます。ちょうど窓を開けて全体の中の一部を覗くようなイメージでです。そして、このWindowに対して、最大、最小、合計、平均などの集計を行う関数が用意されており、これらのことをWindow関数と呼んでいます」【ひたすら図で説明】一番やさしい SQL window 関数（分析関数）の使い方 | 趣味や仕事に役立つ初心者DIYプログラミング入門

=> なんとなくわかった。そして、SUMとかMAXとかMINとか、普段使っている集計関数のこと、Window関数っていうのね...(無知)

ウィンドウ関数は OVER句と一緒に使う

OVER句の( )内には、ORDER BYか、PARTITION BYが入る。PARTITION BY は指定した項目でグループ化するもの、 ORDER BY は指定した項目でソートするもの。 ORDER BYは並び変えであり、グループ化するわけではないので、今回の用途では不向き。

    select
         kojin_cd
        ,siryo_no
        ,row_number() over (order by kojin_cd) as seq
    from
       Siryo

個人コード	資料ID	seq
0001	11111111	1
0001	33333333	2
0001	55555555	3
0002	22222222	4
0002	44444444	5

shocci1214’s blog

【PostgresSQL】縦持ちデータを横並びにする

経緯