最大値を持ったレコードの抽出

【序文】

タイトルの通り。色々と考察してみる。

【環境】

SQLServer	SQLCMD
2017	14.0.1000.169

設定はデフォルト。

【最大値の取得と最大値を持ったレコードの抽出】

以下のようなデータがあったとする。

支店名	売上金額
東京	100
北京	200
南京	300

今回は一時テーブルを使ってみる。

-- 一時テーブル
SELECT * INTO #Src
FROM (VALUES
      ('東京', 100)
    , ('北京', 200)
    , ('南京', 300)
)t([支店名], [売上金額])
;
GO

最大値を求めるクエリは以下。

SELECT MAX([売上金額]) [最大売上金額] FROM #Src;
GO

実行結果は以下。

最大売上金額
-----------
        300

(1 行処理されました)

では、最大値を持ったレコードの抽出は？
初学者ならここで手が止まると思う。
MAX関数はあくまでも 値 を求めるもの。レコードを求めることはできない。ということに気が付く。
ではどうすれば良いか？　典型的な実装例は以下。

SELECT * FROM #Src
WHERE [売上金額] = (SELECT MAX([売上金額]) FROM #Src)
;
GO

実行結果は以下。

支店名  売上金額
---- -----------
南京           300

(1 行処理されました)

一旦 MAX関数で最大値を求め、それと一致するレコードを抽出。
しかし、この程度の処理の為に、2回も SELECT文を発行しなければならないものだろうか？　
使える局面は限定されるが、TOP句を使うという考え方もある。

SELECT TOP 1 * FROM #Src ORDER BY [売上金額] DESC;
GO

売上金額を降順でソートしての先頭 1レコードなので、前述のクエリと同じ結果が得られる。
又は、OFFSET句。

SELECT * FROM #Src ORDER BY [売上金額] DESC
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
;
GO

最近は TOP句よりこちらが推奨されているようだけど。
但し、先頭 1レコードと限定しているので、最大値を持ったレコードが複数存在する場合に対応できない。

その他の方法としてこんなクエリも。

SELECT * FROM #Src [S]
WHERE   NOT EXISTS(
            SELECT 1 FROM #Src WHERE [S].[売上金額] < [売上金額]
        )
;
GO

最大値を持っているということは、自分より大きい値を持ったレコードが他に存在しない、という考え方。
しかし、EXISTS句 を使った相関サブクエリは遅い、外部結合に置き換えるべき、という声が聞こえてきそうなので、以下。

SELECT [S].*
FROM #Src [S]
LEFT OUTER JOIN #Src [D]
    ON  [S].[売上金額] < [D].[売上金額]
WHERE   [D].[売上金額] IS NULL
;
GO

ここまでくると、色々と拗らせている感じ。
しかし考え方としては面白いと思う。引き出しは沢山あった方がいい。

【グループ毎の最大値を持ったレコードの取得】

次に以下のようなデータの場合。

支店名	年月	売上金額
東京	201901	200
東京	201902	100
東京	201903	300
北京	201901	400
北京	201902	300
北京	201903	200
南京	201901	300
南京	201902	500
南京	201903	500

各支店毎の売上金額が最大値のレコードを抽出したい。着地点は以下。

支店名	年月	売上金額
東京	201903	300
北京	201901	400
南京	201902	500
南京	201903	500

再び一時テーブルにデータを用意。

DROP TABLE #Src;
GO
-- 一時テーブル
SELECT * INTO #Src
FROM (VALUES
      ('東京', '201901', 200)
    , ('東京', '201902', 100)
    , ('東京', '201903', 300)
    , ('北京', '201901', 400)
    , ('北京', '201902', 300)
    , ('北京', '201903', 200)
    , ('南京', '201901', 300)
    , ('南京', '201902', 500)
    , ('南京', '201903', 500)
)t([支店名], [年月], [売上金額])
;
GO

支店名毎に売上金額が最大値のレコードを MAX関数で求めて……。面倒くさそう。
TOP句は全体に対する処理だから、支店名毎に先頭の、ということはできない。
ここは素直に RANK関数 だろう。その為に用意されているようなものだから。
実際にクエリを組んで動きを確認する。

SELECT
      *
    , RANK() OVER(PARTITION BY [支店名] ORDER BY [売上金額] DESC) [順位]
FROM #Src
;
GO

実行結果は以下。

支店名  年月     売上金額        順位
---- ------ ----------- --------------------
東京   201903         300                    1
東京   201901         200                    2
東京   201902         100                    3
南京   201902         500                    1
南京   201903         500                    1
南京   201901         300                    3
北京   201901         400                    1
北京   201902         300                    2
北京   201903         200                    3

(9 行処理されました)

PARTITION BY で支店名、ORDER BY で 金額 を指定することにより支店毎の順位が求められる。同率1位 にも対応している。これは期待した結果。
後は順位が 1位のレコードを抽出すれば良い。
ところが以下のクエリはエラーとなる。

SELECT
      *
    , RANK() OVER(PARTITION BY [支店名] ORDER BY [売上金額] DESC) [順位]
FROM #Src
WHERE [順位] = 1
;
GO
メッセージ 207、レベル 16、状態 1、サーバー ***、行 5
列名 '順位' が無効です。

ならばと WHERE句で直接 RANK関数を指定してみるもダメ。

SELECT *
FROM #Src
WHERE RANK() OVER(PARTITION BY [支店名] ORDER BY [売上金額] DESC) = 1
;
GO
メッセージ 4108、レベル 15、状態 1、サーバー ***、行 3
ウィンドウ関数は、SELECT 句または ORDER BY 句だけで使用できます。

これは誰もが最初に通る道では。
初学者に欠けているのは、クエリの解釈順の知識。一塊のクエリでも、データベースエンジンは分解して解釈する。
といっても難しい話ではない。常識的に考えた順番となる。
単純な SELECT文であれば以下。

① FROM句で対象テーブルを決定
② WHERE句でレコードを絞り込み
③ SELECT句で取得

対象テーブルが決定しなければ WHERE句で絞り込みようがないし、レコードが決定しなけれ SELECT句で取得はできない。常識的な判断で合っている。

その観点で改めて前述のクエリを見れば問題点が理解できる。
別名（シノニム）である 順位 は SELECT句で決定されるので、WHERE句で指定することは順番的に不可能。

RANK関数が WHERE句で指定できないのも、大雑把な話としては同じ。
もう少し詳しく言えば、ウィンドウ関数は FROM句や WHERE句で決定したレコードに対して作用する仕様なので、WHERE句には書けない。
これは SQL標準の仕様でもあるので、SQLServer に限った話でもない。

じゃぁどうすればいいの？　といえば、サブクエリや CTE で囲み一旦独立したデータセットとして確定し、それに対して改めて WHERE句で絞り込む。
実装例は以下。

;WITH [CTE_Ext]([支店名], [年月日], [売上金額], [順位]) AS (
    SELECT
          *
        , RANK() OVER(PARTITION BY [支店名] ORDER BY [売上金額] DESC)
    FROM #Src
)
SELECT * FROM [CTE_Ext]
WHERE [順位] = 1
;
GO

実行結果は以下。

支店名  年月日    売上金額        順位
---- ------ ----------- --------------------
東京   201903         300                    1
南京   201902         500                    1
南京   201903         500                    1
北京   201901         400                    1

(4 行処理されました)

少々回りくどいが、 RANK関数を使えば、上位〇位といった仕様にも対応できるし、応用範囲は広いのが利点。

【最大値を持ったレコードの抽出の補足】

再び最初のテーマに戻る。
以下のようなクエリが書ければ便利だろうけど無理。

SELECT * FROM #Src
WHERE [売上金額] = MAX([売上金額])
;
GO

理由はもう分かるよね。MAX関数は集計関数であり、ウィンドウ関数同様に SELECT句でしか使えないから。厳密には GROUP BY句と HAVING句でも使えるけど、少なくとも WHERE句では使えない。
従って前述のようにサブクエリ内で使うしかない。

【サブクエリの憂鬱（そのサブクエリ本当に必要？）】

システム開発の現場では、既存システムの改修等で恐ろしく複雑怪奇なクエリに直面し、ゲンナリすることがある。
特にサブクエリが多用されていて、サブクエリが入れ子になっているようなやつ。
しかも何故かサブクエリの中身は単なる SELECT文でそれを外部結合しているとか。
頼むから普通にテーブルの外部結合で組んでくれと。それじゃインデックスは効かないし、見辛いだけだし。（※）

前述のように、集計関数やウィンドウ関数はサブクエリ化しないと扱えない局面が多い。だから逆に言えば、それらではない単なる SELECT文がサブクエリに書かれていたら最適化されていない、適切ではない、と判断できる。必ずもっとスマートな組み方がある。コードレビュー時のチェックポイントの一つとして覚えておきた。

※）実行プランを確認すると、そういったクエリは結局普通のテーブル同士の外部結合に置き換えられる場合もあるようだけど。だからといってそれでいい訳ない。