MySQL GROUP BY并填充空行

一尘不染

MySQL GROUP BY并填充空行

mysql

我敢肯定这已经得到回答，但是我找不到我需要的详细信息。

对于分析系统，我需要能够对行进行分组并按分钟，小时，天，月或年对行进行归还。我可以正常工作（下面的示例代码）。

SELECT COUNT( DISTINCT user_id ) ,  `hour` ,  `timestamp` 
FROM tracking_request
WHERE site_id =  '3'
AND  `timestamp` <  '2011-08-31 04:05:45'
AND  `timestamp` >  '2011-08-29 22:00:00'
GROUP BY  `hour` ,  `day` ,  `month` ,  `year` 
ORDER BY  `timestamp` ASC

问题是，像大多数图表一样，我需要填补数据不存在的空白（例如，最近3分钟没有行）。我读过有关创建“日历表”并连接该数据的信息，但是我如何才能针对每个刻度有效地做到这一点（例如，一年会比分钟容易得多，因为一分钟需要表中有很多行）？如果有帮助，则表中的每个字段都有一列（如上所示，您可以看到“小时”，“天”等）。

编辑：

我最终使用PHP通过使用一个空数组然后填充它来实现这一点。如果有人能想到一个全（或大部分）SQL解决方案，那就更好了。

阅读 740

2020-05-17

共1个答案

一尘不染

在此答案中，我将概述如何生成日历表。

创建三个表，分别表示天，小时和分钟：

CREATE TABLE days (
  day DATE,
  PRIMARY KEY (day)
) 
CREATE TABLE hours (
  hour INT,
  PRIMARY KEY (hour)
) 
CREATE TABLE minutes (
  minute INT,
  PRIMARY KEY (minute)
)

用0到23的数字填充小时表，用0到59的数字填充分钟表。要填充天表，可以创建如下过程：

CREATE PROCEDURE make_days(IN start_date DATE, IN end_date DATE)
BEGIN
  DECLARE curr_date DATE;
  SET curr_date = start_date;
  WHILE curr_date <= end_date DO
    INSERT IGNORE INTO days(day)  VALUES(curr_date);
    SET curr_date = DATE_ADD(curr_date, INTERVAL 1 DAY);
  END WHILE;
END

然后，您可以调用此过程来创建如下日期：

CALL make_days('2011-01-01','2012-12-31');

现在，您可以使用类似于以下内容的查询为给定时间间隔中的每一分钟创建值：

SELECT YEAR(day) AS year, MONTH(day) AS month, DAYOFMONTH(day) AS day, hour, minute
FROM days, hours, minutes
WHERE CAST(CONCAT(day,' ',hour,':',minute) AS DATETIME) BETWEEN '2011-08-31 22:00' AND '2011-09-01 10:00'
ORDER BY year, month, day, hour, minute

2020-05-17