Oracle事件计数查询

尝试：

CREATE OR REPLACe VIEW my_view ASWITH 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 tConNECT 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 hCROSS JOIN sample s  GROUP BY h.hour

不使用子查询分解：

CREATE OR REPLACe VIEW my_view ASSELECT 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) tConNECT BY LEVEL <= ( t.max_date - t.min_date + 1) * 24) hCROSS JOIN sample sGROUP BY h.hour

该查询两次访问SAMPLES表-第一次获取最早和最新的日期来构造

date_by_hour