MySQL: grupowanie i zliczanie według dat

Bardzo często podczas pobierania danych z bazy, zachodzi potrzeba budowania raportu z grupowaniem na okresy: dni, tygodnie, miesiące, itd. Niekiedy dochodzi do tego potrzeba podziału na wybrane grupy. W niniejszym tekście przedstawię kilka przykładowych sposobów na takie zapytania z użyciem dat w MySQL.

Do poniższych przykładów będzie wykorzystana fikcyjna, uproszczona baza danych, zawierająca statystyki wejść na stronę www. Tabela stats zawiera dane na temat wejść i jest powiązana z tabelą sources zawierającą źródła wejść (bezpośrednie, wyszukiwarka, odnośnik).

Model bazy danych: statystyki strony www

Do wypełnienia tabeli fikcyjnymi danymi można użyć poniższej procedury:

DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `randstats`(insertLimit INT)
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE randIP VARCHAR(15);
DECLARE randSoudce_id INT;
DECLARE randCreated_at DATETIME;

WHILE i &lt; insertLimit DO
SET randIP = CONCAT_WS('.', FLOOR(1 + (RAND() * 255)), FLOOR(1 + (RAND() * 255)), 
FLOOR(1 + (RAND() * 255)), FLOOR(1 + (RAND() * 255)));
SET randSoudce_id = FLOOR(1 + (RAND() * 3));
SET randCreated_at = '2010-01-01 00:00:00' + INTERVAL FLOOR(1 + (RAND() * 60*24*7*4*12*2)) MINUTE;

INSERT INTO `stats`(source_id, created_at, IP) VALUES (randSoudce_id, randCreated_at, randIP);

SET i = i + 1;
END WHILE;
END $$

DELIMITER ;

call randstats(100000);

Grupowanie względem dnia

Powiedzmy, że zależy nam na wyciągnięciu statystyk wejść na stronę www z ostatniego miesiąca, z podziałem na poszczególne dni. W tym celu wystarczy skorzystać z funkcji DATE(), która zwraca samą datę z wyrażenia typu datetime.

SELECT
DATE(created_at) AS dzien,
COUNT(*) AS ilosc
FROM stats
WHERE created_at BETWEEN "2011-01-01 00:00:00" AND "2011-01-31 23:59:59"
GROUP BY dzien
ORDER BY dzien;

Wynik wywołania zapytania będzie następujący:

dzien       ilosc
----------  ------
2011-01-01  168
2011-01-02  187
2011-01-03  190
2011-01-04  201
...

Grupowanie względem tygodnia

Oczywiście sytuacja jest analogiczna do powyższego, będziemy jednak pobierali statystyki z ostatniego roku. Do odpowiedniego grupowania potrzebna będzie funkcja DATE_FORMAT() lub YEARWEEK(). Lepiej będzie skorzystać z tej drugiej, z uwagi na lepszą wydajność.

Funkcja YEARWEEK() zwraca w MySQL sześciocyfrowy ciąg, określający rok oraz numer tygodnia. Pierwszym parametrem jest data, natomiast drugim, opcjonalnym, tryb w jakim ma działać. Określa czy tydzień zaczyna się od niedzieli, czy też od poniedziałku (domyślną wartością jest zmienna systemowa default_week_format).

SELECT
YEARWEEK(created_at) AS tydzien,
COUNT(*) AS ilosc
FROM stats
WHERE created_at BETWEEN "2011-01-03 00:00:00" AND "2011-04-31 23:59:59"
GROUP BY tydzien
ORDER BY tydzien;

Wynik wywołania zapytania będzie następujący:

tydzien  ilosc
-------  ------
201101   1320
201102   1320
201103   1321
201104   1250
...

Grupowanie względem miesiąca

W tym przypadku najlepiej będzie skorzystać z funkcji EXTRACT() z odpowiednim parametrem YEAR_MONTH, dzięki czemu otrzymamy sześcioznakowy ciąg: połączenie roku oraz miesiąca. Pobranie pogrupowanych statystyk na dany miesiąc w roku 2011 może więc wyglądać następująco:

SELECT
EXTRACT(YEAR_MONTH FROM created_at) AS miesiac,
COUNT(*) AS ilosc
FROM stats
WHERE created_at BETWEEN "2011-01-01 00:00:00" AND "2011-12-31 23:59:59"
GROUP BY miesiac
ORDER BY miesiac;

Wynik wywołania zapytania będzie następujący:

miesiac  ilosc
-------  ------
201101   5738
201102   5130
201103   5713
201104   5405
...

Grupowanie względem roku

W tym przypadku sprawa jest prosta – wystarczy wykorzystać funkcję YEAR(), która zwraca rok z przekazanej daty:

SELECT
YEAR(created_at) AS rok,
COUNT(*) AS ilosc
FROM stats
GROUP BY rok
ORDER BY rok;

Wynik wywołania zapytania będzie następujący:

rok     ilosc
------  ------
2010    66793
2011    56547

Grupowanie z uwzględnieniem typu

Na zakończenie mała ciekawostka, czyli sposób na policzenie ilości wejść na stronę z podziałem na źródła. Do tego celu wykorzystamy pierwsze zapytanie (grupowanie na dzień), wymaga ono jednak pewnej modyfikacji, w postaci odpowiedniego wywołania funkcji SUM().

Na początek należy przypomnieć, że źródła wejść w tabeli sources przedstawiają się następująco:

source_id  source_name
---------  -------------
1          bezpośrednie
2          wyszukiwarka
3          odnośnik

Tak, więc aby zliczyć wystąpienia poszczególnych źródeł w podziale na dni, należy użyć odpowiednio instrukcji CASE w połączeniu z SUM(). Widać to na poniższym przykładzie:

SELECT
DATE(created_at) AS dzien,
SUM(CASE WHEN`source_id` = 1 THEN 1 ELSE 0 END) AS bezposrednie,
SUM(CASE WHEN`source_id` = 2 THEN 1 ELSE 0 END) AS wyszukiwarka,
SUM(CASE WHEN`source_id` = 3 THEN 1 ELSE 0 END) AS odnosnik
FROM stats
WHERE created_at BETWEEN "2011-01-01 00:00:00" AND "2011-01-31 23:59:59"
GROUP BY dzien
ORDER BY dzien;

Wynik wywołania zapytania będzie następujący:

dzien       bezposrednie  wyszukiwarka  odnosnik
----------  ------------  ------------  ---------
2011-01-01  50            69            49
2011-01-02  77            47            63
2011-01-03  61            66            63
2011-01-04  62            55            84
2011-01-05  55            57            56
2011-01-06  69            64            59
...

Artykuł został oparty na tekście ze strony http://www.techfounder.net