Oracle统计历史月度厂商OPEN条目及查询数据变更记录的方法

统计逻辑说明 #

你需要的月末OPEN状态判断规则非常明确，只要满足以下两个条件的条目，即为对应月末有效OPEN条目：

• 入库日期receipt_date ≤ 当月最后一天
• 售出日期sold_date为空，或sold_date > 当月最后一天

该逻辑天然支持后续修改日期后的归属统计：哪怕用户在次月修改了两个日期字段，统计时直接用修改后的最新值判断对应月末的状态即可，完全符合你提到的“修改后数据归属对应月份”的要求。

统计查询示例 #

你可以用如下Oracle SQL实现全量月份统计，可自行调整统计的时间区间：

WITH month_list AS (
    -- 生成统计区间的所有月份起始日，示例为2021年全年，按需修改起止日期即可
    SELECT ADD_MONTHS(DATE '2021-01-01', LEVEL - 1) AS month_start
    FROM dual
    CONNECT BY ADD_MONTHS(DATE '2021-01-01', LEVEL - 1) <= DATE '2021-12-01'
)
SELECT
    TO_CHAR(m.month_start, 'YYYY-MM') AS 统计月份,
    t.mnfct AS 厂商,
    COUNT(1) AS OPEN条目数
FROM month_list m
LEFT JOIN 你的业务表名 t
    ON t.receipt_date <= LAST_DAY(m.month_start)
    AND (t.sold_date IS NULL OR t.sold_date > LAST_DAY(m.month_start))
GROUP BY TO_CHAR(m.month_start, 'YYYY-MM'), t.mnfct
ORDER BY 统计月份, 厂商;

Oracle本身提供多种数据变更追溯能力，可按需选择：

• 闪回查询（Flashback Query）：如果数据库开启了归档模式，且UNDO_RETENTION参数配置的保留时间足够长，可直接查询历史任意时点的表快照。比如要获取2021年5月31日23:59:59的原始数据，只需执行SELECT * FROM 你的业务表名 AS OF TIMESTAMP TO_TIMESTAMP('2021-05-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')，完全不受后续数据修改的影响，可用于还原真实的历史时点状态。
• 审计功能：开启标准审计或细粒度审计（FGA）后，可配置记录针对该表的所有增删改操作，包括操作人、操作时间、修改前后的字段值，满足审计追溯需求。
• 自定义触发器日志：可自行在业务表上创建行级触发器，每次receipt_date或sold_date被修改时，自动将修改前后值、操作人、操作时间写入自定义的变更日志表，可控性最高，无需依赖数据库额外配置。

内容的提问来源于stack exchange，提问作者Саят Оразов