Sortowanie względem pola nie będącego w DISTINCT ON

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

2 komentarze do “Sortowanie względem pola nie będącego w DISTINCT ON”

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *