StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Try:</p> <pre><code>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 </code></pre> <p>Without using Subquery Factoring:</p> <pre><code>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 </code></pre> <p>The query accesses the SAMPLES table twice - the first time to get the earliest & latest date to frame the construction of the <code>date_by_hour</code> value.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload