统计Oracle中两个日期之间每小时的记录数 [英] Counting number of records hour by hour between two dates in oracle

问题描述

 从table1中选择count（*）
在oracle中执行此序列的单个查询其中request_time< timestamp'2012-05-19 12:00:00'和（end_time> timestamp'2012-05-19 12:00:00'或end_time = null）; 
 
从table1中选择count（*）
其中request_time< timestamp'2012-05-19 13:00:00'和（end_time> timestamp'2012-05-19 13:00:00'或end_time = null）; 
 
从table1中选择count（*）
其中request_time< timestamp'2012-05-19 14:00:00'和（end_time> timestamp'2012-05-19 14:00:00'或end_time = null）; 
 
 select count（*）table1 
 where request_time< timestamp'2012-05-19 15:00:00'和（end_time> timestamp'2012-05-19 15:00:00'或end_time = null）; 
 
从table1中选择count（*）
其中request_time< timestamp'2012-05-19 16:00:00'和（end_time> timestamp'2012-05-19 16:00:00'或end_time = null）; 
  

正如您所看到的小时数正逐渐增加。
这里是输出

  COUNT（*）
 1085 
  

---

  COUNT（*）
 1233 
  

---

  COUNT（ *）
 1407 
  

---

  COUNT（*）
 1322 
  

---

  COUNT（*）
 1237 
  

< hr>

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

 <$ c $从
中选择col1，count（*）（从table1 
中选择TO_CHAR（request_time，'YYYY-MM-DD HH24'）作为col1，其中request_time <= timestamp'2012-05-19 12 ：00：00'和（end_time> = timestamp'2012-05-19 12:00:00'或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 
  

解决方案

请注意 trunc 表达式与日期值的用法。如果您没有在sql * plus中运行查询，则可以省略 alter session 。

  SQL> alter session set nls_date_format ='yyyy-mm-dd hh24：mi：ss'; 
 
会议改变了。 
 
 SQL> SELECT 
 trunc（创建，'HH'），
 count（*）
 FROM 
 test_table 
 WHERE 
创建> trunc（SYSDATE -2）
 by trunc（created，'HH'）; 
 
 
 TRUNC（已创建，'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个行。 
  

I need a SINGLE query that does this sequence in oracle.

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

As you see the hour is increasing one by one. here is the output

COUNT(*)               
1085                   

---

COUNT(*)               
1233                   

---

COUNT(*)               
1407                   

---

COUNT(*)               
1322                   

---

COUNT(*)               
1237

---

I have written a query but it does not give me the right answer!

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;

this query gives me a result set that sum of it's count(*) is equal to the first query written above! here is the result:

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  

解决方案

Note the usage of trunc expression with date values. You can omit the alter session if you are not running the query in sql*plus.

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.

这篇关于统计Oracle中两个日期之间每小时的记录数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！