第3回　SQL流条件分岐（3）　表頭の複雑な集計

表頭の複雑な集計

これはCASE式の使い方の中で最も使い勝手がよく応用範囲の広い技術なので、ぜひマスターしてください。表6のような、性別・年齢・部署別の給与を管理する人事テーブルがあるとします。

表6　Employeesテーブル

emp_id （社員ID）	dept （部署）	sex （性別）	age （年齢）	salary （給与：万円）
001	製造	男	32	30
002	製造	男	30	29
003	製造	女	23	19
004	会計	男	45	35
005	会計	男	50	45
006	営業	女	40	50
007	営業	女	42	40
008	営業	男	52	38
009	営業	男	34	28
010	営業	女	41	25
011	人事	男	29	25
012	人事	女	36	29

ここからいろいろな組み合わせのクロス表を作ってみましょう。こういう要件は実務の中でも頻繁に発生すると思いますが、まずは表7のような表頭が年齢階級・性別、表側が部署で、人数を集計した表です。いま年齢階級は便宜的に30歳以下を「若手⁠」⁠、それ以上を「ベテラン」という簡単な区分にしておきます。

「第1回　ウォーミングアップ」でも見たように、CASE式をSELECT句で使うことによって、条件に応じて集計したい行を指定できます。すると、表頭の4列をCASE式で作ることが可能になります（リスト7⁠）⁠。

表7　表頭：年齢階級・性別、表側：部署

	若手		ベテラン
	男	女	男	女
製造	1	1	1	0
会計	0	0	2	0
営業	0	0	2	3
人事	1	0	0	1

リスト7　表頭：部署・性別、表側：年齢階級

SELECT dept,
       SUM(CASE WHEN age <= 30 AND sex = '男' THEN 1 ELSE 0 END) AS "若手（男）",
       SUM(CASE WHEN age <= 30 AND sex = '女' THEN 1 ELSE 0 END) AS "若手（女）",
       SUM(CASE WHEN age >= 31 AND sex = '男' THEN 1 ELSE 0 END) AS "ベテラン（男）",
       SUM(CASE WHEN age >= 31 AND sex = '女' THEN 1 ELSE 0 END) AS "ベテラン（女）"
  FROM Employees
 GROUP BY dept;

CASE式の戻り値を0/1で指定しているのは、各行に対してビットフラグを立てていると思えばわかりやすいでしょう。あとはこのフラグが1の行数をSUM関数で数えることで、条件に合致した行数だけをカウントできるのです（このトリックは第3章でもう一度見ることになります⁠）⁠。

では、表頭に小計・合計の列も追加して、表8の場合はどうでしょう。

表8　表頭：年齢階級・性別　表側：部署（小計・合計あり）

	合計	若手			ベテラン
		計	男	女	計	男	女
製造	3	2	1	1	1	1	0
会計	2	0	0	0	2	2	0
営業	5	0	0	0	5	2	3
人事	2	1	1	0	1	0	1

これも、リスト7のクエリを簡単に修正するだけで対応できます（リスト8⁠）⁠。

リスト8　表頭：年齢階級・性別、表側：部署（小計・合計あり）

SELECT dept,
       COUNT(*),
       SUM(CASE WHEN age <= 30 THEN 1 ELSE 0 END) AS "若手（計）",
       SUM(CASE WHEN age <= 30 AND sex = '男' THEN 1 ELSE 0 END) AS "若手（男）",
       SUM(CASE WHEN age <= 30 AND sex = '女' THEN 1 ELSE 0 END) AS "若手（女）",
       SUM(CASE WHEN age >= 31 THEN 1 ELSE 0 END) AS "ベテラン（計）",
       SUM(CASE WHEN age >= 31 AND sex = '男' THEN 1 ELSE 0 END) AS "ベテラン（男）",
       SUM(CASE WHEN age >= 31 AND sex = '女' THEN 1 ELSE 0 END) AS "ベテラン（女）"
  FROM Employees
 GROUP BY dept;

合計列のCOUNT(*)はSUM(1)としても同じです。あるいはそう書いたほうが「無条件に行数をカウントしている」という意味が明確になるかもしれませんが、一般的にあまり見ない書式なので初めて見た人が驚くかもしれません。

また、リスト9のように、全列をCOUNT関数で揃えてもかまいません。その場合は、ELSE句で返すときにNULLを指定する必要があります。これは、COUNT関数が集計の際にNULLを除外してからカウントを取るという特性を利用しています（0を指定するとCOUNT関数はその行も数えてしまうのです⁠）⁠。

リスト9　全列をCOUNT関数で揃える

SELECT dept,
       COUNT(*),
       COUNT(CASE WHEN age <= 30 THEN 1 ELSE NULL END) AS "若手（計）",
       COUNT(CASE WHEN age <= 30 AND sex = '男' THEN 1 ELSE NULL END) AS "若手（男）",
       COUNT(CASE WHEN age <= 30 AND sex = '女' THEN 1 ELSE NULL END) AS "若手（女）",
       COUNT(CASE WHEN age >= 31 THEN 1 ELSE NULL END) AS "ベテラン（計）",
       COUNT(CASE WHEN age >= 31 AND sex = '男' THEN 1 ELSE NULL END) AS "ベテラン（男）",
       COUNT(CASE WHEN age >= 31 AND sex = '女' THEN 1 ELSE NULL END) AS "ベテラン（女）"
  FROM Employees
 GROUP BY dept;

このように、どれだけ表頭が複雑でも、入れ子が深くても、CASE式を集約関数の中に埋め込むことで簡単に記述できます。今の例題では人数を集計していましたが、もし給与の合計や平均を出したいならば、CASE式のTHEN句で返す値にsalary列を指定することで問題なく可能です。

ちなみに、表側が複雑な表を作る場合は、今回見たような表頭の複雑な表を作るよりもはるかに難しくなります。表側が入れ子であったり、合計・小計も合わせて出力するには、少し工夫をこらさねばなりません。これらの話については、また別の機会にすることにしましょう。