SQLiteでメールのDateヘッダーを変換
SQLite3では「Tue, 3 Aug 1999 23:10:00 +0900」とか対応日付関数も正規表現置換も出来なくて厄介…
「過去のメールからメアドをGREPし、同時にDateヘッダーからやり取りした日付も取得する」という作業をしていました。メールデータはプレーンテキスト、抽出にはとりあえず適当なGREPを使い【ファイルパス、Dateヘッダー、From|To|Cc|BCCヘッダー】を抽出します。*1
抽出済みのデータがこんな感じです。
SELECT file, date_, mail FROM den8wk1_view;
file | date_ | |
---|---|---|
Y:\Sample\Denshin8\IN\0000.TXT | Tue, 3 Aug 1999 23:10:00 +0900 | From: mag2 |
Y:\Sample\Denshin8\IN\0000.TXT | Tue, 3 Aug 1999 23:10:00 +0900 | To: pochi-p@example.com |
目的はメアドの使用順の調査なので、ここから日付順にソートしたりしたい訳です。しかしRFC822で定義されている書式はソート処理に不向きな形式の文字列です。
From:フィールドのフォーマットは,RFC822で定義されている次のフォーマットに従います。
[daytime,] d mon yy hh:mm[:ss] zone
http://itdoc.hitachi.co.jp/manuals/3020/30203D1390/0173.htm
[daytime,] dd mon yy hh:mm[:ss] zone
[daytime,] d mon yyyy hh:mm[:ss] zone
[daytime,] dd mon yyyy hh:mm[:ss] zone
(……FromではなくDateの気がしますが、まあtypoは無視して続けます。)
最終的にソート&閲覧しやすくする為に下記の結果が欲しいです。
file | date_ | yyyy | mm | dd | |
---|---|---|---|---|---|
Y:\Sample\Denshin8\IN\0000.TXT | Tue, 3 Aug 1999 23:10:00 +0900 | From: mag2 |
1999 | 08 | 03 |
Y:\Sample\Denshin8\IN\0000.TXT | Tue, 3 Aug 1999 23:10:00 +0900 | To: pochi-p@example.com | 1999 | 08 | 03 |
DBによっては変換関数があるかもしれませんが、今回使用するのはSQLite3です。標準で実装されている日付関数strftimeでは上記形式に対応していないようです。
正規表現で適当に加工しようにも、SQLite3には正規表現の検索/置換機能は現在実装されていない様です。
という事はSQLite3では自作の関数を別途作る必要があるのでしょうか…。正直面倒過ぎるのでもっと楽に解決したいです。
という訳で、それを力業で何とかするのが今回の課題。さっそく実際のSQLを見ていきましょう。
(1)CASEとLIKEで何とかする
偉い人も言ってます。「正規表現が使えないならLIKEを使えば良いじゃない」と。まあLIKEだけでは置換出来ないのでCASEを使って条件分けしてしまいましょう。力業 is Power!
SELECT file , date_ , mail , CASE WHEN date_ LIKE '% 2000 %' THEN '2000' WHEN date_ LIKE '% 2001 %' THEN '2001' WHEN date_ LIKE '% 2002 %' THEN '2002' WHEN date_ LIKE '% 2003 %' THEN '2003' WHEN date_ LIKE '% 2004 %' THEN '2004' WHEN date_ LIKE '% 2005 %' THEN '2005' WHEN date_ LIKE '% 2006 %' THEN '2006' WHEN date_ LIKE '% 2007 %' THEN '2007' WHEN date_ LIKE '% 2008 %' THEN '2008' WHEN date_ LIKE '% 2009 %' THEN '2009' WHEN date_ LIKE '% 2010 %' THEN '2010' WHEN date_ LIKE '% 2011 %' THEN '2011' WHEN date_ LIKE '% 2012 %' THEN '2012' WHEN date_ LIKE '% 2013 %' THEN '2013' WHEN date_ LIKE '% 2014 %' THEN '2014' WHEN date_ LIKE '% 2015 %' THEN '2015' WHEN date_ LIKE '% 2016 %' THEN '2016' WHEN date_ LIKE '% 2017 %' THEN '2017' WHEN date_ LIKE '% 00 __:%' THEN 2000 WHEN date_ LIKE '% 01 __:%' THEN 2001 WHEN date_ LIKE '% 02 __:%' THEN 2002 WHEN date_ LIKE '% 03 __:%' THEN 2003 WHEN date_ LIKE '% 04 __:%' THEN 2004 WHEN date_ LIKE '% 05 __:%' THEN 2005 WHEN date_ LIKE '% 06 __:%' THEN 2006 WHEN date_ LIKE '% 07 __:%' THEN 2007 WHEN date_ LIKE '% 08 __:%' THEN 2008 WHEN date_ LIKE '% 09 __:%' THEN 2009 WHEN date_ LIKE '% 10 __:%' THEN 2010 WHEN date_ LIKE '% 11 __:%' THEN 2011 WHEN date_ LIKE '% 12 __:%' THEN 2012 WHEN date_ LIKE '% 13 __:%' THEN 2013 WHEN date_ LIKE '% 14 __:%' THEN 2014 WHEN date_ LIKE '% 15 __:%' THEN 2015 WHEN date_ LIKE '% 16 __:%' THEN 2016 WHEN date_ LIKE '% 17 __:%' THEN 2017 END yyyy , CASE WHEN date_ LIKE '%Jan%' THEN '01' WHEN date_ LIKE '%Feb%' THEN '02' WHEN date_ LIKE '%Mar%' THEN '03' WHEN date_ LIKE '%Apr%' THEN '04' WHEN date_ LIKE '%May%' THEN '05' WHEN date_ LIKE '%Jun%' THEN '06' WHEN date_ LIKE '%Jul%' THEN '07' WHEN date_ LIKE '%Aug%' THEN '08' WHEN date_ LIKE '%Sep%' THEN '09' WHEN date_ LIKE '%Oct%' THEN '10' WHEN date_ LIKE '%Nov%' THEN '11' WHEN date_ LIKE '%Dec%' THEN '12' END mm , CASE WHEN date_ LIKE '%, 1 %' THEN '01' WHEN date_ LIKE '%, 2 %' THEN '02' WHEN date_ LIKE '%, 3 %' THEN '03' WHEN date_ LIKE '%, 4 %' THEN '04' WHEN date_ LIKE '%, 5 %' THEN '05' WHEN date_ LIKE '%, 6 %' THEN '06' WHEN date_ LIKE '%, 7 %' THEN '07' WHEN date_ LIKE '%, 8 %' THEN '08' WHEN date_ LIKE '%, 9 %' THEN '09' WHEN date_ LIKE '%, 01 %' THEN '01' WHEN date_ LIKE '%, 02 %' THEN '02' WHEN date_ LIKE '%, 03 %' THEN '03' WHEN date_ LIKE '%, 04 %' THEN '04' WHEN date_ LIKE '%, 05 %' THEN '05' WHEN date_ LIKE '%, 06 %' THEN '06' WHEN date_ LIKE '%, 07 %' THEN '07' WHEN date_ LIKE '%, 08 %' THEN '08' WHEN date_ LIKE '%, 09 %' THEN '09' WHEN date_ LIKE '%, 10 %' THEN '10' WHEN date_ LIKE '%, 11 %' THEN '11' WHEN date_ LIKE '%, 12 %' THEN '12' WHEN date_ LIKE '%, 13 %' THEN '13' WHEN date_ LIKE '%, 14 %' THEN '14' WHEN date_ LIKE '%, 15 %' THEN '15' WHEN date_ LIKE '%, 16 %' THEN '16' WHEN date_ LIKE '%, 17 %' THEN '17' WHEN date_ LIKE '%, 18 %' THEN '18' WHEN date_ LIKE '%, 19 %' THEN '19' WHEN date_ LIKE '%, 20 %' THEN '20' WHEN date_ LIKE '%, 21 %' THEN '21' WHEN date_ LIKE '%, 22 %' THEN '22' WHEN date_ LIKE '%, 23 %' THEN '23' WHEN date_ LIKE '%, 24 %' THEN '24' WHEN date_ LIKE '%, 25 %' THEN '25' WHEN date_ LIKE '%, 26 %' THEN '26' WHEN date_ LIKE '%, 27 %' THEN '27' WHEN date_ LIKE '%, 28 %' THEN '28' WHEN date_ LIKE '%, 29 %' THEN '29' WHEN date_ LIKE '%, 30 %' THEN '30' WHEN date_ LIKE '%, 31 %' THEN '31' WHEN date_ LIKE '1 %' THEN '01' WHEN date_ LIKE '2 %' THEN '02' WHEN date_ LIKE '3 %' THEN '03' WHEN date_ LIKE '4 %' THEN '04' WHEN date_ LIKE '5 %' THEN '05' WHEN date_ LIKE '6 %' THEN '06' WHEN date_ LIKE '7 %' THEN '07' WHEN date_ LIKE '8 %' THEN '08' WHEN date_ LIKE '9 %' THEN '09' WHEN date_ LIKE '01 %' THEN '01' WHEN date_ LIKE '02 %' THEN '02' WHEN date_ LIKE '03 %' THEN '03' WHEN date_ LIKE '04 %' THEN '04' WHEN date_ LIKE '05 %' THEN '05' WHEN date_ LIKE '06 %' THEN '06' WHEN date_ LIKE '07 %' THEN '07' WHEN date_ LIKE '08 %' THEN '08' WHEN date_ LIKE '09 %' THEN '09' WHEN date_ LIKE '10 %' THEN '10' WHEN date_ LIKE '11 %' THEN '11' WHEN date_ LIKE '12 %' THEN '12' WHEN date_ LIKE '13 %' THEN '13' WHEN date_ LIKE '14 %' THEN '14' WHEN date_ LIKE '15 %' THEN '15' WHEN date_ LIKE '16 %' THEN '16' WHEN date_ LIKE '17 %' THEN '17' WHEN date_ LIKE '18 %' THEN '18' WHEN date_ LIKE '19 %' THEN '19' WHEN date_ LIKE '20 %' THEN '20' WHEN date_ LIKE '21 %' THEN '21' WHEN date_ LIKE '22 %' THEN '22' WHEN date_ LIKE '23 %' THEN '23' WHEN date_ LIKE '24 %' THEN '24' WHEN date_ LIKE '25 %' THEN '25' WHEN date_ LIKE '26 %' THEN '26' WHEN date_ LIKE '27 %' THEN '27' WHEN date_ LIKE '28 %' THEN '28' WHEN date_ LIKE '29 %' THEN '29' WHEN date_ LIKE '30 %' THEN '30' WHEN date_ LIKE '31 %' THEN '31' END dd FROM den8wk1_view
とりあえず上記で2000年以降2017年までの日付に関しては対応可能になりました。
年部分は4桁であれば
WHEN date_ LIKE '% 2000 %' THEN '2000'
…で簡単に変換出来ます。列挙が面倒ですけど。年2桁は位置特定を後続の時刻内区切り文字":"を利用して列挙すればなんとかなります。
WHEN date_ LIKE '% 50 __:%' THEN 1950 --省略-- WHEN date_ LIKE '% 99 __:%' THEN 1999 WHEN date_ LIKE '% 00 __:%' THEN 2000 --省略-- WHEN date_ LIKE '% 49 __:%' THEN 2049
月に関しては月を表す3文字を12種類列挙するだけですね。簡単です。
WHEN date_ LIKE '%Jan%' THEN '01'
日付に関してはこちらも頭ゼロ埋め2桁ゼロ埋め無しかの問題と、年の方が2桁の場合の対処が必要です。位置特定する為に曜日直後のカンマか、曜日がない前提で位置指定をして何とかしましょう。
WHEN date_ LIKE '%, 1 %' THEN '01' --省略-- WHEN date_ LIKE '%, 9 %' THEN '09' WHEN date_ LIKE '%, 01 %' THEN '01' --省略-- WHEN date_ LIKE '%, 31 %' THEN '31' WHEN date_ LIKE '1 %' THEN '01' --省略-- WHEN date_ LIKE '9 %' THEN '09' WHEN date_ LIKE '01 %' THEN '01' --省略-- WHEN date_ LIKE '31 %' THEN '31'
この方法でやればSQLite3以外でも基本的なCASEやLIKEを使えるDBならほぼ実現可能です。
ただしパフォーマンスは保証出来かねます。LIKEだしSQL長過ぎだし遅くなる要素満載です。
そして残念ながらSQLite3でも件数が多いと「メモリが足りません」とエラーになるので、残念ながら条件を絞る等の工夫が必要になってしまいます。
数百件程度は大丈夫でしたが92911件のデータでは使っていたツール*2を巻き込んで不安定になってしまいました。
(2)汎用的な形にする(条件と値の変換テーブルを用意しJOIN)
件数制限の問題と、SQL文が長くなる問題を何とかする為改造します。
結局のところWHENで描いてるのは「判断条件と取得する値の組み合わせ」だけなので、それを変換用テーブルとして登録しちゃいましょう。
テーブル定義して
create table rfc822date( type varcahr(15) , liketext varchar (30) , valtext varchar (15) )
type | liketext | valtext |
---|---|---|
type | liketext | valtext |
yyyy | % 1950 % | 1950 |
省略 | 省略 | 省略 |
yyyy | % 1999 % | 1999 |
yyyy | % 2000 % | 2000 |
省略 | 省略 | 省略 |
yyyy | % 2049 % | 2049 |
yyyy | % 50 __:% | 1950 |
省略 | 省略 | 省略 |
yyyy | % 99 __:% | 1999 |
yyyy | % 00 __:% | 2000 |
省略 | 省略 | 省略 |
yyyy | % 49 __:% | 2049 |
mm | %Jan% | 01 |
省略 | 省略 | 省略 |
mm | %Dec% | 12 |
dd | %, 1 % | 01 |
省略 | 省略 | 省略 |
dd | %, 9 % | 09 |
dd | %, 01 % | 01 |
省略 | 省略 | 省略 |
dd | %, 31 % | 31 |
dd | 1 % | 01 |
省略 | 省略 | 省略 |
dd | 9 % | 09 |
dd | 01 % | 01 |
省略 | 省略 | 省略 |
dd | 31 % | 31 |
な感じでデータ登録します。省略箇所はただの連続データなので説明要りませんよね? あとはSQLをJOINの形式に置き換えです。
SELECT file , date_ , mail , y.valtext yyyy , m.valtext mm , d.valtext dd FROM den8wk1_view s LEFT OUTER JOIN rfc822date y ON s.date_ LIKE y.liketext AND y.type = 'yyyy' LEFT OUTER JOIN rfc822date m ON s.date_ LIKE m.liketext AND m.type = 'mm' LEFT OUTER JOIN rfc822date d ON s.date_ LIKE d.liketext AND d.type = 'dd'
こちらの方式であれば、92911件のデータであっても問題なく取得できました。検索時間は結構かかってしまいますけどね。*3
毎回実行は遅くなりますので、あらかじめ変換誤の値を格納する列を設けたテーブルに用意し、1度SELECT INTO等で変換値を保存してやる事で実用上問題無くなると思います。
以上、CASEとLIKEとJOINで遊んでみるコーナーでした。
*1:複数件宛先のあるメールも存在しましたが、その辺は事前に加工や情報絞り込みで排除しています。
*2:みんな大好きA5:SQL Mk-2! http://a5m2.mmatsubara.com/
*3:DBによっては前述のCASEの方が早かったりするかもしれません。その辺は実装次第ですね。