StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Tricky problem, but I think the following solution works as expect:</p> <pre><code>with data as ( select to_date('03-11-13 06.56.01','dd/mm/yyyy hh24:mi:ss') change_date, 3027.97 value from dual union all select to_date('03-11-13 06.57.01','dd/mm/yyyy hh24:mi:ss'), 3030.59 from dual union all select to_date('03-11-13 06.58.01','dd/mm/yyyy hh24:mi:ss'), 3032.33 from dual union all select to_date('03-11-13 06.59.01','dd/mm/yyyy hh24:mi:ss'), 3047.41 from dual union all select to_date('03-11-13 07.00.02','dd/mm/yyyy hh24:mi:ss'), 3045.82 from dual union all select to_date('03-11-13 07.01.01','dd/mm/yyyy hh24:mi:ss'), 3046.63 from dual union all select to_date('03-11-13 07.02.01','dd/mm/yyyy hh24:mi:ss'), 3020.29 from dual union all select to_date('03-11-13 07.03.02','dd/mm/yyyy hh24:mi:ss'), 3019.38 from dual union all select to_date('03-11-13 07.04.01','dd/mm/yyyy hh24:mi:ss'), 3020.76 from dual union all select to_date('03-11-13 07.05.01','dd/mm/yyyy hh24:mi:ss'), 3008.53 from dual ) SELECT change_date, value FROM data WHERE change_date IN ( SELECT MIN(change_date) FROM ( SELECT t.*, (SELECT MAX(change_date) FROM data WHERE TRUNC(change_date) = TRUNC(t.change_date) AND change_date < t.change_date AND ABS(value - t.value) / value > 0.001) AS prev FROM data t ) GROUP BY prev ) ORDER BY 1 ; </code></pre> <p>Firstly, for each row we find the maximal <code>change_date</code> that has a <code>value</code> that differs from currently processed row's <code>value</code> by more than 0.1%. Then, we select the minimal dates from that set grouped by the <code>prev</code> dates, and, finally, we select the corresponding values for those dates.</p> <p>Output:</p> <pre>CHANGE_DATE VALUE ---------------- ---------- 13/11/03 06:56 3027.97 13/11/03 06:58 3032.33 13/11/03 06:59 3047.41 13/11/03 07:02 3020.29 13/11/03 07:05 3008.53</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