sql - 如何比较表中的行并在Oracle中保留行中的最高值

Question

我正在寻找一个查询，它将当前行值与前一行值进行比较，如果当前和前一个之间的差异百分比小于 10%，则保留前一个值。我确信使用 oracle 滞后函数可以实现这一点，但我无法找到确切的解决方案。我试过下面的查询，但它没有保留 future 行的先前值。对此的任何帮助将不胜感激。

我使用下面的查询来获取结果，但它没有解决我的问题。

select /*+ parallel(64) */ a, b, c, datevalue, pricevalue, 
        lag(pricevalue,1,0) over (partition by a, b, c order by a, b, c, datevalue) as prev_pricevalue,
        (pricevalue - lag(pricevalue,1,0) over (partition by a, b, c order by a, b, c, datevalue))/pricevalue as diff,
        case 
            when (pricevalue - lag(pricevalue,1,0) over (partition by a, b, c order by a, b, c, datevalue))/pricevalue
                  < 0.1 then lag(pricevalue,1,0) over (partition by a, b, c order by a, b, c, datevalue)
            else pricevalue
            end new_pricevalue
      from table1
      where datevalue between '18-MAY-2019' and '31-MAY-2019';

我有如下数据。列名称为 A、B、C、DATE 和 VALUE

A               B       C       DATE        VALUE
16587EA_1005    RETAIL  7207    18/05/2019  7.04
16587EA_1005    RETAIL  7207    19/05/2019  7.04
16587EA_1005    RETAIL  7207    20/05/2019  7.04
16587EA_1005    RETAIL  7207    21/05/2019  7.04
16587EA_1005    RETAIL  7207    22/05/2019  7.04
16587EA_1005    RETAIL  7207    23/05/2019  7
16587EA_1005    RETAIL  7207    24/05/2019  7
16587EA_1005    RETAIL  7207    25/05/2019  7
16587EA_1005    RETAIL  7207    26/05/2019  7
16587EA_1005    RETAIL  7207    27/05/2019  7
16587EA_1005    RETAIL  7207    28/05/2019  7
16587EA_1005    RETAIL  7207    29/05/2019  8
16587EA_1005    RETAIL  7207    30/05/2019  8
16587EA_1005    RETAIL  7207    31/05/2019  8
16587EA_1005    RETAIL  7207    01/06/2019  8.05
16587EA_1005    RETAIL  7207    02/06/2019  8.05
16587EA_1005    RETAIL  7207    03/06/2019  8.05

而且，我想要如下输出。

A               B       C       DATE        VALUE
16587EA_1005    RETAIL  7207    18/05/2019  7.04
16587EA_1005    RETAIL  7207    19/05/2019  7.04
16587EA_1005    RETAIL  7207    20/05/2019  7.04
16587EA_1005    RETAIL  7207    21/05/2019  7.04
16587EA_1005    RETAIL  7207    22/05/2019  7.04
16587EA_1005    RETAIL  7207    23/05/2019  7.04
16587EA_1005    RETAIL  7207    24/05/2019  7.04
16587EA_1005    RETAIL  7207    25/05/2019  7.04
16587EA_1005    RETAIL  7207    26/05/2019  7.04
16587EA_1005    RETAIL  7207    27/05/2019  7.04
16587EA_1005    RETAIL  7207    28/05/2019  7.04
16587EA_1005    RETAIL  7207    29/05/2019  8
16587EA_1005    RETAIL  7207    30/05/2019  8
16587EA_1005    RETAIL  7207    31/05/2019  8
16587EA_1005    RETAIL  7207    01/06/2019  8
16587EA_1005    RETAIL  7207    02/06/2019  8
16587EA_1005    RETAIL  7207    03/06/2019  8

此致
MMR

Answer 1

不完全是一种简洁的方法，但递归 CTE 可以做到这一点。

WITH CTE AS
(
  -- adding a rank and rownum
  SELECT t.*
  , DENSE_RANK() OVER (ORDER BY a, b, c) AS rnk
  , ROW_NUMBER() OVER (PARTITION BY a, b, c ORDER BY datevalue) rn
  FROM table1 t
),
RCTE (rnk, rn, a, b, c, datevalue, pricevalue) AS
(
  -- seeding the recursion
  SELECT rnk, rn, a, b, c, datevalue, pricevalue
  FROM CTE
  WHERE rn = 1

  UNION ALL

  -- loop through the records for each rank
  SELECT c.rnk, c.rn, c.a, c.b, c.c, c.datevalue,
  CASE 
  WHEN ABS(r.pricevalue - c.pricevalue) / c.pricevalue < 0.1
  THEN r.pricevalue
  ELSE c.pricevalue
  END
  FROM RCTE r
  JOIN CTE c
    ON c.rnk = r.rnk
   AND c.rn = r.rn + 1
)
SELECT * 
FROM RCTE
ORDER BY rnk, rn;

返回:

RNK | RN | A  | B  | C  | DATEVALUE | PRICEVALUE--: | -: | :- | :- | :- | :-------- | ---------:  1 |  1 | a  | b  | c  | 18-MAY-19 |       7.04  1 |  2 | a  | b  | c  | 19-MAY-19 |       7.04  1 |  3 | a  | b  | c  | 20-MAY-19 |       7.04  1 |  4 | a  | b  | c  | 21-MAY-19 |       7.04  1 |  5 | a  | b  | c  | 22-MAY-19 |       7.04  1 |  6 | a  | b  | c  | 23-MAY-19 |       7.04  1 |  7 | a  | b  | c  | 24-MAY-19 |       7.04  1 |  8 | a  | b  | c  | 25-MAY-19 |       7.04  1 |  9 | a  | b  | c  | 26-MAY-19 |       7.04  1 | 10 | a  | b  | c  | 27-MAY-19 |       7.04  1 | 11 | a  | b  | c  | 28-MAY-19 |       7.04  1 | 12 | a  | b  | c  | 29-MAY-19 |          8  1 | 13 | a  | b  | c  | 30-MAY-19 |          8  1 | 14 | a  | b  | c  | 31-MAY-19 |          8  1 | 15 | a  | b  | c  | 01-JUN-19 |          8  1 | 16 | a  | b  | c  | 02-JUN-19 |          8

db<>fiddle here 上的测试