データベースの講義13日目です。
【講義内容】
- 自習
【ワンポイント】
クロス集計
WITH count_tmp AS( SELECT gender, user_place, COUNT(user_id) AS user_count FROM customers GROUP BY user_place, gender ORDER BY user_place DESC ), tmp AS( SELECT *, ROW_NUMBER() OVER(PARTITION BY gender) AS sequence FROM count_tmp ) SELECT gender, MAX(CASE sequence WHEN 1 THEN user_count ELSE NULL END) AS Tokyo, MAX(CASE sequence WHEN 2 THEN user_count ELSE NULL END) AS Osaka, MAX(CASE sequence WHEN 3 THEN user_count ELSE NULL END) AS Kanagawa FROM tmp GROUP BY gender ;
出力結果
+--------+-------+-------+----------+ | gender | Tokyo | Osaka | Kanagawa | +--------+-------+-------+----------+ | M | 2 | 1 | 2 | | W | 4 | 2 | 1 | +--------+-------+-------+----------+ 2 rows in set (0.00 sec)
【今日の積み上げ】
‐ データベースの基礎