Comment remplir les dates manquantes dans une plage de dates MySQL ?

Remplissez les dates manquantes dans la plage de dates dans MySQL

Dans certains cas, vous pouvez rencontrer des tableaux avec des dates manquantes dans une plage de dates. Cela peut perturber le processus d’analyse des données et de traçage. Pour combler cette lacune, MySQL propose une solution permettant de remplir les dates manquantes avec des valeurs numériques spécifiées.

Prenons l'exemple d'un tableau qui enregistre les scores associés à une date précise. Si certaines dates sont omises, notre objectif est de les compléter avec des valeurs d'espace réservé (par exemple 0) pour obtenir une plage contiguë.

Relever les défis

MySQL ne dispose pas de fonctionnalités récursives natives, mais nous pouvons utiliser l'astuce de la table NUMBERS pour générer des séquences de dates basées sur des colonnes à incrémentation automatique.

Générer une séquence de dates

1. Créer un tableau NUMBERS :

<code class="language-sql">DROP TABLE IF EXISTS `numbers`;
CREATE TABLE `numbers` (
  `id` int(10) unsigned NOT NULL auto_increment,
   PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;</code>

2. Remplir le tableau :

<code class="language-sql">INSERT INTO `numbers`
  ( `id` )
VALUES
  ( NULL ); -- 添加足够多的空行以覆盖所需的日期范围</code>

Cela générera autant de lignes que la plage de dates l'exige. (Remarque : le nombre de lignes insérées doit être ajusté en fonction de la plage de dates. La simple insertion de NULL ne peut pas générer automatiquement suffisamment de lignes)

3. Séquence de dates de construction :

<code class="language-sql">SELECT DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY)
FROM `numbers` `n`
WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` -1 DAY) <= '2010-06-14';</code>

Remplacez les dates "2010-06-06" et "2010-06-14" par les dates de début et de fin souhaitées au format AAAA-MM-JJ.

Remplissez les dates manquantes

1. Créer une jointure à gauche :

<code class="language-sql">SELECT `x`.`timestamp` AS `timestamp`,
          COALESCE(`y`.`score`, 0) AS `cnt`
FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY), '%Y-%m-%d') AS `timestamp`
             FROM `numbers` `n`
            WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY) <= '2010-06-14') x
LEFT JOIN `y` ON STR_TO_DATE(`y`.`date`, '%Y-%m-%d') = `x`.`timestamp`;</code>

Remplacez y par votre tableau de données réel et ajustez le format de date en conséquence. (Il est recommandé d'utiliser un format YYYY-MM-DD unifié pour éviter les problèmes causés par la conversion de format)

En exécutant cette requête, vous obtenez l'ensemble complet des dates dans la plage spécifiée, où les valeurs manquantes sont remplies avec la valeur 0 spécifiée. (Remarque : numbersLe tableau doit être pré-rempli avec suffisamment de lignes pour garantir que toute la plage de dates est couverte.)

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!