MySQL组BY和填充空行

Question

我相信这个问题以前已经有答案了，但是我找不到我需要的细节。

对于分析系统，我需要能够对行进行分组并在图表上返回它们，可以按分钟、小时、日、月或年分组。我有这个工作正常(下面的示例代码)。

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解决方案，那将更棒。

Answer 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

Answer 2

这通常在数据仓库中通过具有包含所有可能日期列表的日期维度来完成。您对日期维度执行一个OUTER JOIN，并将空值COALESCE为0。