MySQL Abfrage GROUP BY Tag / Monat / Jahr

Ist es möglich, eine einfache Abfrage zu machen, um zu zählen, wie viele Datensätze ich in einem bestimmten Zeitraum wie einem Jahr, Monat oder Tag habe, mit einem TIMESTAMP Feld, wie:

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

Oder auch:

 SELECT COUNT(id) FROM stats GROUP BY record_date.YEAR, record_date.MONTH 

Um eine monatliche Statistik zu haben.

Vielen Dank!

 GROUP BY YEAR(record_date), MONTH(record_date) 

Überprüfen Sie die Datums- und Uhrzeitfunktionen in MySQL.

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

Hinweis (in erster Linie für potentielle Downvoter). Derzeit ist dies möglicherweise nicht so effizient wie andere Vorschläge. Dennoch überlasse ich es als eine Alternative, und auch eine, die dazu dienen kann, zu sehen, wie schnell andere Lösungen sind. (Denn man kann nicht wirklich schnell von langsam unterscheiden, bis man den Unterschied sieht.) Auch mit der Zeit könnten Änderungen an der MySQL-Engine in Bezug auf die Optimierung vorgenommen werden, um diese Lösung bei einigen (vielleicht nicht so) zu machen Fernpunkt) in Zukunft, um mit den meisten anderen in Effizienz vergleichbar zu werden.

Ich habe versucht, die WHERE-statement oben zu verwenden, ich dachte, es sei richtig, da niemand es korrigiert habe, aber ich habe mich geirrt; Nach einigen Suchen habe ich herausgefunden, dass dies die richtige Formel für die WHERE-statement ist, so dass der Code wie folgt aussieht:

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

Probier diese

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

EXTRACT (Einheit FROM Datum) function ist besser, da weniger Gruppierung verwendet wird und die function einen Zahlenwert zurückgibt.

Die Vergleichsbedingung beim Gruppieren ist schneller als die function DATE_FORMAT (die einen Zeichenfolgenwert zurückgibt). Verwenden Sie die function | field, die einen Wert ohne Zeichenfolgen für die SQL-Vergleichsbedingung zurückgibt (WHERE, HAVING, ORDER BY, GROUP BY).

Wenn Ihre Suche mehrere Jahre dauert und Sie dennoch monatlich gruppieren möchten, schlage ich vor:

Version 1:

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

Version # 2 (effizienter) :

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

Ich habe diese Versionen auf einem großen Tisch mit 1.357.918 Zeilen ( innodb ) verglichen , und die 2. Version scheint bessere Ergebnisse zu haben.

Version1 (Durchschnitt von 10 ausgeführt) : 1.404 Sekunden
Version2 (Durchschnitt von 10 ausgeführt) : 0,780 Sekunden

( SQL_NO_CACHE Schlüssel hinzugefügt, um zu verhindern, dass MySQL Caching für Abfragen verwendet.)

Wenn Sie in MySQL nach Datum gruppieren möchten, verwenden Sie den folgenden Code:

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

Hoffe, das spart Zeit für diejenigen, die diesen Thread finden werden.

Wenn Sie Datensätze für ein bestimmtes Jahr filtern möchten (zB 2000), dann optimieren Sie die WHERE Klausel wie WHERE :

 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. 

Anstatt von:

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

Die Ergebnisse wurden für eine Tabelle generiert, die 300.000 Zeilen und einen Index für die Datumsspalte enthält.

Wie für die GROUP BY Klausel habe ich die drei Varianten gegen die oben genannte Tabelle getestet; Hier sind die Ergebnisse:

 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. 

Der letzte ist der Gewinner.

Wenn Sie eine monatliche Statistik mit Zeilenanzahl pro Monat für jedes Jahr erhalten möchten, die bis zum letzten Monat bestellt wurde, versuchen Sie Folgendes:

 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 

Die folgende Abfrage funktionierte für mich in 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); 

Komplette und einfache Lösung mit ähnlich leistungsfähiger und gleichzeitig kürzerer und flexiblerer Alternative derzeit aktiv:

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

Sie können dies einfach Mysql DATE_FORMAT () -function in GROUP BY tun. Vielleicht möchten Sie eine zusätzliche Spalte für mehr Klarheit in einigen Fällen hinzufügen, wie z. B. wo Datensätze über mehrere Jahre dann denselben Monat in verschiedenen Jahren auftritt.Hier so viele Optionen können Sie dies anpassen. Bitte lesen Sie dies vor dem Start. Ich hoffe, es sollte sehr hilfreich für Sie sein. Hier ist eine Beispielabfrage für Ihr Verständnis

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

Ich bevorzuge es, die einjährige Gruppenauswahl wie folgt zu optimieren:

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

Auf diese Weise können Sie das Jahr einmalig, zB '2009' , mit einem benannten Parameter verbinden und müssen sich nicht darum sorgen, '-01-01' oder '2010' separat zu übergeben.

Da wir vermutlich nur Zeilen zählen und die id niemals NULL , bevorzuge ich COUNT(*) zu 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') —> MAI

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