Problem:
Chcemy wyświetlić jedynie unikalne rekordy względem konkretnego pola/pól i posortować innym polu. W naszym przykładzie chcemy otrzymać zestawienie id_user – ostatnie logowanie
Posiadane dane:
Tabela z datami logowań użytkowników aplikacji w tabeli postaci:
CREATE TABLE logs(
id SERIAL PRIMARY KEY, -- unikalny klucz główny
id_user INTEGER, -- klucz obcy tabeli użytkowników
date TIMESTAMP -- data logowania
);
Przykładowe rekordy:
INSERT INTO logs(id_user,date) VALUES(1,'2008-01-01');
INSERT INTO logs(id_user,date) VALUES(1,'2008-01-01');
INSERT INTO logs(id_user,date) VALUES(4,'2008-01-01');
INSERT INTO logs(id_user,date) VALUES(2,'2008-01-01');
INSERT INTO logs(id_user,date) VALUES(3,'2008-04-01');
INSERT INTO logs(id_user,date) VALUES(7,'2008-04-01');
INSERT INTO logs(id_user,date) VALUES(1,'2008-05-01');
INSERT INTO logs(id_user,date) VALUES(4,'2008-05-01');
INSERT INTO logs(id_user,date) VALUES(5,'2008-06-01');
INSERT INTO logs(id_user,date) VALUES(5,'2008-08-01');
INSERT INTO logs(id_user,date) VALUES(7,'2008-10-01');
Błędne zapytanie nr 1:
W pierwszym momencie wielu początkujących programistów pomyśli o zapytaniu:
SELECT
DISTINCT ON (id_user)
*
FROM
logs
ORDER BY
date DESC;
Niestety nie jest to poprawne zapytanie, postgres wyrzuci błąd:
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
Oznacza to, że pola, które zawiera klauzula ORDER BY muszą się znaleźć na początku klauzuli DISTINCT ON.
W tym przypadku poprawne zapytanie musiało by zawierać DISTINCT ON (date, id_user).
Niestety wtedy nie dostaniemy pożądanego rezultatu (unikalność po kolumnie id_user) gdyż badana będzie
unikalność względem dwóch pól.
Błędne zapytanie nr 2:
SELECT
*
FROM
(SELECT
DISTINCT ON (id_user)
*
FROM
logs
) AS sub
ORDER BY
sub.date DESC;
Idąc tropem unikalności można użyć podzapytania, które zwraca unikalne względem id_user rekordy a dopiero później, zwrócone przez nie rekordy posortować. Powyższe zapytanie wykona się i zwróci pewne rezultaty.
id id_user date
11 7 2008-10-01 00:00:00
9 5 2008-06-01 00:00:00
8 4 2008-05-01 00:00:00
5 3 2008-04-01 00:00:00
2 1 2008-01-01 00:00:00
4 2 2008-01-01 00:00:00
Niestety nie będą one do końca poprawne gdyż podzapytanie DISTINCT pozostawia pierwszy napotkany rekord zawierający unikalną wartość id_user. Zatem jeżeli dane nie są posortowane względem id_user i daty chronologicznie to uzyskamy wyniki nieprawidłowe. W naszym przypadku id_user=1 została zwrócona data=2008-01-01 gdyż jest ona bliżej początku niż rekord z datą=2008-05-01.
Rozwiązanie:
SELECT
*
FROM
(SELECT
DISTINCT ON (sub2.id_user)
sub2.*
FROM
(SELECT
*
FROM
logs
ORDER BY
id_user ASC,
date DESC
) AS sub2
) AS sub
ORDER BY
sub.date DESC;
W podzapytaniu sub2 sortujemy dane po id_user i date malejąco tak aby każdy id_user na czele miał rekord z datą najpóźniejszą. Takie dane przekazujemy zapytaniu wcześniejszemu, które najpierw wyciąga rekordy unikalne względem kolumny id_user a następnie tak ograniczony zbiór sortuje względem kolumny date. Jako że dostarczyliśmy dane posortowane otrzymane rezultaty są zgodne z prawdą:
id id_user date
11 7 2008-10-01 00:00:00
10 5 2008-08-01 00:00:00
7 1 2008-05-01 00:00:00
8 4 2008-05-01 00:00:00
5 3 2008-04-01 00:00:00
4 2 2008-01-01 00:00:00