1. ホーム
  2. mysql

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

2023-08-22 16:13:56

質問

私は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`