MySQL如何填充范围内的缺失日期？

  • Z时代
  • 2024-01-10
  • 分类：问答

我有一个包含2列的表格，日期和分数。它最多有30个条目，最近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 )

…根据需要提供尽可能多的值。

  1. 使用DATE_ADD构造日期列表，并根据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 numbersn

    WHERE DATE_ADD(‘2010-06-06’, INTERVAL n.id -1 DAY) <= ‘2010-06-14’ ) x

  2. 根据时间部分将联接左移到数据表中：

       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`

以上是 MySQL如何填充范围内的缺失日期？ 的全部内容， 来源链接： utcz.com/qa/424097.html

回到顶部