【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

【VBA】シート串刺しを扱うVBA

どうも、Bugです。

Twitterのタイムラインで、関数のシート串刺しが話題になってたのでなんとなくVBAで串刺し処理（文字結合）を作成しました。

実際の運用で考えると、Subtotal関数みたいに引数に応じて、 処理が変化するみたいな関数の方が楽し気かなとは思います。

まあ、実務では多分使わないんので、私的にはネタ的VBAかな。 というかCONCATも串刺し処理いけるんだね。

【VBA】～結合セルのアドレス指定～

どうも、Bugです。 前回の記事に使用したセル結合のアドレス範囲について説明したいと思います。 どうせ、次に書くときは自分も忘れているし

※結合セルのアドレス範囲指定は、レイアウト依存になりますので非推奨です。 個人的には、推奨Lv20くらい上の力が必要になるイメージでいます。 直せるなら、データやロジックから結合セルとアドレスをなくす方が吉です。

データ＞＞ロジック＞＞＞＞（絶望の淵）＞＞結合セルのアドレス範囲指定

前回の記事

buggaiqai.hatenablog.com

結合セルのアドレス取得

まず、アドレスを取得する場合、VBAでは基本Range.Addressを使いますよね。

結合セルのアドレス取得

B2:D4のセルが結合セルとした場合、
単独セルをSetされている場合、Range.Addressは単一範囲で帰ってくる。
（Range("B2").address = $B$2）
複数セルをSetされている場合、Range.Addressは複数範囲で帰ってくる。
（Range("B2:D4").address = $B$2:$D$4）

あ～なるほど、挙動的にはわかるよね。……いや怖いんだけど
プログラムの書き手は、処理のRangeの返り値が先頭で返しているか、範囲で返しているか認識しなきゃいけないの？
それは、めんどい。どっちかに合わせたい。明示的に分岐したい。

単一範囲で統一するなら
Range("B2:D4").Offset(0,0).address = $B$2
複数範囲で統一するなら
Range(単一セル).MergeArea.Address = $B$2:$D$4

とするのが楽な気がする。
個人的には単一範囲の方が扱いやすい。だって複数セルだとMergeArea異常起こすし……

Offsetの選択範囲

前回の記事では用いられている結合セルでのOffsetの仕様について触れていこうと思います。
（注1）たしか作成時に、Offsetの仕様についてdocsにも書いてあった気がするんだけど、見つからない。
どなたか知っている人がいらっしゃったら教えてください。

前回の処理では、結合セルにテキストをコピペするという仕様上、
結合セルから結合セルに移動する必要があります。
他の例だと、『結合されているヘッダー行で判定してから、処理をする』みたいなパターンもあり得ますね。

というわけで、ここでOffsetが出てくるわけですけど、
↓同じタイミングで、Offsetの指定位置だけ変えて実行してみます。

結合セルのOffsetの選択先範囲

こんな感じで、Offset(1,0)とOffset(2,0)の選択範囲は直感と反するような振る舞いをします。
というよりも、Offsetの仕様として起点セルの外枠に出るときだけ、結合セルとして扱うというような動きをしているようにみえます。
（ここの仕様がdocsのどこかに記述されている気がするんだけど、記載箇所わからない。どなたか教えてください。）

逆に言えば、結合セルが連続的・構造的であるなら
結合セルの起点をSetで取り直して、Offset(1,0)やOffset(0,1)で移動する方法もありかも

注釈

和風スパゲティさんの記事にOffsetについてより詳しい情報があった。 www.limecode.jp -1とかで戻るパターンもあるか～～。想定が足りてなかった。

戻るパターンで先頭セルを取得する

先頭セルはとれなくはない。…とれなくはないけど、普段からそんなアクロバティックなコードの書き方したくない。

【VBA】楽なコピペをめざして ～セル結合へのテキスト情報入力～

どうも、バグです。 今回、なぜかVBAのものが完成してたので、備忘録として記事を書きます。

目的

週末にエクセルをイジる機会があったんですが、コピペをした際にエラーではじかれました。 はい、皆さんダイスキ、セル結合です。 ……えっ、一行ごとにセル結合しているの？ いちいち皆、解除しながら作業しているの？めんどくない？ という思いもあって、タイミングよく以下のTogetterも公開されて、「やってみるしかないよな～」という気持ちで作成。 VBAerなら、セル範囲の指定が面白いと思うのでぜひともおすすめです。

ワクワクV036回目レンジのグループと組み合わせ方（ほえほえ@hoehoe1234さんまとめ） togetter.com

なので、今回のマクロは、「複数のセル結合している範囲に対して、コピーしてきたテキスト情報を出力、再結合」が目的となってます。

環境

Office365

処理概要

処理概要フロー
1. クリップボードからテキスト情報を配列で取得
2. エラー判定（選択範囲より大きいテキスト情報を格納していないかチェック）
3. 入力対象のセルの結合解除、テキスト入力、再結合

動作イメージ

動作としては、以下の通りとなります。
①結合セルを選択し、マクロ登録したボタンをクリック！

結合セルを選択し、隣のセルをコピペする

②コピーしていたテキスト情報が、結合セルの中に入力されます。なおかつ、結合もそのままです。

結合セルに、結合状態を維持したままテキスト情報が 入力

③もし、コピーしたテキストが、選択範囲よりも大きい場合

もし、テキスト情報が選択範囲よりも多い場合

④下の画面のようにエラーを返します。

もし、テキスト情報が選択範囲よりも多い場合エラーを返します

ソース

▶クリックで展開

Sub f_Stream_FromClipboard_ToMergedcells()
'@function: 結合セルへのテキスト結合
'@param   : クリップボードのテキスト情報
'@param   : 結合セルの選択
'@注意事項
'    *1 テキスト行より、小さい範囲には貼り付けできません。
'    *2 行方向にテキスト貼り付けをします。複数列にはテキスト貼り付けできません。
'    *3 マクロなので、Undo機能が無効化されます。誰かマクロ実行前のUndo履歴を保持する方法を教えてください。

'処理概要フロー
'   １．クリップボードからテキスト情報を配列で取得
'   ２．エラー判定（選択範囲より大きいテキスト情報を格納していないかチェック）
'   ３．入力対象のセルの結合解除、テキスト入力、再結合
    
    Dim CB As New DataObject 'クリップボード
    Dim txt_clip As Variant  'クリップボードが保持するテキスト情報(配列)  ＊１．インデックス＝0始まり
    Dim cnt_line             'テキスト情報の行数
    
    
'１．クリップボードからテキスト情報を取得
    With CB
        .GetFromClipboard
        txt_clip = Split(CB.GetText, vbCrLf, , vbBinaryCompare)
    End With
    cnt_line = UBound(txt_clip) + 1
    
    
'２．エラー判定（選択範囲より大きいテキスト情報を格納していないかチェック）

    Dim tmp_count_row: tmp_count_row = 0    ' 選択範囲の行数カウント（.Rows.Countでは結合が解除された状態でカウントされるため）
    Dim tmp_area As Range                   ' 結合セルの起点セル
        
    ' 選択範囲の行数カウント
    Set tmp_area = Selection.Cells(1, 1)
    
    '【条件式】
    '   @if 行を回して、選択範囲からはみ出るまで実行
    '   @if_True 行数カウント
    '【コラム】
    'Set tmp_area = tmp_area.Offset(1, 0)は結合セル対応のため
    Do Until (Application.Intersect(Selection, tmp_area.Rows(1).Cells(1).MergeArea) Is Nothing)
        tmp_count_row = tmp_count_row + 1
        Set tmp_area = tmp_area.Offset(1, 0)
    Loop
    
    ' エラー判定（選択範囲より大きいテキスト情報を格納していないか）
    ' -------------------------------------------------------------------------------
    '【エラー判定式】
    '   @err_if 選択範囲より大きいテキスト情報を格納していないか？
    '   @err_if_True(テキスト情報の方が大きい) 処理の終了
    If tmp_count_row < cnt_line Then
        MsgBox "選択範囲よりもクリップボードの情報が大きいです。選択範囲またはテキスト情報を修正してください。"
        Exit Sub
    End If
    
'３．入力対象のセルの結合解除、テキスト入力、再結合

    Dim x As Range        ' 分割後のテキスト入力セルを示す一時変数
    Dim idx: idx = 0      ' テキスト情報の配列要素を示す一時変数
    Dim tmp_merge_area    ' セル結合解除前にアドレス範囲を退避させる一時変数
    
    '【条件式】
    '   @for テキスト情報が最大要素数まで出力するまで
    Set x = Selection.Offset(0, 0).Cells(1, 1).MergeArea
    For idx = 0 To cnt_line - 1
        '  結合範囲の退避
        tmp_merge_area = x.Address
        x.UnMerge
        x.Cells(1).Value = txt_clip(idx)
        Range(tmp_merge_area).merge
        Set x = x.Offset(1, 0).Cells(1, 1).MergeArea
    Next
    
End Sub

今後の課題

……ソースの乗っけ方わかりません……？ なんかコメントが外れたりしてます？？？

なんか……もっと勉強してきます。

後、VBAに関しては、このソース、 テキスト情報をコピーしないとエラー停止してしまうので、そこは大きな課題ですね。

あと、Undo機能をちゃんと搭載できれば、普通に [Ctrl] + [V] で結合セルか判断して実行できるので有益なんだけどな(´・ω・｀)

このブログは後編みたいな形で一応の解説をつけるつもりです。 もし、質問があればコメントいただけると幸いです。（文章読みづらいでも可）

【VBAネタだし】仕様書情報抽出【いつか頑張る】

どうも、Bugです。

季節の挨拶

最近、妙に暑くないです？もう5月くらいの奇行もとい気候がないですか？

本題？？

最近、この気候で弱っているのか、記憶力が落ちてきてまして。 どの仕様書にどんな情報が書いてあったかというのが覚えられないんですよね。

…じゃあくっつけるか

覚えられないなら、全仕様書くっつけるしかないですよね。

• WHO ：誰が更新しているか？誰が使用しているか？
• WHERE：どこの仕様書にどんなことが書いてあるか？
• WHEN ：最新の情報を取得しているか？どんな経緯でこんな仕様になったか？
• WHAT ：どんなことを説明しているか？
• HOW ：どのようにしているか？

目下の課題としては、以下の感じかな？

1. テンプレートとかから抽出内容を再編できる。
2. 更新を短時間で可能
3. ヘッダーから抽出する「繰り返し項目」「項目」を設定できること

とりあえず、やれるかやってみますか～

追記1🤔 検索1つで探せる環境にしたいだけだから、よく考えたら、シート統合の方がそれっぽく動くかもしれない。

とりあえず開設〜

はい～、どうもMr.bugです。

ブログ開設をしました。
内容としては、以下の感じを考えています。
○読書の感想
（内容の要約はしないよ〜）
○適当な日常（中身なし）
○VBA（でも、コレはほとんどしないかな〜）

各内容の説明でもしましょうか。

読書の感想
○どんな人に読んでほしいか？
○それを読んだ感想
を書くつもりです。

内容の要約は、原本見てほしいから公開するつもりはないよ〜。
困った時に見てほしいし、自分でも思い出すために、『困ったの事例までに留めておこうかな』と思ってます。

という訳で、『ボクと一緒に作者の養分になってよ』という気持ちで書いていきます。

適当な日常
日常の内容を適当に書いていくかもしれないです。
私の日常をコンテンツに出来る気はしないから、まあ、あんまり書かないとは思いませんが……

VBA
本流とは別に、VBAを趣味でやっているので開発中のをブログに上げるかもしれないです。
ただ、VBAとかを真面目に書こうとすると、調査、調査、調査の雨嵐なのでコストが高いのよねー。まあ、実力がつくのでいいけど、頻度は確実に低いです。リソースは多くないです。

今後、適当に色々でっちあげながら書いていくと思います。
以後よろしくおねがいします。