昨日は ↓ 普通の年齢の列を年齢階級の範囲型に変換する例でした。これだと年齢階級の定義に例外がある場合クエリが複雑になるので、あらかじめ別のテーブルかビューで範囲型のセットを作っておき、必要な時に結合することを考えます。今日はセットを作る所まで。明日も続きます。
Contents


ドキュメントの関連ページ（追加）、参考リンク
今日の途中から少し使うPostgreSQLのウィンドウ関数、範囲型の関数・演算子の日本語ドキュメントと、範囲型についての分かりやすい解説です。



範囲型のセットを作る（a）ベタ書き
今日のクエリは、テーブルまたはビューに入れる中身だけ記します。実際使う時は、各クエリの前にCREATE TABLE (VIEW) ... ASを付けて実行するイメージ。

まず範囲型一つずつベタ書きで定義し、縦に重ねる場合。階級区分が少ない時はこれで十分。上限をNULLにすることで「○歳以上」を表現できます。
VALUES (int4range(0, 15)),
    (int4range(15, 65)),
    (int4range(65, NULL));

 column1
---------
 [0,15)
 [15,65)
 [65,)
(3 行)


上はコンストラクタ関数を使用、下は文字列で定義してキャストする例。半角空白は少し注意が必要で、数値の前にあっても問題ありませんが「上限がNULL」の場合はあると駄目。下のようにエラーになる。
VALUES ('[0, 15)' :: int4range),
    ('[15, 65)'),
    ('[65,)');

 column1
---------
 [0,15)
 [15,65)
 [65,)
(3 行)

VALUES ('[65, )' :: int4range);
ERROR:  invalid input syntax for integer: " "
行 1: VALUES ('[65, )' :: int4range)
              ^


範囲型のセットを作る（b）境界値を指定
前項のようにベタ書きだと、同じ数値をだいたい2回書くことになります（ある範囲の下限、次範囲の上限。ただし先頭・末尾の範囲を除く）。区分が変わることを考え、できれば数値を1回書くだけで済ませたい。ということで「下限を3つ」書くだけで3つの範囲型を作る例です。↓ 
SELECT int4range(low,
    lead(low) over()) AS agegroup
FROM unnest(ARRAY[ 0, 15, 65 ]) AS low;

 agegroup
----------
 [0,15)
 [15,65)
 [65,)
(3 行)


ウィンドウ関数のleadを使い、各行において「次行の下限」を取得し、これを当該行の範囲型の上限にする仕組み。自動的に最後の範囲の上限はNULLになって「○歳以上」になります。ウィンドウ関数の所だけ抜き出すと ↓ こんな感じ。
SELECT low, lead(low) over() AS up
FROM unnest(ARRAY[ 0, 15, 65 ]) AS low;


ウィンドウ関数を使う時、普通は行の並び順を指定しますが（over(ORDER BY ...)など）、ここではunnestで配列の先頭から順番に行になると思うので、省きました。

最後の範囲が自動的に「○歳以上」になって便利な一方、逆に「最後の範囲も○～○歳にする」場合はちょっと面倒。とりあえず「○歳以上」まで作っておいて最後に省く方法 ↓ を考えましたが、もっと良い方法があるかも。upper_infというのは「その範囲の上限が無限か否か、の真偽を返す」関数です。
SELECT * FROM (
    SELECT int4range(low,
        lead(low) over()) AS agegroup
    FROM unnest(ARRAY[ 0, 15, 65, 100 ])
        AS low
) AS foo
WHERE upper_inf(agegroup) IS FALSE;

 agegroup
----------
 [0,15)
 [15,65)
 [65,100)
(3 行)


範囲型のセットを作る（c）年齢階級の最初、区分幅、最後を指定
前項までは階級区分が少ない時や、境界値に「○歳ずつ」などの規則性がない時に使う想定。次は「○歳ずつに区切る」場合です。例えば「5歳階級区分、ただし80歳以上は一括」だと ↓ こんな感じ。先ほどの境界値でのunnestをgenerate_seriesに変えて一定間隔での連番にした以外、全く同じです。
SELECT int4range(low,
    lead(low) over()) AS agegroup
FROM generate_series(0, 80, 5) AS low;

 agegroup
----------
 [0,5)
 [5,10)
 [10,15)
 [15,20)
 [20,25)
 [25,30)
 [30,35)
 [35,40)
 [40,45)
 [45,50)
 [50,55)
 [55,60)
 [60,65)
 [65,70)
 [70,75)
 [75,80)
 [80,)
(17 行)


10歳区分にするなら ↓ こう。generate_seriesの第3引数（連番の間隔）を変えるだけです。
SELECT int4range(low,
    lead(low) over()) AS agegroup
FROM generate_series(0, 80, 10) AS low;

 agegroup
----------
 [0,10)
 [10,20)
 [20,30)
 [30,40)
 [40,50)
 [50,60)
 [60,70)
 [70,80)
 [80,)
(9 行)


範囲型のセットを作る（d）基本は年齢階級、ただし例外あり
ここから、昨日ネックになってた例外ありの場合。どうしてもクエリが複雑になるので、生の年齢の列を変換する中で行うより、別のテーブルかビューで範囲型のセットだけ作ることにします。

例えば「基本は5歳階級別、ただし20歳未満は一括」の場合 ↓ こんな感じ。前項で作った5歳階級別のセットをサブクエリにして、CASE式で分岐し20歳未満なら[0, 20)に変えます。このままだと同じ[0, 20)の範囲が複数行できるのでGROUP BYでまとめ。
SELECT CASE
    WHEN upper(agegroup) <= 20 THEN
        int4range(0, 20)
    ELSE agegroup
    END AS agegroup2
FROM (
    SELECT int4range(low,
        lead(low) over()) AS agegroup
    FROM generate_series(0, 80, 5) AS low
) AS foo
GROUP BY agegroup2
ORDER BY agegroup2;

 agegroup2
-----------
 [0,20)  -- #
 [20,25)
 [25,30)
 [30,35)
 [35,40)
 [40,45)
 [45,50)
 [50,55)
 [55,60)
 [60,65)
 [65,70)
 [70,75)
 [75,80)
 [80,)
(14 行)


最後に、昨日頭が痛かった「5歳階級別、80歳以上は一括、19歳は20～25歳と併合」という変な区分。19歳が入る範囲が変わるだけなので上のようなGROUP BYは不要で、CASE式に範囲の変更をベタ書きすれば意外と簡単にできました。↓
SELECT CASE agegroup
    WHEN '[15,20)' THEN '[15,19)'
    WHEN '[20,25)' THEN '[19,25)'
    ELSE agegroup
    END AS agegroup2
FROM (
    SELECT int4range(low,
        lead(low) over()) AS agegroup
    FROM generate_series(0, 80, 5) AS low
) AS foo;

 agegroup2
-----------
 [0,5)
 [5,10)
 [10,15)
 [15,19)  -- #
 [19,25)  -- #
 [25,30)
 [30,35)
 [35,40)
 [40,45)
 [45,50)
 [50,55)
 [55,60)
 [60,65)
 [65,70)
 [70,75)
 [75,80)
 [80,)
(17 行)


これでだいたい、年齢階級を範囲型のセットにするひな型にできそう。明日は、これらのセットをテーブルかビューにして、生の年齢の列があるテーブルと結合する予定。

あと、上のように例外を処理したりすると、うっかりクエリを間違えて「重なり」や「抜け」ができかねません。結果として、ある年齢が「複数の範囲に入る」または「どの範囲にも入らない」という事態になって困るので、そのチェックについても明日考えます。