データサイエンス100本ノックをやってみた【その１：SQL編】
２週間ほど前に公開された『データサイエンス100本ノック（構造データ加工編）』を、仕事終わりに少しずつ進めていました。
（構造データ加工編）という名前からわかるように、中身は『データ前処理100本ノック』です。『前処理大全』対応問題集みたいな感じですよね。
SQL, R, Pythonで各100問ずつなので、合計300問が解答付きで公開されています。ありがたやありがたや。新卒入社してデータ分析始めた頃にこういうコンテンツが欲しかったです…！
100/300
— にゃん (@meow3571) 2020年6月26日
ということで、SQL編を一通りやってみたので感想記事です。*1
環境設定
Macユーザーなので下記の記事を参考にしました。
こんぴゅうたあよくわからないので、いつも環境設定でつまづいてしまいます。今回も「docker…？仮想環境…？」からのスタートだったので少し不安でしたが、拍子抜けするくらいスムーズにできました。*2
主な内容
postgreSQLをJupyterLab上で実行して問題を解いていきます。*3
よくあるSQL入門本ではなかなか試す機会の無いWindow関数やWITH句もいっぱい使いますし*4、正規表現とかテキストファイルへのインポート／エクスポートなんかの問題もあります。
『SQL 第2版 ゼロからはじめるデータベース操作』を読んで手を動かしたくなった人に丁度いいくらいの難易度だと思いました。
類書では『スッキリわかるSQL入門 第2版 ドリル222問付き』のドリルより難易度高めな問題ではありますが、100本ノックのほうが遥かに実践的でおすすめです。*5
印象に残った設問たち
正規表現
S-015: 顧客テーブル（customer）から、ステータスコード（status_cd）の先頭がアルファベットのA〜Fで始まり、末尾が数字の1〜9で終わるデータを全項目抽出し、10件だけ表示せよ。
「正規表現が使えると便利」というのは分かってたんですが、必要なときはその都度ググってましたし、なんとなく意味不明な記号の羅列に見えて苦手意識を持っていました。
今回の100本ノックでも文字列のあいまい条件検索の問題がいくつか出題されており、解いていくなかで「あれ、なんで正規表現をそんなに避けてたんだ…？」「というか普通に覚えたほうが便利じゃない…？」という気分になってきた設問でした。
サルにもわかる正規表現入門とかを読んでみたいなーと思いました。
基本統計量の算出
S-028: レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の中央値を計算し、降順でTOP5を表示せよ。
そういえばRのmedian()のような、中央値を算出するための便利関数がSQLでも存在してるとは限らないんだなーとしみじみした設問です。
PERCENTILE_CONTのありがたみを知りました。
日付の操作
S-070: レシート明細テーブル（receipt）の売上日（sales_ymd）に対し、顧客テーブル（customer）の会員申込日（application_date）からの経過日数を計算し、顧客ID（customer_id）、売上日、会員申込日とともに表示せよ。結果は10件表示させれば良い（なお、sales_ymdは数値、application_dateは文字列でデータを保持している点に注意）。
「とりあえずdate / timestampで出力しておいて、細かい処理は出力後の別ツールでやればいいや」の精神で生きてきたので、SQLでの日付操作にちょっと苦戦しました。
EXTRACTとかAGEとかあったんだなあ、というのが収穫です。最近は何でもかんでもTableauにデータ突っ込むマンなので、Tableauのドリルダウン機能とDATEPARTで良きに計らってもらってました。
WITH句に慣れてくる
S-084: 顧客テーブル（customer）の全顧客に対し、全期間の売上金額に占める2019年売上金額の割合を計算せよ。ただし、販売実績のない場合は0として扱うこと。そして計算した割合が0超のものを抽出せよ。 結果は10件表示させれば良い。
特別難しい問題じゃないんですが、WITH句で中間テーブルを2つ3つ作って対応する必要があるよという、「知ってる知識を実務で使うための練習」みたいな難易度の問題になっているのが、100本ノックの特徴だなあって思います。
自分自身そんなにSQLが得意というわけでもなく、AlteryxとかSPSS modelerでデータ前処理をやっちゃうことが多いタイプです。なので、WITH句でいくつか繋げて処理を書けるようになると、使い慣れてきたツールと同じことができるようになってきて、SQLの位置づけが「データベース上のデータを抽出するもの」から少し広がってくる感覚があります。
それと同時に、それぞれの中間テーブルごとのchkがSQLだとなかなかやりにくいので、その点Alteryxとかって中間の処理を確認しやすくって優秀だなって思う次第です。データがそんなに重くないか、データベースとの接続がボトルネックにならなければAlteryxとか使っていたい。ライセンス料がネックです。
名寄せ
S-087: 顧客テーブル（customer）では、異なる店舗での申込みなどにより同一顧客が複数登録されている。名前（customer_name）と郵便番号（postal_cd）が同じ顧客は同一顧客とみなし、1顧客1レコードとなるように名寄せした名寄顧客テーブル（customer_u）を作成せよ。ただし、同一顧客に対しては売上金額合計が最も高いものを残すものとし、売上金額合計が同一もしくは売上実績の無い顧客については顧客ID（customer_id）の番号が小さいものを残すこととする。
名寄せとかクリーニングって、
・持っていきたいデータの形をイメージすること
・その形に持っていくための発想・手段をイメージできること
どちらも必要だなって思ってます。
後者についてずっと我流でやってきてたんですが、これまで（別ツールで）やってたアプローチがS-087の解答が取ってたのと同じ作戦でちょっと安心しました。
感想
SQLでよくやる内容がほとんど網羅されていて、はじめてSQL触りますって人も入門書１冊読んでいれば全く手が動かないことは無い難易度なので、絶妙なコンテンツだなあと思います。入門書と業務のあいだをつなぐのに丁度いいやつ。会社でSQLやったことない人に教えなきゃいけないんですという場面が来たら、迷わずこの100本ノックをおすすめしたいです。２〜３年前に出逢いたかったコンテンツだ…
別解がありそうな問題はぜひ別解も考えたいですね。特にpostgreSQLでないと回らないタイプの書き方が何問かあった気がするので。データ分割系の問題も、他にもいろんなアプローチがありそうです。
ということで、今度はR編をやってみたいところ。
*1:わかんなかったところはすぐに解答見て写経した
*2:ちなみにPC終了した後に再開するときには、docker立ち上げた後にターミナルで下記２行を実行しました。
> cd 100knocks-preprocess
> docker-compose start
*3:postgreSQL触ったことないからか、解答の知らない関数が自分のせいなのかぽすぐれのせいか分かんなかったです。
*4:奇しくも「複雑なサブクエリ書くならWITH句を使おう」って先日ネットで話題になりましたね
*5:個人的にdokoQLがマトモに動いた例を聞いたことないです。dokoQL環境であの222問やりきった人って実在するんですかね…？
