StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Great question, this was an interesting challenge!</p> <p>What you need is an ancillary table to store each time division (in this case, hours), then join to it where the status updates overlap. <code>LEAD()</code> can grab the next status entry to check when it was, and <code>GREATEST()</code> and <code>LEAST()</code> can figure out which time is applicable for the start/end of the status for each hour.</p> <p>Of course, this is much easier explained in an example. Here is the <code>HOURS</code> table needed:</p> <pre><code>SQL> CREATE TABLE hours (HOUR NUMBER(2), start_m date, end_m date); Table created. SQL> BEGIN 2 FOR i IN 0..23 LOOP 3 INSERT INTO hours VALUES(i, to_date(lpad(i, 2, '0')||':00:00', 'HH24:MI:SS') 4 , to_date(lpad(i, 2, '0')||':59:59', 'HH24:MI:SS')); 5 END loop; 6 COMMIT; 7 END; 8 / PL/SQL procedure successfully completed. </code></pre> <p>The following is just population of your test data from your question.</p> <pre><code>SQL> CREATE TABLE status_updates (NAME VARCHAR2(3), status_change_time DATE, status CHAR(1)); Table created. SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 18:52', 'DD-MON-RR HH24:MI:SS'), 'A'); 1 row created. SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 18:38', 'DD-MON-RR HH24:MI:SS'), 'A'); 1 row created. SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 18:33', 'DD-MON-RR HH24:MI:SS'), 'B'); 1 row created. SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 16:53', 'DD-MON-RR HH24:MI:SS'), 'A'); 1 row created. SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 16:47', 'DD-MON-RR HH24:MI:SS'), 'B'); 1 row created. SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 13:37', 'DD-MON-RR HH24:MI:SS'), 'A'); 1 row created. SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 13:33', 'DD-MON-RR HH24:MI:SS'), 'C'); 1 row created. SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 10:23', 'DD-MON-RR HH24:MI:SS'), 'C'); 1 row created. SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 10:17', 'DD-MON-RR HH24:MI:SS'), 'A'); 1 row created. SQL> commit; Commit complete. </code></pre> <p>Now here is the select statement to get the required percentages.</p> <pre><code>SELECT t.NAME, t.HOUR, t.status, sum(round((status_end_h-start_status_h)*24*100)) per_cent FROM ( SELECT A.NAME , A.status , A.status_change_time , A.next_change_time , b.HOUR , greatest(status_change_time, trunc(status_change_time)+(b.start_m-trunc(b.start_m))) start_status_h , least(next_change_time, trunc(next_change_time)+(b.end_m-trunc(b.end_m))) status_end_h FROM ( SELECT NAME , status , status_change_time , lead(status_change_time) OVER (ORDER BY NAME, status_change_time) next_change_time FROM status_updates ) A, hours b WHERE TO_CHAR(b.start_m, 'HH24:MI:SS') BETWEEN TO_CHAR(A.status_change_time, 'HH24:MI:SS') AND TO_CHAR(A.next_change_time, 'HH24:MI:SS') OR TO_CHAR(b.end_m, 'HH24:MI:SS') BETWEEN TO_CHAR(A.status_change_time, 'HH24:MI:SS') AND TO_CHAR(A.next_change_time, 'HH24:MI:SS') OR (TO_CHAR(A.status_change_time, 'HH24:MI:SS') BETWEEN TO_CHAR(b.start_m, 'HH24:MI:SS') AND TO_CHAR(b.end_m, 'HH24:MI:SS') AND TO_CHAR(A.next_change_time, 'HH24:MI:SS') BETWEEN TO_CHAR(b.start_m, 'HH24:MI:SS') AND TO_CHAR(b.end_m, 'HH24:MI:SS')) ) t GROUP BY t.NAME, t.HOUR, t.status ORDER BY t.HOUR; NAM HOUR S PER_CENT --- ---------- - ---------- foo 10 A 10 foo 10 C 62 foo 11 C 100 foo 12 C 100 foo 13 A 38 foo 13 C 62 foo 14 A 100 foo 15 A 100 foo 16 A 90 foo 16 B 10 foo 17 A 100 NAM HOUR S PER_CENT --- ---------- - ---------- foo 18 A 78 foo 18 B 8 13 rows selected. </code></pre>

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