一尘不染

Oracle事件计数查询

sql

我的SAMPLE表包含以下五列:

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

我想要一个每小时每小时(受给定日期范围限制)和五列的查询:

  1. 小时 YYYY-MM-DD HH24
  2. 该小时内采样的样本数
  3. 一小时内收到的样本数
  4. 在该小时内完成的样本数
  5. 该小时内授权的样品数量

请提供查询或至少要指出正确的方向。

重新开恩惠:
听听 300的信誉 为所述第一人掺入 罗布面包车Wijk
的回答(单访问样本)转换成图,其中我可以有效地查询由时间范围(start_date/end_datestart_date/num_days)。


阅读 103

收藏
2021-05-16

共1个答案

一尘不染

尝试:

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值的构造。

2021-05-16