如何编写查询语句实现指定时间段内数据库字段变更情况监控

前提说明 #

要实现指定时间段内单字段变更记录的筛选，需要你的数据库环境具备历史数据追溯能力，常见适用场景分为两类：

• 场景1：CAM_CONCEN表自带审计字段（如update_time修改时间、change_id变更序号），或存在配套的审计日志表、历史快照表存储所有变更轨迹
• 场景2：数据库本身支持闪回/时态查询能力（如Oracle Flashback、SQL Server Temporal Table、MySQL Binlog解析能力等）

场景1：自带审计字段/审计表的实现方案 #

核心逻辑 #

• 按ACCOUNT_NUMBER分组，对比同账号相邻两条记录的CONCTACT字段值是否发生变化
• 筛选变更时间落在「指定日期往前推6个月」的时间区间内

示例SQL（支持窗口函数的数据库） #

-- 定义指定日期变量，可根据数据库语法调整
SET @target_date = '2024-06-30'; -- 替换为你实际的指定日期

SELECT 
  ACCOUNT_NUMBER,
  LAG(CONCTACT) OVER (PARTITION BY ACCOUNT_NUMBER ORDER BY change_time) AS old_conctact,
  CONCTACT AS new_conctact,
  change_time AS modify_time
FROM CAM_CONCEN_AUDIT -- 替换为你的实际表名/审计表名
WHERE change_time >= DATE_SUB(@target_date, INTERVAL 6 MONTH)
  AND change_time <= @target_date
-- 仅保留字段发生变更的记录
QUALIFY old_conctact IS NOT NULL AND old_conctact != new_conctact;

示例SQL（不支持窗口函数的数据库） #

可以用自关联实现相邻记录对比：

SET @target_date = '2024-06-30';

SELECT 
  a.ACCOUNT_NUMBER,
  a.CONCTACT AS old_conctact,
  b.CONCTACT AS new_conctact,
  b.change_time AS modify_time
FROM CAM_CONCEN_AUDIT a
INNER JOIN CAM_CONCEN_AUDIT b
  ON a.ACCOUNT_NUMBER = b.ACCOUNT_NUMBER
  AND a.change_id = b.change_id - 1 -- 假设change_id是自增的连续变更序号
WHERE b.change_time >= DATE_SUB(@target_date, INTERVAL 6 MONTH)
  AND b.change_time <= @target_date
  AND a.CONCTACT != b.CONCTACT;

场景2：无审计表时的替代方案 #

Oracle 闪回查询实现 #

SET @target_date = '2024-06-30';
SET @start_date = ADD_MONTHS(@target_date, -6);

-- 对比当前数据和6个月前的快照，筛选CONCTACT发生变化的记录
SELECT 
  current.ACCOUNT_NUMBER,
  history.CONCTACT AS old_conctact,
  current.CONCTACT AS new_conctact
FROM CAM_CONCEN current
LEFT JOIN CAM_CONCEN AS OF TIMESTAMP TO_TIMESTAMP(@start_date, 'YYYY-MM-DD') history
  ON current.ACCOUNT_NUMBER = history.ACCOUNT_NUMBER
WHERE NVL(current.CONCTACT, 'NULL_PLACEHOLDER') != NVL(history.CONCTACT, 'NULL_PLACEHOLDER');

SQL Server 时态表实现 #

如果你的表已经开启了系统版本控制：

DECLARE @target_date DATE = '2024-06-30';
DECLARE @start_date DATE = DATEADD(MONTH, -6, @target_date);

SELECT * FROM CAM_CONCEN 
FOR SYSTEM_TIME BETWEEN @start_date AND @target_date
-- 后续字段对比逻辑同场景1

注意事项 #

• 注意字段拼写校验，你提到的CONCTACT大概率是CONTACT的笔误，实际查询前请先核对表结构的字段名
• 不同数据库的时间函数语法存在差异：Oracle用ADD_MONTHS、MySQL用DATE_SUB、SQL Server用DATEADD，请根据你的数据库类型调整语法
• 空值比较需要额外处理，建议用IFNULL/NVL函数给空值设置统一占位符，避免NULL != 有效值的判断失效

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