Oracle sql查询，用于显示每天的事件计数和过去七天内的事件总数

Question

下表：event_table，其中包含有关object_name、event_number、event_supplementary_info和event_time的信息。我想有一个sql查询，它显示了过去七天每天事件的数量和总数。

我需要这样的东西

select Object_name, event_number
max(decode(trim(dow),'MONDAY',totalquantity,0)) Mon, 
max(decode(trim(dow),'TUESDAY',totalquantity,0)) Tue, 
max(decode(trim(dow),'WEDNESDAY',totalquantity,0)) Wed, 
max(decode(trim(dow),'THURSDAY',totalquantity,0)) Thu, 
max(decode(trim(dow),'FRIDAY',totalquantity,0)) Fri,
max(decode(trim(dow),'SATURDAY',totalquantity,0)) Sat, 
max(decode(trim(dow),'SUNDAY',totalquantity,0)) Sun,  
( 
max(decode(trim(dow),'MONDAY',totalquantity,0)) + 
max(decode(trim(dow),'TUESDAY',totalquantity,0)) + 
max(decode(trim(dow),'WEDNESDAY',totalquantity,0)) + 
max(decode(trim(dow),'THURSDAY',totalquantity,0)) + 
max(decode(trim(dow),'FRIDAY',totalquantity,0)) +
max(decode(trim(dow),'SATURDAY',totalquantity,0)) + 
max(decode(trim(dow),'SUNDAY',totalquantity,0)) 
) TOTAL 
from  
( 
  select event_name,  
  to_char(event_time, 'DAY') as dow,  
  sum(event_time) as totalquantity 
  from event_table a 
  where a.event_time >= trunc(sysdate-7,'D') 
  and a.tradedate <= trunc(sysdate-7,'D') + 7 
  group by a.event_name, alarm_time
) 
group by Object_name, event_number;

Answer 1

以下是使用此示例数据的一次尝试。下次请自己提供一些样本数据。

SQL> create table event_table (event_number,object_name,event_time,event_supplementary_info)
  2  as
  3  select 1, 'A', sysdate - 7, 'info' from dual union all
  4  select 1, 'B', sysdate - 7, 'info' from dual union all
  5  select 1, 'B', sysdate - 7, 'info' from dual union all
  6  select 2, 'A', sysdate - 6, 'info' from dual union all
  7  select 2, 'B', sysdate - 6, 'info' from dual union all
  8  select 2, 'B', sysdate - 6, 'info' from dual union all
  9  select 3, 'A', sysdate - 5, 'info' from dual union all
 10  select 3, 'A', sysdate - 5, 'info' from dual union all
 11  select 4, 'C', sysdate - 4, 'info' from dual union all
 12  select 4, 'C', sysdate - 4, 'info' from dual union all
 13  select 4, 'C', sysdate - 4, 'info' from dual union all
 14  select 4, 'C', sysdate - 4, 'info' from dual union all
 15  select 4, 'C', sysdate - 4, 'info' from dual union all
 16  select 4, 'D', sysdate - 4, 'info' from dual union all
 17  select 5, 'A', sysdate - 3, 'info' from dual union all
 18  select 6, 'D', sysdate - 2, 'info' from dual union all
 19  select 6, 'D', sysdate - 2, 'info' from dual union all
 20  select 7, 'A', sysdate - 1, 'info' from dual union all
 21  select 7, 'A', sysdate - 1, 'info' from dual union all
 22  select 7, 'A', sysdate - 1, 'info' from dual union all
 23  select 7, 'A', sysdate - 1, 'info' from dual
 24  /

Table created.

您的查询不起作用:有多余的右括号。如果我删除它们，它仍然不起作用，因为您正在将日期与varchar2的进行比较：

SQL> select object_name,
  2         event_number,
  3         count(*),
  4         event_supplementary_info
  5    from event_table
  6   where event_time between to_char(sysdate -7, 'YYYY-MM-DD')
  7                        and to_char(sysdate , 'YYYY-MM-DD')
  8  group by object_name, event_number, event_supplementary_info
  9  /

no rows selected

因此，我的基本查询是将日期与日期进行比较：

SQL> select object_name
  2       , event_number
  3       , count(*)
  4       , event_supplementary_info
  5    from event_table
  6   where event_time between sysdate -7 and sysdate
  7   group by object_name
  8       , event_number
  9       , event_supplementary_info
 10   order by object_name
 11       , event_number
 12  /

O EVENT_NUMBER   COUNT(*) EVEN
- ------------ ---------- ----
A            1          1 info
A            2          1 info
A            3          2 info
A            5          1 info
A            7          4 info
B            1          2 info
B            2          2 info
C            4          5 info
D            4          1 info
D            6          2 info

10 rows selected.

我解释了您的问题，即您希望记录每个object_name的事件总数。有了这个示例数据，您需要4个额外的记录，用于对象A、B、C和D。为此，我仅在object_name上添加了另一个分组集。为了清晰起见，我将trunc(event_time)包含在现有的分组集中。

SQL> select object_name
  2       , event_number
  3       , count(*)
  4       , event_supplementary_info
  5       , trunc(event_time)
  6    from event_table
  7   where event_time between sysdate -7 and sysdate
  8   group by grouping sets
  9         ( ( object_name
 10           , event_number
 11           , event_supplementary_info
 12           , trunc(event_time)
 13           )
 14         , ( object_name )
 15         )
 16   order by object_name
 17       , event_number
 18  /

O EVENT_NUMBER   COUNT(*) EVEN TRUNC(EVENT_TIME)
- ------------ ---------- ---- -------------------
A            1          1 info 15-08-2010 00:00:00
A            2          1 info 16-08-2010 00:00:00
A            3          2 info 17-08-2010 00:00:00
A            5          1 info 19-08-2010 00:00:00
A            7          4 info 21-08-2010 00:00:00
A                       9
B            1          2 info 15-08-2010 00:00:00
B            2          2 info 16-08-2010 00:00:00
B                       4
C            4          5 info 18-08-2010 00:00:00
C                       5
D            4          1 info 18-08-2010 00:00:00
D            6          2 info 20-08-2010 00:00:00
D                       3

14 rows selected.

编写这样的查询的简单方法是将分组集转换为汇总：

SQL> select object_name
  2       , event_number
  3       , count(*)
  4       , event_supplementary_info
  5       , trunc(event_time)
  6    from event_table
  7   where event_time between sysdate -7 and sysdate
  8   group by object_name
  9       , rollup ((event_number,event_supplementary_info,trunc(event_time)))
 10   order by object_name
 11       , event_number
 12  /

O EVENT_NUMBER   COUNT(*) EVEN TRUNC(EVENT_TIME)
- ------------ ---------- ---- -------------------
A            1          1 info 15-08-2010 00:00:00
A            2          1 info 16-08-2010 00:00:00
A            3          2 info 17-08-2010 00:00:00
A            5          1 info 19-08-2010 00:00:00
A            7          4 info 21-08-2010 00:00:00
A                       9
B            1          2 info 15-08-2010 00:00:00
B            2          2 info 16-08-2010 00:00:00
B                       4
C            4          5 info 18-08-2010 00:00:00
C                       5
D            4          1 info 18-08-2010 00:00:00
D            6          2 info 20-08-2010 00:00:00
D                       3

14 rows selected.

问候你，罗伯。

Answer 2

或许这就是你要找的：http://psoug.org/reference/rollup.html？