PostgreSQL Advent Calendar 2012 の12日目を担当します@_john_doe_です。

他の方のディープな内容と比べて箸休め程度になりますが、実際運用中のサービスの中から

の体験記からお話させて頂きたいと思います。

私自身WEB開発者なのですが、運用中のDB周りメンテナンス/管理も行っています。なので純粋なDBAというよりもちょっと発想がWEB開発よりになっています。すなわち

  • いかに効率良く速度を求めたクエリをかけるか
  • プログラムに組み込んだクエリ自体のメンテナンス性の考慮
  • ちょっとしたパフォーマンスチューニング

がミッションになります。

つまりポリシーとしては「速いが正義」ではあるのですが、メンテナンス性のことも考えて開発作業を行う必要があります。

本題

で、本題なのですが、業務に当たっていると当然クライアントのデータをメンテナンスすることになるのですが

  • 日付のデータなのにchar/integer(YYYYMMDDとか…)
  • フラグなのにchar、もしくはinteger(0/1しかないのに…)
  • 数字しか入ってないのにchar
  • etc ...

とか、とにかく糞すぎる独創的なデータにどう対峙するかに日々腐心するわけなんですが、あまりにも複雑すぎるクエリを書いてしまうと、後から参入してきたメンバが、読めない!とかになってしまうわけで…

そんな中遭遇したデータに対して自作functionを作って解決した、という話です。

例えばこんなデータ。

test=# \d+ keiyaku
keiyaku                   Table "public.keiyaku"
    Column    |  Type   | Modifiers | Storage  | Description
 --------------+---------+-----------+----------+-------------
 id           | integer |           | plain    | 
 keiyaku_date | text    |           | extended | 
Has OIDs: no

test=# SELECT * FROM keiyaku;
 id | keiyaku_date 
----+--------------
  1 | H24.7
  2 | H24.07
  3 | H24.9
  4 | H24.09
  5 | H24.10
  6 | H24.12
  7 | H24.01
  8 | なし
  9 | 不明
 10 | H23.1
(10 rows)

普段は表示用としてしか使ってないカラムだったので、問題なかったのですが

  • ソートで使いたい
  • 絞込み機能を実装したい

といった要望がクライアントからあり、一瞬絶望しました。

あまり複雑なクエリを書いてもなあ…と思ったので、今回functionを作って対応しました。

そもそもこのデータの問題は、日付での処理をしたいのに、日付として処理しづらいデータであることかと考えています。

  • そもそも和暦
  • 日付に関係しない文字列が混在(不明とかなしとか)
  • 0埋めされてたり、されてなかったりフォーマットが一定ではない

とりあえず、日付らしきデータのフォーマットを統一する所から始めました。

test=# SELECT 
test=#     REGEXP_REPLACE(keiyaku_date,
test=#                    'H([0-9]{2}).0*([1-9]*0*)',
test=#                    '\1.\2',
test=#                    'g') 
test=# FROM keiyaku;
 regexp_replace
----------------
 24.7
 24.7
 24.9
 24.9
 24.10
 24.12
 24.1
 なし
 不明
 23.1
(10 rows)

これで和暦っぽいデータのフォーマットを統一することができました。(因みにココの正規表現の対応が一番時間かかりましたw)

次に和暦-> 西暦にしてdate型に変換します。めんどくさいので「なし」「不明」は対象外にしておきます。

test=# SELECT 
test=#      keiyaku_date,
test=#      TO_DATE(
test=#                 (TO_NUMBER(
test=#                     REGEXP_REPLACE(keiyaku_date,
test=#                                    'H([0-9]{2}).0*([1-9]*0*)',
test=#                                    '\1.\2',
test=#                                    'g'
test=#                                  )
test=#                     ,'99.90'
test=#                 )+1988)::text ||'.31'
test=#             ,'YYYY.MM.DD'
test=#         ) AS convert_date
test=# FROM keiyaku
test=# WHERE keiyaku_date != 'なし' AND keiyaku_date != '不明'
test=# ;
 keiyaku_date | convert_date
--------------+--------------
 H24.7        | 2012-07-31
 H24.07       | 2012-07-31
 H24.9        | 2012-10-01
 H24.09       | 2012-10-01
 H24.10       | 2012-10-31
 H24.12       | 2012-12-31
 H24.01       | 2012-01-31
 H23.1        | 2011-01-31
(8 rows)

一度数値化したkeiyaku.keiyaku_dateに対して+1988(年)してまた文字列型にCAST、今回は文字列'.31'を結合させて「YYYY.MM.DD」とし、それをdate型にCASTしてあります。

月までの情報しかないので、日付を31日固定にするのか1日固定にするか、対象外のデータが入ってきた場合はどうするのかは決めとか仕様の問題ですのでここでは割愛。

ここまでくればもう余裕なので最終的にはこんなクエリになります。

test=# SELECT
test-#         keiyaku_date,
test-#         CASE WHEN keiyaku_date = 'なし' THEN '9999-12-31'
test-#              WHEN keiyaku_date = '不明' THEN '9998-12-31'
test-#              WHEN keiyaku_date ~ 'H[0-9]{2}.[0-9]+' THEN
test-#                 TO_DATE(
test(#                            (TO_NUMBER(
test(#                                REGEXP_REPLACE(keiyaku_date,
test(#                                              'H([0-9]{2}).0*([1-9]*0*)',
test(#                                              '\1.\2',
test(#                                              'g'
test(#                                              )
test(#                                ,'99.90'
test(#                            )+1988)::text ||'.31'
test(#                            ,'YYYY.MM.DD'
test(#                        )
test-#         ELSE '9999-12-31'
test-#         END AS convert_date
test-# FROM keiyaku ;
 keiyaku_date | convert_date
--------------+--------------
 H24.7        | 2012-07-31
 H24.07       | 2012-07-31
 H24.9        | 2012-10-01
 H24.09       | 2012-10-01
 H24.10       | 2012-10-31
 H24.12       | 2012-12-31
 H24.01       | 2012-01-31
 なし         | 9999-12-31
 不明         | 9998-12-31
 H23.1        | 2011-01-31
(10 rows)

げ、H24.9 が2012-10-01になってるやん!って思いましたが、9/31はないので勝手にpostgresが日付演算してくれていたんですね。

postgresすげえ。

当然ですが、この自作functionを使って表示のフォーマットを変えたいとかはできません。やるなら別functionとかにするべきでしょうね。

で、これをfunction化します。

test=# CREATE FUNCTION TO_DATE_KEIYAKU_DATE(text)
test-# RETURNS DATE
test-# AS '
test'# SELECT
test'#         CASE WHEN $1 = ''なし'' THEN ''9999-12-31''
test'#              WHEN $1 = ''不明'' THEN ''9998-12-31''
test'#              WHEN $1 ~ ''H[0-9]{2}.[0-9]+'' THEN
test'#                 TO_DATE(
test'#                            (TO_NUMBER(
test'#                                REGEXP_REPLACE($1,
test'#                                              ''H([0-9]{2}).0*([1-9]*0*)'',
test'#                                              ''\1.\2'',
test'#                                              ''g'')
test'#                                ,''99.90''
test'#                            )+1988)::text ||''.31''
test'#                            ,''YYYY.MM.DD''
test'#                        )
test'#         ELSE ''9999-12-31''
test'#         END AS convert_date
test'# '
test-# LANGUAGE 'SQL' IMMUTABLE;
CREATE FUNCTION
test=#
test=# SELECT keiyaku_date, TO_DATE_KEIYAKU_DATE(keiyaku_date) FROM keiyaku ;
 keiyaku_date | to_date_keiyaku_date
--------------+-----------------------
 H24.7        | 2012-07-31
 H24.07       | 2012-07-31
 H24.9        | 2012-10-01
 H24.09       | 2012-10-01
 H24.10       | 2012-10-31
 H24.12       | 2012-12-31
 H24.01       | 2012-01-31
 なし         | 9999-12-31
 不明         | 9998-12-31
 H23.1        | 2011-01-31
(10 rows)

できた。

因みにIMMUTABLE はこのデータが10件20件ならどうでもいいですが、10万20万とかになってくると遅すぎて実用に耐えきれません。

従って式INDEXを貼る必要があるのですが、

http://www.postgresql.jp/document/9.2/html/sql-createfunction.html

IMMUTABLEは、データベースに対する変更を行わないこと、および、その関数に同じ引数値を与えた場合に常に同じ結果を返すことを示します。 これは、データベースを検索したり、引数リスト中に直接存在しない情報を使用したりしないということです。 このオプションが指定された場合、引数が全て定数である関数呼び出しは、即座に関数値と置き換えることができます。

というわけで、IMMUTABLEを指定し、式INDEXを貼りました。

test=# CREATE INDEX keiyaku_convert_date_idx ON keiyaku (TO_DATE_KEIYAKU_DATE(keiyaku_date));
CREATE INDEX
test=#

ホントはこれにはまだ続きがあって、現在の日付と比較してもう過ぎていれば、なし扱いにするとかって

要件もあったのですが、本題とずれるので、割愛してます。

まとめ

  • ちゃんと型があるものはその型で定義しましょう。後世の人(担当者)にバカにされます。
  • Excelの感覚でDBを使うのやめようぜ…
  • NULLはホントに撲滅したい。でもたまに使う。COUNT(CASE WHEN CLUMN = 1 THEN 1 ELSE NULL END)みたいな。

全然関係ないまとめでしたが、今回はこんな所で。

本記事でホントはこうすべきじゃないの?とかあれば教えて下さい

明日はhayamizさんです。性能測定関係とは…気になる…

Add Comments

名前
URL
 
  絵文字
 
 
livedoor プロフィール
Twitter
instagram(SnapWidget)
タグクラウド
  • ライブドアブログ