SQLを用いた複数のグループで分けるクロス集計をcase文で実行する記述について

Question

参考書に記載されていたcase文の読み方はよくわかりません

次のようなテーブルを作成しデータを入れました。

DROP TABLE IF EXISTS master_customer;
create table master_customer(
user_id char(008),
age int,
sex char(008),
user_place char(016)
);
insert into master_customer values
('UID0001',45,'M','Kanagawa'),
('UID0002',31,'M','Osaka'),
('UID0003',22,'M','Tokyo'),
('UID0004',25,'W','Tokyo'),
('UID0005',36,'W','Tokyo'),
('UID0006',40,'W','Kanagawa'),
('UID0007',34,'M','Kanagawa'),
('UID0008',24,'W','Tokyo'),
('UID0009',30,'M','Tokyo'),
('UID0010',25,'W','Osaka'),
('UID0011',20,'W','Tokyo'),
('UID0012',31,'W','Osaka')

そこで次のようなSQLを実行し
それぞれの地域の男女の数をsqlで集計しました。

WITH count_tmp AS( 　　　　　　　　　　　　  -- (1)
    SELECT
        sex,
        user_place,
        COUNT(user_id) AS user_count
    FROM
        master_customer
    GROUP BY
        user_place,
        sex
    ORDER BY
        user_place DESC
),
tmp AS(                                   -- (2)
    SELECT
        *,
        ROW_NUMBER() OVER(
            PARTITION BY sex
            ORDER BY user_place
        ) AS sequence
    FROM
        count_tmp
)
SELECT                                    -- (3)
    sex,
    MAX(CASE sequence WHEN 1 THEN user_count ELSE NULL END) AS Kanagawa,
    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 Tokyo
FROM
    tmp
GROUP BY
    sex
;

集計用SQLについて
(1)と(2)のSQLは理解できました。
(1)はこれはuser_placeと性別でグループで分割した後にグループごとのデータ数を取得しています。さらに(2)ではウインドウ関数を使用して性別ごとに分割し場所ごとにorder byで並び替えること場所とsequenceという列の値がそれぞれ対応するようなデータが作成されます。
ここまでは理解できたのですが
(3)のcase文が理解できません
そもそもこのcase文は(1)、(2)によって作成されたデータがさらに性別でグループ分けされたデータを1文づつ相関サブクエリのように評価しているような記載が参考書にありました。
case文は集約関数であれば問題はないようです
case文はsequenseの値を一文づつ評価して該当しないものに関してはNULLを返し
sequenseというテーブルの中にNULLとそれ以外のデータが作りださせそれが集約関数でNULLが排除され値を返すイメージですか？
case文によって疑似的なテーブルが作成されるのでしょうか？
なんだかこのSQLの実行タイミングなどなかなかイメージが付きづらいので
どなたかどのようにcase文が結果を返しているの解説していただけますでしょうか？

以上です、よろしくお願いいたします。

Answer 1

こんな書換もあります。

select sex
     , count(user_place = 'Kanagawa' or null) Kanagawa
     , count(user_place = 'Osaka'    or null) Osaka
     , count(user_place = 'Tokyo'    or null) Tokyo
from master_customer
group by sex

共通テーブル式のテーブルを使うならこんな書換とか。

select
  sex
  , (array_agg(user_count)) [1] Kanagawa
  , (array_agg(user_count)) [2] Osaka
  , (array_agg(user_count)) [3] Tokyo 
from
  (select sex, user_count from tmp order by sex, sequence) tmp
group by
  sex

しかし一次元配列(集合)になるのか
僕は知らなかった。

Comments

[mayu-]

私の回答、一次元配列(集合)
という表現は確かに変でした。ご指摘感謝いたします。

[buzz]

そうなんですね。指摘だなんてそんな自覚は全くなかったです！

[mayu-]

肯定的に受け取っていますので
お気になさらないで下さい
今回も私は言葉の選択がまずいですね。

PostgreSQLは数あるDBの中でも
配列を扱う関数や機能が特に充実しているため
「配列」という言葉で
閲覧された方に誤解を与えないようにする
工夫と配慮が足りていませんでした。

[buzz]

丁寧な回答ありがとうございます。言葉の選択はなかなか難しいですよね。

Answer 2

ご提示いただいたSQL文
_ WITH count_tmp AS( ... GROUP BY sex ;
は
以下にご紹介するシンプルなSQLに書き換えが可能です。

SELECT sex
     , Sum( case user_place when 'Kanagawa' then 1 else 0 end ) Kanagawa
     , Sum( case user_place when 'Osaka'    then 1 else 0 end ) Osaka
     , Sum( case user_place when 'Tokyo'    then 1 else 0 end ) Tokyo
FROM master_customer
GROUP BY sex ;

次に、上記のSQLについて動作を説明いたしますと

case文によって疑似的なテーブルが作成されるのでしょうか？

「 疑似的なテーブル 」といいますか、case文によって作成されるのは
列ごとの一次元配列(集合)になります。
case文を実行した段階では、step1 のようなイメージになると申し上げれば
ご理解いただけるでしょうか。

• step1

sex	kanagawa	osaka	tokyo
M	[1,0,0,1,0]	[0,1,0,0,0]	[0,0,1,0,1]
W	[1,0,0,0,0,0,0]	[0,0,0,1,1,0,0]	[0,1,1,0,0,1,1]

　　
次に step1 の配列の中に含まれる数値を、sum関数を用いて合算します

• result

sex	kanagawa	osaka	tokyo
M	2	1	2
W	1	2	4

　
参考書の記載どおりになりました。

追記

k.fujisawaさんのご指摘を受けて回答を見返すと
先の私の回答、一次元配列(集合)という表現は確かに変でした。

インラインビューを用い、多段クエリにしたら

case文によって疑似的なテーブルが作成されるのでしょうか？

の「 疑似的なテーブルが作成される 」に近いSQLになりそうですね。

SELECT sex
     , sum(k) Kanagawa
     , sum(o) Osaka
     , sum(t) Tokyo
FROM
(
    SELECT sex
         , case user_place when 'Kanagawa' then 1 else 0 end k
         , case user_place when 'Osaka'    then 1 else 0 end o
         , case user_place when 'Tokyo'    then 1 else 0 end t
    FROM master_customer
) q
GROUP BY sex ;

このSQLだとどうでしょうか。
インラインビューqは「 疑似的なテーブル 」になっており、
結果セットは以下のようになります。

sex	k	o	t
M	1	0	0
M	0	1	0
M	0	0	1
M	1	0	0
M	0	0	1
W	1	0	0
W	0	0	1
W	0	0	1
W	0	1	0
W	0	1	0
W	0	0	1
W	0	0	1

Comments

[Carlos_is_only]

なるほど疑似的なテーブルがサブクエリ(?)として実行された結果作成され
集約関数で結果を取得できるということが理解できました。

Answer 3

先のコメントの続きです

値
A
B
A
B

上記のテーブルから値が"A"のレコードの数を合計するロジックを考えた時

SELECT SUM(CASE WHEN 値="A" THEN 1 ELSE 0 END)  AS 合計
FROM TBL

のロジックは

For i = 1 To 4
    合計 = 合計 + IIf(値(i) = "A", 1, 0)
Next

というイメージ

SELECT SUM(A) AS 合計
FROM (SELECT CASE WHEN 値="A" THEN 1 ELSE 0 END AS A
      FROM TBL)

のロジックは

For i = 1 To 4
    A(i) = IIf(値(i) = "A", 1, 0)
Next
For i = 1 To 4
    合計 = 合計 + A(i)
Next

というイメージ

結果的にはどちらも同じになります

Answer 4

SQLを理解するには、該当箇所を単純化して実行し結果を確認する方法が有効です。

(3)部分についてgroup byする前のSQLを実行してみます

SELECT
   sex,
   sequence,
   user_count,
   CASE sequence WHEN 1 THEN user_count ELSE NULL END AS Kanagawa,
   CASE sequence WHEN 2 THEN user_count ELSE NULL END AS Osaka,
   CASE sequence WHEN 3 THEN user_count ELSE NULL END AS Tokyo
FROM
   tmp

結果

sex	sequence	user_count	kanagawa	osaka	tokyo
M	1	2	2	(null)	(null)
M	2	1	(null)	1	(null)
M	3	2	(null)	(null)	2
W	1	1	1	(null)	(null)
W	2	2	(null)	2	(null)
W	3	4	(null)	(null)	4

(3)部分は上記の結果に対して集計関数を実行したのと同じです

SELECT 
  sex,
  MAX(Kanagawa),
  MAX(Osaka),
  MAX(Tokyo)
FROM (SELECT
          sex,
          CASE sequence WHEN 1 THEN user_count ELSE NULL END AS Kanagawa,
          CASE sequence WHEN 2 THEN user_count ELSE NULL END AS Osaka,
          CASE sequence WHEN 3 THEN user_count ELSE NULL END AS Tokyo
      FROM
          tmp
      ) t
GROUP BY 
  sex

結果

sex	kanagawa	osaka	tokyo
M	2	1	2
W	1	2	4

Comments

[Carlos_is_only]

なるほど、今回の場合ですとtmpテーブルからデータをcase文がサブクエリとして実行されさらにそこで集約関数が実行されnullデータが排除されるということですね

[takanaweb5]

>tmpテーブルからデータをcase文がサブクエリとして実行されさらにそこで集約関数が実行され

結果的にサブクエリとして実行された結果の集計結果と一致しますが、SQLの実行計画でサブクエリが実行されているわけではありません。
ただ、結果的にサブクエリとして実行された場合と結果は一致しますので、SUM関数などの集計関数の中で、CASE文が記述されている場合は、サブクエリをイメージして考えると理解しやすいでしょう。