データサイエンス100本ノック(構造化データ編)SQL版全問題解説

データサイエンティスト協会スキル定義委員によるデータサイエンス100本ノック(構造化データ編)をやってみたので、簡単に解説を書いていきます。全問題といいましたが風呂敷を広げすぎたので、適宜端折ります。今のところ、おおよそ数問毎にどのような知識が必要かというガイドライン程度に止まっていますが、目安になると幸いです(問題個別の解説になるように定期的に改善していく予定)。

 

githubレポジトリはこちら。

GitHub - The-Japan-DataScientist-Society/100knocks-preprocess: データサイエンス100本ノック(構造化データ加工編)

 

問題を解くためのセットアップはこちらの記事を参考にしました。

qrunch.net

 

Windows 10 ProであればDocker Desktop推奨。

 

Macの方はこちら。

qiita.com

 

ちなみにIEやEdgeではlocalhost:8888とやってもJupyter Labが走らないので気をつけてください。

 

ちなみに100本ノックに臨む前にSQLの最低限の記法を知っているほうが良いと思います。ただ、答えを見つつ、ググりつつやれば(プログラミングの経験がある人などは特に)初学者でも進められるかもしれません。SQLの文法を気楽にまず学びたい方で英語が得意な方は実際に実行しつつ学べるKaggleの二つのSQL Courseがあります。もちろん、100本全てを解けるだけの題材はカバーしてないのですが、基礎事項は押さえられると思います。日本語が良い方は自分に合いそうな本や記事を探してみてください。

www.kaggle.com

www.kaggle.com

 

1-9問目

基礎の基礎です。最初の9問は

SELECT文でカラムの名前を指定する

FROM文で参照するテーブルの名前を指定する

WHERE文でデータを取ってくるときの条件を指定する

(実際に何千行もある全データを表示できないので)LIMIT文で表示するデータの行数を指定する

この4つさえわかっていれば進めることができます。

 

SELECT *で全てのカラムを指定できること

文字列は'(シングルクォーテーション)で囲むこと

WHERE文の条件は複数のものをANDやORでつなげられること

などがちょっと進んだ概念としていくつかの問題で必要になってきます。

 

10-16問目

WHERE文の中で文字列を比較する問題が続いて出てきます。

where 変数 like '文字列'で比較できます。PostgreSQLでは%によって任意の文字を表すことができます。

また、いくつかの問題は「正規表現」というSQLに限らずプログラミング言語ではよく出てくる別の記法で解く必要があります。その場合、likeではなく~(チルダ)を代わりに使います。where 変数 ~ '正規表現'

 

17-33問目

大きなトピックとして、GROUP BY, ORDER BY, HAVING文と、SUM(),AVG(),MIN(),MAX()などの集計関数が出てきます。また、Rank(), Row_number()といった少しハイレベルな集計関数も出てきます。

GROUP BYと集計関数を使うことで、とあるカラムに注目して、集計ができるようになります(例:genderに注目して、異なるgenderごとに売り上げの合計や平均を取るなど)。

ORDER BYでは行の順番を指定します。例えば、売り上げの合計が大きい順に顧客IDを表示できたりします。

また、GROUP BYとセットで使用されるHAVING文があります。これはGROUP BYで着目するグループに条件を課すことができる点でWHEREに似ています。例えば、顧客毎に売り上げの合計を算出する時、そもそもIDがZで始まる顧客は非会員なので計算から除外する場合などに使います。

 

34-44問目

大事な構文として、WITHとJOINそしてUNIONが出てきます。

WITH句はいったん集計して一時的な表を作るもので、その後最終的な表や数値を出すために使います。たとえば一旦WITHで顧客ごとに今までの売り上げを合計しておき、最終的には顧客ごとの合計売り上げの平均を取ることで、顧客一人あたりの売り上げを出すといったクエリを書くことができます。

JOINは複数のテーブルを(横に)統合することができます。例えばreceiptというテーブルにはカラムとして商品が売れた日時と売れた商品の商品コードがあり、productというテーブルにはカラムとして商品コードと商品の名前が書かれているとします。これらreceiptとproductというテーブル二つを共通のカラムである商品コードを参照として統合することで、いつなんという名前の商品が売れたのかという新しいテーブルができることになります。JOINにはINNER JOIN, LEFT JOIN, OUTER JOINなどの種類があり、それらの違いを理解していくことも重要です。(ちなみにPostgreSQLでは何もついていないJOINはINNER JOINとして解釈されます。)

UNIONは複数のテーブルを縦に結合する(つまり単純に行数を増やす)ものです。このノックで2-3回だけの出番でした。

 

また、Tableを作る、消すといった作業が必要になる問題も問題43で出てきます。

 

そのほかの重要な概念として、NULL値が入っていた場合に補完できるcoalesce()、時系列データなどで集計の際、n個手前の時刻(行)の数値を参照できるlag()、条件分岐に応じて入れる数値を指定できるcase文などが出てきます。もちろん、それ以前に用いた構文等もフル活用するので、このあたりから少しづつ難しくなっていくかと思います。焦らず復習するなどして、少しづつ理解していきましょう。

 

45-74問目

DATE型、timestamp型といった時刻を表すデータの扱いと、文字列型の扱い、そして標本標準偏差や対数、小数点切り捨てといった数値を計算する問題が並んでいます。必要なものは多岐に渡るので、また個別の解説を書く際に詳しく言及しようと思います。特に時刻は関数もたくさんあり、引数の違いなどにも気を使う必要があります。

また、既出の構文ですがcase文を用いて、ダミー変数を作るという問題も出てきます。機械学習系の人にはよく使う技術の一つですね。

 

 

75-88問目

サンプリングや欠損値の比較、そして欠損値の補完や名寄せといった実務上とても重要なものが出てきます。またここでは、nestされたカラムというのが初めて出てきます。ちょっとややこしい概念の内の一つだと思いますが、ここでしか出てこないようです。新しい概念を一度の問題で複数学ぶ必要も出てきうるので、一つ一つ丁寧に理解していく必要があります。わからない場合は思い切って飛ばして後で戻ってくるのも良いかと思います。

 

89-100問目

データセットの作成や、ファイル出力といった問題が並んでいます。問題43や80で使ったテーブルの作成方法を思い出しつつやっていきましょう。データセットの作成にはサンプリングをするのでやはり今までに習ったことを使う必要があります。早く終わらせたくなる気持ちはわかりますが、これを機に復習しつつ進めてみてください。

 

おまけ:処理順

各々の句がどの順番で処理されるかは覚えた方が絶対に良いです。(暗記する、というより流れをイメージできて自然と思い出せる形で)

1. from

2. on

3. join

4. where

5. group by

6. having

7. select

8. distinct

9. order by

10. limit