グループ毎に最大値や最小値を持つレコードを抽出する（その２）

課題

以前「グループ毎に最大値や最小値を持つレコードを抽出する」という記事をまとめた（下記リンク）。

2015-09-28 - とりあえず半歩

この時は自前のサンプルデータを用意して試していたが、コードが小奇麗になったくらいで実行時間などいまいち違いがわからなかった。そこで今回は、昨日アタッチしたAdventureWorksのサンプルデータを使って試し、実行プランを眺めてみる。

実行

実行環境はSQL Server 2008 R2 + Management Studio.

サンプルデータとしてAdventureWorks.Sales.SalesPersonテーブルを用いる。構成は以下のようになってる（レコードはもう少しあるけれど省略）。

TerritoryID	Name	CountryRegionCode	Group	SalesYTD	SalesLastYear	CostYTD	CostLastYear	rowguid	ModifiedDate
1	Northwest	US	North America	7887186.7882	3298694.4938	0.00	0.00	43689A10-E30B-497F-B0DE-11DE20267FF7	2002-06-01 00:00:00.000
2	Northeast	US	North America	2402176.8476	3607148.9371	0.00	0.00	00FB7309-96CC-49E2-8363-0A1BA72486F2	2002-06-01 00:00:00.000
3	Central	US	North America	3072175.118	3205014.0767	0.00	0.00	DF6E7FD8-1A8D-468C-B103-ED8ADDB452C1	2002-06-01 00:00:00.000

今回はTerritoryIDごとにSalesYTDが最大値のレコードを抽出する（因みにYTDはYearToDateで、去年から今日までとか過去１年間とかという意味らしい）。

JOIN文での実現

SELECT
  *
FROM
  [AdventureWorks2008R2].[Sales].[SalesPerson] AS [base]
JOIN (
  SELECT
      [TerritoryID]
    , MAX( [SalesYTD] ) AS [MaxSalesYTD]
  FROM
    [AdventureWorks2008R2].[Sales].[SalesPerson]
  GROUP BY
    [TerritoryID]
) AS [temp]
ON (
  (
    [temp].[TerritoryID] = [base].[TerritoryID]
    OR ( [temp].[TerritoryID] IS NULL AND [base].[TerritoryID] IS NULL )
  )
  AND [temp].[MaxSalesYTD] = [base].[SalesYTD]
);

以下は抽出結果。

BusinessEntityID	TerritoryID	SalesQuota	Bonus	CommissionPct	SalesYTD	SalesLastYear	rowguid	ModifiedDate
274	NULL	NULL	0.00	0.00	559697.5639	0.00	48754992-9EE0-4C0E-8C94-9451604E3E02	2005-01-28 00:00:00.000
284	1	300000.00	3900.00	0.019	1576562.1966	0.00	AC94EC04-A2DC-43E3-8654-DD0C546ABC17	2006-10-25 00:00:00.000
275	2	300000.00	4100.00	0.012	3763178.1787	1750406.4785	1E0A7274-3064-4F58-88EE-4C6586C87169	2005-06-24 00:00:00.000
277	3	250000.00	2500.00	0.015	3189418.3662	1997186.2037	39012928-BFEC-4242-874D-423162C3F567	2005-06-24 00:00:00.000
276	4	250000.00	2000.00	0.015	4251368.5497	1439156.0291	4DD9EEE4-8E81-4F8C-AF97-683394C1F7C0	2005-06-24 00:00:00.000
279	5	300000.00	6700.00	0.01	2315185.611	1849640.9418	52A5179D-3239-4157-AE29-17E868296DC0	2005-06-24 00:00:00.000
282	6	250000.00	5000.00	0.015	2604540.7172	2038234.6549	31FD7FC1-DC84-4F05-B9A0-762519EACACC	2005-06-24 00:00:00.000
290	7	250000.00	985.00	0.016	3121616.3202	2396539.7601	F509E3D4-76C8-42AA-B353-90B7B8DB08DE	2006-06-24 00:00:00.000
288	8	250000.00	75.00	0.018	1827066.7118	1307949.7917	224BB25A-62E3-493E-ACAF-4F8F5C72396A	2007-06-24 00:00:00.000
286	9	250000.00	5650.00	0.018	1421810.9242	2278548.9776	9B968777-75DC-45BD-A8DF-9CDAA72839E1	2007-06-24 00:00:00.000
289	10	250000.00	5150.00	0.02	4116871.2277	1635823.3967	25F6838D-9DB4-4833-9DDC-7A24283AF1BA	2006-06-24 00:00:00.000

そして次は実行プラン。サブツリーの推定コストは0.0151164。

ROW_NUMBER関数の実現

SELECT
    [BusinessEntityID]
  , [TerritoryID]
  , [SalesQuota]
  , [Bonus]
  , [CommissionPct]
  , [SalesYTD]
  , [SalesLastYear]
  , [rowguid]
  , [ModifiedDate]
FROM (
  SELECT
      *
    , ROW_NUMBER() OVER ( PARTITION BY
                            [TerritoryID]
                          ORDER BY
                            [SalesYTD] DESC ) AS [SEQ]
  FROM
    [AdventureWorks2008R2].[Sales].[SalesPerson]
) AS [temp]
WHERE
  [SEQ] = 1

抽出結果はJOIN文での結果と同じ。実行プランは次の図で、サブツリーの推定コストは0.0147803。

評価

ROW_NUMBER関数で実現したクエリがJOIN文での実現方法よりもほんの少しコストが低い。 実行プランを見るとJOIN文の方が少しわちゃわちゃしている。 どうも[TerritoryID]がNULLのレコードを抽出するためのON句の条件が原因みたい。 NULLは無視するようON句内のOR以降の条件を外すと実行プランはシンプルになった。

あくまでSalesPersonテーブルでの結果なので、レコード数が増えるとまた違うかもしれないし、更に差が顕著になるかもしれない。 今回のようなクエリを書いて実行時間に時間がかかるようなら、JOIN文とROW_NUMBER関数の両方を用意して実行プランを見て選ぶと良いかな。