MySQLはどのように範囲の欠落した日付を埋めるのですか？

質問

私は2つの列、日付とスコアを持つテーブルを持っています。それは過去30日間1つのそれぞれについて、最大30エントリを持っています。

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

私の問題は、いくつかの日付が欠落していることです - 私は見てみたい。

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
...

単一のクエリから必要なのは 19,21,9,14,0,0,10,0,0,14... つまり、欠落している日付は0で埋められるということです。

私はすべての値を取得する方法を知っているし、サーバー側の言語で日付を繰り返し、空白を欠落させる。しかし、これはmysqlで行うことが可能で、結果を日付でソートし、不足している部分を取得します。

編集：このテーブルには、UserIDという名前の別のカラムがあり、私は30.000人のユーザーを持っており、それらのいくつかはこのテーブルのスコアを持っています。私は各ユーザーの過去30日間のスコアを必要とするため、30日前の日付は毎日削除しています。理由は、私は過去30日間のユーザーの活動のグラフを作成し、グラフをプロットするために、私はカンマで区切られた30の値が必要だからです。だから私はクエリで私にUSERID=10203活動を取得し、クエリは私に30スコア、過去30日間のそれぞれを得ることができると言うことができます。私は今より明確であることを願っています。

どのように解決するのですか？

MySQL には再帰的な機能がないので、NUMBERS テーブルのトリックを使用することになります。

1. 増分する数字だけを保持するテーブルを作成します - 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. を使用してテーブルを入力します。

   INSERT INTO `example`.`numbers`
     ( `id` )
   VALUES
     ( NULL )
   
   

   ...必要な数だけ値を指定します。

3. 使用する 日付の追加 を使用して、NUMBERS.id値に基づいて日数を増やして、日付のリストを作成します。 2010-06-06" と "2010-06-14" をそれぞれの開始日と終了日に置き換えてください（ただし、同じフォーマット、YYYY-MM-DDを使用します）。

   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します。

      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`
   
   

日付の書式を維持したい場合は DATE_FORMAT関数 :

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