MySQL „Grupuj według” i „Zamów według”

Chcę móc wybrać kilka wierszy z tabeli e-maili i pogrupować je według nadawcy. Moje zapytanie wygląda następująco:

SELECT 
    `timestamp`, `fromEmail`, `subject`
FROM `incomingEmails` 
GROUP BY LOWER(`fromEmail`) 
ORDER BY `timestamp` DESC

Zapytanie działa prawie tak, jak chcę - wybiera rekordy pogrupowane według wiadomości e-mail. Problem polega na tym, że temat i sygnatura czasowa nie odpowiadają najnowszemu rekordowi dla określonego adresu e-mail.

Na przykład może zwrócić:

fromEmail: john@example.com, subject: hello
fromEmail: mark@example.com, subject: welcome

Gdy rekordy w bazie danych to:

fromEmail: john@example.com, subject: hello
fromEmail: john@example.com, subject: programming question
fromEmail: mark@example.com, subject: welcome

Jeśli temat „pytanie programistyczne” jest najnowszy, jak mogę zmusić MySQL do wybrania tego rekordu podczas grupowania wiadomości e-mail?

Prostym rozwiązaniem jest zawinięcie zapytania do podselekcji z najpierw instrukcją ORDER, a później zastosowanie GROUP BY :

SELECT * FROM ( 
    SELECT `timestamp`, `fromEmail`, `subject`
    FROM `incomingEmails` 
    ORDER BY `timestamp` DESC
) AS tmp_table GROUP BY LOWER(`fromEmail`)

Jest to podobne do używania złączenia, ale wygląda o wiele ładniej.

Używanie niezagregowanych kolumn w SELECT z klauzulą ​​GROUP BY jest niestandardowe. MySQL zwróci wartości pierwszego znalezionego wiersza, a resztę odrzuci. Wszelkie klauzule ORDER BY będą miały zastosowanie tylko do zwróconej wartości kolumny, a nie do odrzuconych.

WAŻNA AKTUALIZACJA Wybieranie kolumn nieagregowanych używanych w praktyce, ale nie należy na nich polegać. Zgodnie z dokumentacją MySQL „jest to przydatne przede wszystkim wtedy, gdy wszystkie wartości w każdej niezagregowanej kolumnie, której nie podano w GROUP BY, są takie same dla każdej grupy. Serwer może wybrać dowolną wartość z każdej grupy, więc jeśli nie są one takie same, wartości wybrane są nieokreślone . "

Od 5.7.5 ONLY_FULL_GROUP_BY jest domyślnie włączone, więc kolumny nieagregowane powodują błędy zapytań (ER_WRONG_FIELD_WITH_GROUP)

Jak @mikep wskazuje poniżej, rozwiązaniem jest użycie ANY_VALUE () od 5.7 i nowszych

Zobacz http://www.cafewebmaster.com/mysql-order-sort-group https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html https: //dev.mysql .com / doc / refman / 5.7 / pl / group-by-handling.html https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value

Oto jedno podejście:

SELECT cur.textID, cur.fromEmail, cur.subject, 
     cur.timestamp, cur.read
FROM incomingEmails cur
LEFT JOIN incomingEmails next
    on cur.fromEmail = next.fromEmail
    and cur.timestamp < next.timestamp
WHERE next.timestamp is null
and cur.toUserID = '$userID' 
ORDER BY LOWER(cur.fromEmail)

Zasadniczo dołączasz do stołu samodzielnie, wyszukując późniejsze wiersze. W klauzuli where stwierdzasz, że nie może być późniejszych wierszy. To daje tylko najnowszy wiersz.

Jeśli może istnieć wiele e-maili z tą samą sygnaturą czasową, to zapytanie wymaga doprecyzowania. Jeśli w tabeli e-maili znajduje się przyrostowa kolumna ID, zmień JOIN na przykład:

LEFT JOIN incomingEmails next
    on cur.fromEmail = next.fromEmail
    and cur.id < next.id

Jak już wskazano w odpowiedzi, bieżąca odpowiedź jest błędna, ponieważ GROUP BY arbitralnie wybiera rekord z okna.

Jeśli ktoś używa MySQL 5.6 lub MySQL 5.7 z ONLY_FULL_GROUP_BY, poprawne (deterministyczne) zapytanie to:

SELECT incomingEmails.*
  FROM (
    SELECT fromEmail, MAX(timestamp) `timestamp`
    FROM incomingEmails
    GROUP BY fromEmail
  ) filtered_incomingEmails
  JOIN incomingEmails USING (fromEmail, timestamp)
GROUP BY fromEmail, timestamp

Aby zapytanie działało sprawnie, wymagane jest odpowiednie indeksowanie.

Zwróć uwagę, że dla uproszczenia usunąłem rozszerzenie LOWER(), które w większości przypadków nie będzie używane.

Wykonaj GROUP BY po ORDER BY, opakowując zapytanie GROUP BY w następujący sposób:

SELECT t.* FROM (SELECT * FROM table ORDER BY time DESC) t GROUP BY t.from

Zgodnie ze standardem SQL nie można używać kolumn nieagregowanych na liście wyboru. MySQL pozwala na takie użycie (bez użycia trybu ONLY_FULL_GROUP_BY), ale wynik nie jest przewidywalny.

ONLY_FULL_GROUP_BY

Najpierw należy wybrać fromEmail, MIN (czytaj), a następnie, przy drugim zapytaniu (lub podzapytaniu) - Temat.

Zmagałem się z oboma tymi podejściami w przypadku bardziej złożonych zapytań niż te pokazane, ponieważ podejście do podzapytań było strasznie nieefektywne bez względu na to, jakie indeksy założyłem, i ponieważ nie mogłem uzyskać zewnętrznego sprzężenia samoczynnego przez Hibernate

Najlepszym (i najłatwiejszym) sposobem na to jest grupowanie według czegoś, co jest tak skonstruowane, że zawiera konkatenację wymaganych pól, a następnie wyciąganie ich za pomocą wyrażeń w klauzuli SELECT. Jeśli musisz wykonać MAX (), upewnij się, że pole, które chcesz MAX (), znajduje się zawsze na najbardziej znaczącym końcu łączonej encji.

Kluczem do zrozumienia tego jest to, że zapytanie może mieć sens tylko wtedy, gdy te inne pola są niezmienne dla dowolnej jednostki, która spełnia funkcję Max (), więc pod względem sortowania można zignorować inne elementy konkatenacji. Wyjaśnia, jak to zrobić, na samym dole tego łącza. http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

Jeśli możesz uzyskać zdarzenie wstawiania / aktualizacji (takie jak wyzwalacz), aby wstępnie obliczyć konkatenację pól, możesz je zindeksować, a zapytanie będzie tak szybkie, jakby grupa obejmowała tylko pole, które faktycznie chciałeś MAX ( ). Możesz nawet użyć go, aby uzyskać maksymalnie wiele pól. Używam go do wykonywania zapytań dotyczących drzew wielowymiarowych wyrażonych jako zbiory zagnieżdżone.