sql - 选择在oracle中按百分比变化的值

Question

假设我有这样一个数据集(在 Oracle 11g 数据库环境中)

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

我感兴趣的是一个 select 语句，它只显示足够大的变化的值，例如0.1%。在上面的数据集中，所需的输出将是

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

编辑:解释目的:第一行是第一个引用值。任何后续行值都应与此进行比较。如果相对于引用值的变化不超过 x%，则继续。如果值确实超过阈值，请选择此行并保留此新值作为引用以与下一行进行比较。

如果我只是按照此处讨论的内容在整数值之间翻转，我知道如何实现这样的目标:Select rows where column value has changed

我尝试使用以下方法实现一些东西:

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; 

给出

3047.41 03-NOV-13
3020.29 03-NOV-13
3008.53 03-NOV-13

这并不太离谱，但比较总是只与先前的值进行比较，而不是第一个未抑制的值。显然，这只是进行了四舍五入，并没有寻找任何百分比变化。

我也试过这样玩延迟

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;

结果

 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

这似乎也是朝着正确方向迈出的一步，但存在同样的问题，即比较不是针对“start_of_group”列而是“值”列

对于如何实现这一目标的任何提示，我将不胜感激。如果问题足够清楚或者我是否应该添加任何信息，请告诉我。

附言第一次发布，希望我能以有意义的方式发布问题

Answer 1

棘手的问题，但我认为以下解决方案按预期工作:

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
;

首先，对于每一行，我们找到最大的 change_date，它的 value 与当前处理的行的 value 相差超过 0.1% .然后，我们从按 prev 日期分组的集合中选择最小日期，最后，我们为这些日期选择相应的值。

输出:

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