计数记录并按小时分组

深红色589

我试图对表中的记录进行计数，并按小时对它们进行分组，我在查询中获得结果，但是我希望它每小时返回一次，即使没有记录也是如此。

我目前的查询是

SELECT nvl(count(*),0) AS transactioncount, trunc(date_modified, 'HH') as TRANSACTIONDATE
FROM TABLE 
WHERE date_modified between to_date('23-JAN-19 07:00:00','dd-MON-yy hh24:mi:ss') and to_date('24-Jan-19 06:59:59','dd-MON-yy hh24:mi:ss') 
group by trunc(date_modified, 'HH');

这样返回的结果是，

TRANSACTIONCOUNT    |    TRANSACTIONDATE
      43            |   23-Jan-19 07:00:00
      47            |   23-Jan-19 08:00:00
      156           |   23-Jan-19 14:00:00
      558           |   23-Jan-19 15:00:00

我想要的是让它在我的两个约会之间每小时返回一次，所以，

TRANSACTIONCOUNT    |    TRANSACTIONDATE
      43            |   23-Jan-19 07:00:00
      47            |   23-Jan-19 08:00:00
      0             |   23-Jan-19 09:00:00
      0             |   23-Jan-19 10:00:00
      0             |   23-Jan-19 11:00:00
      0             |   23-Jan-19 12:00:00
      0             |   23-Jan-19 13:00:00
      156           |   23-Jan-19 14:00:00
      558           |   23-Jan-19 15:00:00
  --......
      0             |   24-Jan-19 00:00:00
      0             |   24-Jan-19 01:00:00
      0             |   24-Jan-19 02:00:00
  --and so on

巴巴罗斯·奥赞

您可以考虑将以下内容与CONNECT BY level逻辑结合使用：

SELECT sum(transactioncount) as transactioncount, transactiondate 
  FROM
  (
   with "TABLE"(date_modified) as
   (
     SELECT timestamp'2019-01-23 08:00:00' FROM dual union all
     SELECT timestamp'2019-01-23 08:30:00' FROM dual union all
     SELECT timestamp'2019-01-23 09:00:00' FROM dual union all
     SELECT timestamp'2019-01-24 05:01:00' FROM dual   
   )   
  SELECT nvl(count(*),0) AS transactioncount, trunc(date_modified, 'hh24') as transactiondate
    FROM "TABLE" t 
   GROUP BY trunc(date_modified, 'HH24')
  UNION ALL
  SELECT 0, timestamp'2019-01-23 07:00:00' + ( level - 1 )/24
    FROM dual    
 CONNECT BY level <=  24 * extract( day  from 
                            timestamp'2019-01-24 06:59:59'-
                            timestamp'2019-01-23 07:00:00') +
                           extract( hour from 
                            timestamp'2019-01-24 06:59:59'-
                            timestamp'2019-01-23 07:00:00') + 1      
)    
 GROUP BY transactiondate
 ORDER BY transactiondate

Rextester Demo

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。