我的SAMPLE表包含以下五列:
SAMPLE
sample_id (PK) (NUMBER) sampled_on (DATE) received_on (DATE) completed_on (DATE) authorized_on (DATE)
我想要一个每小时每小时(受给定日期范围限制)和五列的查询:
YYYY-MM-DD HH24
请提供查询或至少要指出正确的方向。
重新开恩惠: 听听 300的信誉 为所述第一人掺入 罗布面包车Wijk 的回答(单访问样本)转换成图,其中我可以有效地查询由时间范围(start_date/end_date或start_date/num_days)。
start_date/end_date
start_date/num_days
尝试:
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值的构造。
date_by_hour