T-SQL歴2,3年目に伝えたい関数や構文

本記事は、私がT-SQLを触り始めて数年経った頃にMicrrosoftのドキュメントを斜め読みして知った関数や構文を記載してます。
知る前と知った後ではSQL文の読みやすさが変わりますので知ってて損はないと思います。

各セクションの構成は、簡単な説明と関数や構文を知らない人が書いた場合のSQL、知ってる人が書くであろうSQLを合わせて記載しています。

## 本記事で伝えたい関数や構文
1. COALESCE（式）
1. OVER句（ウィンドウ関数）
1. GROUP BY GROUPING SETS（GROUP BY句内使用できるキーワード）
1. PIVOT（FROM句内使用できるキーワード）
1. 再帰処理

# COALESCE（式）

ザックリ説明すると、ISNULL式の引数が複数設定できる版です。
厳密には少し動作が異なるので興味がある場合は1度ドキュメントを見る事を推奨します。

```sql:知る前
-- 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
```

```sql:知った後
SELECT COALESCE(Item1,Item2,Item3)
```

# OVER句（ウィンドウ関数）

ザックリ説明すると、取得結果のレコードは集計しなくても、特定のカラムだけ任意の範囲で集計やソートが出来る機能です。

```sql:知る前
-- インラインビューパターン
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

```

```sql:知った後
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の機能です。

```sql:知る前
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 人口データ 
```

```sql:知った後
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句内使用できるキーワード）

ザックリ説明すると、特定の列を横に展開し、集計する機能です。

```sql:知る前
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
    年度
```

```sql:知った後
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:知る前
-- 再帰処理の方法を知らない場合は、繰り返す数だけサブクエリを明示的に増やすしかない。
-- ただし、この方法だと動的に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
```

```sql:知った後
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/ |