Comment combler les écarts de date dans les requêtes MySQL ?

Remplir les lacunes de date dans MySQL

Lorsque vous travaillez avec des requêtes basées sur la date dans MySQL, vous pouvez rencontrer des situations où il y a des lacunes dans les données pour des dates précises. Cela peut être problématique si vous souhaitez présenter une vue complète des données ou effectuer des calculs qui reposent sur un ensemble complet de dates.

Requête avec intervalles de dates :

Pour illustrer le problème, considérons la requête suivante :

SELECT DATE(posted_at) AS date,
    COUNT(*) AS total,
    SUM(attitude = 'positive') AS positive,
    SUM(attitude = 'neutral') AS neutral,
    SUM(attitude = 'negative') AS negative
    FROM `messages`
    WHERE (`messages`.brand_id = 1)
    AND (`messages`.`spam` = 0
    AND `messages`.`duplicate` = 0
    AND `messages`.`ignore` = 0)
    GROUP BY date ORDER BY date

Cette requête renvoie les résultats regroupés par date, affichant le nombre total et les sommes d'attitude pour chaque date. Cependant, s'il y a des dates sans données, elles ne seront pas incluses dans les résultats, ce qui entraînera des lacunes dans l'ensemble de données.

Remplir les lacunes :

Pour combler ces écarts avec des zéros, nous pouvons utiliser une table d'assistance pour générer un ensemble complet de dates entre une date de début et une date de fin. La table d'assistance peut être créée à l'aide de la requête suivante :

SELECT date AS dt FROM (
    SELECT CURDATE() AS date
    UNION ALL
    SELECT date + INTERVAL 1 DAY FROM dates
    FROM dates
    WHERE date < DATE_ADD(CURDATE(), INTERVAL 1 YEAR)
) dates

Cette requête ajoute récursivement un jour à la date actuelle jusqu'à ce qu'elle atteigne un an dans le futur.

Requête avec Table d'assistance :

Maintenant, nous pouvons joindre la table d'assistance avec notre requête principale en utilisant un LEFT JOIN pour remplir la date lacunes :

SELECT  d.dt AS date,
        COUNT(*) AS total,
        SUM(attitude = 'positive') AS positive,
        SUM(attitude = 'neutral') AS neutral,
        SUM(attitude = 'negative') AS negative
FROM    dates d
LEFT JOIN
        messages m
ON      m.posted_at >= d.dt
        AND m.posted_at < d.dt + INTERVAL 1 DAYS
        AND spam = 0
        AND duplicate = 0
        AND ignore = 0
GROUP BY
        d.dt
ORDER BY
        d.dt

Cette requête renverra un ensemble complet de dates, y compris celles sans données, et comblera les lacunes avec des zéros.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!