5月からプログラミング研修が始まりました。メインはJavaですが、SQLも毎朝30分演習問題を解いています。そんな時、Amazon prime readingに以下の書籍が無料で読めたので(prime会員であれば)、学んだ事・新しく知った事を記録で残していけたらなと思います（まだ全部終わってないです、、、）。
本書の内容
本書はエンジニア向けというよりかは、非技術職のビジネスパーソン向けであるものの、使用するDBなどはCRMシステムを意識したものなど業務に近しいものであり、私の様なひよっこエンジニアにとっては十分すぎる（というか普通に難しい笑）内容であると思います。実際に自身のPCにSQLの実行環境を構築せず、Googleの運営するサービスである「BigQuery」のサンドボックスという無料サービス上に用意されたDBをインポートし、それを使ってSQLを実際に実行し手を動かして理解するという内容です。
BigQueryに関して、分かりやすい記事があったので共有しておきます。
新しく知った事
以下、本書を読んで初めて知ったSQL文法の一部です。
SELF JOIN(自己結合)
Q.どんな時に使うのか
→同一のテーブルを用いて、商品の販売数に関して、前年比成長率を取得する時など。
以下の図(book_table)のように、1レコードずらした同一テーブルを結合します。
前年比の成長率を求めるSQL文は以下のようになります。
SELECT self.year AS base_year // 左側→self1
,self2.year AS next_year // 右側→self2
,self1.qty AS base_qty
,self2.qty AS next_qty
,self2.qty/self.qty1 AS growth_rate // 成長率の計算
FROM book_table AS self1
INNER JOIN book_table AS self2
ON self1.year = self2.year - 1 // 結合条件
結果、以下のテーブルが返ってきます。
IFNULL関数 / COALESCE関数
Qどんな時に使うのか
→テーブル内のNULL値を別の値で置換する時など。
以下の図の様に、NULLを含んだ二つのテーブル(user_table,user_info_table)があるとします。
この場合、登録日(register_year)がNULLになっている部分には、このサービスが開始した2016年を登録日として登録する様に処理します。
以下、SQL文です。
SELECT
IFNULL(register_year,2016)AS reg_year
SUM(point) AS sum_point
FROM user_table
INNER JOIN user_info_table USING (user_id)
GROUP BY reg_year
結果、NULL値であった部分に2016年の登録となり、以下のテーブルが返ってきます。
NULL値の部分に固定値の2016ではなく、登録した年に初めて利用すると考えて、first_user_yearを入れたい場合は、COALESCE関数を用います。
SELECT
COALESCE(register_year,first_user_year)AS reg_year
SUM(point) AS sum_point
FROM user_table
INNER JOIN user_info_table USING (user_id)
GROUP BY reg_year
結果、NULL値であった部分にfirst_user_yearの値が登録され、以下のテーブルが返ってきます。
仮想テーブルの作成
Qどんな時に使うのか
→テーブルの結合や集合演算を組み合わせて使う時。SQLを実行した結果を仮に保存して利用します。例えば、以下の図の様に
①1月の販売テーブル(jan_table)と2月の販売テーブル(feb_table)を結合
②結合したテーブルを仮想テーブル(jab_feb_table)として、count(*)を実行すれば、product_nameのレコード数を得られる。
といった具合です。
以下、SQL文です。
WITH jan_feb_table AS( // WITH 仮想テーブルの名前 AS(仮想テーブルを作成するSQL)
SELECT product_name FROM jan_table
UNION DISTINCT // 重複を取り除いて結合
SELECT product_name FROM feb_table)
SELECT count(*) AS number_of_product FROM jan_feb_table
結果、以下のテーブルが返ってきます。
ABS関数
Q.どんな時に使うのか
→絶対値を求める時。例えば、平均との差異に関して、「上回っているか・下回っているか」だけを知りたい時。（下回っている場合のマイナス表記を省く）
// avg_price = 100
// price = 120
// とする
ABS(avg_price - price)
// 結果→20
CONCAT関数
Q.どんな時に使うのか
→文字列同士を連結させる時
// name = ともすけ
SELECT CONCAT(name,"さん") AS fellName
FROM customoers
// 結果 → ともすけさん
SUBSTR関数
Q.どんな時に使うのか
→文字列の一部を取り出したい時
SUBSTR(文字列、検索位置、文字数)
// date = 2022-0101
SUBSTR(string,1,4) // → 2022
INSTR関数
Q.どんな時に使うのか
→検索文字列の文字列中の位置を取り出したい時。
INSTR(文字列、検索文字列、検索開始位置、出現回数)
*検索開始位置と出現回数はオプション、検索した値がなかったら0を返す
// date = 2022-01-01
INSTR(date,"-") // →5
INSTR(date,"0",5) // →6
INSTR(date,"7") // →0
REGEXP_REPLACE関数
Q.どんな時に使うのか
→特定のパターンに合致する文字列を判定する時。
・一致した時は「true」
・一致しない時「false」
を返す。
正規表現の表記に関しては以下の記事を参照。
// 【prefecture】テーブルに格納された47都道府県(pref)から該当する都道府県を「近畿」という地域にまとめる
SELECT pref
CASE
// r""は正規表現であることを示す目印
WHEN REGEXP_REPLACE(pref,r"大阪|京都|兵庫|奈良|和歌山|滋賀|三重")
IS TRUE THEN "近畿"
ELSE "その他"
END AS region
FROM prefecture
この様にすると、一つずつCASE文を書く必要がないので便利。
結果、以下の様なテーブルが返る。
演習で使用したデータベース
CSVファイル形式で沢山のデータが入ったDBが4つ用意されていました。
書籍のスクショはまずいので、少し簡略化したものをExcelで作成しました。()は格納されたいるデータ行数です。それぞれのDBはsampleファイル直下にあるものとします。
演習問題
①
[cutomers]テーブルから、プレミアム顧客が最も多い都道府県とプレミアム顧客数を求める。結果テーブルは都道府県(prefecture)とプレミアム顧客数(users)の2カラムとする。
○ポイント
・SELECT句とGROUP BY句のフィールド名は同じものを指定
・count(*) as users で１都道府県におけるプレミアム顧客数を集計
SELECT prefeture,count(*) as users FROM sample.customers
WHERE is_pemium is true
GROUP BY prefeture
ORDER BY users desc
LIMIT 1
②
[product]テーブルから、仕入金額(cost)ごとの商品アイテム数を取得。仕入金額は「0円以上300円未満」「300円以上600円未満」・・・と「900円以上1199円未満」まで分類してcost_rangeとする。
アイテム数はitemsとする。cost_rangeの小さい順に並べて表示。
○ポイント
・CASE文で分類する時、先頭に数字を入れORDER BY句で並び替えができるよう工夫
・範囲はANDで繋ぐよりBETWEEN句を使用
・〇〇ごとに〜とあったら〇〇でGROUP BYをする
# 度数分布表
select
case
when cost between 0 and 299 then "1.0円以上300円未満"
when cost between 300 and 599 then "2.300円以上600円未満"
when cost between 600 and 899 then "3.600円以上900円未満"
when cost between 900 and 1199 then "4.900円以上1199円未満"
end as cost_range
,count(distinct product_id) as items
from sample.products
group by cost_range
order by cost_range
以下、結果テーブル。
③
[sales]テーブルから商品ID別の販売個数の合計(sum_qty)と、その合計が全体の何%なのか(sales_share、少数で可)を調べる。結果テーブルはproduct_id、sum_qty、sales_shareの３カラムでsales_shareの高い順で表示
○ポイント
・「商品ID別の販売個数の合計」
と
「その合計が全体の何%なのか」
の二段階で考える
→ 仮想テーブルを使う
・すべてのレコードに値を持たせたいので「商品ID別の販売個数の合計(total_table)」と「salesテーブル」をCROSS JOINで結合
// 商品別の販売個数の合計の仮想テーブル
WITH total_table AS
(SELECT SUM(quantity) AS ttt_qty FROM sales)
// salesテーブルと結合
SELECT product_id,
SUM(quantity) AS sum_qty,
// MAX(ttt_qty) ＝ 販売金額の合計
SUM(quantity)/MAX(ttt_qty) AS sales_share
FROM sales
CROSS JOIN total_table
GROUP BY product_id
ORDER BY sales_share DESC
LIMIT 5
以下、結果テーブル。
④
[sales]テーブルで、初めて値引きが実施されたときの日付（date_time）の抽出
○ポイント
・値引き→定価販売でない→[is_proper] = false
SELECT MIN(date_time) FROM sales WHERE is_proper = false
⑤
[sales]テーブルから、3でも7でも割り切れる[order_id]のうち、最も小さい[user_id]の抽出
○ポイント
・MOD関数を使用する
SELECT MIN(user_id) FROM sales WHERE
MOD(order_id, 3) = 0
AND MOD(order_id, 7) = 0
⑥
[product]テーブルから、[product_category]はクッキーだが、[product_name]はクッキーで終わらないないものを抽出
○ポイント
・クッキーで終わらない→REGEXP_CONTAINS()がfalseの時
SELECT * FROM products WHERE product_category = "クッキー"
AND REGEXP_CONTAINS(product_name, r"クッキー$") = false
気をつける事
・SQL文は大文字で書く！
select * from product
SELECT * FROM product
上記の二つのSQLの句は小文字と大文字という違いはあれど、全く同じ結果を返します。ただこの場合、記述ミスに気付きにくいので大文字で書く様に心がける！
・「内側」から読み解く
演習問題にもあった様な長文のSQLは内側から読み解く。内側のクエリが外側にどの様な戻り値を返しているのか考えるのが重要です。