DISTINCT MySQL vs PostgreSQL
Pomagałem dzisiaj na IRCnet (#php.pl) w zapytaniu do bazy MySQL, które polegało na wybraniu rekordów z najmniejszą ceną pogrupowanych według wybranej kolumny. Bardzo szybko napisałem jak to ma wyglądać w PostgreSQL, ale niestety w MySQL użycie DISTINCT jest trochę ułomne.
Zacznijmy jednak od początku. Przykładowe rekordy w tabeli "cennik":
| id | data | cena |
|---|---|---|
| 1 | 2009-12-01 | 900 |
| 2 | 2009-12-01 | 900 |
| 3 | 2009-12-01 | 500 |
| 4 | 2009-12-02 | 800 |
| 5 | 2009-12-02 | 900 |
| 6 | 2009-12-05 | 700 |
| 7 | 2009-12-05 | 900 |
| 8 | 2009-12-05 | 500 |
Chcemy teraz wybrać jeden rekord z każdego dnia, który posiada najniższą cenę. W PostgreSQL wyglądać to będzie następująco:
Wszystko ślicznie, pięknie, natomiast w MySQL nie można użyć DISTINCT na wybranej kolumnie i jednocześnie zwrócić rekordów z kolumny, z której wartość została wyodrębniona. Trzeba się niestety posłużyć podzapytaniem i GROUP BY.
Zwracam uwagę na fakt, że sortowanie odbywa się w podzapytaniu. Gdyby było na zewnątrz najpierw rekordy zostałyby zgrupowane, a dopiero potem posortowane co uniemożliwiłoby wybranie najniższej ceny.
Dzisiaj krótki wpis, ale cieszę się, że znowu udało mi się coś napisać bo po (nie)dawnej zapowiedzi częstszego pisania dałem kolokwialnie pisząc dupy :). Pozwolę sobie jeszcze dać linka do poprzedniego wpisu gdzie też wyszły pewne różnice między MySQL i PostgreSQL.
Słowa kluczowe: PostgreSQL, Techblog, baza, mysql, sql, artykuł, blog
Komentarze i opinie
Pewnie chodzi o to, że nie ma pewności co do tego, który z wierszy zostaje wyjęty. Bo załóż na przykład, że masz dwa rekordy o tej samej cenie tego samego dnia, zrobisz distinct na dacie i... ID którego wiersza dostaniesz? Zapewne "pierwszego" (czyli zależy od sortowania po dacie), ale nie jestem pewny, czy jest to gdziekolwiek opisane jakimś standardem.
Coś mi trochę śmierdzi w tym jak postgres to realizuje. Ale ja ogólnie nie przepadam za bazami, które nie wytkną Ci, że po cichu coś gdzieś agregują.
Żeby nie było, w mysql jest IMO gorszy (bo popularniejszy) babol -- można sobie zrobić group by na jakiejś kolumnie, i powyciągać pozostałe kolumny (bez uruchamiania funkcji agregujących! mysql po prostu dziabnie sobie wartości z pierwszego lepszego wiersza). Z grubsza sprowadza się to do tego samego "ułatwienia" -- agregujesz sobie wiersze wg. jakiejś kolumny, a baza sama decyduje co zwrócić w pozostałych. Zastanawiam się, czy dzięki temu "ułatwieniu" nie dałoby się tego kodu dla mysql zapisać równie prosto, co tego z distinctem.
Coraz mniej lubię i mysql, i postgresa... :)
Gah! Dlaczego zobaczyłem właśnie flashowego popupa zamykanego iksem? To w ogóle dozwolone na joggerze? ;)
To generuje stat.pl od jakiegoś czasu. Też mnie to denerwuje strasznie, zwłaszcza, że nie kończy się po jednym odświeżeniu tylko ciągle się pojawia. Za jakiś czas będę zmieniał je na statystyki google.
@Hoppke: w teorii relacyjnych baz danych nie ma czegoś takiego jak "pierwszy" rekord, ponieważ dane nie są w żaden sposób uporządkowane.
Zwykle zwrócone zostaną dane najwcześniej wprowadzone, czyli w tym wypadku o najmniejszym kluczu głównym, ale nigdy nie możesz być w 100% pewien.
DISTINCT ON to chyba jest tylko w PostgreSQL, bo nie ma tego także w MSSQL, nie kojarzę też by było w Oracle.
Zresztą MSSQL nie wykona też tego drugiego zapytania, bo nie dopuszcza ORDER BY w podzapytaniach już tak nawiasem.
Sam przyznam, że do zrobienia tego to ja się już zacząłem zastanawiać nad jakimiś rozbudowanymi podzapytaniami i takimi tam, ale ja mam skłonności do przesady ;-)
PS I masz literówkę, "GROPU BY", w drugim zapytaniu.
@Ktos: bo to jest zapytanie dla M*y*SQL ;-)
Co firma to standard niestety.
@Hoppke: Twoja teoria jest całkowicie moim zdaniem błędna, bo teraz jak posortowałem w MySQL to nie dodałem sortowania po id więc też sobie wybierze "pierwszy" z brzegu i też to zrobi jak to ująłeś po "cichu".
Do tego PostgreSQL wcale nie jest takie "ciche" bo to drugie zapytanie w PostgreSQL się nie wykona. Dlaczego? Bo jeśli grupujesz to musisz grupować po wszystkich kolumnach, które chcesz zwrócić w SELECT.
@Ktos: dzięki, poprawione
@Wierny czytelnik: Ja się tylko zacząłem zastanawiać jak zrobić to zgodnie z SQL-owym standardem... i w sumie to nie wiem :-)
(Cholera jasna, Jogger nie pamięta mi nicku ;p)
@Ktos: właśnie jest to dla mnie dziwne zachowanie MSSQL bo IMO powinno dać się sortować elementy podzapytania i to właśnie jest jedyne rozwiązanie, które przychodzi mi do głowy.
Z drugiej strony MSSQL dużo nie używałem i brak LIMIT mnie rozwalał ;)
@sf, ale czy ja gdziekolwiek sugerowałem, że mysql się zachowa lepiej niż postgres?
jedno i druge potrafi ssać.
@BTM: A zamiast LIMIT mssql ma chyba TOP (select TOP 200 ...)?
Znaczy coś takiego w MySQL:
SELECT min(cena), data, id
FROM cennik
GROUP BY data
Na szybko, więc może coś przeoczyłem.
@Hoppke: źle zrozumiałem :)
@ajot: nie bardzo, zwrócić może id, którego cena jest inna
Pewnie przejdzie, choć IMO nie powinno (kolumna id niedookreślona). Ale to właśnie ten myk z group by w mysql o którym mówiłem :)
sf: zwraca id odpowiednie dla min(cena)
Nie wiem czy jest to w dokumentacji opisane - nie mam teraz czasu szukać.
@ajot: jeśli testowałeś na danych, które tutaj przedstawiłem to je zaktualizowałem bo tak się składało, że "pierwsze" rekordy były z najmniejszą kwotą
w tym akurat wypadku to nieistotne, bo data jest zgrupowana (taka sama w każdym wierszu), cenę weźmie minimalną, więc wartości tych kolumn dla danego ID będą faktycznie takie, jak zwrócone przez bazę.
Gorzej, że mysql zadziała i bez min(), IIRC przechodzą takie potworki:
select * from cennik group by data
Nie jest to problemem samym w sobie (trzeba po prostu pamiętać, że ta konkretna baza na to pozwala), ale ludzie przyzwyczajeni do bardziej wymagających systemów mogą się naciąć, zwłaszcza gdy szlifuje się jakieś skomplikowane zapytanie i zapomni o zagregowaniu którejś z kolumn, a baza to zaakceptuje...
No ale to mysql. W końcu to naza, w której nazwy kolumn w zapytaniach są case sensitive na Linuksie, i case insensitive na Windowsie (czyli po skopiowaniu bazy z mysql na windowsie do mysql na linuksie aplikacja może przestać działać...)
sf: Testowałem na zupełnie innych danych. Na tym co miałem pod ręką.
Jeżeli ktoś ma czas i ochotę, niech sprawdzi dla podanych wartości.
Ja mogę to zweryfikować później (jeżeli nikt do tego czasu nie sprawdzi) - teraz mam inne zajęcia.
@ajot: przetestowałem na wersji MySQL 5.0.75
CREATE TABLE cennik(
id int,
data date,
cena int
);
INSERT INTO cennik(id, data, cena) VALUES(1, '2009-12-01', 900);
INSERT INTO cennik(id, data, cena) VALUES(2, '2009-12-01', 900);
INSERT INTO cennik(id, data, cena) VALUES(3, '2009-12-01', 500);
INSERT INTO cennik(id, data, cena) VALUES(4, '2009-12-02', 800);
INSERT INTO cennik(id, data, cena) VALUES(5, '2009-12-02', 900);
INSERT INTO cennik(id, data, cena) VALUES(6, '2009-12-05', 700);
INSERT INTO cennik(id, data, cena) VALUES(7, '2009-12-05', 900);
INSERT INTO cennik(id, data, cena) VALUES(8, '2009-12-05', 500);
rezultat Twojego zapytania taki jak sądziłem, bierze "pierwsze" id
min( cena ) data id
500 2009-12-01 1
800 2009-12-02 4
500 2009-12-05 6
id = 1 odpowiada cenie 900, a wg zapytania 500
psql=> SELECT min(cena), data, id FROM cennik GROUP BY data ;
ERROR: column "cennik.id" must appear in the GROUP BY clause or be used in an aggregate function
I słusznie. Jeśli stare wersje psql to przyjmowały, to ewidentnie jakiś błąd był. Jak MySQL przyjmuje, to błąd jest.
Myślę, że prawidłowym rozwiązaniem, bez żadnych hacków i nadużyć konkretnej implementacji SQL będzie:
SELECT * FROM cennik WHERE (data, cena) IN (SELECT data, min(cena) FROM cennik GROUP BY data);
Co więcej, jeżeli kilka produktów będzie miało najniższą cenę to zapytanie poda wszystkie, a nie wybierze przypadkowy wiersz. Oczywiście można w zewnętrznym SELECT dodać 'DISTINCT', ale to mało eleganckie (odrzucanie części wyników bez konkretnego kryterium wyboru).
@Jajcuś: dobrze, że producenci nie wymagają wyłącznie takiej składni bo użycie tego zapytania (wzbogaconego o konkretniejsze WHERE) przy 800 000 rekordów to czyste samobójstwo ;) wyłączyłem po 4 minutach czekania na wynik
@sf: cóż, jak podzapytanie nie jest skorelowane ot przy dużej bazie jest śmiesznie ;>
sf: Rozwiązania dostosowuje się do zbioru danych, a format danych do zapytań jakie się ma zamiar wykonywać. W praktyce, na dużej bazie tego po prostu nie będzie robić się jednym zapytaniem (ale serią zapytań z aplikacji, albo procedurą składowaną), albo sama baza będzie inaczej zorganizowana.
Używanie hacków takich jak niestandardowe użycie składni SQL, które przypadkiem działa, w praktycznym projekcie może się kiedyś zemścić.
A co do bazy, na której próbowałeś. Jakie masz tam indeksy? Jeśli indeks na kolumnie „data” nie wystarczy (bo taki chyba masz, inaczej nie mamy o czym gadać), to indeks na (data, cena) powinien pomóc. Może nawet indeks na "cena" dużo przyspieszyć (szczególnie operację min()), jeśli produktów jest dużo, a dat mało. Właściwie, przed dodaniem tych indeksów warto by jakieś "EXPLAIN" zapuścić, żeby zobaczyć co jest wąskim gardłem.
Jeżeli masz zasoby dyskowe to robisz indeks data+cena i wyszukiwanie ma miejsce w samym indeksie co *znacznie* przyśpiesza wyszukiwanie
Bartosz: to też zaproponowałem, ale który z indeksów najlepiej pomoże może zależeć od konkretnych danych w bazie. I zasobami dyskowymi bym się nie martwił – indeksy zajmują dużo mniej niż dane. Problemem jest dodatkowy koszt zapisu danych do bazy– to głównie z tego powodu unika się zbędnych indeksów, albo wręcz usuwa indeksy na czas większego importu danych.
sf: Jak widać miałem szczęście, że w wyrywkowo sprawdzonych danych dla mojej tabeli dostałem dokładnie to co powinienem dostać. Przed momentem dokładniej to sprawdziłem i faktycznie MySQL zwraca "pierwsze z brzegu"
@Jajcuś: jesteś pewien, że indeksy zajmują mniej niż dane? Bo wiesz, to zależy od danych, ich rodzaju i ilości ;]
Mam taką jedną małą tabelę, która ma 2 indeksy, na pola typu int i dane mają 300mb a indeksy 400 ;-)
Indeksy na prawdę potrafią zająć DUŻO więcej niż dane.
A czy tak nie wystarczy ??
SELECT id, DATA, MIN(cena) FROM cennik GROUP BY `data`
Opps, już ktoś o tym pisał powyżej, sorki za ponowny post. Admin może to usunąć :)
BTM: no cóż, ja się z czymś takim chyba nie spotkałem (albo nie zauważyłem) – zwykle miałem wiele kolumn i indeksy najwyżej na jednej, czy dwóch (i to nie tych typu TEXT). Ale tak już jest w bazach danych – wiele zależy od konkretnej bazy i konkretnych danych.
Jajcuś: no cóż, są bazy i bazy ;-)
pre.
Wykorzystanie przestrzeni Typ Wykorzystanie
Dane 326 k KB
Indeks 428 k KB
Sumarycznie 754 k KB
Statystyka rekordów Cecha Wartość
Format stały
Rekordów 15 109 653
Długość rekordu ø 29
Rozmiar rekordu ø 51 bajtów
Następne Autoindex 45 574 471
Utworzenie 16 Sty 2006, 10:55
Ostatnia aktualizacja 20 Cze 2009, 07:42
Ostatnie sprawdzenie 05 Sty 2007, 09:08
Obejrzałem właśnie jedną swoją domową bazę: pół miliona rekordów w 'głównej' tabeli, 4 indeksy, każdy dziesięć razy mniejszy od tabeli.
Jednak w drugiej bazie (około 4 milionów rekordów) rzeczywiście indeksy zajmują trzy razy więcej niż dane… Co ciekawe, największy z tych indeksów jest w tej chwili „pusty”. ;)
BTW. jak dla PostgreSQL wyciągnąć takie ładne statystyki jak BTM wkleił? Ja wykombinowałem sobie:
SELECT sum(reltuples::bigint), sum(relpages) from pg_class WHERE relname not like 'pg_%' and relkind='r'
dla tabel i:
SELECT sum(reltuples::bigint), sum(relpages) from pg_class WHERE relname not like 'pg_%' and relkind='i';
dla indeksów. Wydaje się, że to zwraca te informacje o które chodzi (przybliżoną ilość wierszy i ilość zajmowanego miejsca na dysku), ale nie tak elegancko.
A nie wiem - phpMyAdmin pokazuje. Może pgMyAdmin też ma? :>
BTM: W życiu nie czułem potrzeby używania takich nakładek, chowających mechanizmy bazy danych przede mną. I dla samych „ładnych statystyk” raczej sobie czegoś takiego nie zainstaluję. Myślałem, że może jakieś narzędzie command line jest do tego, ale jak nawet wujek Google nic o tym nie wie, to pewnie nie ma.
Witam,
mógłby ktoś napisać JAK PROCEDURA MOŻE ZWRACAĆ KILKA WARTOŚCI NA RAZ?
Chcę, aby przeszukała tabelę według np. pola Name i zwrócł wszystkie ID gdzie Name=Jan, ale wyskakuje błąd "Result consisted of more than one row"
PS. wiem co oznacza błąd, ale nie znam rozwiązania, czy w ogole można to rozwiązać?
W czym? Nie wiem jak w MSSQL ale w MySQL możesz użyć GROUP_CONCAT:
http://epicweb.pl/103/smaczki-mysql-group_concat/
nie pomogło, próbowałem zastosowac jakoś ale nic z tego nie wyszlo. Postaram sie opisac problem dokładnie na prostym przykładzie:
w MySQL mam tabelę `imie` (id, name)
tworze procedurę z parametrami: (out idu INT, in n INT)
i chcę, aby zwróciła mi wszystkie id dla name=n
w php wyglądałoby to mniej wiecej tak:
1. $n = 'Jan';
2. $select = ("SELECT `id` FROM `imie` WHERE `name`='".$n."';")
tylko, że powyższe zapytanie jak przepisze do procedury, wprowadzajac n jako parametr to zwróci błąd: "Result consisted of more than one row"
Chciałbym, aby procedura zwracała np. text w postaci:
id1|id2|id3|id4...
No to właśnie powinno działać, tylko nie ma szans, żeby ta procedura miała "out idu INT" - jeżeli chcesz mieć "id1|id2" etc. to albo VARCHAR, jeżeli masz 100% pewności że danych będzie mało albo jakiś MEDIUMTEXT:
SELECT GROUP_CONCAT(id ORDER BY name SEPARATOR '|') AS `result` FROM `imie` WHERE `name` = "Jan" GROUP BY `name`
BTM: Zwracanie danych jako jedna wartość tekstowa jest mało eleganckie i niepotrzebne, bo teraz w PostgreSQL procedury składowane mogą już zwracać wiele wierszy danych (za pomocą „RETURN NEXT” lub „RETURN QUERY”).
Jajcuś: ale @mart pisze, że "w MySQL mam" - MySQL też może zwracać kolekcję danych jako wynik procedury?
Gadamy tu dość ogólnie o bazach danych. Więc pokazuję jak to się robi elegancko w innych bazach. Może to będzie argument do zmiany. A działające więzy integralności itp. podstawy przyjdą przy okazji…
@Jajcuś: no ok, ale kolega prosił o pomoc w konkretnym przypadku abstrachując od naszej rozmowy ;]
sprubuj to co ajot, ale z :
GROUP BY data,id
mysql nie ma bug;a w grupowania, a jezeli ktos czytac dokumentacji nie umie, to niech lepiej nie komentuje, funkcjonalnosc dostepu do kolumn nie zagregowanych w group by zostala wprowadzona celowo, wiecej info tutaj:
http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html
Nowy komentarz