Oracle PRICING表LAST_RETAIL字段更新：基于历史记录赋值

表DDL定义 #

CREATE TABLE PRICING (ITEM          VARCHAR2(80) NOT NULL,
                      LOCATION      NUMBER(10)   NOT NULL,
                      PRICE_DATE    DATE         NOT NULL,
                      PRICE_TYPE    VARCHAR2(2)  NOT NULL,
                      RETAIL_VALUE  NUMBER(20,4),
                      LAST_RETAIL   NUMBER(20,4) );

测试数据插入语句 #

Insert into PRICING  values ('30888273',77,to_date('20-SEP-20','DD-MON-RR'),'0',2,NULL);
Insert into PRICING  values ('30888273',77,to_date('03-MAR-23','DD-MON-RR'),'8',1.4,NULL);
Insert into PRICING  values ('30888273',77,to_date('06-MAR-23','DD-MON-RR'),'4',3,NULL);
Insert into PRICING  values ('30888273',77,to_date('04-APR-23','DD-MON-RR'),'8',1.4,NULL);
Insert into PRICING  values ('30888273',77,to_date('10-APR-23','DD-MON-RR'),'4',4,NULL);
Insert into PRICING  values ('30888273',77,to_date('02-MAY-23','DD-MON-RR'),'8',1.4,NULL);
Insert into PRICING  values ('30888273',77,to_date('08-MAY-23','DD-MON-RR'),'4',5,NULL);
Insert into PRICING  values ('30888273',77,to_date('30-MAY-23','DD-MON-RR'),'8',1.4,NULL);
Insert into PRICING  values ('30888273',77,to_date('05-JUN-23','DD-MON-RR'),'4',6,NULL);
Insert into PRICING  values ('30888273',77,to_date('04-JUL-23','DD-MON-RR'),'8',1.4,NULL);
Insert into PRICING  values ('30888273',77,to_date('05-JUL-23','DD-MON-RR'),'4',7,NULL);
commit;

查看表数据的查询语句 #

select * from PRICING order by price_date;

需求说明 #

将所有PRICE_TYPE为'8'的记录的LAST_RETAIL字段，赋值为该记录PRICE_DATE之前最近的、PRICE_TYPE为'0'或'4'的记录的RETAIL_VALUE，优先使用UPDATE语句实现。

解决方案 #

方法1：使用UPDATE语句（关联子查询） #

UPDATE PRICING p8
SET LAST_RETAIL = (
    SELECT RETAIL_VALUE
    FROM (
        SELECT RETAIL_VALUE,
               ROW_NUMBER() OVER (ORDER BY PRICE_DATE DESC) AS rn
        FROM PRICING
        WHERE ITEM = p8.ITEM
          AND LOCATION = p8.LOCATION
          AND PRICE_DATE < p8.PRICE_DATE
          AND PRICE_TYPE IN ('0', '4')
    )
    WHERE rn = 1
)
WHERE PRICE_TYPE = '8';

COMMIT;

逻辑说明：针对每条PRICE_TYPE='8'的记录，子查询筛选出同ITEM、同LOCATION且日期更早的PRICE_TYPE='0'或'4'的记录，按日期倒序排序后取第一条（即最近的一条）的RETAIL_VALUE，赋值给当前记录的LAST_RETAIL。

方法2：使用MERGE语句（Oracle高效更新方式） #

如果数据量较大，MERGE的性能更优：

MERGE INTO PRICING p
USING (
    SELECT ITEM, LOCATION, PRICE_DATE,
           LAST_VALUE(CASE WHEN PRICE_TYPE IN ('0','4') THEN RETAIL_VALUE END IGNORE NULLS) 
               OVER (PARTITION BY ITEM, LOCATION ORDER BY PRICE_DATE) AS LAST_RETAIL_VAL
    FROM PRICING
) src
ON (p.ITEM = src.ITEM 
    AND p.LOCATION = src.LOCATION 
    AND p.PRICE_DATE = src.PRICE_DATE 
    AND p.PRICE_TYPE = '8')
WHEN MATCHED THEN UPDATE 
    SET p.LAST_RETAIL = src.LAST_RETAIL_VAL;

COMMIT;

逻辑说明：通过窗口函数LAST_VALUE，按ITEM和LOCATION分区、日期排序，忽略NULL值的情况下，取每条记录之前最近的符合PRICE_TYPE='0'或'4'的RETAIL_VALUE，再匹配PRICE_TYPE='8'的记录进行更新。

内容的提问来源于stack exchange，提问作者cai tools