2014/08/31
PostgreSQL > 層別集計に範囲型を使う
PostgreSQL > 層別集計に範囲型を使う
ここで言う層別集計とは、例えば年齢のあるデータに対して「15歳未満」「15〜64歳」「65歳以上」などに階級区分して集計すること。この区分を PostgreSQL の範囲型で定義し集計するテンプレートをメモ。実行環境は 2014/08/13 の実機を、範囲型の詳細は下記ドキュメントを参照。
■ PostgreSQL 9.3.2文書 : 範囲型
https://www.postgresql.jp/document/9.3/html/rangetypes.html
■ PostgreSQL 9.3.2文書 : 範囲関数と演算子
https://www.postgresql.jp/document/9.3/html/functions-range.html
↓ 最初にサンプルデータを作成。100歳までの年齢と何らかの数値の、1000行のテーブル。
↓ とりあえず三つの区分を範囲型で作る。直接定義文を書いてもいいが（その例は冒頭のドキュメントを参照）、ここでは配列から自動的に作った。区分を変える場合は最初の a ブロックの配列の中身だけを変えて済むように。境界の定義はオーソドックスな「○○以上 〜 ○○未満」。変更する場合は b ブロックの concat の中身を変える。
■ クエリ 1
後は、上の範囲型の行をサンプルテーブルに結合して集計するだけ。↓ 結合条件「範囲型 @> 値」で、サンプルテーブルの各行が三つの区分に割り振られる。
■ クエリ 2
階級区分を変えた再集計も、最初の a ブロックの配列の中身だけ変えれば ↓ 済む。先ほど範囲型の定義文を直接書かなかったのは、こういう柔軟性を得るため。
ここまでは WITH 句で一時的に「配列 → 範囲型」を作った。もう少し実用的に、範囲型階級区分の元になる配列をテーブルに保存して使う例。例えば ↓ 三つの異なる年齢階級区分を想定してみた。配列の各要素が境界値を意味しているが、最終要素の NULL は「○○歳以上」という開いた範囲を作成するのに必要。
先ほどのクエリ 1 と同じ「配列 → 範囲型」を上のテーブルに用い、三種類の全区間を一度に作成できる。↓ 列 bid で三つの階級区分種別を見分ける。
後は、クエリ 2 で範囲型とサンプルテーブルを結合したのとほぼ同じ。違うのは三つの階級区分種別を見分ける bid 列の存在。
配列から範囲型を作る部分（上では b ブロックまで）を VIEW にすれば、それとデータテーブルを結合する SELECT 一文だけで集計ができ、より実用的かも。
■ PostgreSQL 9.3.2文書 : 範囲型
https://www.postgresql.jp/document/9.3/html/rangetypes.html
■ PostgreSQL 9.3.2文書 : 範囲関数と演算子
https://www.postgresql.jp/document/9.3/html/functions-range.html
↓ 最初にサンプルデータを作成。100歳までの年齢と何らかの数値の、1000行のテーブル。
CREATE TABLE "201408"."31_sample_dat" AS
SELECT ceil(random() * 100) :: int age, random() val
FROM generate_series(1, 1000) sub ;
-- 確認
SELECT * FROM "201408"."31_sample_dat" ;
↓ とりあえず三つの区分を範囲型で作る。直接定義文を書いてもいいが（その例は冒頭のドキュメントを参照）、ここでは配列から自動的に作った。区分を変える場合は最初の a ブロックの配列の中身だけを変えて済むように。境界の定義はオーソドックスな「○○以上 〜 ○○未満」。変更する場合は b ブロックの concat の中身を変える。
■ クエリ 1
WITH a (brk) AS (
SELECT unnest(ARRAY[0, 15, 65, NULL])
), b (rng) AS (
SELECT concat('[', brk, ',', lead(brk) over(), ')') :: int4range
FROM a WHERE brk IS NOT NULL
)
SELECT * FROM b ;
後は、上の範囲型の行をサンプルテーブルに結合して集計するだけ。↓ 結合条件「範囲型 @> 値」で、サンプルテーブルの各行が三つの区分に割り振られる。
■ クエリ 2
WITH a (brk) AS (
SELECT unnest(ARRAY[0, 15, 65, NULL])
), b (rng) AS (
SELECT concat('[', brk, ',', lead(brk) over(), ')') :: int4range
FROM a WHERE brk IS NOT NULL
) -- ここまで上と同じ
SELECT rng, count(*), avg(val)
FROM b JOIN "201408"."31_sample_dat" ON rng @> age
GROUP BY rng ORDER BY rng ;
階級区分を変えた再集計も、最初の a ブロックの配列の中身だけ変えれば ↓ 済む。先ほど範囲型の定義文を直接書かなかったのは、こういう柔軟性を得るため。
WITH a (brk) AS (
SELECT unnest(ARRAY[0, 20, 40, 60, 80, NULL]) -- ここだけ変更
), b (rng) AS (
SELECT concat('[', brk, ',', lead(brk) over(), ')') :: int4range
FROM a WHERE brk IS NOT NULL
)
SELECT rng, count(*), avg(val)
FROM b JOIN "201408"."31_sample_dat" ON rng @> age
GROUP BY rng ORDER BY rng ;
ここまでは WITH 句で一時的に「配列 → 範囲型」を作った。もう少し実用的に、範囲型階級区分の元になる配列をテーブルに保存して使う例。例えば ↓ 三つの異なる年齢階級区分を想定してみた。配列の各要素が境界値を意味しているが、最終要素の NULL は「○○歳以上」という開いた範囲を作成するのに必要。
CREATE TABLE "201408"."31_sample_brk" AS
SELECT 1 bid, ARRAY[0, 15, 65, NULL] brk
UNION ALL
-- 20歳刻み
SELECT 2, array_agg(sub) || '{NULL}' :: int[]
FROM generate_series(0, 80, 20) sub
UNION ALL
-- 10歳刻み
SELECT 3, array_agg(sub) || '{NULL}' :: int[]
FROM generate_series(0, 100, 10) sub ;
-- 確認
SELECT * FROM "201408"."31_sample_brk" ;
先ほどのクエリ 1 と同じ「配列 → 範囲型」を上のテーブルに用い、三種類の全区間を一度に作成できる。↓ 列 bid で三つの階級区分種別を見分ける。
WITH a AS (
SELECT bid, unnest(brk) brk FROM "201408"."31_sample_brk"
), b AS (
SELECT bid, brk, lead(brk) over w
, concat('[', brk, ',', lead(brk) over w, ')') :: int4range rng
FROM a WHERE brk IS NOT NULL
WINDOW w AS (PARTITION BY bid ORDER BY brk)
) SELECT * FROM b ;
後は、クエリ 2 で範囲型とサンプルテーブルを結合したのとほぼ同じ。違うのは三つの階級区分種別を見分ける bid 列の存在。
WITH a AS (
SELECT bid, unnest(brk) brk FROM "201408"."31_sample_brk"
), b AS (
SELECT bid, concat('[', brk, ',', lead(brk) over w, ')') :: int4range rng
FROM a WHERE brk IS NOT NULL
WINDOW w AS (PARTITION BY bid ORDER BY brk)
) -- ここまで上と同じ
SELECT bid, rng, count(*), avg(val)
FROM b JOIN "201408"."31_sample_dat" ON rng @> age
GROUP BY bid, rng ORDER BY bid, rng ;
配列から範囲型を作る部分（上では b ブロックまで）を VIEW にすれば、それとデータテーブルを結合する SELECT 一文だけで集計ができ、より実用的かも。