generate_seriesのちょっとしたTIPS - シュンツのつまづき日記

このエントリはPostgreSQL Advent Calendar 2015の24日目の記事です。
昨日は@ooyamams1987さんのPostgreSQL BDR (Bi-Directional Replication) を使ってみたでした。
明日は@hide_kawさんの担当になります。

【前提条件】

[環境]

PostgreSQL 9.4.4

【概要】

generate_series関数を先輩から教えてもらい、
割と便利な使い方ができるなと思ったので色々と書いてみようと思います。

【何をする関数か】

そもそもgenerate_seriesとはどんなものかというのは
「集合を返す関数」と公式マニュアルにはあります。

[使ってみる]

マニュアルにも書かれていますが、使い方は開始値と終了値を渡します。

postgres=# SELECT * FROM generate_series(1, 5);

 generate_series
-----------------
               1
               2
               3
               4
               5
(5 rows)

1から5までの集合が作成されました。

日付も対応しています。日付の場合は開始日、終了日、インターバルを指定します。

postgres=# select * from generate_series('2015-12-20'::timestamp, '2015-12-26'::timestamp, '1 days');
   generate_series
---------------------
 2015-12-20 00:00:00
 2015-12-21 00:00:00
 2015-12-22 00:00:00
 2015-12-23 00:00:00
 2015-12-24 00:00:00
 2015-12-25 00:00:00
 2015-12-26 00:00:00
(7 rows)

【TIPS】

これだけだとあまり嬉しいことがないので、
実際に仕事中に使った例をTIPSとして書き出してみます。

[データ作成ツール]

「データ作成ツールを使え」と言われると「そうですね・・・」となってしまうのですが、
そこまできっちりしていなくてもテストデータを作成したい場合に使うTIPSです。

INSERT INTO
    hoge
SELECT
    data_id,
    CASE WHEN gender_seed = 1 THEN '男性'
         ELSE '女性'
    END AS gender,
    CASE WHEN name_seed = 1 THEN '山田'
         WHEN name_seed = 2 THEN '佐藤'
         ELSE '鈴木'
    END AS name,
    20 + (age_seed * 4) AS age
FROM
    (
        SELECT
            data_id,
            mod(data_id, 2) AS gender_seed,
            mod(data_id, 3) AS name_seed,
            mod(data_id, 4) AS age_seed
        FROM
            generate_series(1, 10) AS func(data_id)
    ) gen_val

1から10までデータIDを割り振ってmodで算出した値によって
データの値を決めるというかなり荒技をやっています。

generate_seriesのパラメータを変えれば、自由に件数を変えられるのが楽です。

[日付算出]

ある一定期間の毎月第3水曜日を取得したい。
みたいな要望があって、プログラム側でやるとコードが汚くなりそうだったので、SQLでやってみました。

SELECT
    *
FROM
    (
        SELECT
            all_day.*,
            -- [4]
            ROW_NUMBER() OVER (PARTITION BY year_month) AS week_of_month
        FROM
            (
                SELECT
                    target_date,
                    EXTRACT(DOW FROM target_date) AS day_of_week,
                    -- [3]
                    TO_CHAR(target_date, 'yyyy-mm') AS year_month
                FROM
                    -- [1]
                    generate_series('2015-1-1'::timestamp, '2015-12-31'::timestamp, '1 day') AS func(target_date)
                WHERE
                    -- [2]
                    EXTRACT(DOW FROM target_date) = 3
            ) all_day
    ) only_target_dow
WHERE
    -- [5]
    week_of_month = 3

[1]ではgenerate_seriesで2015年の日付を全て取得します。
[2]では[1]の結果から曜日が水曜日（day_of_weekが3)ものを対象にします。
[3]で月ごとにグルーピングするために文字属性をもたせます。
[4]で[1]から[3]で加工した結果に対して、WINDOW関数で第何週かを求めます
[5]で[4]までの結果から第3週目のものを取得します

という5ステップで1年間の第3週目の結果が取得できます。

target_date     | day_of_week | year_month | week_of_month
---------------------+-------------+------------+---------------
2015-01-21 00:00:00 |           3 | 2015-01    |             3
2015-02-18 00:00:00 |           3 | 2015-02    |             3
2015-03-18 00:00:00 |           3 | 2015-03    |             3
2015-04-15 00:00:00 |           3 | 2015-04    |             3
2015-05-20 00:00:00 |           3 | 2015-05    |             3
2015-06-17 00:00:00 |           3 | 2015-06    |             3
2015-07-15 00:00:00 |           3 | 2015-07    |             3
2015-08-19 00:00:00 |           3 | 2015-08    |             3
2015-09-16 00:00:00 |           3 | 2015-09    |             3
2015-10-21 00:00:00 |           3 | 2015-10    |             3
2015-11-18 00:00:00 |           3 | 2015-11    |             3
2015-12-16 00:00:00 |           3 | 2015-12    |             3
(12 rows)

ローカルで1900年1月1日から2500年12月31日で検索して、
380msだったのでレスポンス的にも問題ないかなぁと思います。

【まとめ】

generate_seriesのちょっとしたTIPSを書いてみました。

日付関係で使うのは色々と使い道がありそうだなぁと思います。
日付という集合（おそらく集合）からある条件を抜き出しているので
リレーショナルモデルとしても間違った使い方でもないのかなと思います。