Uzyskaj rekordy z najwyższym / najmniejszym <cokolwiek> na grupę

88

Jak to zrobić?

Dawny tytuł tego pytania brzmiał „ Używanie rangi (@Rank: = @Rank + 1) w złożonym zapytaniu z podzapytaniami - czy to zadziała? ”, Ponieważ szukałem rozwiązania przy użyciu rang, ale teraz widzę, że rozwiązanie przesłane przez Billa to dużo lepiej.

Oryginalne pytanie:

Próbuję utworzyć zapytanie, które pobierze ostatni rekord z każdej grupy przy 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 + 1jest zwykle używane do rangi, ale dla mnie wygląda podejrzanie, gdy jest używane w 2 podzapytaniach, ale jest inicjowane tylko raz. Czy to zadziała w ten sposób?

Po drugie, czy zadziała z jednym podzapytaniem, które jest oceniane wielokrotnie? Podobnie jak podzapytanie w klauzuli where (lub mającej) (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ękuję!

mysql  subquery  greatest-n-per-group  rank 
— TMS
źródło
2
bardziej zaawansowane pytanie tutaj stackoverflow.com/questions/9841093/ ...
— TMS

Odpowiedzi:

174

Czy chcesz uzyskać wiersz z najwyższą wartością OrderFieldna grupę? Zrobiłbym to w ten sposób:

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)

( EDYTUJ według Tomasa: Jeśli w tej samej grupie jest więcej rekordów z tym samym polem zamówienia 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, zwróć wiersz, t1dla którego nie ma innego wiersza t2z tym samym GroupIdi większym OrderField. Kiedy t2.*ma wartość NULL, oznacza to, że lewe sprzężenie zewnętrzne nie znalazło takiego dopasowania i dlatego t1ma największą wartość OrderFieldw grupie.

Bez rang, bez podzapytań. Powinno to działać szybko i optymalizować dostęp do t2 za pomocą opcji „Korzystanie z indeksu”, jeśli masz włączony indeks złożony (GroupId, OrderField).

Jeśli chodzi o wydajność, zobacz moją odpowiedź na temat Pobieranie ostatniego rekordu w każdej grupie . Wypróbowałem metodę podzapytania i metodę łączenia przy użyciu zrzutu danych przepełnienia stosu. Różnica jest niezwykła: w moim teście metoda łączenia działała 278 razy szybciej.

Aby uzyskać najlepsze wyniki, ważne jest, aby mieć odpowiedni indeks!

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

Wstawiłem kilka fikcyjnych danych, z dodatkowym polem, które ma wartość 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 zapytanie wewnętrzne 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 łączenia, 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 pozycja na grupę jest poprawna, ale wtedy @Rank nadal rośnie, gdy przetwarza drugą tabelę pochodną, ​​do 7 i wyżej. Zatem rangi z drugiej tabeli pochodnej w ogóle nie będą się pokrywać z rangami z pierwszej tabeli pochodnej.

Musiałbyś dodać kolejną tabelę pochodną, ​​aby zmusić @Rank do zresetowania do zera między przetwarzaniem dwóch tabel (i mieć nadzieję, że optymalizator nie zmieni kolejności, w której ocenia tabele, lub użyj 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 w trudny sposób, a nawet używa bufora łączenia, ponieważ nie może również używać indeksu podczas łączenia tabel tymczasowych. Oto przykładowe dane wyjściowe 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                  |
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+

Natomiast moje rozwiązanie wykorzystujące lewe złącze zewnętrzne optymalizuje się znacznie lepiej. Nie używa tabeli tymczasowej, a nawet raportów, "Using index"co oznacza, że ​​może rozwiązać łączenie, używając 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, którzy twierdzą na swoich blogach, że „przyłączenia spowalniają SQL”, ale to nonsens. Słaba optymalizacja spowalnia SQL.

— Bill Karwin
źródło
Może się to okazać przydatne (również w przypadku PO), ale niestety nie daje odpowiedzi na żadne z dwóch zadanych pytań.
— Andriy M,
Dzięki Bill, to dobry pomysł, jak unikać szeregów, ale ... czy dołączanie nie byłoby powolne? Łączenie (bez ograniczenia klauzuli gdzie) miałoby znacznie większy rozmiar niż w moich zapytaniach. W każdym razie dzięki za pomysł! Ale byłbym również interesujący w pierwotnym pytaniu, tj. Czy rangi działałyby w ten sposób.
— TMS,
Dzięki za doskonałą odpowiedź, Bill. A co jeśli użyję @Rank1i @Rank2, po jednym dla każdego podzapytania? Czy to rozwiązałoby problem? Czy to byłoby szybsze niż twoje rozwiązanie?
— TMS
Używanie @Rank1i @Rank2nie miałoby znaczenia.
— Bill Karwin,
2
Dzięki za to świetne rozwiązanie. Długo walczyłem z tym problemem. Dla osób, które chcą dodać filtry dla innych pól, np. "Foo", musisz dodać je do warunku złączenia, ... AND t1.foo = t2.fooaby później uzyskać prawidłowe wyniki dlaWHERE ... AND foo='bar'
— ownking
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.