Tag: agregacja

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