1.我们从上游系统多次接收每日价格数据

1.管理员可以直接批准或更新价格和批准
直接批准-无版本增量

更新和批准

1.管理员可以纠正已经批准的数据的价格（一年期的审计目的或理货价格）

我的样本数据

预期输出
第15

第十六届

我们需要根据业务日期提取最新版本。我们正在使用下面的查询来提取数据，它按预期工作，但需要更多的时间。我需要帮助来提高性能
我正在添加样本数据和我的表包含至少数百万的记录，即13个月的记录，我需要拉每天的数据，这是接近2万条记录。
请分享更好的方式来填充相同的，而不是我的方法
请在这里找到查询执行-http：//sqlfiddle.com/#！4/757c2/13
请避免在同一个fiddle页面中更新查询
Thanks in advance

WITH LATEST_ITEMS AS (
  SELECT ORIGINAL.ITEM_ID, ORIGINAL.ITEM_SUB_ID, ORIGINAL.BUSINESS_DT,ORIGINAL.REPORT_DT,
  ORIGINAL.VERSION, ORIGINAL.STATE, ORIGINAL.PRICE FROM ITEM_PRICE ORIGINAL
  JOIN (
    SELECT ITEM_ID, ITEM_SUB_ID, BUSINESS_DT, MAX(VERSION) MAX_VERSION FROM ITEM_PRICE 
    WHERE STATE IN ('RECEIVED', 'APPROVED') 
    GROUP BY ITEM_ID, ITEM_SUB_ID, BUSINESS_DT) 
      MAX_VERSION
  ON NVL(MAX_VERSION.ITEM_SUB_ID,0) = NVL(ORIGINAL.ITEM_SUB_ID,0)
  AND MAX_VERSION.ITEM_ID = ORIGINAL.ITEM_ID
  AND TRUNC(MAX_VERSION.BUSINESS_DT) = TRUNC(ORIGINAL.BUSINESS_DT)
  AND TRUNC(MAX_VERSION.MAX_VERSION) = TRUNC(ORIGINAL.VERSION)
),
MAX_WITH_CORRECTED AS (
  SELECT ORIGINAL.ITEM_ID, ORIGINAL.ITEM_SUB_ID, ORIGINAL.BUSINESS_DT,ORIGINAL.REPORT_DT,
  ORIGINAL.VERSION, ORIGINAL.STATE, ORIGINAL.PRICE FROM ITEM_PRICE ORIGINAL
  JOIN (
    SELECT ITEM_ID, ITEM_SUB_ID, BUSINESS_DT, MAX(VERSION) MAX_VERSION FROM ITEM_PRICE 
    GROUP BY ITEM_ID, ITEM_SUB_ID, BUSINESS_DT) 
      MAX_VERSION
  ON NVL(MAX_VERSION.ITEM_SUB_ID,0) = NVL(ORIGINAL.ITEM_SUB_ID,0)
  AND MAX_VERSION.ITEM_ID = ORIGINAL.ITEM_ID
  AND TRUNC(MAX_VERSION.BUSINESS_DT) = TRUNC(ORIGINAL.BUSINESS_DT)
  AND TRUNC(MAX_VERSION.MAX_VERSION) = TRUNC(ORIGINAL.VERSION)
)

SELECT MAX_WITH_CORRECTED.* FROM MAX_WITH_CORRECTED INNER JOIN LATEST_ITEMS 
ON NVL(MAX_WITH_CORRECTED.ITEM_SUB_ID,0) = NVL(LATEST_ITEMS.ITEM_SUB_ID,0)
  AND MAX_WITH_CORRECTED.ITEM_ID = LATEST_ITEMS.ITEM_ID
  AND TRUNC(MAX_WITH_CORRECTED.BUSINESS_DT) = TRUNC(LATEST_ITEMS.BUSINESS_DT)
  AND TRUNC(MAX_WITH_CORRECTED.BUSINESS_DT) = '15-AUG-2023'
  ;

样本数据;

CREATE TABLE ITEM_PRICE
       (ITEM_ID NUMBER(10), ITEM_SUB_ID NUMBER(10), BUSINESS_DT DATE, REPORT_DT DATE,
        VERSION NUMBER(3), STATE VARCHAR2(10), PRICE NUMBER(7,2)) ;

INSERT INTO ITEM_PRICE VALUES ( 100, 1000, '15-AUG-2023', '15-AUG-2023', 1, 'RECEIVED', 10 );
INSERT INTO ITEM_PRICE VALUES ( 100, 1000, '15-AUG-2023', '15-AUG-2023', 2, 'APPROVED', 20 );
INSERT INTO ITEM_PRICE VALUES ( 100, 1001, '15-AUG-2023', '15-AUG-2023', 1, 'RECEIVED', 10 );
INSERT INTO ITEM_PRICE VALUES ( 100, 1001, '15-AUG-2023', '15-AUG-2023', 2, 'APPROVED', 20 );
INSERT INTO ITEM_PRICE VALUES ( 101, NULL, '15-AUG-2023', '15-AUG-2023', 1, 'APPROVED', 10 );

INSERT INTO ITEM_PRICE VALUES ( 100, 1000, '16-AUG-2023', '16-AUG-2023', 1, 'RECEIVED', 10 );
INSERT INTO ITEM_PRICE VALUES ( 100, 1000, '16-AUG-2023', '16-AUG-2023', 2, 'RECEIVED', 20 );
INSERT INTO ITEM_PRICE VALUES ( 100, 1000, '16-AUG-2023', '16-AUG-2023', 3, 'APPROVED', 30 );
INSERT INTO ITEM_PRICE VALUES ( 100, 1000, '16-AUG-2023', '16-AUG-2023', 4, 'CORRECTED', 50 );
INSERT INTO ITEM_PRICE VALUES ( 100, 1001, '16-AUG-2023', '16-AUG-2023', 1, 'APPROVED', 10 );
INSERT INTO ITEM_PRICE VALUES ( 101, NULL, '16-AUG-2023', '16-AUG-2023', 1, 'RECEIVED', 10 );

INSERT INTO ITEM_PRICE VALUES ( 100, 1000, '15-AUG-2023', '16-AUG-2023', 3, 'CORRECTED', 15 );
INSERT INTO ITEM_PRICE VALUES ( 100, 1000, '15-AUG-2023', '16-AUG-2023', 4, 'CORRECTED', 18 );
INSERT INTO ITEM_PRICE VALUES ( 101, NULL, '15-AUG-2023', '16-AUG-2023', 2, 'CORRECTED', 20 );

如果您需要更多详细信息，请添加评论。