SQLアタマ養成講座

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

この記事を読むのに必要な時間:およそ 2 分

表頭の複雑な集計

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

表6 Employeesテーブル

emp_id
(社員ID)
dept
(部署)
sex
(性別)
age
(年齢)
salary
(給与:万円)
001製造3230
002製造3029
003製造2319
004会計4535
005会計5045
006営業4050
007営業4240
008営業5238
009営業3428
010営業4125
011人事2925
012人事3629

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

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

表7 表頭:年齢階級・性別,表側:部署

 若手ベテラン
製造1110
会計0020
営業0023
人事1001

リスト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 表頭:年齢階級・性別 表側:部署(小計・合計あり)

 合計若手ベテラン
 
製造3211110
会計2000220
営業5000523
人事2110101

これも,リスト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列を指定することで問題なく可能です。

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

著者プロフィール

ミック

SI企業に勤務するDBエンジニア。主にデータウェアハウス業務に従事している。自身のサイト「リレーショナル・データベースの世界」でデータベースとSQLについての技術情報を公開している。『Web+DB Press』で「SQLアタマアカデミー」を連載中。

著書:『達人に学ぶ SQL徹底指南書』(翔泳社、2008)訳書:J.セルコ『SQLパズル 第2版』(翔泳社、2007)

SQLアタマアカデミー:サポートページ

コメント

コメントの記入