Grupa zapytań MySQL według dnia / miesiąca / roku

GROUP BY DATE_FORMAT(record_date, '%Y%m')

Uwaga (przede wszystkim do potencjalnych downvoterów). Obecnie może to nie być tak skuteczne, jak inne sugestie. Mimo to pozostawiam go jako alternatywę, a także taką, która może posłużyć do sprawdzenia, jak szybsze są inne rozwiązania. (Bo nie można odróżnić szybko od powoli, dopóki nie zobaczysz różnicy.) Również, z biegiem czasu, mogą być wprowadzane zmiany w silniku MySQL w zakresie optymalizacji tak, aby To rozwiązanie, w pewnym (być może, nie tak odległym) punkcie w przyszłości, do stają się dość porównywalne pod względem wydajności z większością innych.

Próbowałem użyć powyższej instrukcji "WHERE", myślałem, że jest poprawna, ponieważ nikt jej nie poprawił, ale się myliłem; po kilku poszukiwaniach dowiedziałem się, że jest to właściwa formuła dla instrukcji WHERE, więc kod staje się taki:

SELECT COUNT(id)  
FROM stats  
WHERE YEAR(record_date) = 2009  
GROUP BY MONTH(record_date)

Spróbuj tego

SELECT COUNT(id)
FROM stats
GROUP BY EXTRACT(YEAR_MONTH FROM record_date)

Funkcja EXTRACT (unit FROM date) jest lepsza, ponieważ używane jest mniejsze grupowanie i funkcja zwraca wartość liczbową.

Warunek porównania, gdy grupowanie będzie szybsze niż funkcja DATE_FORMAT (która zwraca wartość łańcuchową). Spróbuj użyć funkcji / pola, które zwracają wartość non-string dla warunku porównania SQL (WHERE, HAVING, ORDER BY, GROUP BY).

Jeśli twoje poszukiwania trwają kilka lat, a nadal chcesz grupować co miesiąc, proponuję:

Wersja #1:

SELECT SQL_NO_CACHE YEAR(record_date), MONTH(record_date), COUNT(*)
FROM stats
GROUP BY DATE_FORMAT(record_date, '%Y%m')

Wersja # 2 (bardziej efektywna):

SELECT SQL_NO_CACHE YEAR(record_date), MONTH(record_date), COUNT(*)
FROM stats
GROUP BY YEAR(record_date)*100 + MONTH(record_date)

Porównałem te wersje na dużym stole z 1,357,918 wierszy (innodb ), a druga wersja wydaje się mieć lepsze wyniki.

Wersja1 (average of 10 executes) : 1.404 seconds
wersja2 (średnia z 10 ocen) : 0.780 sekund

(SQL_NO_CACHE klucz dodany, aby zapobiec buforowaniu MySQL do zapytań.)

Jeśli chcesz pogrupować według daty w MySQL użyj poniższego kodu:

 SELECT COUNT(id)
 FROM stats
 GROUP BY DAYOFMONTH(record_date)

Mam nadzieję, że zaoszczędzi to trochę czasu dla tych, którzy znajdą ten wątek.

Jeśli chcesz filtrować rekordy dla danego roku (np. 2000), zoptymalizuj WHERE klauzulę tak:

SELECT MONTH(date_column), COUNT(*)
FROM date_table
WHERE date_column >= '2000-01-01' AND date_column < '2001-01-01'
GROUP BY MONTH(date_column)
-- average 0.016 sec.

Zamiast:

WHERE YEAR(date_column) = 2000
-- average 0.132 sec.

Wyniki zostały wygenerowane na podstawie tabeli zawierającej 300K wierszy i indeksu w kolumnie daty.

Jeśli chodzi o klauzulę GROUP BY, przetestowałem trzy warianty na podstawie powyższej tabeli; oto wyniki:

SELECT YEAR(date_column), MONTH(date_column), COUNT(*)
FROM date_table
GROUP BY YEAR(date_column), MONTH(date_column)
-- codelogic
-- average 0.250 sec.

SELECT YEAR(date_column), MONTH(date_column), COUNT(*)
FROM date_table
GROUP BY DATE_FORMAT(date_column, '%Y%m')
-- Andriy M
-- average 0.468 sec.

SELECT YEAR(date_column), MONTH(date_column), COUNT(*)
FROM date_table
GROUP BY EXTRACT(YEAR_MONTH FROM date_column)
-- fu-chi
-- average 0.203 sec.

Jeśli chcesz otrzymywać miesięczne statystyki z liczeniem wierszy na miesiąc każdego roku uporządkowane według ostatniego miesiąca, spróbuj tego:

SELECT count(id),
      YEAR(record_date),
      MONTH(record_date) 
FROM `table` 
GROUP BY YEAR(record_date),
        MONTH(record_date) 
ORDER BY YEAR(record_date) DESC,
        MONTH(record_date) DESC

Kompletne i proste rozwiązanie z podobnie działającą jeszcze krótszą i bardziej elastyczną alternatywą obecnie aktywną:

SELECT COUNT(*) FROM stats
-- GROUP BY YEAR(record_date), MONTH(record_date), DAYOFMONTH(record_date)
GROUP BY DATE_FORMAT(record_date, '%Y-%m-%d')

Poniższe zapytanie działało dla mnie w Oracle Database 12c Release 12.1.0.1.0

SELECT COUNT(*)
FROM stats
GROUP BY 
extract(MONTH FROM TIMESTAMP),
extract(MONTH FROM TIMESTAMP),
extract(YEAR  FROM TIMESTAMP);

Możesz to zrobić po prostu Mysql DATE_FORMAT () funkcja w GROUP BY. Możesz dodać dodatkową kolumnę dla zwiększenia jasności w niektórych przypadkach, na przykład, gdy rekordy obejmują kilka lat, a następnie ten sam miesiąc występuje w różnych latach.Tutaj tak wiele opcji można dostosować to. Proszę przeczytać to przed rozpoczęciem. Mam nadzieję, że to powinno być bardzo pomocne dla Ciebie. Oto przykładowe zapytanie dla twojego zrozumienia

SELECT
    COUNT(id),
    DATE_FORMAT(record_date, '%Y-%m-%d') AS DAY,
    DATE_FORMAT(record_date, '%Y-%m') AS MONTH,
    DATE_FORMAT(record_date, '%Y') AS YEAR,

FROM
    stats
WHERE
    YEAR = 2009
GROUP BY
    DATE_FORMAT(record_date, '%Y-%m-%d ');

Wolę optymalizować dobór grupy na rok w ten sposób:

SELECT COUNT(*)
  FROM stats
 WHERE record_date >= :year 
   AND record_date <  :year + INTERVAL 1 YEAR;

W ten sposób możesz po prostu powiązać rok raz, np. '2009', z nazwanym parametrem i nie musisz się martwić o dodanie '-01-01' lub podanie '2010' osobno.

Ponadto, ponieważ przypuszczalnie tylko liczymy wiersze i id nigdy nie jest NULL, wolę COUNT(*) od COUNT(id).

.... group by to_char(date, 'YYYY') --> 1989

.... group by to_char(date,'MM') -->05

.... group by to_char(date,'DD') --->23

.... group by to_char(date,'MON') --->maj

.... group by to_char(date,'YY') --->89