在Oracle两个日期之间计数记录小时数以小时(Counting number of records

我需要做的甲骨文这个序列中的单个查询。

select count(*) from table1
where request_time < timestamp'2012-05-19 12:00:00' and (end_time > timestamp'2012-05-19 12:00:00' or end_time=null);

select count(*) from table1
where request_time < timestamp'2012-05-19 13:00:00' and (end_time > timestamp'2012-05-19 13:00:00' or end_time=null);

select count(*) from table1
where request_time < timestamp'2012-05-19 14:00:00' and (end_time > timestamp'2012-05-19 14:00:00' or end_time=null);

select count(*) table1
where request_time < timestamp'2012-05-19 15:00:00' and (end_time > timestamp'2012-05-19 15:00:00' or end_time=null);

select count(*) from table1
where request_time < timestamp'2012-05-19 16:00:00' and (end_time > timestamp'2012-05-19 16:00:00' or end_time=null);

正如你所看到的是每小时逐一增加。 这里是输出

COUNT(*)               
1085                   

---

COUNT(*)               
1233                   

---

COUNT(*)               
1407                   

---

COUNT(*)               
1322                   

---

COUNT(*)               
1237

---

我已经写了查询，但它并没有给我正确的答案！

select col1, count(*) from
(select TO_CHAR(request_time, 'YYYY-MM-DD HH24') as col1 from table1
 where request_time <= timestamp'2012-05-19 12:00:00' and (end_time >= timestamp'2012-05-19 12:00:00' or end_time=null))
group by col1 order by col1;

此查询给了我一个结果集，它的那笔是COUNT（*）等于上面写的第一个查询！ 这里是结果：

COL1          COUNT(*)               
------------- ---------------------- 
2012-05-19 07      22                     
2012-05-19 08      141                    
2012-05-19 09      322                    
2012-05-19 10      318                    
2012-05-19 11      282  

Answer 1:

需要注意的使用trunc表达日期值。 您可以省略alter session ，如果你不运行在SQL *查询加分。

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> SELECT 
       trunc(created,'HH'), 
       count(*) 
     FROM 
       test_table 
     WHERE 
       created > trunc(SYSDATE -2) 
     group by trunc(created,'HH');


TRUNC(CREATED,'HH')   COUNT(*)
------------------- ----------
2012-05-21 09:00:00        748
2012-05-21 16:00:00         24
2012-05-21 17:00:00         12
2012-05-21 22:00:00        737
2012-05-21 23:00:00        182
2012-05-22 20:00:00         16
2012-05-22 21:00:00        293
2012-05-22 22:00:00        610

8 ROWS selected.

Answer 2:

您个人的查询似乎是重叠的匹配记录集。 它将帮助，如果你包括在你的问题有些样本数据，但我可以猜...

例如，所有这些具有END_TIME = null，并且一个REQUEST_TIME记录= 2012-05-19 13点30分零零秒将被第一和第二查询都被计数; 但他们将只进行一次在您的“整体”的查询计数。

也许你的意思是对REQUEST_TIME日期范围查询，而不是具有开放式的谓词像request_time < timestamp'2012-05-19 12:00:00' ？

Answer 3:

对于Oracle数据库如预期其工作。

SELECT TO_CHAR（更新， 'DD-MM-YYYY HH'），COUNT（*）FROM顾客WHERE TRUNC（更新）> = TO_CHAR（'02 -JUL-2017' ）和TRUNC（更新）<= TO_CHAR（'02 - JUL-2017 '）由TO_CHAR组（更新，' DD-MM-YYYY HH'）

Answer 4:

试试这个

select TO_CHAR(request_time, 'HH24') as "hourOfDay",count(*)as
"numOfLogin", TO_CHAR(request_time, 'DD') as "date" from table1 
where request_time<= timestamp'2017-08-04 23:59:59' and
(request_time>= timestamp'2017-08-03 00:00:01' ) group by
TO_CHAR(request_time, 'HH24'),TO_CHAR(request_time, 'DD');

文章来源: Counting number of records hour by hour between two dates in oracle