複数列の最大値取得 #初心者

【序文】

列1、列2、列3、……の中の最大値を取得という処理は実務でもあるテーマ。列版 MAX関数のイメージ。Oracle なら GREATEST関数だが、SQLServer には相当のシステム関数はない。
SQLServer での実装方法を考察する。

【環境】

SQLServer	SQLCMD
2017	14.0.1000.169

設定はデフォルト。

【検証用データ】

以下のデータを想定。

氏名	国語	算数	理科
織田信長	60	50	40
豊臣秀吉	80	90	10
徳川家康	40	70	80

着地点は以下のイメージ。

氏名	国語	算数	理科	最大点
織田信長	60	50	40	60
豊臣秀吉	80	90	10	90
徳川家康	40	70	80	80

国語、算数、理科の中から最大の点数を取得し、[最大点]列に表示するものとする。

一時テーブル登録クエリは以下。

-- 一時テーブル
SELECT * INTO #Src
FROM (VALUES
      ('織田信長', 60, 50, 40)
    , ('豊臣秀吉', 80, 90, 10)
    , ('徳川家康', 40, 70, 80)
)t([氏名], [国語], [算数], [理科])
;
GO

【力技】

CASE式での実装例は以下。

-- CASE式
SELECT
      [氏名]
    , [国語]
    , [算数]
    , [理科]
    , CASE
        WHEN [国語] > [算数] AND [国語] > [理科] THEN [国語]
        WHEN [算数] > [国語] AND [算数] > [理科] THEN [算数]
        ELSE                                          [理科]
      END       [最大点]
FROM #Src
;
GO

実行結果は以下。

氏名       国語          算数          理科          最大点
-------- ----------- ----------- ----------- -----------
織田信長              60          50          40          60
豊臣秀吉              80          90          10          90
徳川家康              40          70          80          80

(3 行処理されました)

期待した結果が得られるがこれは悪手だろう。
もし集計対象列が増えたら CASE式全体に手を加えなければならず、保守性が悪いから。
しかしもし対象列が 2列限定なら以下のような IIF関数を利用したコードが簡潔で良いかもしれない。

IIF([国語] > [算数], [国語], [算数])

注）IIF関数は CASE式のシンタックスシュガー。

【パンがなければお菓子を食べればいいじゃない】

列の MAX関数がなければ列を行に変えればいいじゃない。
UNPIVOT関係演算子で列を行に変換し、MAX関数で最大値を取得。実装例は以下。

-- UNPIVOT
SELECT
      [Src].[氏名]
    , [Src].[国語]
    , [Src].[算数]
    , [Src].[理科]
    , MAX([Upv].[点])                                   [最大点]
FROM #Src                                               [Src]
INNER JOIN #Src
UNPIVOT([点] FOR [科目] IN ([国語], [算数], [理科]))    [Upv]
    ON  [Src].[氏名] = [Upv].[氏名]
GROUP BY
      [Src].[氏名]
    , [Src].[国語]
    , [Src].[算数]
    , [Src].[理科]
;
GO

これが模範的な回答か。

【VALUESコンストラクタ】

しかし、PIVOT UNPIVOT って構文が分かりにくいよなぁ。未だに空で書けないのは俺だけ？
VALUESコンストラクタは複数列を生成できるので、上記と同様のコードが書ける。実装例を以下。

-- VALUESコンストラクタ
SELECT
      [Src].[氏名]
    , [Src].[国語]
    , [Src].[算数]
    , [Src].[理科]
    , (SELECT MAX([点]) FROM (VALUES
              ((SELECT [国語] FROM #Src WHERE [氏名] = [Src].[氏名]))
            , ((SELECT [算数] FROM #Src WHERE [氏名] = [Src].[氏名]))
            , ((SELECT [理科] FROM #Src WHERE [氏名] = [Src].[氏名]))
      )t([点]))                                         [最大点]
FROM #Src                                               [Src]
GROUP BY
      [Src].[氏名]
    , [Src].[国語]
    , [Src].[算数]
    , [Src].[理科]
;
GO

【UNION ALL】

VALUESコンストラクタで書けるということは UNION ALL演算子でも書けるということ。
UNPIVOT関係演算子とかVALUESコンストラクタとかよく分からないし、という向きもこれなら書けるか。実装例は以下。

-- UNION ALL
;WITH [CTE_Unpivot]([氏名], [点]) AS (
                SELECT [氏名], [国語] FROM #Src
    UNION ALL   SELECT [氏名], [算数] FROM #Src
    UNION ALL   SELECT [氏名], [理科] FROM #Src
)
SELECT
      [Src].[氏名]
    , [Src].[国語]
    , [Src].[算数]
    , [Src].[理科]
    , MAX([Upv].[点])                                   [最大点]
FROM #Src                                               [Src]
INNER JOIN [CTE_Unpivot]                                [Upv]
    ON  [Src].[氏名] = [Upv].[氏名]
GROUP BY
      [Src].[氏名]
    , [Src].[国語]
    , [Src].[算数]
    , [Src].[理科]
;
GO

国語のレコード、算数のレコード、理科のレコードを UNION ALL演算子で結合することにより、列を行に変換している。
若干冗長なコードにはなってしまうが、これが一番単純で分かりやすのかも。
またクエリ全体構成として、結果的にこれが一番 UNPIVOT関係演算子を使う例に一番近いようにも見える。

【最後に】

結局行列変換の話になってしまった。
行列変換はややこしく、ヘルプや解説書を読んだだけでは中々頭に入ってこない。実際に書いて動きを確認すべし。