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

问题描述

我写了一个按小时计数的查询：

  select TO_CHAR（copied_timestamp，'YYYY-MM-DD HH24'），从请求组计数（*）到
 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 
  

另外我也有这些查询按日期和月份分组！

  select TO_CHAR（ TO_CHAR（copied_timestamp，'YYYY-MM-DD'）;从req 
组计数（*）到TO_CHAR（copied_timestamp，'YYYY-MM-DD'）; 
 
通过TO_CHAR（copied_timestamp，'YYYY-MM'）选择TO_CHAR（copied_timestamp，'YYYY-MM'），从req 
组中计数（*）; 
  

我需要填充零或零。
任何帮助都非常值得赞赏。

注意：
在oracle中使用 CONNECT BY 但我需要Mysql中的答案，因为Mysql不支持 CONNECT BY 。
这里是链接

解决方案

我创建了一个名为TBL_NUMBERS的表

  CREATE TABLE`TBL_NUMBER`（`n` int（11）NOT NULL）
  

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

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

然后我可以加入这张表格来填补日期空白。
如果我需要超过1000个日期范围，我可以在 TBL_NUMBER

中插入更多记录如果您有任何更好的主意，我渴望知道;）

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');

the result is:

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

But I need a result like this:

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.

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

解决方案

I created a table called TBL_NUMBERS

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

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';

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屋！