本記事は、私がT-SQLを触り始めて数年経った頃にMicrrosoftのドキュメントを斜め読みして知った関数や構文を記載してます。
知る前と知った後ではSQL文の読みやすさが変わりますので知ってて損はないと思います。
各セクションの構成は、簡単な説明と関数や構文を知らない人が書いた場合のSQL、知ってる人が書くであろうSQLを合わせて記載しています。
本記事で伝えたい関数や構文
- COALESCE（式）
- OVER句（ウィンドウ関数）
- GROUP BY GROUPING SETS（GROUP BY句内使用できるキーワード）
- PIVOT（FROM句内使用できるキーワード）
- 再帰処理
COALESCE（式）
ザックリ説明すると、ISNULL式の引数が複数設定できる版です。
厳密には少し動作が異なるので興味がある場合は1度ドキュメントを見る事を推奨します。
-- ISNULLで書くパターン
SELECT ISNULL(Item1,ISNULL(Item2,Item3))
-- CASEで書くパターン
SELECT
CASE
WHEN Item1 IS NOT NULL THEN Item1
WHEN Item2 IS NOT NULL THEN Item2
WHEN Item3 IS NOT NULL THEN Item3
ELSE NULL END
SELECT COALESCE(Item1,Item2,Item3)
OVER句（ウィンドウ関数）
ザックリ説明すると、取得結果のレコードは集計しなくても、特定のカラムだけ任意の範囲で集計やソートが出来る機能です。
-- インラインビューパターン
WITH Tmp売上合計 AS
(
SELECT
顧客コード
,SUM(売上金額) AS 合計売上金額
FROM
売上データ
GROUP BY
顧客コード
)
SELECT
T1.顧客コード
,T1.売上日
,T1.売上金額
,T2.合計売上金額
FROM
売上データ AS T1
LEFT JOIN
Tmp売上合計 AS T2
ON T1.顧客コード = T2.顧客コード
;
-- サブクエリパターン
SELECT
T1.顧客コード
,T1.売上日
,T1.売上金額
,(
SELECT
SUM(売上金額)
FROM
売上データ AS T2
WHERE T1.顧客コード = T2.顧客コード
GROUP BY
顧客コード
) AS 合計売上金額
FROM
売上データ AS T1
SELECT
T1.顧客コード
,T1.売上日
,T1.売上金額
,SUM(T1.売上金額) OVER(PARTITION BY 顧客コード) AS 合計売上金額
FROM
売上データ AS T1
例ではSUM関数でOVER句を使用しましたが、集計関数(AVG,MAXなど)や順位付け関数(RANK,ROW_NUMBERなど)などに使用できます。
GROUP BY GROUPING SETS（GROUP BY句内使用できるキーワード）
ザックリ説明すると、集計の組み合わせが複数のパターンで指定が出来きるGROUP BYの機能です。
SELECT '-' AS 国 ,'-' AS 県 ,'-' AS 市 ,SUM(人口) AS 人口
FROM 人口データ
--全集計の場合はGROUP BYは省略可能
UNION ALL
SELECT 国 ,'-' AS 県 ,'-' AS 市 ,SUM(人口) AS 人口
FROM 人口データ
GROUP BY 国
UNION ALL
SELECT 国 ,県 ,'-' AS 市 ,SUM(人口) AS 人口
FROM 人口データ
GROUP BY 国 ,県
UNION ALL
SELECT 国 ,県 ,市 ,人口
FROM 人口データ
SELECT
CASE WHEN GROUPING(国) = 1 THEN '-' ELSE 国 END AS 国
,CASE WHEN GROUPING(県) = 1 THEN '-' ELSE 県 END AS 県
,CASE WHEN GROUPING(市) = 1 THEN '-' ELSE 市 END AS 市
,SUM(人口) AS 人口
FROM
人口データ
GROUP BY GROUPING SETS
(()
,(国)
,(国,県)
,(国,県,市))
GROUPING SETS以外にも「ROLLUP 」「CUBE」がありますので興味があれば調べてみてください。
ちなみに、今回の例の場合はROLLUPの方が簡潔に書けます。
SELECT句内でGROUPING関数を使ってますが無くても動作します。GROUPING関数は、引数で渡した項目が集計対象とされているかを判断出来る関数で、該当項目が集計された場合の設定値をNULL以外に変更するときなどに使ったりします。
PIVOT（FROM句内使用できるキーワード）
ザックリ説明すると、特定の列を横に展開し、集計する機能です。
SELECT
年度
,SUM(CASE WHEN 地方区域 = '東日本' THEN 人口 ELSE 0 END) AS 東日本人口
,SUM(CASE WHEN 地方区域 = '中日本' THEN 人口 ELSE 0 END) AS 中日本人口
,SUM(CASE WHEN 地方区域 = '西日本' THEN 人口 ELSE 0 END) AS 西日本人口
FROM 人口データ
GROUP BY
年度
SELECT
PV.年度
,PV.東日本 AS 東日本人口
,PV.中日本 AS 中日本人口
,PV.西日本 AS 西日本人口
FROM 人口データ AS T
PIVOT (
SUM(人口)
FOR 地方区域 IN (東日本,中日本,西日本)
) AS PV
UNPIVOTもあり、挙動はPIVOTの反対で複数のカラムを1つのカラムにまとめる機能です。
再帰処理
ザックリ説明すると、前回の処理結果を使って同様の処理を何度も繰り返す処理です。
サンプルデータ
| 親コード | 子コード |
|---|---|
| 1 | 10 |
| 1 | 11 |
| 1 | 12 |
| 10 | 100 |
| 11 | 101 |
| 10 | (空) |
-- 再帰処理の方法を知らない場合は、繰り返す数だけサブクエリを明示的に増やすしかない。
-- ただし、この方法だと動的にSQL文を作らない限り繰り返し回数の上限が必ず存在してしまう。
WITH Tmp1 AS
(
SELECT
親コード
,子コード
,親コード + '/' + 子コード AS 階層
FROM
サンプルデータ
WHERE
親コード = 1
),
Tmp2 AS
(
SELECT
T1.親コード
,T1.子コード
,T2.階層 + '/' + T1.子コード AS 階層
FROM
サンプルデータ AS T1
INNER JOIN
Tmp1 AS T2
ON T1.親コード = T2.子コード
),
Tmp3 AS
(
SELECT
T1.親コード
,T1.子コード
,T2.階層 + '/' + T1.子コード AS 階層
FROM
サンプルデータ AS T1
INNER JOIN
Tmp2 AS T2
ON T1.親コード = T2.子コード
)
SELECT * FROM Tmp1 UNION ALL
SELECT * FROM Tmp2 UNION ALL
SELECT * FROM Tmp3
WITH TmpLoop AS
(
SELECT
親コード
,子コード
,親コード + '/' + 子コード AS 階層
FROM
サンプルデータ
WHERE
親コード = 1
UNION ALL
SELECT T1.親コード
,T1.子コード
,T2.階層 + '/' + T1.子コード AS 階層
FROM
サンプルデータ AS T1
,TmpLoop AS T2
WHERE
T1.親コード = T2.子コード
)
SELECT * FROM TmpLoop
OPTION (MAXRECURSION 2) -- 繰り返し回数の上限
結果
| 親コード | 子コード | 階層 |
|---|---|---|
| 1 | 10 | 1/10 |
| 1 | 11 | 1/11 |
| 1 | 12 | 1/12 |
| 10 | 100 | 1/10/100 |
| 11 | 101 | 1/11/101 |
| 100 | (空) | 1/10/100/ |