StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POselect on value changing by percentage in oracle
text
Body
copied!<p>suppose I have a dataset like this (in an Oracle 11g db environment)</p> <pre><code>CHANGE_DATE VALUE ------------------ ---------- 03-NOV-13 06.56.01 3027.97 03-NOV-13 06.57.01 3030.59 03-NOV-13 06.58.01 3032.33 03-NOV-13 06.59.01 3047.41 03-NOV-13 07.00.02 3045.82 03-NOV-13 07.01.01 3046.63 03-NOV-13 07.02.01 3020.29 03-NOV-13 07.03.02 3019.38 03-NOV-13 07.04.01 3020.76 03-NOV-13 07.05.01 3008.53 </code></pre> <p>what I would be interested in is a select statement which only displays values on a sufficiently large change, e.g. 0.1%. In the above dataset the desired output would be </p> <pre><code>03-NOV-13 06.56.01 3027.97 03-NOV-13 06.58.01 3032.33 03-NOV-13 06.59.01 3047.41 03-NOV-13 07.04.01 3020.29 03-NOV-13 07.05.01 3008.53 </code></pre> <p>EDIT: to explain the aim: the first row is the first reference value. Any subsequent row value should be compared to this. If the change with respect to the reference values does not exceed x%, continue. If the value <strong>does</strong> exceed the threshold, select this row and keep this new value as the reference to compare the next rows to.</p> <p>I know how I can achieve something like this in case I am only flipping between integer values along the lines of what has been discussed here: <a href="https://stackoverflow.com/questions/11127461/select-rows-where-column-value-has-changed">Select rows where column value has changed</a></p> <p>I tried to implement something along those lines using: </p> <pre><code>with t as ( select to_date('03-NOV-13 06.56.01','dd/mm/yyyy hh24:mi:ss') change_date, 3027.97 value from dual union all select to_date('03-NOV-13 06.57.01','dd/mm/yyyy hh24:mi:ss'), 3030.59 from dual union all select to_date('03-NOV-13 06.58.01','dd/mm/yyyy hh24:mi:ss'), 3032.33 from dual union all select to_date('03-NOV-13 06.59.01','dd/mm/yyyy hh24:mi:ss'), 3047.41 from dual union all select to_date('03-NOV-13 07.00.02','dd/mm/yyyy hh24:mi:ss'), 3045.82 from dual union all select to_date('03-NOV-13 07.01.01','dd/mm/yyyy hh24:mi:ss'), 3046.63 from dual union all select to_date('03-NOV-13 07.02.01','dd/mm/yyyy hh24:mi:ss'), 3020.29 from dual union all select to_date('03-NOV-13 07.03.02','dd/mm/yyyy hh24:mi:ss'), 3019.38 from dual union all select to_date('03-NOV-13 07.04.01','dd/mm/yyyy hh24:mi:ss'), 3020.76 from dual union all select to_date('03-NOV-13 07.05.01','dd/mm/yyyy hh24:mi:ss'), 3008.53 from dual ) , x as ( select value, ROUND(value,-1) round_value, change_date, ROW_NUMBER() OVER (ORDER BY change_date) as rn from t order by change_date) select x.value, x.change_date from x join x y on x.rn = y.rn+1 and x.round_value <> y.round_value; </code></pre> <p>which gives </p> <pre><code>3047.41 03-NOV-13 3020.29 03-NOV-13 3008.53 03-NOV-13 </code></pre> <p>which is not too far off the mark but the comparison is always made just to previous value not the first not-suppressed value. Obviously this simply does a rounding and does not look for any percentage change. </p> <p>I also tried to play with lag like this</p> <pre><code>with t as ( select to_date('03-NOV-13 06.56.01','dd/mm/yyyy hh24:mi:ss') change_date, 3027.97 value from dual union all select to_date('03-NOV-13 06.57.01','dd/mm/yyyy hh24:mi:ss'), 3030.59 from dual union all select to_date('03-NOV-13 06.58.01','dd/mm/yyyy hh24:mi:ss'), 3032.33 from dual union all select to_date('03-NOV-13 06.59.01','dd/mm/yyyy hh24:mi:ss'), 3047.41 from dual union all select to_date('03-NOV-13 07.00.02','dd/mm/yyyy hh24:mi:ss'), 3045.82 from dual union all select to_date('03-NOV-13 07.01.01','dd/mm/yyyy hh24:mi:ss'), 3046.63 from dual union all select to_date('03-NOV-13 07.02.01','dd/mm/yyyy hh24:mi:ss'), 3020.29 from dual union all select to_date('03-NOV-13 07.03.02','dd/mm/yyyy hh24:mi:ss'), 3019.38 from dual union all select to_date('03-NOV-13 07.04.01','dd/mm/yyyy hh24:mi:ss'), 3020.76 from dual union all select to_date('03-NOV-13 07.05.01','dd/mm/yyyy hh24:mi:ss'), 3008.53 from dual ) select value, change_date, case when abs( lag(value,1,0) over(order by change_date) - value ) / value > 0.001 then value else lag(value,1,0) over(order by change_date) end start_of_group from t; </code></pre> <p>which results in</p> <pre><code> VALUE CHANGE_DA START_OF_GROUP ---------- --------- -------------- 3027.97 03-NOV-13 3027.97 3030.59 03-NOV-13 3027.97 3032.33 03-NOV-13 3030.59 3047.41 03-NOV-13 3047.41 3045.82 03-NOV-13 3047.41 3046.63 03-NOV-13 3045.82 3020.29 03-NOV-13 3020.29 3019.38 03-NOV-13 3020.29 3020.76 03-NOV-13 3019.38 3008.53 03-NOV-13 3008.53 </code></pre> <p>which also seems to be a step in the right direction but has the same problem that the comparison is not done to the 'start_of_group' column but the 'value' column</p> <p>I would appreciate any hints on how to achieve this. Please let me know if the question is sufficiently clear or if I should add any information. </p> <p>P.S. first time poster, hope I managed to post the question in a meaningful way</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