【SQL】データ整形ツールとしてのDB/SQL

どうも、Mr.bugです。

背景

Twitterのタイムラインに、このような話題が飛んできました。

『非正規化どころか、項目を横にもっている かつ カンマを項目にもっているデータの整形を皆どうやっている？』

……だいぶ、元の話題の原形とどめてませんね。 でも要約するとそんな感じです。

話題の元のサンプルを元に作成したデータが以下の画像です。 無くはない感じのデータ項目の持ち方ですね。（例示サンプルは頭おかしいくらいの項目数がありましたが……）

提供された非正規データ

これを正規化された下のようなデータに変換したいという内容ですね。 では、今回はSQLで無理やり整形してみましょう。

抽出したい正規化データ

今回の話題の進め方

①抽出用SQL （※今回DDLに関しては、データ通りに文字列型で作成しただけなので省略します。） ②SQLで整形する利点 ③SQLのおすすめ書籍

①抽出用SQL

/*処理イメージ（Postgres）
１．カラムのみのテーブルDDL（table_gop）を作成
２．どの項目を出力するか判断するseq（連番サブクエリ）を付与
３．seqの数値に合わせて、出力のカラムをCaseで分岐
４．regexp_split_to_tableでカンマ区切りのレコードを分割
*/
SELECT
    convert.id_kokyaku
    , regexp_split_to_table(goods_buy, ',')   AS goods_buy
    , to_date(date_buy,'YYYY/MM/DD')      AS date_buy
    , num
FROM
    (SELECT
          id_kokyaku                                -- 顧客ID
        , seq.num                                   -- 出力列番号
        , -- 購入商品
        (CASE WHEN seq.num = 1 THEN goods_buy_1                               
                    WHEN seq.num = 2 THEN goods_buy_2
                    WHEN seq.num = 3 THEN goods_buy_3
                    WHEN seq.num = 4 THEN goods_buy_4
            END)  AS goods_buy
        , -- 購入日付
         (CASE WHEN seq.num = 1 THEN date_buy_1                              
                    WHEN seq.num = 2 THEN date_buy_2
                    WHEN seq.num = 3 THEN date_buy_3
                    WHEN seq.num = 4 THEN date_buy_4
            END) AS date_buy
    FROM
        PUBLIC.table_gop 
    Cross join
        -- 項目の作業列を付与
        (select generate_series(1,4) AS num ) AS seq
    ) convert

WHERE 
    -- 商品購入履歴があるレコードのみ出力（NULLでも空文字でもはじける様にcoalesce）
    coalesce(goods_buy,'') <> ''
    
ORDER BY
id_kokyaku,
to_date(date_buy,'YYYY/MM/DD'),
goods_buy

②SQLで整形する利点

個人的に思う、SQLで整形する利点をあげていきます。 １．コード中に存在する要素の存在がすくない。 今回のコードを、別のプログラミング言語でやろうとすると、以下の処理フローになると思います。 行を選択→顧客IDを変数に格納→購入ごとにループを回す→購入商品が複数あれば複数の明細を作成 →明細がある場合リストに格納→次の行へ進む

SQLでやる場合、どうでしょうか。 データ投入→ 作業列（goods_buy、date_buy）のCase定義→連番作成→購入商品がある場合出力 →CSV区切りで展開

多分、上記のようなフローになると思います。（※もっと上手くやる方法があれば教えてください。） それぞれのフローを比較すると、SQLのフローにはない概念がありますね。 ●多重ループ（行のループ→明細のループ→（購入商品が複数ある場合、明細の作成）） ●リスト ●変数

プログラムにおいて、順接・分岐・反復は重要な要素であるといわれます。 反面、分岐や反復は実行タイミングを誤るとバグの原因になります。まあ、当たり前の話ですけど。 特に、反復においては再度、要素を扱うため、初期化がうまく行われていない場合などのようにバグの原因になりやすいです。

そこにおいて、SQLのクエリ単位での条件判定は、強みになります。 今回の処理のように、ループや変数といったものを減らし簡易的に書くことができます。 ●処理フロー中の反復を分岐に変更する。 ●処理フロー中の反復を削ることができる。 ●処理フロー中の変数をけずることができる。

２．より担当者に近い目線で記述ができる。 データ投入→ 作業列（goods_buy、date_buy）のCase定義→連番作成→購入商品がある場合出力 →CSV区切りで展開

このSQLのフローですが、作業列のCase定義→連番作成を除けば、人間の認知に近い記述ができていると
思いませんか？業務担当者との目線が近いほど、実装のコストが下がります。
トレーニング自体は中々大変ですが、クイズ感覚でやってみてはいかがでしょうか？

逆に、SQLで処理を行うデメリットは大量にあります。 まず、修正コストが高いです。 条件が全部直書きみたいな状態なので、「これはどこを修正すればいいのか？」という調査のコストが特に高いです。条件の組み合わせがよろしくないので、バグになるというのもよくあります。 あと、テーブル内のデータに依存しており、Assertや例外処理などの機能が強くない点です。 なので、複雑な処理をSQLで書こうとすると、間違えていることにすら気づかないことがよくあります。 ですので複雑な処理を含む場合、SQLだけで完結させようとするのは悪手となってしまいます。

そもそも、SQL自体がそれそのもので完結するような発展の仕方はしていないと認識しています。それは完結できるとしてもです。 SQLは、DBの恩恵を受けて、プログラミング言語 +α の相互作用を生み出す、そんな言語だと認識していただければ幸いです。

………あれ？最終的になにを言いたいんだろう。私…？？

③おすすめ書籍

SQLを学んでいて、おもしろかった書籍です。 SQLの思想や今回やった内容のやり方などが載っているので見てみると楽しいと思います。

達人に学ぶ SQL徹底指南書（著：ミック） https://www.amazon.co.jp/%E9%81%94%E4%BA%BA%E3%81%AB%E5%AD%A6%E3%81%B6-SQL%E5%BE%B9%E5%BA%95%E6%8C%87%E5%8D%97%E6%9B%B8-CodeZine-BOOKS-%E3%83%9F%E3%83%83%E3%82%AF/dp/4798115169