Oracle 事件计数查询

Question

我的 SAMPLE 表具有以下五列：

sample_id (PK) (NUMBER)
sampled_on (DATE)
received_on (DATE)
completed_on (DATE)
authorized_on (DATE)

我想要一个每小时一行（受给定日期范围限制）和五列的查询：

1. 小时 YYYY-MM-DD HH24< /code>
2. 该小时内采样的样品数量
3. 该小时内收到的样品数量 该小时
4. 内完成的样品数量
5. 该小时内授权的样品数量

请提供查询或至少提供正确方向的一点。

以赏金重新开放：
  +300 声誉，是第一个将 Rob van Wijk 的答案（对示例的单次访问）合并到我可以按日期范围高效查询的视图中的人（start_date/end_date 或 start_date/num_days）。

Answer 1

这可能不是最漂亮或最优化的解决方案，但它似乎有效。说明：首先将所有日期转换为YYYY-MM-DD HH24格式，然后按日期+HH24收集采样/接收/完成/授权的数量，最后合并在一起。

with sample_hour as
    (select sample_id, 
            to_char(sampled_on, 'YYYY-MM-DD HH24') sampled_on,
            to_char(received_on, 'YYYY-MM-DD HH24') received_on,
            to_char(completed_on, 'YYYY-MM-DD HH24') completed_on,
            to_char(authorized_on, 'YYYY-MM-DD HH24') authorized_on
     from sample),
s as 
    (select sampled_on thedate, count(*) num_sampled 
     from sample_hour 
     group by sampled_on),
r as 
    (select received_on thedate, count(*) num_received 
     from sample_hour 
     group by received_on),
c as 
    (select completed_on thedate, count(*) num_completed 
     from sample_hour 
     group by completed_on),
a as 
    (select authorized_on thedate, count(*) num_authorized 
     from sample_hour 
     group by authorized_on)
select s.thedate, num_sampled, num_received, num_completed, num_authorized 
from s 
left join r on s.thedate = r.thedate
left join c on s.thedate = c.thedate
left join a on s.thedate = a.thedate
;

这假设表“样本”创建了如下内容：

create table sample
    (sample_id number not null primary key,
     sampled_on date,
     received_on date,
     completed_on date,
     authorized_on date);

Answer 2

这是一个例子。首先创建表并插入一些随机数据。

SQL> create table sample
  2  ( sample_id     number primary key
  3  , sampled_on    date
  4  , received_on   date
  5  , completed_on  date
  6  , authorized_on date
  7  )
  8  /

Tabel is aangemaakt.

SQL> insert into sample
  2   select level
  3        , trunc(sysdate) + dbms_random.value(0,2)
  4        , trunc(sysdate) + dbms_random.value(0,2)
  5        , trunc(sysdate) + dbms_random.value(0,2)
  6        , trunc(sysdate) + dbms_random.value(0,2)
  7     from dual
  8  connect by level <= 1000
  9  /

1000 rijen zijn aangemaakt.

然后引入给定日期范围的变量并填充它们。

SQL> var DATE_RANGE_START varchar2(10)
SQL> var DATE_RANGE_END varchar2(10)
SQL> exec :DATE_RANGE_START := '2009-10-23'

PL/SQL-procedure is geslaagd.

SQL> exec :DATE_RANGE_END := '2009-10-24'

PL/SQL-procedure is geslaagd.

首先，您必须生成给定日期范围内的所有小时。这可以确保，如果您有一小时没有日期，您仍然会拥有包含 4 个零的记录。实现在 all_hours 查询中。查询的其余部分（只有一个表访问示例表！）可以像这样非常简单。

SQL> with all_hours as
  2  ( select to_date(:DATE_RANGE_START,'yyyy-mm-dd') + numtodsinterval(level-1,'hour') hour
  3      from dual
  4   connect by level <=
  5           (  to_date(:DATE_RANGE_END,'yyyy-mm-dd')
  6            - to_date(:DATE_RANGE_START,'yyyy-mm-dd')
  7            + 1
  8           ) * 24
  9  )
 10  select h.hour
 11       , count(case when h.hour = trunc(s.sampled_on,'hh24') then 1 end) sampled#
 12       , count(case when h.hour = trunc(s.received_on,'hh24') then 1 end) received#
 13       , count(case when h.hour = trunc(s.completed_on,'hh24') then 1 end) completed#
 14       , count(case when h.hour = trunc(s.authorized_on,'hh24') then 1 end) authorized#
 15    from all_hours h
 16         cross join sample s
 17   group by h.hour
 18  /

HOUR                  SAMPLED#  RECEIVED# COMPLETED# AUTHORIZED#
------------------- ---------- ---------- ---------- -----------
23-10-2009 00:00:00         18         25         20          20
23-10-2009 01:00:00         26         24         16          13
23-10-2009 02:00:00         16         26         17          15
23-10-2009 03:00:00         19         18         27          13
23-10-2009 04:00:00         28         20         18          23
23-10-2009 05:00:00         17         13         19          21
23-10-2009 06:00:00         18         23         16          15
23-10-2009 07:00:00         19         24         14          22
23-10-2009 08:00:00         21         19         23          22
23-10-2009 09:00:00         25         20         23          24
23-10-2009 10:00:00         16         21         25          18
23-10-2009 11:00:00         21         29         21          18
23-10-2009 12:00:00         33         28         24          20
23-10-2009 13:00:00         24         19         15          15
23-10-2009 14:00:00         20         27         16          25
23-10-2009 15:00:00         15         25         27          13
23-10-2009 16:00:00         19         14         27          18
23-10-2009 17:00:00         22         22         15          27
23-10-2009 18:00:00         20         19         29          23
23-10-2009 19:00:00         20         18         17          23
23-10-2009 20:00:00         11         18         20          27
23-10-2009 21:00:00         13         25         24          19
23-10-2009 22:00:00         22         13         22          29
23-10-2009 23:00:00         20         20         19          24
24-10-2009 00:00:00         18         17         18          29
24-10-2009 01:00:00         23         30         26          21
24-10-2009 02:00:00         28         19         28          25
24-10-2009 03:00:00         21         21         11          23
24-10-2009 04:00:00         23         20         21          17
24-10-2009 05:00:00         24         16         23          23
24-10-2009 06:00:00         23         26         22          30
24-10-2009 07:00:00         25         26         18          12
24-10-2009 08:00:00         24         20         23          17
24-10-2009 09:00:00         18         26         15          19
24-10-2009 10:00:00         20         19         25          18
24-10-2009 11:00:00         19         27         17          20
24-10-2009 12:00:00         23         16         18          20
24-10-2009 13:00:00         15         15         22          19
24-10-2009 14:00:00         23         23         16          29
24-10-2009 15:00:00         18         31         32          28
24-10-2009 16:00:00         22         15         18          13
24-10-2009 17:00:00         25         17         20          26
24-10-2009 18:00:00         19         20         21          16
24-10-2009 19:00:00         22         13         28          29
24-10-2009 20:00:00         23         17         23          14
24-10-2009 21:00:00         18         18         21          22
24-10-2009 22:00:00         22         20         18          21
24-10-2009 23:00:00         21         18         22          22

48 rijen zijn geselecteerd.

希望这有帮助。

问候，
抢。

Answer 3

我会做 4 个这样的查询（每个日期一个）：

SELECT <date to hour>, count(*) FROM sample GROUP BY <date to hour>

然后将数据放在应用程序中。如果您确实想要单个查询，您可以在小时上加入各个查询。

Answer 4

试试这个...

WITH src_data AS
        ( SELECT sample_id
               , TRUNC( sampled_on, 'HH24' )   sampled_on
               , TRUNC( received_on, 'HH24' )   received_on
               , TRUNC( completed_on, 'HH24' )   completed_on
               , TRUNC( authorized_on, 'HH24' )   authorized_on
            FROM sample
        )
   , src_hours AS
        ( SELECT sampled_on   the_date 
            FROM src_data
           WHERE sampled_on IS NOT NULL
           UNION
          SELECT received_on   the_date
            FROM src_data
           WHERE received_on IS NOT NULL
           UNION
          SELECT completed_on   the_date
            FROM src_data
           WHERE completed_on IS NOT NULL
           UNION
          SELECT authorized_on   the_date
            FROM src_data
           WHERE authorized_on IS NOT NULL
        )
SELECT h.the_date
     , ( SELECT COUNT(*) 
           FROM src_data s
          WHERE s.sampled_on = h.the_date )   num_sampled_on
     , ( SELECT COUNT(*)
           FROM src_data r
          WHERE r.received_on = h.the_date )   num_received_on
     , ( SELECT COUNT(*)
           FROM src_data c
          WHERE c.completed_on = h.the_date )   num_completed_on
     , ( SELECT COUNT(*)
           FROM src_data a
          WHERE a.authorized_on = h.the_date )   num_authorized_on
  FROM src_hours h

Answer 5

也许像创建这个视图：

create view hours as
select hour, max(cnt_sample) cnt_sample, max(cnt_received) cnt_received, max(cnt_completed) cnt_completed, max(cnt_authorized) cnt_authorized
  from (
    select to_char(sampled_on   , 'yyyymmddhh24') hour, 
           count(sample_id) over (partition by to_char(sampled_on    ,'yyyymmddhh24')) cnt_sample,    
           0                                                                           cnt_received,
           0                                                                           cnt_completed, 
           0                                                                           cnt_authorized from sample union all
    select to_char(received_on  , 'yyyymmddhh24') hour, 
           0                                                                           cnt_sample, 
           count(sample_id) over (partition by to_char(received_on   ,'yyyymmddhh24')) cnt_received, 
           0                                                                           cnt_completed, 
           0                                                                           cnt_authorized from sample union all
    select to_char(completed_on , 'yyyymmddhh24') hour, 
           0                                                                           cnt_sample, 
           0                                                                           cnt_received, 
           count(sample_id) over (partition by to_char(completed_on  ,'yyyymmddhh24')) cnt_completed,
           0                                                                           cnt_authorized from sample union all
    select to_char(authorized_on, 'yyyymmddhh24') hour,
           0                                                                           cnt_sample, 
           0                                                                           cnt_received,
           0                                                                           cnt_completed, 
           count(sample_id) over (partition by to_char(authorized_on ,'yyyymmddhh24')) cnt_authorized from sample
  )
group by hour
;

然后从视图中选择：

select * from hours where hour >= '2001010102' and hour <= '2001010105'
order by hour;

Answer 6

我现在建议：

create view hours_ as 
    with four as (   
      select 1 as n from dual union all    
      select 2 as n from dual union all    
      select 3 as n from dual union all    
      select 4 as n from dual ) 
 select   
      case when four.n = 1 then trunc(sampled_on   , 'hh24')    
           when four.n = 2 then trunc(received_on  , 'hh24')
           when four.n = 3 then trunc(completed_on , 'hh24')
           when four.n = 4 then trunc(authorized_on, 'hh24')   
       end                                                     hour_, 
      sum (   case when four.n = 1 then 1 
               else                     0
               end )                                           sample_,   
      sum (   case when four.n = 2 then 1 
               else                     0
               end )                                           receive_,   
      sum (   case when four.n = 3 then 1 
               else                     0
               end )                                           complete_,   
      sum (   case when four.n = 4 then 1 
               else                     0
               end )                                           authorize_ 
from   
    four cross join sample 
group by    
      case when four.n = 1 then trunc(sampled_on   , 'hh24')   
           when four.n = 2 then trunc(received_on  , 'hh24')
           when four.n = 3 then trunc(completed_on , 'hh24')
           when four.n = 4 then trunc(authorized_on, 'hh24')   
    end ;

为了查看视图是否确实只访问一次：

explain plan for select * from hours_ 
where hour_ between sysdate -1 and sysdate;

select * from table (dbms_xplan.display);

这会导致：

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |    61 |    16   (7)| 00:00:01 |
|   1 |  VIEW                 | HOURS_ |     1 |    61 |    16   (7)| 00:00:01 |
|   2 |   HASH GROUP BY       |        |     1 |    39 |    16   (7)| 00:00:01 |
|*  3 |    FILTER             |        |       |       |            |          |
|   4 |     NESTED LOOPS      |        |     1 |    39 |    15   (0)| 00:00:01 |
|   5 |      VIEW             |        |     4 |    12 |     8   (0)| 00:00:01 |
|   6 |       UNION-ALL       |        |       |       |            |          |
|   7 |        FAST DUAL      |        |     1 |       |     2   (0)| 00:00:01 |
|   8 |        FAST DUAL      |        |     1 |       |     2   (0)| 00:00:01 |
|   9 |        FAST DUAL      |        |     1 |       |     2   (0)| 00:00:01 |
|  10 |        FAST DUAL      |        |     1 |       |     2   (0)| 00:00:01 |
|* 11 |      TABLE ACCESS FULL| SAMPLE |     1 |    36 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Answer 7

这就是我的想法，但我不确定它对于视图来说是否足够最佳。

select 
    the_date,
    sum(decode(the_type,'S',the_count,0)) samples,
    sum(decode(the_type,'R',the_count,0)) receipts,
    sum(decode(the_type,'C',the_count,0)) completions,
    sum(decode(the_type,'A',the_count,0)) authorizations
from(
    select 
        trunc(sampled_on,'HH24') the_date,
        'S' the_type,
        count(1) the_count
    FROM sample
    group by trunc(sampled_on,'HH24')
    union all 
    select 
        trunc(received_on,'HH24'),
        'R',
        count(1)
    FROM sample
    group by trunc(received_on,'HH24')
    union all
    select 
        trunc(completed_on,'HH24'),
        'C',
        count(1)
    FROM sample
    group by trunc(completed_on,'HH24')
    union all
    select 
        trunc(authorized_on,'HH24'),
        'A',
        count(1)
    FROM sample
    group by trunc(authorized_on,'HH24')
)
group by the_date

然后，要查询，您可以使用正常的日期结构进行查询：

select * from magic_view where the_date > sysdate-1;

编辑

好的，所以我创建了一个示例表并做了一些指标：

create table sample ( 
  sample_id     number primary key, 
  sampled_on    date,
  received_on   date,
  completed_on  date,
  authorized_on date
);

insert into sample (
  select 
    level,
    trunc(sysdate) + dbms_random.value(0,2),
    trunc(sysdate) + dbms_random.value(0,2),
    trunc(sysdate) + dbms_random.value(0,2),
    trunc(sysdate) + dbms_random.value(0,2),
  from dual
  connect by level <= 1000
);

解释计划是：

---------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |  4000 |    97K|    25  (20)|
|   1 |  HASH GROUP BY        |        |  4000 |    97K|    25  (20)|
|   2 |   VIEW                |        |  4000 |    97K|    24  (17)|
|   3 |    UNION-ALL          |        |       |       |            |
|   4 |     HASH GROUP BY     |        |  1000 |  9000 |     6  (17)|
|   5 |      TABLE ACCESS FULL| SAMPLE |  1000 |  9000 |     5   (0)|
|   6 |     HASH GROUP BY     |        |  1000 |  9000 |     6  (17)|
|   7 |      TABLE ACCESS FULL| SAMPLE |  1000 |  9000 |     5   (0)|
|   8 |     HASH GROUP BY     |        |  1000 |  9000 |     6  (17)|
|   9 |      TABLE ACCESS FULL| SAMPLE |  1000 |  9000 |     5   (0)|
|  10 |     HASH GROUP BY     |        |  1000 |  9000 |     6  (17)|
|  11 |      TABLE ACCESS FULL| SAMPLE |  1000 |  9000 |     5   (0)|
---------------------------------------------------------------------

在我的机器上，过去 24 小时针对此视图的查询完成23 毫秒。不错，但只有 1,000 行。在考虑 4 个单独的查询之前，您需要对各个解决方案进行性能分析。

Answer 8

与 René Nyffenegger 的想法类似。按每种类型的日期字段进行筛选，然后合并计数。

请注意，不可能在一个 Select 中执行此查询，因为您需要对每个日期字段进行分组和排序，如果不拆分为单独的子查询，这是不可能的。

对于此示例，我已将日期范围编码为“2009-11-04”到“2009-11-04 23:59:59”：

SELECT
    DateHour,
    SUM(sampled) total_sampled,
    SUM(received) total_received,
    SUM(completed) total_completed,
    SUM(authorized) total_authorized
FROM
    (SELECT
        TO_CHAR(CREATED_DATE, 'YYYY-MM-DD HH24') DateHour,
        1 sampled,
        0 received,
        0 completed,
        0 authorized
    FROM
        SAMPLE
    WHERE
        sampled_on >= TO_DATE('2009-11-04', 'YYYY-MM-DD')
        AND sampled_on <= TO_DATE('2009-11-04 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
    UNION ALL
    SELECT
        TO_CHAR(CREATED_DATE, 'YYYY-MM-DD HH24') DateHour,
        0 sampled,
        1 received,
        0 completed,
        0 authorized
    FROM
        SAMPLE
    WHERE
        received_on >= TO_DATE('2009-11-04', 'YYYY-MM-DD')
        AND received_on <= TO_DATE('2009-11-04 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
    UNION ALL
    SELECT
        TO_CHAR(CREATED_DATE, 'YYYY-MM-DD HH24') DateHour,
        0 sampled,
        0 received,
        1 completed,
        0 authorized
    FROM
        SAMPLE
    WHERE
        completed_on >= TO_DATE('2009-11-04', 'YYYY-MM-DD')
        AND completed_on <= TO_DATE('2009-11-04 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
    UNION ALL
    SELECT
        TO_CHAR(CREATED_DATE, 'YYYY-MM-DD HH24') DateHour,
        0 sampled,
        0 received,
        0 completed,
        1 authorized
    FROM
        SAMPLE
    WHERE
        authorized_on >= TO_DATE('2009-11-04', 'YYYY-MM-DD')
        AND authorized_on <= TO_DATE('2009-11-04 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
GROUP BY
    DateHour
ORDER BY
    DateHour

Answer 9

尝试：

CREATE OR REPLACE VIEW my_view AS
WITH date_bookends AS (
  SELECT LEAST(MIN(t.sampled_on), MIN(t.received_on), MIN(t.completed_on), MIN(t.authorized_on)) 'min_date'
         GREATEST(MAX(t.sampled_on), MAX(t.received_on), MAX(t.completed_on), MAX(t.authorized_on)) 'max_date'
    FROM SAMPLE t),
    all_hours AS (
  SELECT t.min_date + numtodsinterval(LEVEL - 1,'hour') date_by_hour
    FROM date_bookends t
CONNECT BY LEVEL <= ( t.max_date - t.min_date + 1) * 24)
SELECT h.date_by_hour,
       COUNT(CASE WHEN h.hour = TRUNC(s.sampled_on,'hh24') THEN 1 END) sampled#
       COUNT(CASE WHEN h.hour = TRUNC(s.received_on,'hh24') THEN 1 END) received#
       COUNT(CASE WHEN h.hour = TRUNC(s.completed_on,'hh24') THEN 1 END) completed#
       COUNT(CASE WHEN h.hour = TRUNC(s.authorized_on,'hh24') THEN 1 END) authorized#
  FROM all_hours h
CROSS JOIN sample s
  GROUP BY h.hour

不使用子查询分解：

CREATE OR REPLACE VIEW my_view AS
SELECT h.date_by_hour,
       COUNT(CASE WHEN h.hour = TRUNC(s.sampled_on,'hh24') THEN 1 END) sampled#
       COUNT(CASE WHEN h.hour = TRUNC(s.received_on,'hh24') THEN 1 END) received#
       COUNT(CASE WHEN h.hour = TRUNC(s.completed_on,'hh24') THEN 1 END) completed#
       COUNT(CASE WHEN h.hour = TRUNC(s.authorized_on,'hh24') THEN 1 END) authorized#
  FROM (SELECT t.min_date + numtodsinterval(LEVEL - 1,'hour') date_by_hour
         FROM (SELECT LEAST(MIN(t.sampled_on), MIN(t.received_on), MIN(t.completed_on), MIN(t.authorized_on)) 'min_date'
                            GREATEST(MAX(t.sampled_on), MAX(t.received_on), MAX(t.completed_on), MAX(t.authorized_on)) 'max_date'
                       FROM SAMPLE t) t
CONNECT BY LEVEL <= ( t.max_date - t.min_date + 1) * 24) h
CROSS JOIN sample s
GROUP BY h.hour

查询访问 SAMPLES 表两次 - 第一次获取最早的数据用于构建 date_by_hour 值的最新日期。