抽出でSQLを書くときに考えていること

自分の思考を文章化してみる

SQLを使い始めてちょうど１０年になった。その間には随分たくさんのクエリを書いてきたが、ちゃんとした師匠がいたわけでもなければ同じような仕事を同じレベルでやっている同僚もほとんどいなかったのでほぼ全部独学でやってきた。

これでいいのか未だにわからないので、自分がSQLでデータ抽出をしている際の思考や行動を文章化して公開してみる。是非いろいろと突っ込んでいただけると嬉しい。

何を抽出するかを決める

目的無きデータ分析は無駄であるでの話と同じで、とりあえず何かを出そうと手を動かし始めてもどこにもたどり着けない。なので最初に「何を抽出するか」を決める。

この段階ではどういったデータをどう集計する、といった詳細には立ち入らず、「知りたいことのために何があればよいか」だけを考える。

依頼を受けてデータ抽出を行う場合はここにはどうヒアリングするかや正しくない依頼にどう対応するかといった話もあるが、今回はそこには触れずに何を抽出するかが決まったところからの話をする。

アウトプットに必要なデータは何かを考え、データを確認する

アウトプットが決まったらそこに至るまでの道を考えるために、まずスタートである「どういったデータが必要か」を考える。

その上で実際にデータが存在してちゃんと使えるか、データの内容はアウトプットするのに十分かを確認し、足りなければ集める手配をする。

この段階でデータが手に入らないとか、汚すぎて使えないことが判ることはよくある。その場合は内容を変更したり、依頼なら期限をずらすかそれが無理なら期限内でどこまでならできるかなどを話し合う。

外部からのデータは先に準備に動く

すでにDBに入っており使える状態であればいいが、必ずしもそうとは限らない。社内のデータであればいつまでに使えるか、あるいはダメかなどはわりとすぐわかるので次の動きが取りやすいが、さらに外部からデータを集めて取り込んだりするとなると何かとトラブルに見舞われやすい。

・データが大量で取り込みがうまくいかない
・送ってもらったデータが足りない、欠損している
・いろいろな手続きがあってちょっと先になりそう
・神Excel到来
・やっぱり無理でした

と自分ではコントロールできないところでいろいろ起きるので、早め早めに対応する。

アウトプットの形を考える

どういった形でアウトプットするかも他の人に渡す時は工数に影響が出るため先に決めておく。

・ダッシュボードにする
・全部ないしは途中まで集計する
・整理したデータをCSVもしくはSSにする

ダッシュボードや集計表ならどの単位まで集計するかやどこまでドリルダウンするかも併せて検討。

データからアウトプットに至るまでの大まかな流れを考える

データとアウトプットが揃ったら、その間を結ぶ道を考える。この段階ではあまり詳細に立ち入らずに

「テーブルAとテーブルBでこのデータを作ったらそこにテーブルCをFULL JOINしてDを作る」
「テーブルEからこの条件で抜いてきたFとDにLEFT JOIN」
「これで元のテーブルができるので後はこの単位で集計すればいけるかな」

ぐらいのざっくりしたレベルで考えている。各段階での細かい処理は実際にクエリを書きながらで間に合うが、大まかな流れは先に掴んでおく。

２・３つのテーブルで数ステップぐらいなら頭の中で出来るが、それ以上になったら紙に書いたりして整理している。

やらなくてもよほど複雑なことをしているのでない限りそのうちアウトプットにはたどり着けるのだが、無駄な遠回りをして余計に時間が掛かったりクエリが複雑になってしまえば後で修正が必要になった場合が大変だ。

準備で大体決まると思っている

ここまでの２割ぐらいの時間をかけた準備で８割ぐらい決まると思っている。データの確認のためを除き抽出のためのクエリには一切触れていない。

もちろん大まかな流れは合っていても実際に書いてみるとやはり細かいところで齟齬が出る。特にあると思っていたデータが確認漏れで無かったりおかしかったりということは本当によくある。

なので準備でしっかり方向性を決めることも、実際に手を動かして実現することも両方大事。

SQLの言葉に変換する

データの準備が出来たらここでようやく抽出のためのクエリを書く。ここまでで流れは大体定まっているので、あとはそれをSQLの言葉に変換していく。

　　こういうデータが欲しい　
→　このデータをこう加工したらよさそう
→　SQLだとこの関数を使えばいいのでは
→　SQLで書いてみる

という流れを個別に行っている。いきなりSQLで書こうとしてもあまりうまくいかないのは自分のスキルのせいなのか別の理由なのかは不明。

あと関数ありきだと簡単なことをめんどくさくやりがち。ISNULLで済むのにCASE文を使ったり。

１段階書いたら正しいかを確かめる

複雑なことは段階を分けて考えたり実行することは大切でSQLも同じ。しかもSQLの場合は

・WHEREで絞り込んだテーブルとWITHで処理したテーブルを結合する
・条件分岐を使って新しいカラムを作る
・新しいカラムを使って集計する
・集計結果を絞り込む

などを１つのクエリで行うので全部書いて実行してから間違えに気づいてもどこで間違えたのかが特定しにくいし、エラーにならないけれども意図しない結果になっていることにすら気づかないかもしれない。

なのでできるだけ１段階づつ意図した結果になっているかを確かめながら書いている。特に絞り込みとJOINは要注意。その他にも重複に気づかなかったり、表記ゆれで集計がうまく行かなかったりでよく間違える。

できるだけWITH句で切り分ける

サブクエリを入れ子であちこちで使っているとクエリが複雑怪奇になる上に途中を確かめるのも大変なのでできるだけWITHを利用して切り出すようにしている。

理想的にはWITHで１つの基礎になるテーブル（CSVで出力してExcelやSSでいじるローデータとして使えるレベルのもの）までを作って最後のSELECT文は簡単な集計ぐらいにできるとすっきりする。

後述するがデータの追加依頼が発生した時にこのテーブルを経由しているかどうかで全然違う。

サブクエリを使う基準

ただしWITHを書きまくるとそれはそれで長くなるのでサブクエリと併用している。サブクエリにする基準は１行で書けること。

なので「SELECT カラム少し FROM テーブル WHERE 条件１つ」ぐらいで書けるならサブクエリに入れる。これより長い場合はWITHを使っている。

JOINする場合は大体先にWITHの中で先に処理しておく。そうすると「JOIN テーブル名」だけで書けるのですっきりする。

追加修正があることを見越しておく

抽出している途中にやっぱりあれが足りないこれも追加したい、一度抽出してしばらく経ったら最新版が欲しい＆ちょっと変えたい、事情が変わって他の抽出を先にやる、なんてことも日常茶飯事だ。

自分で使うためならまだしも、修正依頼にその都度対応しているときりがない。

なので追加修正があるのは仕方ないのであることは見越してきつつ、追加修正が来ないようにするのと、それでも来たら手早く対応できるようにする準備を同時にしておく。

追加修正が来るのは
・データが足りない
・項目を追加してほしい
・過去の抽出の更新
・依頼が正しくなかった
あたり。

「データが足りない」への対応

抽出した後にあれが足りてない、と思うことは普通だしむしろ最初から完璧に出来る場合の方が少ないのではないか。自分でやっててそうなのだから、データに慣れていない人の依頼に過不足があるのは仕方ない。

仕方がないが何度も繰り返されるとさすがになんとかしてよと言いたくなるがそこはこらえて、準備をしておく。

当然ヒアリングの段階で出来るだけ聞き出すというのがまず一番。ここで再依頼が来るのは結構減らせるはず。ただそのためには背景まで聞いて何が必要かを考える、ということまで参加するので限度はある。

あとヒアリングをいくらしてもやはり抜け漏れは出る。そこで当初の依頼よりもっと広い範囲を集計段階で作っておく。

例えば東京都の会員についての集計を依頼されたとしたら、全都道府県単位でも同じ集計をする。これは東京の会員に絞り込まずに集計単位に都道府県を追加すれば簡単にできる。そして作っておいて最後にアウトプットを渡す時に東京都にだけ絞り込めばいい。

住所だけでなく年齢年代、性別、店舗など〇〇別の場合は同じように作っておくと後で楽。データが大きすぎてできないとかであれば近県に絞り込むとかで臨機応変に対応。

「この項目を追加して欲しい」への対応

全然違うデータを追加する（性別と年齢だけだったけど入会月もやっぱり欲しい、とか）こともやはりある。むしろこっちのが多い。

これも何が知りたいのか、という背景まで聞くと先に突き止めやすいが、とはいえその時依頼者（自分含む）が頭に描いていないことを先回りするのは難しいので全部は聞き出せない。

なのでどんなアウトプット（よほど単純な場合は例外）になるにしても、一旦基礎になるテーブルを作り、そのテーブルを経由してから集計するようにしているのでそのテーブルに別のWITHで作ったテーブルをJOINすればかなり対応ができる。

この基礎になるテーブルを作らずに集計したりしていると面倒になる。それすら使えないようなデータであれば別の話として扱う。

「過去の抽出の更新」への対応

これも期間や幅を依頼よりもできる限り広くとって集計しておけばさかのぼりが必要な時に楽。渡す時に「依頼は１年前までだけどもっと前はどうする？」と聞いておけば大体「じゃぁ一緒に頂戴」になる。

しばらく経ってから必要になったら基本的にはクエリを渡してあと自分でやってね、にしている。

ただし長いクエリを渡して複数個所を更新して、はそのうち（あるいは即時に）事故を起こすので、渡すならパラメータは極力減らしたり、最初や最後に固めて更新する場所が判るようにしておくぐらいの配慮はする。

「依頼が正しくなかった」への対応

依頼が正しくないのだからどうにもならず、最初からやり直しになる。ここまでちゃんとヒアリングなどやることをやった上でやり直しになったらすっぱりあきらめて次をやる。

作業が無駄になっても依頼者を責めたりはしない、ようにはしてるが多分顔や態度に出ている時もある。親しい人なら小言の一言ぐらいは許されるだろうが、あまり関わりない人や慣れていない人にはできるだけ柔らかく接するようにしているつもりだが、ちゃんとできているかは聞いたことがない。

目新しさはないが、忙しいと忘れる

ゆっくり書いているからまとめられてはいるが、実際には納期もあれば複数依頼が平行して走るし、相手のリテラシーも重要度も違う中で忙しかったりプレッシャーがあるとやはり忘れてしまいがち。

多分流れそのものはわりと身についているはずだけど、途中での確認とかはつい油断して後になって確認にすごい時間をかけてしまうとかは未だにやってしま

抽出の仕事をもっとうまくやるために

軽く書き始めたら結構な量になってしまった。やはり正しいゴールとそこに至る準備でその抽出がうまくいくかどうかがかなり決まる、ということを改めて感じたので次回からはより意識しよう。ただこのやり方が最良なのかも併せて考えることも忘れないようにしなければ。

今回は「何を抽出するかが決まったところ」から話を始めたが、ここは抽出というタスクのそのまた一部であり、何を抽出するかを正しく決めるためにはどうするか、依頼を遂行するためには何をどうヒアリングするかといった他の問題もあって完璧な抽出マン（なにそれ）への道はまだまだ遠い。

第５回 データアーキテクト（データ整備人）を”前向きに”考える会では、このあたりも含めて「データ抽出をうまくやるために必要なこと（仮）」としてまとめて発表する予定だが、まとまるのかは謎。