如何选择按日期分组的记录，包括没有记录的时间 [英] How to select records grouped by the hour of the day including hours that have no records

问题描述

我在Oracle数据库中有一个表，其中包含我们系统中的用户执行的操作。它用于统计分析，我需要显示给定日期执行的操作数，按照一天中的小时分组。

我有一个查询，这样做很好但是，它不显示一天中不包含任何操作的时间（显然因为没有记录显示该小时）。

例如，查询：

  SELECT TO_CHAR（event_date，'HH24'）AS during_hour，
 COUNT（*）
 FROM user_activity 
 WHERE event_date BETWEEN to_date（'15 -JUN-2010 14:00:00'，'DD-MON-YYYY HH24：MI：SS'）
 AND to_date（'16 -JUN-2010 13 ：59：59'，'DD-MON-YYYY HH24：MI：SS'）
 AND event ='user.login'
 GROUP BY TO_CHAR（event_date，'HH24'）
 ORDER BY at_hur; 
  

（不要注意奇怪的开始和结束日期，他们是这样的，因为他们在GMT，我是GMT + 10 - 但这是一个单独的问题，除非它影响回答这个问题的能力）

这产生一个结果集：

  DURING_HOUR COUNT（*）
 -------------------- -  ---------------------- 
 00 12 
 01 30 
 02 18 
 03 20 
 04 12 
 05 24 
 06 20 
 07 4 
 23 8 
 
 9行选择
  

如何更改此查询以显示当天的时间t包含0个事件？

我搜索了Stack Overflow，并发现了一些类似的问题，但不是这样回答我的具体问题。

您的帮助不胜感激。

解决方案

  SELECT h.hrs，NVL（Quantity，0 ）数量
 FROM（SELECT TRIM（to_char（LEVEL  -  1，'00'））hrs 
 FROM dual 
 CONNECT BY LEVEL< 25）h 
 LEFT JOIN（SELECT TO_CHAR （event_date，'HH24'）AS during_hour，
 COUNT（*）数量
 FROM user_activity u 
 WHERE event_date BETWEEN 
 to_date（'15 -JUN-2010 14:00:00 '，'DD-MON-YYYY HH24：MI：SS'）和
 to_date（'16 -JUN-2010 13:59:59'，'DD-MON-YYYY HH24：MI：SS'）
 AND event ='user.login'
 GROUP BY TO_CHAR（event_date，'HH24'））t 
 ON（h.hrs = t.during_hour）
 ORDER BY h.hrs; 
  

I have a table in an Oracle database that contains actions performed by users in one of our systems. It's used for statistical analysis and I need to display the number of actions performed for a given date, grouped by hour of the day.

I have a query that does that fine, however, it does not display the hours of the day that contain no actions (obviously because there are no records to display for that hour).

For example, the query:

SELECT TO_CHAR(event_date, 'HH24') AS during_hour,
  COUNT(*)
FROM user_activity
WHERE event_date BETWEEN to_date('15-JUN-2010 14:00:00', 'DD-MON-YYYY HH24:MI:SS') 
    AND to_date('16-JUN-2010 13:59:59', 'DD-MON-YYYY HH24:MI:SS')
AND event = 'user.login'
GROUP BY TO_CHAR(event_date, 'HH24')
ORDER BY during_hour;

(Pay no attention to the odd start and end dates, they are like this because they're in GMT and I'm GMT+10 - but that's a separate issue, unless it affects the ability to answer this question)

This produces a result set of:

DURING_HOUR            COUNT(*)               
---------------------- ---------------------- 
00                     12                     
01                     30                     
02                     18                     
03                     20                     
04                     12                     
05                     24                     
06                     20                     
07                     4                      
23                     8                      

9 rows selected

How can I ammend this query to display the hours of the day that contain 0 events?

I've searched Stack Overflow, and found some similar questions, but not that answer my specific issue.

Your assistance is appreciated.

解决方案

SELECT h.hrs, NVL(Quantity, 0) Quantity
FROM (SELECT TRIM(to_char(LEVEL - 1, '00')) hrs
       FROM dual
       CONNECT BY LEVEL < 25) h
LEFT JOIN (SELECT TO_CHAR(event_date, 'HH24') AS during_hour,
                  COUNT(*) Quantity
           FROM user_activity u
           WHERE event_date BETWEEN
                 to_date('15-JUN-2010 14:00:00', 'DD-MON-YYYY HH24:MI:SS') AND
                 to_date('16-JUN-2010 13:59:59', 'DD-MON-YYYY HH24:MI:SS')
           AND event = 'user.login'
           GROUP BY TO_CHAR(event_date, 'HH24')) t
ON (h.hrs = t.during_hour)
ORDER BY h.hrs;

这篇关于如何选择按日期分组的记录，包括没有记录的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！