MySQL comment remplir les dates manquantes?

Question

J'ai une table avec 2 colonnes, la date et le score. Il a tout au plus de 30 entrées pour chacun des 30 derniers jours l'un.

date      score
-----------------
1.8.2010  19
2.8.2010  21
4.8.2010  14
7.8.2010  10
10.8.2010 14

Mon problème est que certaines dates sont manquantes - je veux voir:

date      score
-----------------
1.8.2010  19
2.8.2010  21
3.8.2010  0
4.8.2010  14
5.8.2010  0
6.8.2010  0
7.8.2010  10
...

Ce dont j'ai besoin à partir de la seule requête pour obtenir: 19,21,9,14,0,0,10,0,0,14... ce Qui signifie que le manque dates sont remplis avec de 0.

Je sais comment obtenir toutes les valeurs et côté serveur langage de l'itération sur les dates manquantes et les blancs. Mais est-ce possible de le faire dans mysql, de sorte que je trier les résultats par date et obtenir les pièces manquantes...

Merci

Jerry

EDIT: Dans ce tableau il y a une autre colonne nommée UserID, j'ai donc 30.000 utilisateurs et certains d'entre eux ont le score dans ce tableau. J'ai supprimer les dates de tous les jours si la date de moins de 30 jours parce que j'ai besoin des 30 derniers jours score pour chaque utilisateur. La raison en est que je fais un graphique de l'activité de l'utilisateur au cours des 30 derniers jours et pour tracer un graphique j'ai besoin de l'30 valeurs séparées par des virgules. Donc je peux dire que dans la requête de me le USERID=10203 de l'activité et de la requête de me la 30 partitions, une pour chacun des 30 derniers jours. J'espère que je suis plus clair maintenant.

Answer 1

MySQL n'a pas récursive de la fonctionnalité, de sorte que vous êtes de gauche avec l'aide de la table de NOMBRES astuce -

1. Créer une table qui ne contient que des numéros séquentiels - facile à faire à l'aide d'un auto_increment:

   DROP TABLE IF EXISTS `example`.`numbers`;
   CREATE TABLE  `example`.`numbers` (
     `id` int(10) unsigned NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

2. Remplir le tableau à l'aide de:

   INSERT INTO NUMBERS
     (id)
   VALUES
     (NULL)
   

   ...pour toutes les valeurs que vous avez besoin.

3. Utilisation DATE_ADD de construire une liste de dates, d'accroître les jours en fonction des NUMÉROS.valeur de l'id. Remplacer "2010-06-06" et "2010-06-14" avec votre début et dates de fin (mais utilisent le même format AAAA-MM-JJ) -

   SELECT x.*
     FROM (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 ) x
   

4. LEFT JOIN sur votre tableau de données en fonction du temps de partie:

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

Si vous souhaitez conserver le format de la date, utilisez la fonction DATE_FORMAT:

DATE_FORMAT(x.ts, '%d.%m.%Y') AS timestamp

Answer 2

Vous pouvez accomplir cela en utilisant un Calendrier de Table. C'est un tableau que vous créez en une fois, et le remplir avec une plage de dates (par exemple, un ensemble de données pour chaque jour de 2000 à 2050; cela dépend de vos données). Ensuite, vous pouvez faire une jointure externe de votre table contre le calendrier de table. Si une date est manquante dans votre tableau, vous retourner 0 pour le score.

Vous pouvez prendre un coup d'oeil ici pour voir des exemples: Pourquoi devrais-je envisager l'utilisation d'un auxiliaire de calendrier de table?

Answer 3

J'ai trouvé une solution prête à coller ici: http://stackoverflow.com/a/19587992/1112963 Il n'est pas nécessaire de modifier ni même de coller vos noms de base de données spécifiques.