【SQL】データ整形ツールとしてのDB/SQL
どうも、Mr.bugです。
背景
Twitterのタイムラインに、このような話題が飛んできました。
『非正規化どころか、項目を横にもっている かつ カンマを項目にもっているデータの整形を皆どうやっている?』
……だいぶ、元の話題の原形とどめてませんね。 でも要約するとそんな感じです。
話題の元のサンプルを元に作成したデータが以下の画像です。 無くはない感じのデータ項目の持ち方ですね。(例示サンプルは頭おかしいくらいの項目数がありましたが……)
これを正規化された下のようなデータに変換したいという内容ですね。 では、今回はSQLで無理やり整形してみましょう。
今回の話題の進め方
①抽出用SQL (※今回DDLに関しては、データ通りに文字列型で作成しただけなので省略します。) ②SQLで整形する利点 ③SQLのおすすめ書籍
①抽出用SQL
/*処理イメージ(Postgres) 1.カラムのみのテーブルDDL(table_gop)を作成 2.どの項目を出力するか判断するseq(連番サブクエリ)を付与 3.seqの数値に合わせて、出力のカラムをCaseで分岐 4.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で整形する利点をあげていきます。 1.コード中に存在する要素の存在がすくない。 今回のコードを、別のプログラミング言語でやろうとすると、以下の処理フローになると思います。 行を選択→顧客IDを変数に格納→購入ごとにループを回す→購入商品が複数あれば複数の明細を作成 →明細がある場合リストに格納→次の行へ進む
SQLでやる場合、どうでしょうか。 データ投入→ 作業列(goods_buy、date_buy)のCase定義→連番作成→購入商品がある場合出力 →CSV区切りで展開
多分、上記のようなフローになると思います。(※もっと上手くやる方法があれば教えてください。) それぞれのフローを比較すると、SQLのフローにはない概念がありますね。 ●多重ループ(行のループ→明細のループ→(購入商品が複数ある場合、明細の作成)) ●リスト ●変数
プログラムにおいて、順接・分岐・反復は重要な要素であるといわれます。 反面、分岐や反復は実行タイミングを誤るとバグの原因になります。まあ、当たり前の話ですけど。 特に、反復においては再度、要素を扱うため、初期化がうまく行われていない場合などのようにバグの原因になりやすいです。
そこにおいて、SQLのクエリ単位での条件判定は、強みになります。 今回の処理のように、ループや変数といったものを減らし簡易的に書くことができます。 ●処理フロー中の反復を分岐に変更する。 ●処理フロー中の反復を削ることができる。 ●処理フロー中の変数をけずることができる。
2.より担当者に近い目線で記述ができる。 データ投入→ 作業列(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】~結合セルのアドレス指定~
どうも、Bugです。 前回の記事に使用したセル結合のアドレス範囲について説明したいと思います。 どうせ、次に書くときは自分も忘れているし
※結合セルのアドレス範囲指定は、レイアウト依存になりますので非推奨です。 個人的には、推奨Lv20くらい上の力が必要になるイメージでいます。 直せるなら、データやロジックから結合セルとアドレスをなくす方が吉です。
データ>>ロジック>>>>(絶望の淵)>>結合セルのアドレス範囲指定
前回の記事
結合セルのアドレス取得
まず、アドレスを取得する場合、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(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. 入力対象のセルの結合解除、テキスト入力、再結合
動作イメージ
動作としては、以下の通りとなります。
①結合セルを選択し、マクロ登録したボタンをクリック!
②コピーしていたテキスト情報が、結合セルの中に入力されます。なおかつ、結合もそのままです。
③もし、コピーしたテキストが、選択範囲よりも大きい場合
④下の画面のようにエラーを返します。
ソース
今後の課題
……ソースの乗っけ方わかりません……? なんかコメントが外れたりしてます???
なんか……もっと勉強してきます。
後、VBAに関しては、このソース、 テキスト情報をコピーしないとエラー停止してしまうので、そこは大きな課題ですね。
あと、Undo機能をちゃんと搭載できれば、普通に [Ctrl] + [V] で結合セルか判断して実行できるので有益なんだけどな(´・ω・`)
このブログは後編みたいな形で一応の解説をつけるつもりです。 もし、質問があればコメントいただけると幸いです。(文章読みづらいでも可)
【VBAネタだし】仕様書情報抽出【いつか頑張る】
どうも、Bugです。
季節の挨拶
最近、妙に暑くないです?もう5月くらいの奇行もとい気候がないですか?
本題??
最近、この気候で弱っているのか、記憶力が落ちてきてまして。 どの仕様書にどんな情報が書いてあったかというのが覚えられないんですよね。
…じゃあくっつけるか
覚えられないなら、全仕様書くっつけるしかないですよね。
- WHO :誰が更新しているか?誰が使用しているか?
- WHERE:どこの仕様書にどんなことが書いてあるか?
- WHEN :最新の情報を取得しているか?どんな経緯でこんな仕様になったか?
- WHAT :どんなことを説明しているか?
- HOW :どのようにしているか?
目下の課題としては、以下の感じかな?
- テンプレートとかから抽出内容を再編できる。
- 更新を短時間で可能
- ヘッダーから抽出する「繰り返し項目」「項目」を設定できること
とりあえず、やれるかやってみますか~
追記1🤔 検索1つで探せる環境にしたいだけだから、よく考えたら、シート統合の方がそれっぽく動くかもしれない。
とりあえず開設〜
はい~、どうもMr.bugです。
ブログ開設をしました。
内容としては、以下の感じを考えています。
○読書の感想
(内容の要約はしないよ〜)
○適当な日常(中身なし)
○VBA(でも、コレはほとんどしないかな〜)
各内容の説明でもしましょうか。
読書の感想
○どんな人に読んでほしいか?
○それを読んだ感想
を書くつもりです。
内容の要約は、原本見てほしいから公開するつもりはないよ〜。
困った時に見てほしいし、自分でも思い出すために、『困ったの事例までに留めておこうかな』と思ってます。
という訳で、『ボクと一緒に作者の養分になってよ』という気持ちで書いていきます。
適当な日常
日常の内容を適当に書いていくかもしれないです。
私の日常をコンテンツに出来る気はしないから、まあ、あんまり書かないとは思いませんが……
VBA
本流とは別に、VBAを趣味でやっているので開発中のをブログに上げるかもしれないです。
ただ、VBAとかを真面目に書こうとすると、調査、調査、調査の雨嵐なのでコストが高いのよねー。まあ、実力がつくのでいいけど、頻度は確実に低いです。リソースは多くないです。
今後、適当に色々でっちあげながら書いていくと思います。
以後よろしくおねがいします。