PostgreSQLの変わった関数でテーブルレスなテキスト処理をする

またどうせK-POPだろ！？
と思われた方、するどい。

K-POPですが、少しだけまともな事も書きます。

QiitaのK-POPタグの1ゲットォォｫｫ！！
￣￣￣￣￣∨￣￣￣　　　　　　　(´´
　　　　 ∧∧　　　）　　　　　　(´⌒(´
　　⊂（ﾟДﾟ⊂⌒｀つ≡≡≡(´⌒;;;≡≡≡
　　　　　　 ￣￣　 (´⌒(´⌒;;
　　　　　　ｽﾞｻﾞｰｰｰｰｰｯ

#ネタ1
PostgreSQLには変わった関数が沢山あります。
いくつか変な関数を組み合わせてテーブルを使わず、テキスト処理をPostgreSQLにやらせてみたいと思います。
私が応援しているK-POPアイドル、GFRIEND(여자친구:ヨジャチング)のメンバーの名前と誕生日のテキストから今日時点の年齢を計算してみます。

-bash-4.2$ psql
psql (9.5.3)
Type "help" for help.
postgres=# SELECT
        split_part(t.a,E'\t',1)::text AS name,
        split_part(t.a,E'\t',2)::date AS birthday,
        age(CURRENT_DATE,split_part(t.a,E'\t',2)::date) AS current_age,
        CURRENT_DATE AS TODAY
FROM
regexp_split_to_table('
Sowon   1995-12-07
Yerin   1996-08-19
Eunha   1997-05-30
Yuju    1997-10-04
SinB    1998-06-03
Umji    1998-08-19
',E'\n') AS t(a)
WHERE
        length(t.a) >0
;
 name  |  birthday  |       current_age       |   today
-------+------------+-------------------------+------------
 Sowon | 1995-12-07 | 20 years 9 mons         | 2016-09-07
 Yerin | 1996-08-19 | 20 years 19 days        | 2016-09-07
 Eunha | 1997-05-30 | 19 years 3 mons 8 days  | 2016-09-07
 Yuju  | 1997-10-04 | 18 years 11 mons 3 days | 2016-09-07
 SinB  | 1998-06-03 | 18 years 3 mons 4 days  | 2016-09-07
 Umji  | 1998-08-19 | 18 years 19 days        | 2016-09-07
(6 rows)

regexp_split_to_tableは正規表現を使って文字列を分割し、テーブル(行)として返してくれる関数です。
改行を含むテキストファイルをregexp_split_to_tableに改行コード(\n)で分割します。
WHERE句でlength(t.a) >0としているのは空行を省く為です。

これで改行で分割されたテキストはテーブルになりましたが、更に1行がタブ区切りになっていますので、
split_partで更にタブ(\n)で分割し、名前(name)と誕生日(birthday)という列として返してくれました。

あとは日付の差をINTERVAL型で返してくれるage関数に現在の日付(CURRENT_DATE)と誕生日の日付を渡せば、今日時点の年齢が求められます。

#ネタ2
GFRIENDは昨日(2016-09-06)でデビュー600日を迎えました。デビュー日は2015-01-15です。

先ほどのテキスト処理と連番を返してくれる超有能関数generate_seriesを組み合わせて、デビュー日(2015-01-15)を起算日として、100日ごとの記念日と、記念日時点の各メンバーの年齢を計算してみたいと思います。

hoge.sql

-bash-4.2$ psql
psql (9.5.3)
Type "help" for help.

postgres=# SELECT
        g.a||' days' as count,
        ('2015-01-15'::date+(g.a||' days')::INTERVAL)::date,
        MAX(CASE WHEN split_part(t.a,E'\t',1)::text='Sowon' THEN age('2015-01-15'::date+(g.a||' days')::INTERVAL,split_part(t.a,E'\t',2)::date) ELSE NULL END) AS "Sowon Age",
        MAX(CASE WHEN split_part(t.a,E'\t',1)::text='Yerin' THEN age('2015-01-15'::date+(g.a||' days')::INTERVAL,split_part(t.a,E'\t',2)::date) ELSE NULL END) AS "Yerin Age",
        MAX(CASE WHEN split_part(t.a,E'\t',1)::text='Eunha' THEN age('2015-01-15'::date+(g.a||' days')::INTERVAL,split_part(t.a,E'\t',2)::date) ELSE NULL END) AS "Eunha Age",
        MAX(CASE WHEN split_part(t.a,E'\t',1)::text='Yuju'  THEN age('2015-01-15'::date+(g.a||' days')::INTERVAL,split_part(t.a,E'\t',2)::date) ELSE NULL END) AS "Yuju Age",
        MAX(CASE WHEN split_part(t.a,E'\t',1)::text='SinB'  THEN age('2015-01-15'::date+(g.a||' days')::INTERVAL,split_part(t.a,E'\t',2)::date) ELSE NULL END) AS "SinB Age",
        MAX(CASE WHEN split_part(t.a,E'\t',1)::text='Umji'  THEN age('2015-01-15'::date+(g.a||' days')::INTERVAL,split_part(t.a,E'\t',2)::date) ELSE NULL END) AS "Umji Age"
FROM
        generate_series(0,3000,100) AS g(a),
        regexp_split_to_table('
Sowon   1995-12-07
Yerin   1996-08-19
Eunha   1997-05-30
Yuju    1997-10-04
SinB    1998-06-03
Umji    1998-08-19
',E'\n') AS t(a)
WHERE
        length(t.a) > 0
GROUP BY
        g.a||' days',
        ('2015-01-15'::date+(g.a||' days')::INTERVAL)::date
ORDER BY
        2
;
   count   |    date    |        Sowon Age         |        Yerin Age         |        Eunha Age         |         Yuju Age         |         SinB Age         |         Umji Age
-----------+------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------
 0 days    | 2015-01-15 | 19 years 1 mon 8 days    | 18 years 4 mons 27 days  | 17 years 7 mons 16 days  | 17 years 3 mons 11 days  | 16 years 7 mons 12 days  | 16 years 4 mons 27 days
 100 days  | 2015-04-25 | 19 years 4 mons 18 days  | 18 years 8 mons 6 days   | 17 years 10 mons 26 days | 17 years 6 mons 21 days  | 16 years 10 mons 22 days | 16 years 8 mons 6 days
 200 days  | 2015-08-03 | 19 years 7 mons 27 days  | 18 years 11 mons 15 days | 18 years 2 mons 4 days   | 17 years 9 mons 30 days  | 17 years 2 mons          | 16 years 11 mons 15 days
 300 days  | 2015-11-11 | 19 years 11 mons 4 days  | 19 years 2 mons 23 days  | 18 years 5 mons 12 days  | 18 years 1 mon 7 days    | 17 years 5 mons 8 days   | 17 years 2 mons 23 days
 400 days  | 2016-02-19 | 20 years 2 mons 12 days  | 19 years 6 mons          | 18 years 8 mons 20 days  | 18 years 4 mons 15 days  | 17 years 8 mons 16 days  | 17 years 6 mons
 500 days  | 2016-05-29 | 20 years 5 mons 22 days  | 19 years 9 mons 10 days  | 18 years 11 mons 30 days | 18 years 7 mons 25 days  | 17 years 11 mons 26 days | 17 years 9 mons 10 days
 600 days  | 2016-09-06 | 20 years 8 mons 30 days  | 20 years 18 days         | 19 years 3 mons 7 days   | 18 years 11 mons 2 days  | 18 years 3 mons 3 days   | 18 years 18 days
 700 days  | 2016-12-15 | 21 years 8 days          | 20 years 3 mons 27 days  | 19 years 6 mons 16 days  | 19 years 2 mons 11 days  | 18 years 6 mons 12 days  | 18 years 3 mons 27 days
 800 days  | 2017-03-25 | 21 years 3 mons 18 days  | 20 years 7 mons 6 days   | 19 years 9 mons 26 days  | 19 years 5 mons 21 days  | 18 years 9 mons 22 days  | 18 years 7 mons 6 days
 900 days  | 2017-07-03 | 21 years 6 mons 27 days  | 20 years 10 mons 15 days | 20 years 1 mon 4 days    | 19 years 8 mons 30 days  | 19 years 1 mon           | 18 years 10 mons 15 days
 1000 days | 2017-10-11 | 21 years 10 mons 4 days  | 21 years 1 mon 23 days   | 20 years 4 mons 12 days  | 20 years 7 days          | 19 years 4 mons 8 days   | 19 years 1 mon 23 days
 1100 days | 2018-01-19 | 22 years 1 mon 12 days   | 21 years 5 mons          | 20 years 7 mons 20 days  | 20 years 3 mons 15 days  | 19 years 7 mons 16 days  | 19 years 5 mons
 1200 days | 2018-04-29 | 22 years 4 mons 22 days  | 21 years 8 mons 10 days  | 20 years 10 mons 30 days | 20 years 6 mons 25 days  | 19 years 10 mons 26 days | 19 years 8 mons 10 days
 1300 days | 2018-08-07 | 22 years 8 mons          | 21 years 11 mons 19 days | 21 years 2 mons 8 days   | 20 years 10 mons 3 days  | 20 years 2 mons 4 days   | 19 years 11 mons 19 days
 1400 days | 2018-11-15 | 22 years 11 mons 8 days  | 22 years 2 mons 27 days  | 21 years 5 mons 16 days  | 21 years 1 mon 11 days   | 20 years 5 mons 12 days  | 20 years 2 mons 27 days
 1500 days | 2019-02-23 | 23 years 2 mons 16 days  | 22 years 6 mons 4 days   | 21 years 8 mons 24 days  | 21 years 4 mons 19 days  | 20 years 8 mons 20 days  | 20 years 6 mons 4 days
 1600 days | 2019-06-03 | 23 years 5 mons 27 days  | 22 years 9 mons 15 days  | 22 years 4 days          | 21 years 7 mons 30 days  | 21 years                 | 20 years 9 mons 15 days
 1700 days | 2019-09-11 | 23 years 9 mons 4 days   | 23 years 23 days         | 22 years 3 mons 12 days  | 21 years 11 mons 7 days  | 21 years 3 mons 8 days   | 21 years 23 days
 1800 days | 2019-12-20 | 24 years 13 days         | 23 years 4 mons 1 day    | 22 years 6 mons 21 days  | 22 years 2 mons 16 days  | 21 years 6 mons 17 days  | 21 years 4 mons 1 day
 1900 days | 2020-03-29 | 24 years 3 mons 22 days  | 23 years 7 mons 10 days  | 22 years 9 mons 30 days  | 22 years 5 mons 25 days  | 21 years 9 mons 26 days  | 21 years 7 mons 10 days
 2000 days | 2020-07-07 | 24 years 7 mons          | 23 years 10 mons 19 days | 23 years 1 mon 8 days    | 22 years 9 mons 3 days   | 22 years 1 mon 4 days    | 21 years 10 mons 19 days
 2100 days | 2020-10-15 | 24 years 10 mons 8 days  | 24 years 1 mon 27 days   | 23 years 4 mons 16 days  | 23 years 11 days         | 22 years 4 mons 12 days  | 22 years 1 mon 27 days
 2200 days | 2021-01-23 | 25 years 1 mon 16 days   | 24 years 5 mons 4 days   | 23 years 7 mons 24 days  | 23 years 3 mons 19 days  | 22 years 7 mons 20 days  | 22 years 5 mons 4 days
 2300 days | 2021-05-03 | 25 years 4 mons 27 days  | 24 years 8 mons 15 days  | 23 years 11 mons 4 days  | 23 years 6 mons 30 days  | 22 years 11 mons         | 22 years 8 mons 15 days
 2400 days | 2021-08-11 | 25 years 8 mons 4 days   | 24 years 11 mons 23 days | 24 years 2 mons 12 days  | 23 years 10 mons 7 days  | 23 years 2 mons 8 days   | 22 years 11 mons 23 days
 2500 days | 2021-11-19 | 25 years 11 mons 12 days | 25 years 3 mons          | 24 years 5 mons 20 days  | 24 years 1 mon 15 days   | 23 years 5 mons 16 days  | 23 years 3 mons
 2600 days | 2022-02-27 | 26 years 2 mons 20 days  | 25 years 6 mons 8 days   | 24 years 8 mons 28 days  | 24 years 4 mons 23 days  | 23 years 8 mons 24 days  | 23 years 6 mons 8 days
 2700 days | 2022-06-07 | 26 years 6 mons          | 25 years 9 mons 19 days  | 25 years 8 days          | 24 years 8 mons 3 days   | 24 years 4 days          | 23 years 9 mons 19 days
 2800 days | 2022-09-15 | 26 years 9 mons 8 days   | 26 years 27 days         | 25 years 3 mons 16 days  | 24 years 11 mons 11 days | 24 years 3 mons 12 days  | 24 years 27 days
 2900 days | 2022-12-24 | 27 years 17 days         | 26 years 4 mons 5 days   | 25 years 6 mons 25 days  | 25 years 2 mons 20 days  | 24 years 6 mons 21 days  | 24 years 4 mons 5 days
 3000 days | 2023-04-03 | 27 years 3 mons 27 days  | 26 years 7 mons 15 days  | 25 years 10 mons 4 days  | 25 years 5 mons 30 days  | 24 years 10 mons         | 24 years 7 mons 15 days
(31 rows)

generate_series(0,3000,100)は0から開始し、3000まで100づつ足した集合(行)を返します。

あとはCASE句で縦持ちの結果を横持ちにし、MAXで集計する事で有効な結果(最大値)を得ています。

#まとめ
1600日の記念日に21歳の誕生日を迎えるシンビさんのファンは今から準備しておきましょうね☆彡
#真まとめ
PostgreSQLは大変多くの関数があるので、マニュアルの整備・完成度に感謝しつつ、関数のリファレンスは見ておくと新しい発見があるかも知れません。
##余談
実際の韓国人の年齢は日本のような満年齢ではなく、数え年で考える為、最大で2歳多くなります。
韓国に行くと『２才』歳が増える！？
###余談2
次の記念日(700日)は2016-12-15です。
GFRIENDには二人の8月19日生まれがいます！