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

課題

グループ毎に最大値や最小値を持つレコードを抽出する。

少し前までJOINを使って実現してきたけれど、実行時間が長くて困っていた。データ抽出にかかる時間を短くしたい。

実行

実行環境はMicrosoft SQL Server 2008 R2 Express.

次に示すサンプルテーブルを使って、果物ごとに一番安い値段を持つレコードを抽出する。

[ID]列は主キー。

サンプルテーブル

勝手に簡単に作った果物テーブル。

fruitsテーブル

ID	Name	Price
1	apple	100
2	apple	120
3	orange	50
4	orange	40
5	apple	80
6	orange	60
7	banana	120
8	banana	150

JOIN句での実現

SELECT
    [main].[ID]
  , [main].[Name]
  , [main].[Price]
FROM
  [fruits] AS [main]
JOIN (
  SELECT
      [Name]
    , MIN( [Price] ) AS [Price]
  FROM
    [fruits]
  GROUP BY
    [Name]
) AS [sub]
ON (
  [sub].[Name] = [main].[Name]
  AND [sub].[Price] = [main].[Price]
)
ORDER BY
  [main].[ID]

これくらいのテーブルだと全然実行時間は気にならないから例として微妙かも。

話は少しずれるけれど、JOINが増えると急に実行時間が長くなるし、クエリ自体も見難いと思う。

ROW_NUMBERを使った実装

SELECT
    [ID]
  , [Name]
  , [Price]
FROM (
  SELECT
      *
    , ROW_NUMBER() OVER ( PARTITION BY
                            [Name]
                          ORDER BY
                            [Price] ) AS [SEQ]
  FROM
    [fruits]
) AS [fr]
WHERE
  [SEQ] = 1
ORDER BY
  [ID]

クエリがすっきりして見やすい。実行時間については（レコードが少ないから）何も言えない。

評価

JOINとROW_NUMBERでの実現方法をまとめた。同じ目的でも複数の方法で実現できることもある。

サンプルデータが小さすぎて2方法の違いがクエリの見やすさくらいしかなった。もう少し多めのデータを使い、実行プランを見て比較したい。

参考

gihyo.jp