解答
CHAPTER 5
011
SQL文（クエリ）
SELECT product_category
, SUM(revenue) AS sum_revenue
FROM impress_sweets.sales
INNER JOIN impress_sweets.products
USING (product_id)
GROUP BY product_category
ORDER BY 2 DESC
結果テーブル
解説
［sales］テーブルの［product_id］と、［products］テーブルの［product_id］をキーとして結合することにより、［products］に存在する商品カテゴリ（product_category）で集計できるようになります。
［sales］テーブルの［product_id］に記録されている値がすべて、［products］テーブルの［product_id］にも存在しているため、上記のINNER JOINはLEFT OUTER JOINとしてもRIGHT OUTER JOINとしても同じ結果になります。
012
SQL文（クエリ）
WITH total_qty AS
(SELECT SUM(quantity) AS ttl_qty FROM impress_sweets.sales)
SELECT product_id
, SUM(quantity) AS sum_qty
, SUM(quantity) / MAX(ttl_qty) AS sales_share
FROM impress_sweets.sales
CROSS JOIN total_qty
GROUP BY product_id
ORDER BY 3 DESC
LIMIT 5
結果テーブル
解説
まず、WITH句でテーブル全体の販売個数の合計（ttl_qty）を取得します。取得した仮想テーブルは［total_qty］と名付けており、1列1行、つまり1つの値で構成されています。そのうえで［sales］テーブルを［total_qty］を結合します。［sales］テーブルのすべてのレコードに［ttl_qty］の値を持ちたいので、結合のタイプにはCROSS JOINを使います。
結合してしまえば「同一レコードの別フィールド同士の計算」は簡単にできるので、［product_id］でグループ化を行い、SUM関数で［quantity］を合計した値を、販売金額の合計であるMAX(ttl_qty)で割って［sales_share］を求めます。
CROSS JOIN後、すべてのレコードに同じ値の［ttl_qty］が格納されているので、ここで利用するMAX関数は集計関数を利用して値を1つだけ取り出していることになり、MIN関数を使っても、AVG関数を使っても結果は同様になります。
別解として、以下のSQL文があります。こちらはWITH句で商品ID別の販売個数の合計と、テーブル全体の販売個数の合計という2つの仮想テーブルを作成し、メインのSQL文でシンプルにCROSS JOINと割り算のみをするという方法です。
WITH qty_by_product AS
(SELECT product_id, SUM(quantity) AS sum_qty
FROM impress_sweets.sales
GROUP BY product_id)
, total_qty AS
(SELECT SUM(quantity) AS ttl_qty FROM impress_sweets.sales)
SELECT product_id, sum_qty
, sum_qty / ttl_qty AS sales_share
FROM qty_by_product
CROSS JOIN total_qty
ORDER BY 3 DESC
LIMIT 5
013
SQL文（クエリ）
SELECT
CASE gender
WHEN 1 THEN "男性"
WHEN 2 THEN "女性"
ELSE "不明" END AS customer_gender
, product_category
, SUM(revenue) AS sum_revenue
FROM impress_sweets.sales
LEFT JOIN impress_sweets.customers
ON sales.user_id = customers.customer_id
LEFT JOIN impress_sweets.products USING (product_id)
GROUP BY gender, product_category
ORDER BY 1, 3 DESC
結果テーブル
解説
［sales］テーブルを左側として、顧客情報が格納された［customers］テーブルと商品情報が格納された［products］テーブルの2つを結合します。［customers］テーブルにある［gender］をCASE文で「男性」「女性」と読み替えています。
性別（gender）と商品カテゴリ（product_category）の2つのフィールドで集計するため、GROUP BY句にも［gender］と［product_catgory］を指定しています。［sales］テーブルと［customer］テーブルを結合する際、結合キーのフィールド名が異なるのでON句を利用しています。
014
SQL文（クエリ）
WITH qty_jul_sep AS
(SELECT product_id
FROM impress_sweets.sales
WHERE date_time BETWEEN "2023-07-01" AND "2023-09-30"
GROUP BY product_id
HAVING SUM(quantity) >= 10)
, qty_oct_dec AS
(SELECT product_id
FROM impress_sweets.sales
WHERE date_time BETWEEN "2023-10-01" AND "2023-12-31"
GROUP BY product_id
HAVING SUM(quantity) >= 10)
SELECT product_id FROM qty_jul_sep
EXCEPT DISTINCT
SELECT product_id FROM qty_oct_dec
結果テーブル
解説
2023年7～9月に販売個数（quantity）が「10」以上の商品と、2023年10～12月に販売個数（quantity）が「10」以上の商品を調べることがポイントです。日付の期間はBETWEEN句で指定します。
WITH句で条件に合致する2つの仮想テーブルを作成し、それらを集合演算のEXCEPT DISTINCTで差集合を作って商品リストを取得します。
仮に7月-9月に10個以上売れた［producrt_id］がすべて、10月～12月にも10個以上売れていれば、該当なしとなり結果テーブルは返ってこなくなります。逆にいうと、返ってきた［product_id］は10月～12月に10個以上売れなかった商品といえます。
テーブル演算を使ったほかの方法としては、「7月～9月に10個以上売れた［product_id］のリスト」と「10月～12月に10個未満しか売れなかった［product_id］のリスト」をWITH句で作成し、それらの積集合を求めることでも正解を得ることができます。
別解として以下のSQL文があります。こちらはWITH句では「10個以上売れた」という条件は付与しておらず、［pruduct_id］ごとに［quantity］を合計で集計しているだけです。そのうえでメインのクエリで7月から9月には10個以上販売され、10月から12月では10個未満にか販売されなかった［product_id］を取得しています。
WITH qty_jul_sep AS
(SELECT product_id, SUM(quantity) as jul_sep_qty
FROM impress_sweets.sales
WHERE date_time BETWEEN "2023-07-01" AND "2023-09-30"
GROUP BY product_id)
, qty_oct_dec AS
(SELECT product_id, SUM(quantity) as oct_dec_qty
FROM impress_sweets.sales
WHERE date_time BETWEEN "2023-10-01" AND "2023-12-31"
GROUP BY product_id
)
SELECT product_id FROM qty_jul_sep
JOIN qty_oct_dec USING (product_id)
WHERE jul_sep_qty >= 10 AND oct_dec_qty < 10
015
SQL文（クエリ）
SELECT product_name
, SUM(quantity) AS sum_quantity
, SUM(revenue) AS sum_revenue
, SUM(quantity) * MAX(cost) AS sum_cost
, SUM(revenue) -
(SUM(quantity) * MAX(cost)) AS profit
FROM impress_sweets.sales
INNER JOIN impress_sweets.products
USING (product_id)
GROUP BY product_name
ORDER BY 5 DESC
LIMIT 3
結果テーブル
解説
［sales］テーブルと［products］テーブルを［product_id］をキーとして結合したうえで、［products］テーブルにある商品名（product_name）でグループ化しています。コストの合計を求めるために、［products］テーブルの［cost］を集計関数MAX(cost)で取得し、販売個数（quantity）の合計と掛け合わせるところは難しかったのではないでしょうか。
GROUP BY句でグループ化をすると、SELECT句に記述する指標はすべて集計関数で括る必要があります。［products］テーブルから取得した［cost］も例外ではありません。では、どのような集計関数で括ればよいかというと、（正解例ではMAX(cost)を使っていますが）MAXでも、MINでもAVGでもよいことになります。
なぜなら、例えば結合が完了し、かつ集計する前には［product_name］が「レアチーズケーキ」であるレコードは複数存在しますが、［cost］の値は、すべて400であるはずです。
どのレコードも同一の値なので、最大値（MAX）で集計しても、最小値（MIN）で集計しても、平均値（AVG）で集計しても必ず400となるからです。実際に正解SQL文のMAX(cost)をMINやAVGに変えて実験してみてください。集計関数には複数レコードにまたがる同一の値を、1つだけの値に集約する機能もあることを、今一度確認できるでしょう
