[解決済み] グループごとに最も大きい/小さい<whatever>レコードを取得する

質問

どうすればいいのでしょうか？

この質問の以前のタイトルは " でした。 サブクエリを含む複雑なクエリでランク（@Rank := @Rank + 1）を使用する - それは動作しますか？ というのも、私はランクを使用したソリューションを探していたからです。

元の質問です。

私は、定義された順序で各グループから最後のレコードを取得するクエリを作成しようとしています。

SET @Rank=0;

select s.*
from (select GroupId, max(Rank) AS MaxRank
      from (select GroupId, @Rank := @Rank + 1 AS Rank 
            from Table
            order by OrderField
            ) as t
      group by GroupId) as t 
  join (
      select *, @Rank := @Rank + 1 AS Rank
      from Table
      order by OrderField
      ) as s 
  on t.GroupId = s.GroupId and t.MaxRank = s.Rank
order by OrderField

表現方法 @Rank := @Rank + 1 は通常ランクに使用されますが、2つのサブクエリで使用され、一度だけ初期化されると、私には怪しく見えます。このように動作するのでしょうか？

そして第二に、それは複数回評価される1つのサブクエリで動作するのでしょうか？where（またはhaving）句のサブクエリのように（上記の書き方の別の方法）。

SET @Rank=0;

select Table.*, @Rank := @Rank + 1 AS Rank
from Table
having Rank = (select max(Rank) AS MaxRank
              from (select GroupId, @Rank := @Rank + 1 AS Rank 
                    from Table as t0
                    order by OrderField
                    ) as t
              where t.GroupId = table.GroupId
             )
order by OrderField

ありがとうございました。

どのように解決するのですか？

ということは、最も高い値を持つ行を取得したいのですね。 OrderField が最も高い行を取得したいのですか？ 私ならこのようにします。

SELECT t1.*
FROM `Table` AS t1
LEFT OUTER JOIN `Table` AS t2
  ON t1.GroupId = t2.GroupId AND t1.OrderField < t2.OrderField
WHERE t2.GroupId IS NULL
ORDER BY t1.OrderField; // not needed! (note by Tomas)

( Tomas氏による編集。 同じグループ内に同じOrderFieldを持つレコードが複数あり、そのうちの1つだけを必要とする場合、条件を拡張することができます。

SELECT t1.*
FROM `Table` AS t1
LEFT OUTER JOIN `Table` AS t2
  ON t1.GroupId = t2.GroupId 
        AND (t1.OrderField < t2.OrderField 
         OR (t1.OrderField = t2.OrderField AND t1.Id < t2.Id))
WHERE t2.GroupId IS NULL

編集終了)

つまり、行を返す t1 に対して、他の行 t2 が存在し、同じ GroupId が存在し、より大きな OrderField . このとき t2.* がNULLの場合、左外部結合がそのようなマッチを見つけられなかったことを意味し、したがって t1 は最大の値を持つ OrderField の値が最大となる。

ランクもサブクエリもありません。 これは高速に実行され、"Using index"でt2へのアクセスを最適化するはずで、もしあなたが (GroupId, OrderField) .

パフォーマンスについては、以下の回答を参照してください。 各グループの最後のレコードを取得する . 私はStack Overflowのデータダンプを使用してサブクエリメソッドとjoinメソッドを試しました。 私のテストでは、joinメソッドが278倍速く実行されました。

最良の結果を得るためには、正しいインデックスを持つことが重要なのです

Rank変数を使用する方法についてですが、クエリが最初のテーブルを処理した後、@Rankの値はゼロにリセットされないので、あなたが書いたようには動作しません。 例をお見せします。

グループごとに最大であることがわかっている行を除いて、NULLである余分なフィールドを持つ、いくつかのダミーデータを挿入しました。

select * from `Table`;

+---------+------------+------+
| GroupId | OrderField | foo  |
+---------+------------+------+
|      10 |         10 | NULL |
|      10 |         20 | NULL |
|      10 |         30 | foo  |
|      20 |         40 | NULL |
|      20 |         50 | NULL |
|      20 |         60 | foo  |
+---------+------------+------+

ランクは最初のグループで3、2番目のグループで6に増加し、内側のクエリはこれらを正しく返すことがわかります。

select GroupId, max(Rank) AS MaxRank
from (
  select GroupId, @Rank := @Rank + 1 AS Rank
  from `Table`
  order by OrderField) as t
group by GroupId

+---------+---------+
| GroupId | MaxRank |
+---------+---------+
|      10 |       3 |
|      20 |       6 |
+---------+---------+

ここで、全ての行のデカルト積を強制するために、結合条件なしでクエリを実行し、全てのカラムも取得します。

select s.*, t.*
from (select GroupId, max(Rank) AS MaxRank
      from (select GroupId, @Rank := @Rank + 1 AS Rank 
            from `Table`
            order by OrderField
            ) as t
      group by GroupId) as t 
  join (
      select *, @Rank := @Rank + 1 AS Rank
      from `Table`
      order by OrderField
      ) as s 
  -- on t.GroupId = s.GroupId and t.MaxRank = s.Rank
order by OrderField;

+---------+---------+---------+------------+------+------+
| GroupId | MaxRank | GroupId | OrderField | foo  | Rank |
+---------+---------+---------+------------+------+------+
|      10 |       3 |      10 |         10 | NULL |    7 |
|      20 |       6 |      10 |         10 | NULL |    7 |
|      10 |       3 |      10 |         20 | NULL |    8 |
|      20 |       6 |      10 |         20 | NULL |    8 |
|      20 |       6 |      10 |         30 | foo  |    9 |
|      10 |       3 |      10 |         30 | foo  |    9 |
|      10 |       3 |      20 |         40 | NULL |   10 |
|      20 |       6 |      20 |         40 | NULL |   10 |
|      10 |       3 |      20 |         50 | NULL |   11 |
|      20 |       6 |      20 |         50 | NULL |   11 |
|      20 |       6 |      20 |         60 | foo  |   12 |
|      10 |       3 |      20 |         60 | foo  |   12 |
+---------+---------+---------+------------+------+------+

上記から、グループごとの最大ランクが正しいことがわかりますが、その後、@Rankは2番目の派生テーブルを処理するにつれて、7、それ以上と増え続けています。 そのため、2つ目の派生テーブルのランクは、1つ目の派生テーブルのランクと全く重なりません。

2つのテーブルを処理する間に@Rankを強制的にゼロにリセットするために、別の派生テーブルを追加する必要があります（そして、オプティマイザがテーブルを評価する順序を変更しないようにするか、それを防ぐためにSTRAIGHT_JOINを使用することを望みます）。

select s.*
from (select GroupId, max(Rank) AS MaxRank
      from (select GroupId, @Rank := @Rank + 1 AS Rank 
            from `Table`
            order by OrderField
            ) as t
      group by GroupId) as t 
  join (select @Rank := 0) r -- RESET @Rank TO ZERO HERE
  join (
      select *, @Rank := @Rank + 1 AS Rank
      from `Table`
      order by OrderField
      ) as s 
  on t.GroupId = s.GroupId and t.MaxRank = s.Rank
order by OrderField;

+---------+------------+------+------+
| GroupId | OrderField | foo  | Rank |
+---------+------------+------+------+
|      10 |         30 | foo  |    3 |
|      20 |         60 | foo  |    6 |
+---------+------------+------+------+

しかし、このクエリの最適化はひどいものです。 インデックスを使用することができず、2つの一時テーブルを作成し、それらを難しい方法でソートし、さらに一時テーブルを結合する際にインデックスを使用できないので結合バッファを使用しています。 これは以下の出力の例です。 EXPLAIN :

+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+
|  1 | PRIMARY     | <derived4> | system | NULL          | NULL | NULL    | NULL |    1 | Using temporary; Using filesort |
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL | NULL    | NULL |    2 |                                 |
|  1 | PRIMARY     | <derived5> | ALL    | NULL          | NULL | NULL    | NULL |    6 | Using where; Using join buffer  |
|  5 | DERIVED     | Table      | ALL    | NULL          | NULL | NULL    | NULL |    6 | Using filesort                  |
|  4 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | No tables used                  |
|  2 | DERIVED     | <derived3> | ALL    | NULL          | NULL | NULL    | NULL |    6 | Using temporary; Using filesort |
|  3 | DERIVED     | Table      | ALL    | NULL          | NULL | NULL    | NULL |    6 | Using filesort                  |
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+

一方、左外部結合を使用する私のソリューションは、より良く最適化されます。 これはテンポラリテーブルを使用せず、さらに "Using index" を報告します。これはデータに触れることなく、インデックスのみを使用して結合を解決できることを意味します。

+----+-------------+-------+------+---------------+---------+---------+-----------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref             | rows | Extra                    |
+----+-------------+-------+------+---------------+---------+---------+-----------------+------+--------------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL    | NULL    | NULL            |    6 | Using filesort           |
|  1 | SIMPLE      | t2    | ref  | GroupId       | GroupId | 5       | test.t1.GroupId |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+---------+---------+-----------------+------+--------------------------+

ブログで「結合はSQLを遅くする」と主張している人たちを見かけることがありますが、それはナンセンスです。 しかし、それはナンセンスです。不十分な最適化がSQLを遅くするのです。