使用ORACLE中的LAG函数更新前几天的计数 [英] Update previous days count using LAG function in ORACLE

问题描述

我有一个表，其中列为产品，txn_date和no_of_txns.最近在该列中添加了另外一列prev_day_txn.数据如下:

I have a table with columns as product, txn_date and no_of_txns. One more column prev_day_txn added recently to the column. Data is something like below:

Product   TXN_DATE  NO_OF_TXNS 

AA         1-JAN-19      100
AA         2-JAN-19      180
AA         3-JAN-19      290

添加新列后，应使用前一天的数据填充该列中的数据.输出应如下所示:

With the new column added, data in the column should be populated with previous day's data. Output should be something like below:

Product       TXN_DATE     NO_OF_TXNS  PREV_DAY_TXN

    AA         1-JAN-19      100          0
    AA         2-JAN-19      180         100
    AA         3-JAN-19      290         180

我正在尝试使用滞后函数更新数据，但出现错误SQL Error: ORA-30483: window functions are not allowed here在select子句中，相同的查询工作正常.这是我尝试过的:

I am trying to update the data using lag function, but getting error SQL Error: ORA-30483: window functions are not allowed hereSame query is working fine in select clause. This is what I tried:

update PRD_DTLS set PREV_DAY_TXN = LAG(NO_OF_TXNS, 1, 0) OVER (ORDER BY PRODUCT, TXN_DATE);

有人可以帮助我进行查询.谢谢.

Can someone help me with the query. Thanks.

推荐答案

您可以使用MERGE:

You can use MERGE:

MERGE INTO PRD_DTLS A USING 
(
  SELECT
      PRODUCT,
      TXN_DATE,
      LAG(NO_OF_TXNS) OVER(
          PARTITION BY PRODUCT
          ORDER BY
              TXN_DATE
      ) AS PREV_TXN
  FROM
      PRD_DTLS 
)
B ON ( A.PRODUCT = B.PRODUCT
       AND A.TXN_DATE = B.TXN_DATE )
WHEN MATCHED THEN UPDATE SET A.PREV_DAY_TXN = CASE
    WHEN B.PREV_TXN IS NOT NULL THEN B.PREV_TXN
    ELSE 0
END;

干杯！

这篇关于使用ORACLE中的LAG函数更新前几天的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！