SQL問題 テーブルを結合してセグメント別の集計をする(3)

問題:11月の購入本数と人数を性別×ヘビー会員かどうか別で集計する。男性、ヘビー会員の本数と人数はいくつか。性別は男/女の表記にすること。

kiso1の5つExcelファイルにあるテーブル5つから必要なデータを選ぶこと。 使い方はSQL学習用サンプルデータを参照。

まずどういったデータが必要かを考え、次にそのデータをどうやって取得するかを検討する。

この問題は
・11月の購入本数→POSには本数が無い→本数マスタがある→本数マスタとPOSは直接JOINできないので他に方法はないか
・性別とヘビー会員かどうか→それぞれテーブルがあるのでそこから持ってくる

POSデータに必要な情報を追加すればあとは集計するだけ。

解答: 60本・12人

解説:5つのテーブル全部を使うことになるので全部結合してからではなく1つ結合する部分を書いたら実行してレコード数が変わっていないことを確かめながら進めて行く方がよいだろう。全部結合してしまうとあとで気づいてもどこに間違いがあるのか探しにくい。

結合はINNER JOINでも出来るがどこかで欠損が起きるとレコード数がおかしくなるので欠損がないことが保証されていない限り(つまりほぼいつも)情報を追加する場合はLEFT JOINの方が安全。

同様にsexについても CASE WHEN sex=1 THEN '男’ ELSE '女’ END では「1以外は全部女」になり、欠損やおかしな値が入っている時に正しくない集計になる。

SELECT
CASE WHEN sex=1 THEN '男’
WHEN sex=2 THEN '女’
ELSE '不明’
END sex
,flag_heavy,count(distinct customer_id) as uu
,SUM(honsuu) as honsuu
FROM kiso1_pos p
LEFT JOIN kiso1_csutomer c
ON p.customer_id = c.id
LEFT JOIN kiso1_heavy h
USING(customer_id)
LEFT JOIN kiso1_master m
USING(category_id,item_id)
LEFT JOIN kiso1_honsuu ho
USING(item_name)
WHERE date>=’2019-11-01′
AND date<=’2019-11-30′
GROUP BY 1,2

クエリが判りづらい場合はサブクエリを使ってまずは結合だけしたテーブルを作って中身を確認、その後に集計するようにしてみよう。

また問題は11月について聞かれているのでWHEREでdateを絞ってはいるが、先を見越してDATE_TRUNCを使って月別に出しておくと追加で依頼が来たときに対応しやすい。

SQL問題一覧はこちら

2020年2月14日SQL問題

Posted by shinu