Comment gérer les dates manquantes dans les requêtes MySQL ?

Comment combler les lacunes de données dans vos requêtes MySQL

Cette question explore un problème courant dans MySQL, où des lacunes de données peuvent survenir lors d'interrogations entre dates précises. La requête existante de l'utilisateur récupère des métriques basées sur la date, mais elle n'inclut pas les données sur les dates manquantes.

Combler les lacunes de données à l'aide d'une source de données factice

Pour combler les lacunes de données , la solution consiste à créer une table d'assistance nommée « dates ». Ce tableau contient toutes les dates de la plage de début à la fin souhaitée. Ensuite, un LEFT JOIN est effectué entre la table des dates et la table des messages.

Voici la requête modifiée :

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

Explication :

• Le tableau des dates génère un ensemble de dates distinctes dans la période souhaitée plage.
• LEFT JOIN garantit que toutes les dates de la table des dates sont incluses, même s'il n'existe aucun message correspondant.
• Les filtres de la clause ON garantissent que seuls les messages valides pour les dates données sont inclus.
• La clause GROUP BY agrège les données pour chaque date.

Alternatives à l'utilisation de Dummy Rowsource

MySQL est unique par l'absence de fonction intégrée pour générer une séquence de dates. Cependant, il existe des approches alternatives disponibles dans d'autres systèmes de bases de données :

• PostgreSQL possède la fonction generate_series().
• Oracle et SQL Server prennent en charge la récursivité à l'aide de CONNECT BY ou de CTE récursifs.

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!