postgresqlでのクロス集計(crosstab)の動作について

Question

以下のようなテーブルがあります。

create table 申請テーブル (
申請id integer not null
, 従業員番号 integer not null
, 申請日 date not null
, primary key (申請id)
);

上記のテーブルに追加されたレコードを抽出して、いわゆる
「行を列に変換する」SELECTとして、以下のような抽出を行いたいのですが、列数が可変で申請列の上限が不定です。

従業員番号	申請１	申請２	申請３	申請４	申請５	申請６
100	2018-03-01	2018-03-02	－	－	－	－
101	2018-03-01	2018-04-01	2018-04-02	2018-04-03	2018-04-04	2018-04-05
102	2018-03-01	－	－	－	－	－
103	2018-03-01	2018-03-02	－	－	－	－

PostgreSQL9.4を使用しています。
https://www.postgresql.jp/document/9.4/html/tablefunc.html

オンラインマニュアルを見ていると、列数が決まっていないとクロス集計は行えないようなのですが、
上記のような抽出を行う方法はありますでしょうか。crosstabに固執はしていません。

以上、よろしくお願いします。

Answer 1

仮に可変で取り出せたとして、どのように利用するのですか？
カラムが可変で返却されるselect結果を利用する場合、結局カラムの情報を判断する必要があるでしょうから、
select結果を利用する際に配列型を扱えるようなら、array_agg()の利用をお薦めします。

select 従業員番号, array_agg(申請日) as 申請日リスト
from 申請テーブル
group by 従業員番号

array_agg()はdistinctやorder byも指定できますし、配列の要素数はarray_length()で求めることができます。

select *, array_length(申請日リスト, 1) as 申請件数
from (
  select 従業員番号, array_agg(申請日 order by 申請id) as 申請日リスト
  from 申請テーブル
  group by 従業員番号
) agg_list

※array_length()の説明のために記述しただけで、単に件数を求めるのにはcount()を使用すればよくネストさせる必要はありません。また、他にも配列を扱う色々な関数があります。

Comments

[hayash-dev]

御回答ありがとうございます。

用途は、webアプリでクライアントからのajax要求でSQLを実行し、select結果をjson形式で返却し、クライアントで表示します。

その後、仕様を考えていましたが、申請列は｛日付，申請ID｝が必要になりそうですので、以下のように、１つの申請に複数の情報をjson化して、jsonb_agg()で更に配列化した状態でクライアントに返そうと思います。

json_build_object(
'date', :申請日,
'申請ID', :申請ID,
)

[sazi]

jsonですか。postgres 9.5なら便利な関数があるのですけどね。
http://foobar.hatenablog.com/entry/2015/12/05/212238

[sazi]

9.4から9.5へのバージョンアップに支障があるとは思えませんので、検討されては如何でしょうか。

[hayash-dev]

9.5の記事を拝見しました。
9.5へのバージョンアップは、JDBCの対応状況を見て判断したいと思います。
（一見すると問題無さそうです）

Answer 2

string_agg が使えるかも？

Comments

[hayash-dev]

御回答ありがとうございます。

json_build_object()とjsonb_agg()の合わせ技で対応しようと思います。