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

問題:男性の20代30代とそれ以外で11月の購入件数・人数・購入金額を比較する

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

男性の20代30代かどうかの区別を付けるには
・WITHの中で「男性20代30代」のIDリストを作ってJOINする
・サブクエリで結合するときにIDを絞り込む
の2つの方法がある。

金額の計算は単価ではないことに注意

解答:20代30代男性は件数(nobe)18、人数(uu)6、金額(sales)6,170

解説:サブクエリの中でフラグを作ってJOINしている。idだけJOINしてCASE文で作っても同じ。月別に集計しておくと後で追加依頼が来た場合に楽(ただしレポート用などで本当にその月のデータだけしか不要な場合もある)。

SELECT
DATE_TRUNC(date,month) as month
, flag
, COUNT(customer_id) as nobe
, COUNT(distinct customer_id) as uu
, SUM(quantity * tanka) as sales
FROM kiso1_pos p
LEFT JOIN (SELECT id,1 as flag FROM kiso1_customer WHERE sex=1 and age>=20 and age<=39) c
ON p.customer_id = c.id
LEFT JOIN kiso1_master m
USING(category_id,item_id)
GROUP BY 1,2

サブクエリの部分をWITHを使うなら

WITH c as (
SELECT id,1 as flag FROM kiso1_customer WHERE sex=1 and age>=20 and age<=39)
として
LEFT JOIN c
と変更する。

SQL問題一覧はこちら

2020年2月14日SQL問題

Posted by shinu