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

zdwk9cvp 于 2021-06-20 发布在 Mysql

关注(0)|答案(5)|浏览(558)

我有一个表，有两列，日期和分数。它最多有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的列，所以我有30000个用户，其中一些用户的得分在这个表中。如果日期<30天前，我会删除每天的日期，因为我需要每个用户的最后30天分数。原因是我正在制作一个过去30天内用户活动的图表，为了绘制一个图表，我需要用逗号分隔的30个值。所以我可以说在query get me中userid=10203活动，查询将得到30个分数，过去30天中每一天一个。我希望我现在更清楚了。

sql mysql recursive-query date-arithmetic gaps-and-islands

来源：https://stackoverflow.com/questions/50916818/get-last-week-with-all-dates-mysql

5条答案

按热度按时间

woobm2wo1#

mysql没有递归功能，所以只能使用数字表技巧-
创建一个只包含递增数字的表-使用自动递增很容易：

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;

使用以下方法填充表：

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

…尽可能多的价值观。
使用date\u 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 `numbers` `n`
         WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` -1 DAY) <= '2010-06-14' ) x

根据时间部分左键联接到数据表：

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(`x`.`ts`, '%d.%m.%Y') AS `timestamp`

赞(0）回复(0）举报 2021-06-20

ikfrs5lh2#

自从有人问这个问题以来，时间一天天过去了。MySQL8.0于2018年发布，增加了对递归公共表表达式的支持，为解决这个问题提供了一种优雅、最先进的方法。
以下查询可用于生成日期列表，例如2010年8月的前15天：

with recursive all_dates(dt) as (
    -- anchor
    select '2010-08-01' dt
        union all 
    -- recursion with stop condition
    select dt + interval 1 day from all_dates where dt + interval 1 day <= '2010-08-15'
)
select * from all_dates

那你就可以了 left join 此结果集与表一起生成预期输出：

with recursive all_dates(dt) as (
    -- anchor
    select '2010-08-01' dt
        union all 
    -- recursion with stop condition
    select dt + interval 1 day from all_dates where dt + interval 1 day <= '2010-08-15'
)
select d.dt date, coalesce(t.score, 0) score
from all_dates d
left join mytable t on t.date = d.dt
order by d.dt

db小提琴演示：

date       | score
:--------- | ----:
2010-08-01 |    19
2010-08-02 |    21
2010-08-03 |     0
2010-08-04 |    14
2010-08-05 |     0
2010-08-06 |     0
2010-08-07 |    10
2010-08-08 |     0
2010-08-09 |     0
2010-08-10 |    14
2010-08-11 |     0
2010-08-12 |     0
2010-08-13 |     0
2010-08-14 |     0
2010-08-15 |     0

赞(0）回复(0）举报 2021-06-20

k2fxgqgv3#

迈克尔·科纳德的回答很好，但我需要15分钟的时间间隔，时间必须从每15分钟的顶端开始：

SELECT a.Days 
FROM (
    SELECT FROM_UNIXTIME( FLOOR( UNIX_TIMESTAMP() / (15 * 60) ) * (15 * 60)) - INTERVAL 15 * (a.a + (10 * b.a) + (100 * c.a)) MINUTE AS Days
    FROM       (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
) a
WHERE a.Days >= curdate() - INTERVAL 30 DAY

这会将当前时间设置为上一轮15分钟：

FROM_UNIXTIME( FLOOR( UNIX_TIMESTAMP() / (15 * 60) ) * (15 * 60))

这将减少15分钟的时间：

- INTERVAL 15 * (a.a + (10 * b.a) + (100 * c.a)) MINUTE

如果有更简单的方法，请告诉我。

赞(0）回复(0）举报 2021-06-20

lkaoscv74#

我不喜欢其他答案，需要创建表格之类的。这个查询在没有helper表的情况下可以有效地执行。

SELECT 
    IF(score IS NULL, 0, score) AS score,
    b.Days AS date
FROM 
    (SELECT a.Days 
    FROM (
        SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Days
        FROM       (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
    ) a
    WHERE a.Days >= curdate() - INTERVAL 30 DAY) b
LEFT JOIN your_table
    ON date = b.Days
ORDER BY b.Days;

让我们来分析一下。

SELECT 
    IF(score IS NULL, 0, score) AS score,
    b.Days AS date

if将检测没有得分的天数并将其设置为0。b、 days是从当前日期开始选择的已配置天数，最多1000天。

(SELECT a.Days 
    FROM (
        SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Days
        FROM       (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
    ) a
    WHERE a.Days >= curdate() - INTERVAL 30 DAY) b

这个子查询是我在stackoverflow上看到的。它有效地生成一个从当前日期开始的过去1000天的列表。末尾where子句中的间隔（当前为30）决定返回哪些天；最大值为1000。这个查询可以很容易地修改为返回100年的日期，但是1000应该适合大多数情况。

LEFT JOIN your_table
    ON date = b.Days
ORDER BY b.Days;

这是将包含分数的表带入其中的部分。与日期生成器查询中选定的日期范围进行比较，以便能够在需要时填写0（分数将设置为0） NULL 最初，因为它是一个 LEFT JOIN ; 这在select语句中是固定的）。我也按日期点，只是因为。这是首选项，您也可以按分数排序。
在 ORDER BY 您可以很容易地将您在编辑中提到的有关用户信息的表加入到表中，以添加最后一个要求。
我希望这个版本的查询能帮助一些人。谢谢你的阅读。

赞(0）回复(0）举报 2021-06-20

93ze6v8z5#

您可以通过使用日历表来实现这一点。这是一个您创建一次并填充日期范围的表（例如，2000-2050年每天一个数据集；这取决于你的数据）。然后，可以将表与日历表进行外部联接。如果表中缺少日期，则返回0作为分数。

赞(0）回复(0）举报 2021-06-20