Groupe d'interrogation MySQL par jour / mois / année
est - il possible que je fasse une simple requête pour compter combien de dossiers j'ai dans une période de temps déterminée comme une année, un mois ou un jour, ayant un TIMESTAMP
champ, comme:
SELECT COUNT(id)
FROM stats
WHERE record_date.YEAR = 2009
GROUP BY record_date.YEAR
ou même:
SELECT COUNT(id)
FROM stats
GROUP BY record_date.YEAR, record_date.MONTH
pour avoir une statistique mensuelle.
Merci!
13 réponses
GROUP BY YEAR(record_date), MONTH(record_date)
découvrez les fonctions de date et heure dans MySQL.
GROUP BY DATE_FORMAT(record_date, '%Y%m')
Note (principalement à l'intention des personnes susceptibles d'effectuer des invocations). Actuellement, cela peut ne pas être aussi efficace que d'autres suggestions. Encore, je laisse comme une alternative et un, aussi, qui peut servir dans de voir comment rapidement d'autres solutions. (Vous ne pouvez pas vraiment dire rapide, du lent jusqu'à ce que vous voyez la différence.) De plus, au fil du temps, des modifications pourraient être apportées au moteur de MySQL en ce qui concerne l'optimisation afin de rendre solution, à certains moments (peut-être pas si loin) à l'avenir, pour devenir tout à fait comparable en efficacité avec la plupart des autres.
j'ai essayé d'utiliser la déclaration " où "ci-dessus, j'ai pensé qu'elle était correcte puisque personne ne l'a corrigée mais j'ai eu tort; après quelques recherches j'ai découvert que c'est la bonne formule pour la déclaration où ainsi le code devient comme ceci:
SELECT COUNT(id)
FROM stats
WHERE YEAR(record_date) = 2009
GROUP BY MONTH(record_date)
essayer celui-ci
SELECT COUNT(id)
FROM stats
GROUP BY EXTRACT(YEAR_MONTH FROM record_date)
EXTRACT(unit FROM date) la fonction est meilleure car moins de groupage est utilisé et la fonction renvoie une valeur numérique.
condition de comparaison lorsque le groupement sera plus rapide que la fonction DATE_FORMAT (qui renvoie une valeur de chaîne). Essayez d'utiliser la fonction / champ qui renvoie la valeur non-string pour la condition de comparaison SQL (où, avoir, ordre Par, Groupe par).
si votre recherche est sur plusieurs années, et que vous voulez toujours grouper mensuellement, je suggère:
version # 1:
SELECT SQL_NO_CACHE YEAR(record_date), MONTH(record_date), COUNT(*)
FROM stats
GROUP BY DATE_FORMAT(record_date, '%Y%m')
version n ° 2 (plus efficace) :
SELECT SQL_NO_CACHE YEAR(record_date), MONTH(record_date), COUNT(*)
FROM stats
GROUP BY YEAR(record_date)*100 + MONTH(record_date)
j'ai comparé ces versions sur une grande table avec 1,357,918 lignes ( innodb ), et la 2ème version semble avoir de meilleurs résultats.
version1 1519220920 "(moyenne de 10 exécutions) : 1,404 secondes
version2 (moyenne de 10 exécutions) : 0,780 secondes
( SQL_NO_CACHE
clé ajoutée pour éviter de MySQL à partir de la mise en CACHE de requêtes.)
si vous voulez grouper par date dans MySQL puis utiliser le code ci-dessous:
SELECT COUNT(id)
FROM stats
GROUP BY DAYOFMONTH(record_date)
espérons que cela fera gagner du temps à ceux qui vont trouver ce fil.
si vous voulez filtrer les enregistrements pour une année particulière (par exemple 2000), puis optimiser la clause WHERE
comme ceci:
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.
au lieu de:
WHERE YEAR(date_column) = 2000
-- average 0.132 sec.
les résultats ont été générés par rapport à un tableau contenant 300k lignes et l'index de la colonne date.
comme pour la clause GROUP BY
, j'ai testé les trois variantes par rapport au tableau ci-dessus; voici les résultats:
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.
le dernier est Le vainqueur.
si vous voulez obtenir des statistiques mensuelles avec le nombre de rangées par mois de chaque année commandée par le dernier mois, puis essayer ceci:
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
Complète et la solution la plus simple à même d'effectuer encore plus courtes et plus souples d'alternative actuellement active:
SELECT COUNT(*) FROM stats
-- GROUP BY YEAR(record_date), MONTH(record_date), DAYOFMONTH(record_date)
GROUP BY DATE_FORMAT(record_date, '%Y-%m-%d')
la requête suivante a fonctionné pour moi dans la base de données Oracle 12c version 12.1.0.0
SELECT COUNT(*)
FROM stats
GROUP BY
extract(MONTH FROM TIMESTAMP),
extract(MONTH FROM TIMESTAMP),
extract(YEAR FROM TIMESTAMP);
vous pouvez faire ceci simplement Mysql DATE_FORMAT () fonction dans le groupe par. Vous voudrez peut-être ajouter une colonne supplémentaire pour plus de clarté dans certains cas, par exemple lorsque les enregistrements s'étendent sur plusieurs années alors qu'un même mois se produit dans des années différentes.Voici tant d'options que vous pouvez personnaliser cela. Veuillez lire ceci avant de commencer. Espérons qu'il devrait être très utile pour vous. Voici un exemple de requête pour votre compréhension
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 ');
je préfère optimiser la sélection d'un groupe d'un an comme cela:
SELECT COUNT(*)
FROM stats
WHERE record_date >= :year
AND record_date < :year + INTERVAL 1 YEAR;
de cette façon, vous pouvez simplement lier l'année en une seule fois , par exemple '2009'
, avec un paramètre nommé et n'avez pas besoin de vous soucier d'ajouter '-01-01'
ou de passer dans '2010'
séparément.
aussi, comme probablement nous sommes juste en train de compter les lignes et id
n'est jamais NULL
, je préfère COUNT(*)
à 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')
--->151960920"
.... group by to_char(date,'YY')
--->89