Kaggleの"Intro to SQL"をやってみた

データサイエンス系

自分用メモ。ドットインストールの動画をさっと眺めたぐらいでSQLを触ったことがなかったが、勉強がてらやってみた。とりあえず無料のものでいい&自分の環境でやるのはめんどくさいので、Kaggleのチュートリアルを使うことにした。

 

Learn Intro to SQL Tutorials | Kaggle

 

SQLとBigQuery

SQLにはいくつか種類があることは理解しているが、MySQLとPostgreSQLの違いが何かとか、まったくわかっていない。細かい違いはあるがたぶんここに出てくるような公文で大きな違いはないっぽい。KaggleのSQLはGoogle BigQueryを使っているらしい。

 

SELECT, FROM, WHERE

SELECTで引出したい項目を、FROMで対象となるテーブルを、WHEREで条件を指定する。

SELECT * 

FROM table

WHERE country=US

でcountryの項目がUSであるsub tableが生成される。

 

GROUP BY, HAVING, COUNT()

COUNT()は数を数える。たとえばCOUNT(ID)とあるとIDの数が何種類あるか数える。ID0-99までの顧客リストがあるとCOUNT(ID)は(IDが重複していない限り)顧客数100を返す。SUM()などと同様にこういう複数の値から一つ値を返す関数なのでaggregate functionという。

GROUP BYはCOUNT()の結果を種別に返す。たとえば先ほどの顧客リストにregionという項目があって関東、関西などとある場合、COUNT(ID) とGROUP BY regionによって

関東 25

関西 19

東北 13

...

四国 1

などというような地域別のID数(顧客数)を表示できる。

HAVINGは条件式である。GROUP BY region HAVING COUNT(ID)>1で顧客数が2以上の地域別顧客リストが返る。なのでさきほどの例で言えばリストから四国(IDが1なので)が消える。

 

COUNT(1)で行の数を数えられる。

 

 

ORDER BY

順番を決められる。DESCで降順。なのでORDER BY COUNT(1) DESCで個数順

 

AS WITH

ASはaliasだが、WITH...ASと合わせることで一時的なtable(Common Table Expression, CTE)を作ることができ、可読性をあげることができる。

query_with_CTE = """ 
                 WITH Timetable AS 
                 (
                     SELECT DATE(timestamp) AS trans_date
                     FROM `bigquery-public-data.bitcoin.transactions`
                 )
                 SELECT COUNT(1) AS transactions,
                        trans_date
                 FROM Timetable
                 GROUP BY trans_date
                 ORDER BY trans_date
                 """

のような感じ。例えばたくさんのトランザクションのデータから、顧客ごとに合計額を算出して、それから全顧客に対して平均をとるような処理などに使える。

 

Joining data

pandasのmergeのような感じ。複数のtableをid等を目印に統合する。left join, full joinなどの種類がある。

 

実際全部やるのに5時間くらいだったと思う。Advanced編をやったり、100本ノックをしてみたい。

Learn Advanced SQL Tutorials | Kaggle

 

github.com