CASE文を使って縦持ちから横持ちデータに（備忘録）

1.CASE句の特徴

CASE句は縦持ちデータを横持ちにすることを得意としています。
ExcelでいうIF文という解釈で大丈夫だと思います

2.コードの全体像

今回のケースとして、年齢、男性の合計、女性の合計、そのほかの合計
をカラムとして、取得したいとします。

まずは、コードの全体像です。

WITH gender_age_group as (
SELECT gender_cd , age , sum(amount) amount
FROM `100_knock.receipt` r
JOIN `100_knock.customer` c
ON r.customer_id = c.customer_id
GROUP BY c.gender_cd ,c.age
)

SELECT
  age,
  COALESCE(sum(CASE WHEN gender_cd=0 THEN amount END),0) male,
  COALESCE(sum(CASE WHEN gender_cd=1 THEN amount END),0) female,
  COALESCE(sum(CASE WHEN gender_cd=9 THEN amount END),0) unknown
FROM gender_age_group
GROUP BY age,gender_cd
ORDER BY age

まずは、WITH句の中から確認していきます

SELECT gender_cd , age , sum(amount) amount
FROM `100_knock.receipt` r
JOIN `100_knock.customer` c
ON r.customer_id = c.customer_id
GROUP BY c.gender_cd ,c.age

これは至って簡単で、customerテーブルとtransactionデータであるreceiptテーブルを結合しています。

結合した後は、性別と年齢ごとにグルーピングを行なってそれぞれの合計金額を取得しています。

結果が以下の通り

上の結果をWITH句でサブクエリとします
これから、横持ちデータに変えていきたいと思います。
CASE句を使うことでうまくいきます

ですが、まずは年齢ごとにということなので先にGROUP BYでageごとに集計します

意識すべき点

その後に、CASEを使っています。
gender_cdが0で既に年齢でグルーピングしているので、実際にはデータは1つしかないので、sumを用いる必要性は？と僕は思いましたが、
今回の場合は、1つですが、サブクエリの持っているデータによっては2つ以上になりうるので、ここではsumを使わないとエラーが起きてしまいます。

このままだと、欠損値が現れる可能性があるので、
COALESCEで欠損値保管を行なっています。

SELECT
  age,
  COALESCE(sum(CASE WHEN gender_cd=0 THEN amount END),0) male,
  COALESCE(sum(CASE WHEN gender_cd=1 THEN amount END),0) female,
  COALESCE(sum(CASE WHEN gender_cd=9 THEN amount END),0) unknown
FROM gender_age_group
-- GROUP BY age
ORDER BY age