如何留存数据历史，实现基于选定日期范围的仪表板展示？

1. 直接从审计表构建快照查询 #

不用修改临时主库，直接通过审计表计算指定时间点的业务数据状态。假设你的审计表包含record_id（记录ID）、field_name（变更字段）、new_value（变更后值）、change_time（变更时间）、op_type（操作类型：新增/更新/删除），可以用窗口函数快速获取每个记录在指定日期前的最后一次变更：

WITH latest_changes AS (
    SELECT 
        record_id,
        field_name,
        new_value,
        ROW_NUMBER() OVER (PARTITION BY record_id, field_name ORDER BY change_time DESC) AS rn
    FROM audit_table
    WHERE change_time <= '2024-05-20'
)
SELECT 
    record_id,
    MAX(CASE WHEN field_name = 'user_name' THEN new_value END) AS user_name,
    MAX(CASE WHEN field_name = 'balance' THEN new_value END) AS balance
    -- 其他业务字段依次补充
FROM latest_changes
WHERE rn = 1
GROUP BY record_id;

这种方式直接从审计表生成指定时间点的完整快照，无需临时库，计算量远小于全量修改主库。

2. 预计算快照表（适配高频查询场景） #

如果仪表板需要频繁查询历史快照，预计算是更优选择：

• 定时（比如每日凌晨）运行脚本，基于审计表生成前一日的全量快照，存储到独立的snapshot_table，字段包含snaphot_date（快照日期）+所有业务字段
• 查询时直接按日期从snapshot_table筛选，性能最优
• 可根据业务需求保留最近N天快照，旧快照归档存储，避免占用过多空间

3. 单记录变更轨迹展示 #

如果需求是展示单条记录的完整变更历史（而非某时间点的整体状态），直接查询审计表按时间排序即可：

SELECT change_time, op_type, field_name, old_value, new_value
FROM audit_table
WHERE record_id = 'U1001'
ORDER BY change_time DESC;

在仪表板上用时间线或表格呈现每一次变更，直观清晰。

关键避坑点 #

• 绝对不要修改主库：主库是业务核心，临时修改会带来数据一致性风险和性能损耗
• 给审计表加索引：针对record_id、change_time建立联合索引，大幅提升查询速度
• 明确需求类型：区分是要某时间点的整体业务状态（用快照方案），还是单记录的变更过程（用轨迹方案）

内容的提问来源于stack exchange，提问作者Micky Singh