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

Zapytanie grupujące minimalnne wartości w grupie danych

Problem:
Chcemy wyświetlić miesięczne zestawienie liczby nowo zarejestrowanych
użytkowników mając jedynie daty ich logowań.

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-05');
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');

Rozwiązanie:
Rejestracje można utożsamić z pierwszymi logowaniami, zatem:
Najpierw grupujemy dane według użytkownika i dla każdego id_user znajdujemy najwcześniejszą datę logowania. Następnie otrzymane dane grupujemy po roku i miesiącu.

Realizuje to podwójnie grupujące zapytanie:

SELECT 
	extract(year from date) || '-' || extract(month from date) AS month, 
	count(*) AS amount 
FROM 
	logs 
WHERE date IN (
	SELECT 
		min(date) 
	FROM 
		logs 
	GROUP BY 
		id_user 
) 
GROUP BY 
	extract(year from date) || '-' || extract(month from date)
ORDER BY
	amount DESC;

Można też ciut lepiej zoptymalizować zapytanie i operować bezpośrednio na wynikach zwróconych
przez podzapytanie:

	
SELECT 
	extract(year from sub.date) || '-' || extract(month from sub.date) AS month, 
	count(*) AS amount 
FROM 
 (
	SELECT 
		min(date) AS date
	FROM 
		logs 
	GROUP BY 
		id_user 
) AS sub
GROUP BY 
	extract(year from sub.date) || '-' || extract(month from sub.date)
ORDER BY
	amount DESC;	

W przypadku gdy zapytanie to będzie wykonywane często warto założyć index na kolumne z datą:

CREATE INDEX date_idx ON logs(date);

Jak usunąć zduplikowane rekody w tabeli bazy danych?

Czasami zdarza się że, poprzez nie do końca poprawną walidację danych wejściowych lub z powodu błędnego działania aplikacji stworzą nam się w bazie danych zduplikowane rekordy.

Takie niepożądane zduplikowane dane w 90% będą nam przeszkadzać i w najlepszym razie mogą powodować niepotrzebny zamęt w aplikacji. Skutki mogą takżę spowodować dużo poważniejsze problemy jak chociażby rozsynchronizowanie się danych w powiązanych tabelach w bazie.

Co należy robić w przypadku wykrycia zduplikowanych rekordów?

W pierwszej kolejności trzeba dojść do tego w jaki sposób powstały a następnie trzeba usprawnić aplikację w taki sposób aby powstawanie duplikatów było już niemożliwe.
Następnie trzeba wziąć się za zapisane dane i należy usunąć z nich nadmiarowe rekordy.

Usuwanie zduplikowanych rekordów

Usuwać duplikaty można na conajmniej kilka sposobów. Najczęstszą radą spotykaną w internecie to przeniesienie unikalnych rekordów do nowej tymczasowej tabeli, skasowanie wszystkich danych ze starej tabeli, a następnie zgranie z powrotem rekordy unikalne.

Niestety nie jest to metoda ani szybka, ani skuteczna ani bezpieczna.

Na prościutkim przykładzie rzedstawię poniżej najlepszy sposób na pozbycie się duplikatów

Tworzymy przykładową tabelę:

CREATE TABLE people(
	id SERIAL PRIMARY KEY,
	name VARCHAR(100) NOT NULL,
	surname VARCHAR(100) NOT NULL,
	phone VARCHAR(100) NOT NULL,
	email VARCHAR(100) NOT NULL
);

Tabela zawiera autoinkrementowane pole id, które jest kluczem jej głównym i kilka pól przetrzymujących dane.

Wstawiamy zduplikowane rekordy:

INSERT INTO people(name,surname,phone,email) VALUES('Marcin','Szajda','660111111','marcin@nospam.pl');
INSERT INTO people(name,surname,phone,email) VALUES('Marcin','Szajda','660111111','marcin@nospam.pl');
INSERT INTO people(name,surname,phone,email) VALUES('Agnieszka','Czopek','550123123','aga@nospam.pl');
INSERT INTO people(name,surname,phone,email) VALUES('Jas','Kowalski','555123147','jas@nospam.pl');
INSERT INTO people(name,surname,phone,email) VALUES('Jas','Kowalski','555123147','jas@nospam.pl');

Wyświetlamy zawartość tabeli:

SELECT * FROM people;
 id |   name    | surname  |   phone   |      email       
----+-----------+----------+-----------+------------------
  1 | Marcin    | Szajda   | 660111111 | marcin@nospam.pl
  2 | Marcin    | Szajda   | 660111111 | marcin@nospam.pl
  3 | Agnieszka | Czopek   | 550123123 | aga@nospam.pl
  4 | Jas       | Kowalski | 555123147 | jas@nospam.pl
  5 | Jas       | Kowalski | 555123147 | jas@nospam.pl
(5 rows)

Jak widzimy mamy zduplikowane 2 rekordy, pierwszym krokiem do usunięcia rekordów będzie napisanie zapytania, które je wyświetli a następnie
zmienimy SELECT NA DELETE.

SELECT
	id, name, surname, phone, email
FROM
	people
WHERE
	EXISTS (
		SELECT
			NULL
		FROM
			people AS P
		WHERE
			people.name = P.name
			AND people.surname = P.surname
			AND people.phone = P.phone
			AND people.email = P.email
		GROUP BY
			P.name, P.surname, P.phone, P.email
		HAVING
			people.id < MAX(P.id)
	);

Wynikiem zapytania będzie:

 id |  name  | surname  |   phone   |      email       
----+--------+----------+-----------+------------------
  1 | Marcin | Szajda   | 660111111 | marcin@nospam.pl
  4 | Jas    | Kowalski | 555123147 | jas@nospam.pl
(2 rows)

Wyjaśnienie zapytania:

  • SELECT _FIELDS_ FROM people - deklarujemy pola, które checmy wyświetlić
  • WHERE EXISTS( _CONDITION_ ) - które spełniają warunek _CONDITION_
  • SELECT NULL FROM people AS P - w podzapytaniu nic nie wyświetlamy, wykorzystujemy je jedynie do złączenia
  • WHERE _CONDITION_ - joinujemy po wszystkich polach, które mają tworzyć unikalny klucz
  • GROUP BY _FIELDS_ - grupujemy wg. unikalnych pól
  • HAVING people.id < MAX(P.id) - warunek na id

Modyfikujemy teraz powyższe zapytanie SELECT _FIELDS_ na DELETE

DELETE
FROM
	people
WHERE
	EXISTS (
		SELECT
			NULL
		FROM
			people AS P
		WHERE
			people.name = P.name
			AND people.surname = P.surname
			AND people.phone = P.phone
			AND people.email = P.email
		GROUP BY
			P.name, P.surname, P.phone, P.email
		HAVING
			people.id < MAX(P.id)
	);

I otrzymujemy tabele wyczyszczoną z nadmiarowych rekordów.

Jakie to proste.

**********************edit**********************

Jak słusznie zauważył kolega Wild Child powyższe zapytanie nie działa w mySQL.
Przyznaje się bez bicia, że testowałem jedynie w PostgreSQL gdyż powyższy kod SQL
wyglądał na uniwersalny.

Okazało się jednak, że mySQL nie pozwala na jednoczesne wyświetlanie i modyfikowanie
zawartości tabeli. (jednoczesny DELETE w zapytaniu głównym i SELECT w podzapytaniu)

Posiedziałem trochę i wymyśliłem alternatywne zapytanie:

SELECT 
	D.*
FROM 
	people as U
INNER JOIN 
	people AS D ON (
	   	D.name = U.name AND
   		D.surname = U.surname AND
	   	D.phone = U.phone AND
	   	D.email = U.email
	   )
AND 
	D.id > U.id;

Konstrukcja tego zapytania jest nieco inna:

  • Robimy JOINA wewnątrz tabeli po polach, które mają być unikalne
  • Zostawiamy rekord o najniższym id, a resztę kasujemy (warunek AND D.id > U.id)
  • Skróty: D - duplicate, U - unique
  • Oczywiście aby usunąć rekordy należy zamienić słowo SELECT na DELETE

Dumpowanie bazy danych w PostgreSQL (pg_dump)

W trakcie tworzenia aplikacji internetowej początkowo projekt powinien powstawać w lokalnym środowisku programistycznym. Może to być wewnętrzny serwer firmowy, czy po prostu domowy komputer.
Dzięki temu możemy pracować na optymalnej konfiguracji serwerowej, która może nam ułatwiać niektóre czynności. Oszczędzamy także na czasie połączenia ze zdalnym serwerem bazodanowym.
Dopiero gdy aplikacja będzie miała wersję w miarę stabilna (nazwijmy ją beta czy RCx) to możemy ją przenieść na serwer docelowy i dalsze testy przeprowadzać już tam.

W trakcie przenoszenia aplikacji, będziemy musieli oprócz plików przenieść zawartość bazy danych, poniżej przedstawiam opis wszystkich dostępnych parametrów dumpowania dla bazy PostgreSQL 8.2.

Użycie: 
  pg_dump [OPTION]... [DBNAME] 

Podstawowe opcje: 
  -f, --file=FILENAME      nazwa pliku, w którym zapisany będzie dump 
  -F, --format=c|t|p       format pliku (dowolny, tar, tekstowy) 
  -i, --ignore-version     wymuś działanie nawet gdy wersja psql jest inna od wersji pg_dump 
  -v, --verbose            verbose mode 
  -Z, --compress=0-9       poziom kompresji (dla formatów skompresowanych) 
  --help                   po dumpowaniu wyświetl pomoc 
  --version                po dumpowaniu wyświetl informację o wersji 

Opcje kontrolujące dumpowane dane: 
  -a, --data-only             dumpuj tylko dane, bez schematu bazy danych 
  -b, --blobs                 dumpuj także pola tylu blobs 
  -c, --clean                 w dumpie najpierw wyczyć strukture (poprzez dropy) 
  -C, --create                zamieść w dumpie komendę tworzenia bazy danych 
  -d, --inserts               dumpuj dane jako oddzielne komendy INSERT commands (domyślnie dumpowanie jest poprzez COPY) 
  -D, --column-inserts        dumpuj dane jako oddzielne komendy INSERT commands z nazwami kolumn (domyślnie dumpowanie jest poprzez COPY) 
  -E, --encoding=ENCODING     dumpuj dane używając kodowania ENCODING 
  -n, --schema=SCHEMA         dumpuj tylko schemat(y) SCHEMA 
  -N, --exclude-schema=SCHEMA dumpuj wszystko oprócz schamat(ów) SCHEMA 
  -o, --oids                  dumpuje wraz z numerami OID 
  -O, --no-owner              pomiń zapis odnośnie właściciela bazy danych 
  -s, --schema-only           dumuj tylko strukture bazy danych 
  -S, --superuser=NAME        w dumpie będzie zawarta nazwa superusera 
  -t, --table=TABLE           dumpuj jedynie tabele TABLE z bazy danych 
  -T, --exclude-table=TABLE   dumpuj wszystkie tabele oprócz TABLE 
  -x, --no-privileges         dumpuj z pominięciem praw dostępu (grant/revoke) 
  --disable-dollar-quoting    dumpuj z pominięciem 'dollar quoting', zostanie użyte standardowy ' 
  --disable-triggers          dumpuj z pominięciem procedur wyzwalanych automatycznie (triggers) 

Opcje potrzebne do połączenia: 
  -h, --host=HOSTNAME      serwer na którym postawiona jest baza 
  -p, --port=PORT          port serwera pod którym baza jest dostępna (domyślnie: "5432") 
  -U, --username=NAME      nazwa użytkownika bazy danych 
  -W, --password           wymuszenie podania hasła

Osobiście używam polecenia:

pg_dump -c -O -U USERNAME DBNAME -h HOSTNAME -p 5432 --disable-dollar-quoting -i > DBNAME-YYYY-MM-DD.sql

Otrzymany dump najpierw zawiera wyczyszczenie struktury bazy danych (-c) (jest to przydatne gdy nadpisujemy istniejącą już bazę danych), pomijam zapis dotyczący właściciela bazy danych (-O) (gdyż na 99% właściciel bazy danych źródłowej i docelowej będzie inny), pomijam parametry -d -D i dumpuję baze poprzez COPY (takie dumpowanie znacznie przyspiesza wczytywanie zdumpowanych danych) wczytuje użytkownika jakim się łączę (-U USERNAME), nazwę bazy danych (DBNAME), host serwera na którym jest baza danych (-h HOSTNAME), port na którym się łączę z bazą danych (-p 5432), wymuszam standardowe apostrofy (–disable-dollar-quoting) i na wszelki wypadek ignoruję różnice wersji (-i). Dane zapisuję w pliku o nazwie równoważnej z nazwą bazy danych w raz z bieżącą datą (> DBNAME-YYYY-MM-DD.sql)