mysql分日汇总填充0_将记录按小时或按日分组,并在mysql中填充零或空的空白

生成单列dates_hours表,其中包含合理范围内的所有日期和小时(例如,从1900到2200).

然后从此表执行LEFT JOIN到您当前的查询.

要使此技术正确执行,您可能需要在表中添加一个包含转换时间戳的索引列(您的copied_timestamp转换为DATETIME,舍入到小时)

SELECT date_hour, count(req.converted_timestamp)

FROM

dates_hours

LEFT JOIN req ON req.converted_timestamp = dates_hours.date_hour

WHERE date_hour

BETWEEN (SELECT MIN(req.converted_timestamp) FROM req)

AND (SELECT MAX(req.converted_timestamp) FROM req)

GROUP BY date_hour

要生成dates_hours表：

CREATE TABLE dates_hours (date_hour DATETIME PRIMARY KEY);

DELIMITER $$$

CREATE PROCEDURE generate_dates_hours (to_date DATETIME)

BEGIN

DECLARE start_date DATETIME;

DECLARE inc INT;

SELECT MAX(date_hour) INTO start_date FROM dates_hours;

IF start_date IS NULL THEN

SET start_date = '1900-01-01';

END IF;

SET inc = 1;

WHILE start_date + INTERVAL inc HOUR <= to_date DO

INSERT INTO dates_hours VALUE (start_date + INTERVAL inc HOUR);

SET inc = inc +1;

END WHILE;

END $$$

DELIMITER ;

CALL generate_dates_hours('2200-01-01');

好吧,现在我正在自我校对,我意识到这是一个非常牵强的解决方案.我希望有人能想出一个更优雅的人.