MySQL如何填充范围内的缺失日期? [英] MySQL how to fill missing dates in range?

问题描述

我有一张桌子，桌子上有两列，日期和分数.它最多有30个条目，最近30天内每个条目一个.

I have a table with 2 columns, date and score. It has at most 30 entries, for each of the last 30 days one.

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

我的问题是缺少某些日期-我想看看:

My problem is that some dates are missing - I want to see:

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.

What I need from the single query is to get: 19,21,9,14,0,0,10,0,0,14... That means that the missing dates are filled with 0.

我知道如何获取所有值以及如何使用服务器端语言遍历日期和缺少空格.但这可以在mysql中完成，这样我就可以按日期对结果进行排序并得到丢失的片段.

I know how to get all the values and in server side language iterating through dates and missing the blanks. But is this possible to do in mysql, so that I sort the result by date and get the missing pieces.

在此表中还有一个名为UserID的列，因此我有30.000个用户，其中一些用户在此表中具有得分.如果date<我每天删除日期. 30天前，因为我需要为每个用户提供最近30天的得分.原因是我正在绘制过去30天的用户活动图，并绘制图表，我需要用逗号分隔的30个值.因此，我可以说在查询中获得USERID = 10203活动，该查询将获得30分，最近30天中的每一分.我希望我现在更加清楚.

In this table there is another column named UserID, so I have 30.000 users and some of them have the score in this table. I delete the dates every day if date < 30 days ago because I need last 30 days score for each user. The reason is I am making a graph of the user activity over the last 30 days and to plot a chart I need the 30 values separated by comma. So I can say in query get me the USERID=10203 activity and the query would get me the 30 scores, one for each of the last 30 days. I hope I am more clear now.

推荐答案

MySQL没有递归功能，因此您只需要使用NUMBERS表技巧-

MySQL doesn't have recursive functionality, so you're left with using the NUMBERS table trick -

1. 创建仅包含递增数字的表-使用auto_increment易于完成:

1. Create a table that only holds incrementing numbers - easy to do using an 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;

使用以下表格填充表格:

Populate the table using:

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

...根据需要提供任意数量的值.

...for as many values as you need.

使用 DATE_ADD 构建日期列表，并根据NUMBERS.id值增加日期.用您各自的开始和结束日期替换"2010-06-06"和"2010-06-14"(但使用相同的格式，YYYY-MM-DD)-

Use DATE_ADD to construct a list of dates, increasing the days based on the NUMBERS.id value. Replace "2010-06-06" and "2010-06-14" with your respective start and end dates (but use the same format, 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

根据时间部分将联接左移到数据表中:

LEFT JOIN onto your table of data based on the time portion:

   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`

如果要维护日期格式，请使用

If you want to maintain the date format, use the DATE_FORMAT function:

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

这篇关于MySQL如何填充范围内的缺失日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！