mysql - vergleichen - sql datenabfrage




MySQL Abfrage GROUP BY Tag/Monat/Jahr (9)

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!


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

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 id niemals NULL , bevorzuge ich COUNT(*) zu COUNT(id) .


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

Probier diese

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

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

Die Vergleichsbedingung beim Gruppieren ist schneller als die Funktion DATE_FORMAT (die einen Zeichenfolgenwert zurückgibt). Verwenden Sie die Funktion | 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 ) innodb , 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 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 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.


.... 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








group-by