Oracle SQL 组行计数按 10 分钟的时间间隔

Oracle SQL group row count by time intervals of 10 minutes

提问by woodplease

I have a table in oracle which contains data such as the following

我在 oracle 中有一个表，其中包含如下数据

created_date       details  
01-Jan-16 04:45    abcd  
01-Jan-16 04:47    efgh  
01-Jan-16 04:53    ijkl  
01-Jan-16 04:54    mnop  
01-Jan-16 04:58    qrst  

....etc

....等等

I want to be able to count the number of rows in the table for every 10 minutes e.g.

我希望能够每 10 分钟计算一次表中的行数，例如

Time    count
04:40       2
04:50       3

Created Date = Timestamp, details = varchar

创建日期 = 时间戳，详细信息 = varchar

How would i do this?

我该怎么做？

Thanks

谢谢

回答by Thorsten Kettner

You can use TO_CHAR and SUBSTR to build the time string:

您可以使用 TO_CHAR 和 SUBSTR 来构建时间字符串：

select
  substr(to_char(created_date, 'hh24:mi'), 1, 4) || '0' as created,
  count(*)
from mytable
group by substr(to_char(created_date, 'hh24:mi'), 1, 4) || '0'
order by substr(to_char(created_date, 'hh24:mi'), 1, 4) || '0';

Or with a subquery (a derived table), so as to have to write the date expression only once:

或者使用子查询（派生表），以便只需要编写一次日期表达式：

select created, count(*)
from
(
  select substr(to_char(created_date, 'hh24:mi'), 1, 4) || '0' as created
  from mytable
)
group by created
order by created;

回答by Gordon Linoff

One method is to extract the hour and minute and do arithmetic:

一种方法是提取小时和分钟并进行算术运算：

select extract(hour from created_date) as hh,
       floor(extract(minute from created_date) / 6) as min,
       count(*)
from t
group by extract(hour from created_date),
         floor(extract(minute from created_date) / 6)

回答by Florin Ghita

An answer would be:

答案是：

select trunc(sysdate, 'hh')+ trunc(to_char(sysdate,'mi')/10)*10/1440 from dual;

You can replace sysdate with your actual date/timestamp column and dual with your table

您可以将 sysdate 替换为您的实际日期/时间戳列，并使用您的表替换 dual

To understand the components, run:

要了解组件，请运行：

select trunc(sysdate, 'hh') the_hour,
   to_char(sysdate,'mi') the_minutes,
   trunc(to_char(sysdate,'mi')/10)*10 minutes_truncated,
   trunc(to_char(sysdate,'mi')/10)*10/1440 part_of_the_day, --as 1 represents a day in oracle datetime system
   trunc(sysdate, 'hh')+ trunc(to_char(sysdate,'mi')/10)*10/1440 result
from dual;

回答by Emil Moise

Here's a solution if you want to make the group by on an actual timestamp value:

如果您想根据实际时间戳值进行分组，这是一个解决方案：

create table test_10_minutes_group_by (created_date timestamp, details varchar2(4));

insert into test_10_minutes_group_by values (systimestamp, 'aaa'); -- current time
insert into test_10_minutes_group_by values (systimestamp - 1/24/60, 'bbb'); -- 1 minute ago
insert into test_10_minutes_group_by values (systimestamp - 1/24/60 * 10, 'ccc'); -- 10 minutes ago
insert into test_10_minutes_group_by values (systimestamp - 1/24/60 * 20, 'ccc2'); -- 20 minutes ago
insert into test_10_minutes_group_by values (systimestamp - 1/24/60 * 25, 'abc');  -- 25 minutes ago
insert into test_10_minutes_group_by values (systimestamp - 1/24/60 * 30, 'xyz'); -- 30 minutes ago
insert into test_10_minutes_group_by values (systimestamp - 1/24/60 * 35, 'xyz2'); -- 35 minutes ago

select 
    actual_time,
    to_char(actual_time, 'hh24:mi:ss') pretty_date,
    count(1)
  from (
    select 
        trunc(created_date, 'mi') /*remove seconds*/ -  1/24/60 * mod(extract (minute from created_date), 10) /*substract units digit from minutes*/ actual_time, 
        details
      from 
        test_10_minutes_group_by 
  ) 
  group by actual_time;