Uzyskiwanie rekordów z najwyższą / najmniejszą grupą

Question

Uzyskiwanie rekordów z najwyższą / najmniejszą grupą

Jak to zrobić?

Poprzedni tytuł tego pytania brzmiał " using rank (@Rank := @Rank + 1) in complex query with subqueries-will it work? " bo szukałem rozwiązania przy użyciu rankingów, ale teraz widzę, że rozwiązanie opublikowane przez Billa jest o wiele lepsze.

Pytanie pierwotne:

Próbuję skomponować zapytanie, które pobierze ostatni rekord z każdej grupy w określonej kolejności:

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

Wyrażenie @Rank := @Rank + 1 jest zwykle używane do rangi, ale dla mnie to wygląda podejrzanie, gdy jest używany w 2 zapytaniach podrzędnych, ale zainicjowany tylko raz. Czy to zadziała w ten sposób?

A po drugie, czy będzie działać z jednym zapytaniem podrzędnym, które jest oceniane wiele razy? Podobnie jak subquery w klauzuli where (lub having) (inny sposób zapisu powyższego):

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

Z góry dzięki!

76

mysql greatest-n-per-group subquery rank

Author: TMS, 2012-01-06

Source

1 answers

score 147 · Accepted Answer

Więc chcesz dostać rząd z najwyższym OrderField na Grupę? Zrobiłbym to tak:

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)

(EDIT by Tomas: jeśli jest więcej rekordów z tym samym OrderField w tej samej grupie i potrzebujesz dokładnie jednego z nich, możesz chcieć rozszerzyć warunek:

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

Koniec edycji.)

Innymi słowy, zwraca wiersz t1, dla którego żaden inny wiersz t2 nie istnieje z tym samym GroupId i większym OrderField. Gdy t2.* jest NULL, oznacza to, że lewy zewnętrzny join nie znaleziono takiego dopasowania, a zatem t1 ma największą wartość OrderField w grupie.

Brak Rang, brak zapytań podrzędnych. Powinno to działać szybko i zoptymalizować dostęp do t2 za pomocą "Using index", Jeśli masz indeks złożony na (GroupId, OrderField).

Jeśli chodzi o Wydajność, zobacz moją odpowiedź na odzyskanie ostatniego rekordu w każdej grupie . Wypróbowałem metodę zapytań podrzędnych i metodę łączenia, używając zrzutu danych przepełnienia stosu. Różnica jest niezwykła: metoda łączenia działała 278 razy szybciej w moim teście.

Ważne jest, aby mieć odpowiedni Indeks, aby uzyskać najlepsze wyniki!

Jeśli chodzi o metodę używającą zmiennej @Rank, nie będzie ona działać tak, jak ją napisałeś, ponieważ wartości @Rank nie zostaną zresetowane do zera po przetworzeniu pierwszej tabeli przez zapytanie. Pokażę Ci przykład.

Wstawiłem niektóre atrapy danych, z dodatkowym polem, które jest null, z wyjątkiem wiersza, o którym wiemy, że jest największy na grupę:

select * from `Table`;

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

Możemy pokazać, że ranga wzrasta do trzech dla pierwszej grupy i sześciu dla drugiej grupy, a wewnętrzne zapytanie zwraca je poprawnie:

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 |
+---------+---------+

Teraz uruchom zapytanie bez warunku join, aby wymusić iloczyn kartezjański wszystkich wierszy, a także pobieramy wszystkie kolumny:

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 |
+---------+---------+---------+------------+------+------+

Z powyższego widać, że maksymalna ranga na Grupę jest poprawna, ale wtedy @ Ranga nadal rośnie, gdy przetwarza drugą pochodną tabelę, do 7 i wyżej. Tak więc szeregi z drugiej tabeli pochodnej nigdy nie pokrywają się z szeregi z pierwszej tabeli w ogóle.

Będziesz musiał dodać inną pochodną tabelę, aby wymusić @ Rank, aby zresetować do zera pomiędzy przetwarzaniem dwóch tabel (i mieć nadzieję, że optymalizator nie zmieni kolejności, w jakiej ocenia tabele, lub użyć STRAIGHT_JOIN, aby temu zapobiec): {]}

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 |
+---------+------------+------+------+

Ale optymalizacja tego zapytania jest straszna. Nie może używać żadnych indeksów, tworzy dwie tymczasowe tabele, sortuje je na twardo, a nawet używa bufora przyłączeniowego, ponieważ nie może używać indeks podczas dołączania tabel temp. Jest to Przykładowe wyjście z 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                  |
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+

Podczas gdy moje rozwiązanie przy użyciu lewego połączenia zewnętrznego optymalizuje się znacznie lepiej. Nie używa tabeli temp, a nawet raportów "Using index", co oznacza, że może rozwiązać połączenie za pomocą tylko indeksu, bez dotykania danych.

+----+-------------+-------+------+---------------+---------+---------+-----------------+------+--------------------------+
| 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 |
+----+-------------+-------+------+---------------+---------+---------+-----------------+------+--------------------------+

Prawdopodobnie przeczytasz ludzi piszących na swoich blogach twierdzenia, że "dołącza do SQL wolno", ale to nonsens. Słaba optymalizacja sprawia, że SQL jest powolny.