将记录按小时或按天分组，并在 mysql 中用零或空值填充空白 [英] Grouping records hour by hour or day by day and filling gaps with zero or null in mysql

问题描述

我写了一个查询，按小时计算记录:

I have written a query that counts records hour by hour:

select TO_CHAR(copied_timestamp, 'YYYY-MM-DD HH24'),count(*) from req group by
TO_CHAR(copied_timestamp, 'YYYY-MM-DD HH24');

结果是:

2012-02-22 13    2280
2012-02-22 15    1250
2012-02-22 16    1245
2012-02-22 19    1258

但我需要这样的结果:

2012-02-22 13    2280
2012-02-22 14    0
2012-02-22 15    1250
2012-02-22 16    1245
2012-02-22 17    0
2012-02-22 18    0
2012-02-22 19    1258

我也有这些按天和月分组的查询！

Also I have these queries that group by day and month too!

select TO_CHAR(copied_timestamp, 'YYYY-MM-DD'),count(*)  from req
group by TO_CHAR(copied_timestamp, 'YYYY-MM-DD');

select TO_CHAR(copied_timestamp, 'YYYY-MM'),count(*)  from req
group by TO_CHAR(copied_timestamp, 'YYYY-MM');

我也需要用零或空来填充它们的间隙.任何帮助都非常感谢.

I need their gaps to be filled with zero or null too. Any help is really appreciated.

注意:这个问题在 oracle 中有一个使用 CONNECT BY 的答案，但我需要在 Mysql 中得到答案，因为 Mysql 不支持 CONNECT BY.这是链接

Note: There is an answer for this question in oracle using CONNECT BY but i need the answer in Mysql because Mysql does not support CONNECT BY. Here is the link

推荐答案

我创建了一个名为 TBL_NUMBERS 的表

I created a table called TBL_NUMBERS

CREATE TABLE `TBL_NUMBER` (`n` int(11) NOT NULL)

并插入从 1 到 1000 的记录.现在我可以使用这个查询生成任何类型的日期范围:

and inserted records from 1 to 1000. Now I can generate any kind of date range using this query:

SELECT '2012-06-21' + INTERVAL n-1 [DAY | HOUR | MINUTE] or as dateRange
 FROM TBL_NUMBER
WHERE '2012-06-21' + INTERVAL n-1 [DAY | HOUR | MINUTE] <= '2012-06-23';

然后我可以将这个表格与我的结果结合起来以填补日期空白.如果我需要超过 1000 个日期范围，我可以在 TBL_NUMBER

Then I can join this table with my results to fill the date gap. If i need more than 1000 date range I can insert more records in TBL_NUMBER

如果你有更好的想法，我很想知道;)

If you have any better idea, I'm eager to know that ;)

这篇关于将记录按小时或按天分组，并在 mysql 中用零或空值填充空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！