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