はじめに
記事の内容
数日間の学習内容である、SQL基礎文法のや使用方法などを説明した記事です。
情報の正確さや密度は公式ドキュメントのほうが濃いですが、
自分なりにわかりにくかった部分や引っ掛かりやすい部分を記しています。
学習前の状態
過去にRubyを書いてました。
リレーション、ログを読んでデータの抽出状況などは大まかに理解できるレベルです。
しかし腰を据えてSQLの学習してきたことはありませんでした。
学習教材に関して
主にゼロからはじめるデータ分析のための実践的SQL入門〜現場で使えるSQLを最短経路で習得〜を活用しました。
休日使ってトータルだいたい３日で終わりました。
環境はBigQueryでした。
頻出の構文から、具体的な実務での使用例まで、短時間で知りたい内容がある程度網羅的に学べるとても良い教材でした。
どのくらいになったか
基礎的な構文や関数の使用は概ね扱えるようになりました。
仮想テーブルなどを用いて、下記のようにケース別にデータの連結や加工を行い集計できるようになりました。
- カテゴリ毎の商品売上の集計
- リピートユーザーの割合
- ファネル分析など(URLを階層ごとに区分けしたアクセス割合の抽出)
学習内容
前提
教材内容そのままの転載を避けるために、学習した内容や下記の参考情報等をもとに、
自己解釈や学習で使用したケースなどを交えて記載しております。(ソースコード含む)
正確な説明や用途を知りたい場合は、教材や公式のドキュメント等をご確認いただけますと幸いです。
(説明に誤りがあれば修正します🙇♂)
基礎構文
SELECT文
クエリが返却されるためのカラムを定義する。
SELECTの後ろに取得したいデータのカラムを指定することで、
カラムに格納されているデータが返却される。
カンマ区切りで複数指定することも可能。
SELECT name FROM users
SELECT DISTINCT
重複されるレコードを取り除いて、データが返却される。
外部キーに登録されている種類数の確認に使った。
(例：postsテーブルにあるuser_idカラム内部のデータの種類数を取得して、誰が投稿しているかの確認)
SELECT DISTINCT user_id FROM posts
ORDER BY句
並び替えの基準となるカラムを指定して、レコードの順序を昇順または降順に変更する。
複数指定することも可能。
ASCで昇順、DESCで降順に並び替える。(デフォルトはASC)
SELECT id, price
FROM orders
ORDER BY price
GROUP BY句
特定の列に共通の値を持つテーブル内のカラムをまとめる。
グループ毎にまとめているため、ORDER BYで指定した以外のカラムをそのまま取り出そうとすると、
グループのカラム内に複数存在するどのデータを返却するべきかわからずエラーが出る。
SUMなどの集計関数は、グループ内で集計を行い、グループ毎に１つの値が問題なく返却される。
SELECT name, SUM(price)
FROM fruits
ORDER BY name
2種類以上でGROUP BYを行った場合
指定したカラム同士の組み合わせで、共通の値をまとめる。
例えば下記のようにGROUP BYでuser_idとitem_id両方指定した場合、
1, 1のグループ、4, 1のグループのように、2種共通の組み合わせを確認して、グルーピングする。
そのため1, 1と4, 1は同じグループにはならない。
SELECT user_id, orditem_id
FROM orders
ORDER BY user_id, item_id
/*---------+----------+
| item_id | user_id |
+---------+----------+
| 1 | 1 |
| 4 | 1 |
| 4 | 2 |
| 5 | 2 |
| 5 | 3 |
| 6 | 3 |
+---------+----------*/
HAVING句
データの抽出条件を指定する。
GROUP BYのグループ分けに条件を加える目的で使用する機会が多かった。
SELECT name, SUM(price)
FROM fruits
ORDER BY name
HAVING SUM(price) >= 20
WHERE句
データの抽出条件を指定する。
WHERE句の引数内の条件に沿ったデータのみが返却される。
命令の実行順番はWHERE→GROUP BY→HAVINGなので、
上のHAVINGのように、GROUP BYに追加で条件を加える目的で記載することは出来ないはず。
SELECT name
FROM users
WHERE gender = "male"
ORDER BY name
WITH句
サブクエリに名前づけして、仮想のテーブルのように機能する。
複数テーブルを連結する際に、仮想テーブルを用いたり
単一のテーブルに対して、複数個仮想テーブルを用いて異なる抽出を行い、
(初回購入→リピートしているユーザー)
INNER JOIN
レコード同士を結合する。
-
ONの後ろに、両テーブルの結合条件に用いるカラム名を記述する。 - 結合条件のカラム同士で同じ値があれば結合を行い、それ以外は破棄する。
- 結合する際、行が多いテーブルに合わせて少ない方のテーブルの行数は複製される
結合条件の両者のカラムに関して。
例えばitemsテーブルのidカラムとordersテーブルのpriceカラムのように名称や用途がそれぞれ異なっていても、データ型さえ合えば結合条件にできる。
今回の学習では、結合条件の両者のカラムは、基本は両者同じ名称、少なくとも同じ用途目的のカラム同士を用いた。
例えば以下のitemsテーブルとordersテーブルがあったとする。
結合条件はitem_idを用いる。
| item_id | price |
|---|---|
| 1 | 2000 |
| 2 | 5000 |
| 3 | 3000 |
| 4 | 1500 |
| 5 | 6000 |
| 6 | 8000 |
| id | user_id | item_id | price |
|---|---|---|---|
| 1 | 1 | 1 | 2000 |
| 4 | 2 | 2 | 1000 |
| 9 | 3 | 1 | 2000 |
SELECT
items.item_id,
id AS order_id,
items.price AS item_price --カラム名に被りがある場合、抽出時はテーブル名を明示する。
FROM
items
INNER JOIN orders ON items.item_id = orders.item_id
-- item側の行が複製されている。
/*---------+----------+------------+
| item_id | order_id | item_price |
+---------+----------+------------+
| 1 | 1 | 2000 |
| 1 | 9 | 2000 |
| 2 | 4 | 5000 |
+---------+----------+------------*/
LEFT JOIN
右側の行が結合条件を満たしていなくても、左側の行はすべて表示される。(なので名前がLEFT JOIN)
そのためテーブルの左右順を逆にすると出力結果が変わるので注意。
結合出来ず結合先のカラムが取得出来なかった場合、不足箇所にはNULLが格納される。
SELECT
items.item_id,
id AS order_id,
items.price AS item_price --カラム名に被りがある場合、抽出時はテーブル名を明示する。
FROM
items
LEFT JOIN orders ON items.item_id = orders.item_id
/*---------+----------+------------+
| item_id | order_id | item_price |
+---------+----------+------------+
| 1 | 1 | 2000 |
| 1 | 9 | 2000 |
| 2 | 4 | 5000 |
| 3 | null | 3000 |
| 4 | null | 1500 |
| 5 | null | 6000 |
| 6 | null | 8000 |
+---------+----------+------------*/
LIKE演算子
文字列検索(完全一致以外も)を行うことができる。
LIKE単体でも機能するが、WHEREと併用した文字列検索の目的で使用した。
SELECT name FROM users WHERE name LIKE '佐藤';
以下の文字を含めることで、部分一致の検索を行うことができる。
| 文字 | 意味 |
|---|---|
| % | 任意の数の文字 |
| _ | 任意の1文字 |
SELECT
'佐々木' LIKE '%木', -- Returns TRUE
'佐々木' LIKE '_木' -- Returns FALSE
SELECT name FROM users WHERE name LIKE '佐_';
集計関数
COUNT
引数で指定しているカラムの列数を数値で返却する。
例えば下記のnameカラムに4件レコードが存在している場合、数値の4が返却される。
SELECT COUNT(name)
FROM users
MAX, MIN
最大あるいは最小の値を返却する。
最大がMAX、最小がMIN
(一応文字列型でも代入できる)
SELECT MAX(price)
FROM items
SUM
合計値を返却する。
SELECT SUM(price)
FROM items
AVG
平均値を返却する。
SELECT AVG(price)
FROM items
文字列関数
CONCAT
文字列の連結を行う。
特定の演算結果に、文字列で%を加えたり、姓カラム名カラムを連結してフルネームを表示させる時に使用した。
SELECT
CONCAT(first_name, ' ', last_name) AS full_name
FROM users;
SPLIT
引数内で指定した区切り文字列を基準にして、文字列を分割する。
URLの/などを区切りの基準にすることで、アクセスしたURLの階層ごとのデータ集計などを行った。
関数(タイムスタンプ)
TIMESTAMP_DIFF
引数に代入されている２つの値の時間差分を出力する。
第三引数にて、秒分日など時間の粒度を指定することができる。
TIMESTAMP_DIFF(end_timestamp, start_timestamp, granularity)
TIMESTAMP_TRUNC
指定した時間データを日や週単位で丸めるように加工する。
例えば2020-11-20 21:55:12のデータに対して、月単位で丸めるよう指定すると、
2020-11-00 00:00:00のような形で返却される。
SELECT TIMESTAMP_TRUNC(created_at, month)
FROM orders
条件式
CASE
CASE後続のWHENに条件を指定して条件分岐を行う。
WHENを含む条件を複数追加することで、複数の条件分岐を行うことが可能。
THEN以降に条件合致した場合の処理を記述。
基本的にELSEを記述するよう注意する。
ELSE未記入の場合、対象条件以外のデータがNULLとして返却されるため。
SELECT
CASE
WHEN prefecture = "東京" THEN "関東"
WHEN prefecture IN ("茨城", "栃木", "群馬", "埼玉", "千葉", "神奈川") THEN "関東(東京以外)"
ELSE "その他"
END
FROM users
ケース別
注文の男女別リピート率
*教材の学習内容から自分なりにコードの書き換えや分析観点を変えたコードになります。
また詳しい説明は教材の要約になる可能性があるので行いません。
WITH first_orders AS(
SELECT
user_id,
MIN(created) AS created_first
FROM
orders
GROUP BY
user_id
),
repeat_orders AS (
SELECT
orders.user_id,
COUNT(orders.order_id) as ordered_count
FROM
orders
INNER JOIN first_orders ON
first_orders.user_id = orders.user_id
AND first_orders.created_first < orders.created
AND DATE_DIFF(orders.created,first_orders.created_first,DAY) <= 30
GROUP BY
orders.user_id
HAVING
ordered_count >= 2
),
check_two_repeat_users AS (
SELECT
first_orders.user_id AS ordered_user_id,
gender,
age,
first_orders.created_first,
IF(repeat_orders.user_id is not null,1,0) AS is_repeated,
FROM
users
INNER JOIN first_orders AS first_orders ON users.user_id = first_orders.user_id
LEFT JOIN repeat_orders AS repeat_orders
ON first_orders.user_id = repeat_orders.user_id
ORDER BY
first_orders.user_id
)
SELECT
gender,
ROUND(AVG(age)) AS average_age,
COUNT(created_first) AS first_order_count,
SUM(is_repeated) AS repeatd_order_count,
CONCAT(ROUND(SUM(is_repeated)/COUNT(created_first) * 100, 1), "%") AS repeated_rate
FROM
check_two_repeat_users
GROUP BY
gender
さいごに
まだ仮想テーブルの切り出す粒度などがふわついていたり、
1つのテーブルで複数仮想テーブルを切り出すと解読が難解になったりします。
そのあたりも徐々に慣れていければと思います。
参考情報
https://zenn.dev/raksul_data/articles/93fb5c5d7490d1
https://www.udemy.com/course/sql_for_data_analytics/
https://cloud.google.com/bigquery/docs/reference/standard-sql
https://qiita.com/tetocha/items/cb10996b90dcc7aabe25
https://qiita.com/chihiro/items/46da8571d4cf37d3123c
https://qiita.com/sfp_waterwalker/items/acc7f95f6ab5aa5412f3