SQL CASE文で効率よく集計するテクニック
「社員マスタ」と「売上明細」というテーブルがあります。売上明細テーブルには、社員の売上情報が格納されています。
「売上明細」には状態区分というカラムがあり、0:未確定、1:確定という情報を持っています。
| 社員コード | 社員名 | 部署コード | 年齢 |
|---|---|---|---|
| 1 | テスト社員1 | 1 | 22 |
| 2 | テスト社員2 | 2 | 30 |
| 3 | テスト社員3 | null | 20 |
| 売上NO | 社員コード | 売上日 | 売上金額 | 状態区分 |
|---|---|---|---|---|
| 1 | 1 | 20150401 | 200 | 0 |
| 2 | 1 | 20150402 | 300 | 1 |
| 3 | 2 | 20150408 | 100 | 1 |
| 4 | 2 | 20150501 | 150 | 1 |
| 5 | 3 | 20150505 | 550 | 0 |
社員ごとに確定売上金額の合計と未確定売上金額の合計をそれぞれ表示したい。
SQLは下記のようになります。
SELECT MAX(T1.社員名) AS 社員名
,SUM(CASE T2.状態区分
WHEN 0 THEN T2.売上金額
ELSE 0 END) AS 未確定売上金額
,SUM(CASE T2.状態区分
WHEN 1 THEN T2.売上金額
ELSE 0 END) AS 確定売上金額
FROM 社員マスタ AS T1
LEFT JOIN 売上明細 AS T2
ON T1.社員コード = T2.社員コード
GROUP BY T1.社員コード| 社員名 | 未確定売上金額 | 確定売上金額 |
|---|---|---|
| テスト社員1 | 200 | 300 |
| テスト社員2 | 0 | 250 |
| テスト社員3 | 550 | 0 |
SUMで集計する際に売上明細の状態区分を参照し、集計したいデータの場合（未確定金額なら0、確定金額なら1）のみ、そのデータの売上金額を足し、それ以外は0を足す、という処理を行っています。
ちなみに下記SQLのように、FROM句で結合条件を追加し、未確定売上のテーブルと確定売上テーブルとしてデータを取得する方法でも、集計は行えますが、あまり賢いやり方とは言えません。
SELECT MAX(T1.社員名) AS 社員名
,SUM(T2.売上金額) AS 未確定売上金額
,SUM(T3.売上金額) AS 確定売上金額
FROM 社員マスタ AS T1
LEFT JOIN 売上明細 AS T2
ON T1.社員コード = T2.社員コード
AND T2.状態区分 = 0
LEFT JOIN 売上明細 AS T3
ON T1.社員コード = T3.社員コード
AND T3.状態区分 = 1
GROUP BY T1.社員コードこのSQLでは売上明細テーブルを2回使用しているため、売上明細テーブルに大量のデータがあった場合、より多くの検索時間がかかってしまいます。
また、例えば売上明細の状態区分が3：キャンセル、4：返品などとあった場合、どんどんSQLが長く複雑になってしまいます。