grupowanie z jednym polem max Opcje

[MadMark]

cześć,
potrzebuję pomocy z grupowaniem tj. mam tabelę

[SQL] pobierz, plaintext 
CREATE TABLE modifications ( 
    id int PRIMARY KEY AUTO_INCREMENT,
    date timestamp DEFAULT timestmp NOT NULL,
    object_id int, -- fk to objects
    status_id int, -- fk to statuses
);
[SQL] pobierz, plaintext 

i chciałbym pobrać wszystkie wiersze, w których status_id jest najnowszy (max(date)) w ramach jednego object_id. Mówiąc krótko, dla każdego okiektu potrzebny mi najnowszy status.

Działa (ale za wolno, bo ok. 200 ms)

[SQL] pobierz, plaintext 
SELECT f.* FROM modifications f WHERE (f.obiect_id, f.date) IN (SELECT g.obiect_id AS obiect_id, max(g.date) AS date FROM modifications g GROUP BY g.obiect_id)
[SQL] pobierz, plaintext 

Samo zrobienie:

[SQL] pobierz, plaintext 
SELECT * FROM modifications GROUP BY obiect_id HAVING date=max(date)
[SQL] pobierz, plaintext 

nie działa, ponieważ zwraca pierwszy napotkany status, a nie ten pasujący do maksymalnej daty (nie najnowszy, a pierwszy insertowany).

Jak powinno wyglądać najprostsze zapytanie, żeby zwrócić najnowszy status dla każdego obiektu ?

[trueblue]

[SQL] pobierz, plaintext 
SELECT object_id,MAX(status_id) FROM modifications GROUP BY object_id;
[SQL] pobierz, plaintext 

[MadMark]

[SQL] pobierz, plaintext 
SELECT object_id,MAX(status_id) FROM modifications GROUP BY object_id;
[SQL] pobierz, plaintext 

Problem polega na tym, żę taka wersja nie wchodzi w grę. status_id może być w różnej kolejności np. Usunięty = 1, Nowy = 2 - czyli, jeśli status będzie maxem, to nie będzie usuniętych. Dlatego potrzebuję użyć daty (IMG:style_emoticons/default/smile.gif)

[mmmmmmm]

Twoje pierwsze zapytanie jest jedynym (oprócz takiego samego JOINa), które jest prawidłowe i szybkie. 200 ms to nie tragedia.
Drugie (to z HAVING) to tylko na MySQL się wykona. Każda inna baza zasygnalizuje błąd.
Jeśli jednak to rozwiązanie cię nie satysfakcjonuje to pomyśl nad zmianą architektury - tak, bys przechwowywał (w tej lub innej tabeli) tylko najnowsze statusy

[trueblue]

Źle przecyzytałem problem.
Czy większa data oznacza większe id, czy też dla mniejszego id może być większa data (oczywiście w ramach tego samego object_id)?

[MadMark]

Źle przecyzytałem problem.
Czy większa data oznacza większe id, czy też dla mniejszego id może być większa data (oczywiście w ramach tego samego object_id)?

Większa data może oznaczać mniejsze ID. Dlatego właśnie potrzebuje wybrać to id, które należy do rekordu z największą datą, mysql zwraca mi niestety pierwsze napotkane.

[trueblue]

A jak to działa w ten sposób?

[SQL] pobierz, plaintext 
SELECT object_id,status_id FROM modifications AS m
JOIN (SELECT object_id,MAX(date) AS max_date FROM modifications GROUP BY object_id) AS m2 ON m2.max_date=m.date AND m2.object_id=m.object_id;
[SQL] pobierz, plaintext 

Na dacie przydałby się indeks.

[MadMark]

Twoje pierwsze zapytanie jest jedynym (oprócz takiego samego JOINa), które jest prawidłowe i szybkie. 200 ms to nie tragedia.
Drugie (to z HAVING) to tylko na MySQL się wykona. Każda inna baza zasygnalizuje błąd.
Jeśli jednak to rozwiązanie cię nie satysfakcjonuje to pomyśl nad zmianą architektury - tak, bys przechwowywał (w tej lub innej tabeli) tylko najnowsze statusy

200 to tragedia, bo ta sama konstrukcja z self-joinem w postaci:

[SQL] pobierz, plaintext 
SELECT g.* FROM (SELECT obiect_id, max(date) AS date FROM modifications GROUP BY obiect_id) f
LEFT JOIN modifications g ON (f.obiect_id=g.obiect_id AND f.date=g.date)
[SQL] pobierz, plaintext 

wykonuje sie 4x szybciej (czasem nawet 5), ale tworzy tabelę tymczasową w myisamie i większość czasu zabiera przerzucanie danych między innodb i myisamem.
To jest serwer współdzielony, więc nie ma szans na zmianę tabel tymczasowych z myisam na innodb, żeby nie tracić na to czasu, bo wtedy to (i pozostałe) zapytanie wykonywałoby się w okolicach 10 ms (tak wynika z profilera).

Dlatego najlepiej by było zmieścić to w jednym zapytaniu, bez widoków, bez sub-queries, bez unionów.

Ten post edytował MadMark 12.12.2016, 12:43:58

[mmmmmmm]

Sorry, ale ja nie widze różnic pomiędzy tymi zapytaniami. Tym z (x,y) IN (..,..) a JOIN. Zresztą już wcześniej napisałem o JOIN. Optymalizator kosztowy powinien je na taki sam algorytm przerobić.

[MadMark]

Sorry, ale ja nie widze różnic pomiędzy tymi zapytaniami. Tym z (x,y) IN (..,..) a JOIN. Zresztą już wcześniej napisałem o JOIN. Optymalizator kosztowy powinien je na taki sam algorytm przerobić.

Niestety, nie przerabia. mysql 5.5, ok 10k rekordów - jeśli to coś zmienia. Tak jak pisałem wcześniej zapytanie wykonuje się zbyt długo w pierwszej wersji, w drugiej krócej, ale też wiem, że można lepiej - tylko technicznie z wiedzą nie domagam.

[mmmmmmm]

Załóż indeks na parę (obiect_id,date) i wklej tu trzy explainy:
1.
SELECT g.* FROM (SELECT obiect_id, max(date) AS date FROM modifications GROUP BY obiect_id) f
LEFT JOIN modifications g ON (f.obiect_id=g.obiect_id AND f.date=g.date)
2.
SELECT f.* FROM modifications f WHERE (f.obiect_id, f.date) IN (SELECT g.obiect_id AS obiect_id, max(g.date) AS date FROM modifications g GROUP BY g.obiect_id)
3.
SELECT g.* FROM (SELECT obiect_id, max(date) AS date FROM modifications GROUP BY obiect_id) f
LEFT JOIN modifications g using (obiect_id,date)

3 to jest to samo, co 1, ale dla pewności.