如何用SQL实现Table_A与Table_B计数器列每日差异检测及累计统计

核心思路 #

要实现累计差异天数的视图，不能直接通过两表实时对比生成（视图无法留存历史差异记录），必须先建立每日差异快照表存储每日的差异状态，再基于该表累计生成最终视图。

1. 创建每日差异快照表 #

先建一张表来每日记录每个ID的计数器差异情况：

CREATE TABLE Daily_Counter_Diffs (
    ID INT,
    Name VARCHAR(100),
    Code VARCHAR(50),
    Status VARCHAR(20),
    Check_Date DATE,
    Counter_A_Diff INT, -- 1=当日Counter_A差异，0=无差异
    Counter_B_Diff INT, -- 1=当日Counter_B差异，0=无差异
    Counter_C_Diff INT, -- 1=当日Counter_C差异，0=无差异
    PRIMARY KEY (ID, Check_Date) -- 确保同一个ID每日仅存一条记录
);

2. 每日定时执行差异快照插入 #

在每日Table_A批量更新完成后，执行以下脚本，将当日的计数器差异写入快照表：

INSERT INTO Daily_Counter_Diffs (ID, Name, Code, Status, Check_Date, Counter_A_Diff, Counter_B_Diff, Counter_C_Diff)
SELECT
    a.ID,
    a.Name,
    a.Code,
    a.Status,
    CURRENT_DATE(),
    CASE WHEN a.Counter_A != b.Counter_A THEN 1 ELSE 0 END,
    CASE WHEN a.Counter_B != b.Counter_B THEN 1 ELSE 0 END,
    CASE WHEN a.Counter_C != b.Counter_C THEN 1 ELSE 0 END
FROM Table_A a
INNER JOIN Table_B b ON a.ID = b.ID; -- 假设ID是两表的唯一关联键，若为复合键需调整JOIN条件

注：可通过数据库定时任务（如MySQL的Event、PostgreSQL的pg_cron）或外部调度工具（如Cron）自动执行此脚本。

3. 创建累计差异视图View_B #

基于快照表，用窗口函数累计每个ID的历史差异天数：

CREATE VIEW View_B AS
SELECT
    ID,
    Name,
    Code,
    Status,
    SUM(Counter_A_Diff) OVER (PARTITION BY ID ORDER BY Check_Date) AS Counter_A_check,
    SUM(Counter_B_Diff) OVER (PARTITION BY ID ORDER BY Check_Date) AS Counter_B_check,
    SUM(Counter_C_Diff) OVER (PARTITION BY ID ORDER BY Check_Date) AS Counter_C_check,
    MAX(Check_Date) AS Last_Check_Date -- 可选字段，显示最后一次检测的日期
FROM Daily_Counter_Diffs;

关键说明 #

• 快照表的作用：留存每日差异数据，是实现累计天数的核心基础，否则视图只能获取当前时刻的差异，无法统计历史累计值。
• 窗口函数逻辑：SUM() OVER (PARTITION BY ID ORDER BY Check_Date) 会按ID分组，按日期顺序累加每日的差异标识（1），最终得到累计差异天数。
• 兼容性：上述脚本基于标准SQL编写，不同数据库可能需微调语法（如CURRENT_DATE()在SQL Server中为GETDATE()，窗口函数的语法细节）。

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