查找fact_accounts中缺失的11条记录：现有查询失效原因及替代方案

现有查询的问题 #

查询1的核心错误 #

你的第一个查询关联条件写错了：两张表的复合主键是date_code和account_id，但查询里用了client_account_id作为关联字段，这直接导致关联逻辑完全错误，自然无法定位到正确的缺失记录。

查询2可能的失效原因 #

第二个查询的关联字段是正确的，但没查到结果可能是以下情况之一：

• 字段类型不匹配：比如date_code在stage_accounts中是字符串类型（如'202406'），在fact_accounts中是数值类型（如202406），表面值相同但类型不同会导致关联失败。
• 存在NULL值：虽然复合主键理论上不允许NULL，但如果数据中存在date_code或account_id为NULL的记录，NULL = NULL的比较结果是UNKNOWN，NOT EXISTS会判定子查询存在记录，从而漏掉这些特殊记录。
• 隐式转换精度丢失：比如account_id在一张表中是INT类型，另一张是BIGINT类型，当值超出INT范围时，转换后值不一致导致关联失败。

其他获取缺失记录的方法 #

方法1：使用EXCEPT语句（适用于PostgreSQL、SQL Server等） #

直接对比两张表的主键组合，找出stage有但fact没有的记录：

-- 先获取缺失的主键组合
SELECT date_code, account_id
FROM stage_accounts
EXCEPT
SELECT date_code, account_id
FROM fact_accounts;

-- 关联原表获取完整记录
SELECT stage.*
FROM stage_accounts stage
JOIN (
    SELECT date_code, account_id
    FROM stage_accounts
    EXCEPT
    SELECT date_code, account_id
    FROM fact_accounts
) missing 
ON stage.date_code = missing.date_code 
AND stage.account_id = missing.account_id;

方法2：分组统计对比 #

通过分组统计主键组合的出现次数，定位缺失项：

WITH stage_groups AS (
    SELECT date_code, account_id
    FROM stage_accounts
    GROUP BY date_code, account_id
),
fact_groups AS (
    SELECT date_code, account_id
    FROM fact_accounts
    GROUP BY date_code, account_id
)
SELECT stage.*
FROM stage_accounts stage
JOIN stage_groups sg ON stage.date_code = sg.date_code AND stage.account_id = sg.account_id
LEFT JOIN fact_groups fg ON sg.date_code = fg.date_code AND sg.account_id = fg.account_id
WHERE fg.date_code IS NULL;

方法3：处理含NULL的特殊情况 #

如果主键字段存在NULL值，需要显式处理NULL的比较：

SELECT stage.*
FROM stage_accounts stage
WHERE NOT EXISTS (
    SELECT 1
    FROM fact_accounts fact
    WHERE (stage.date_code = fact.date_code OR (stage.date_code IS NULL AND fact.date_code IS NULL))
      AND (stage.account_id = fact.account_id OR (stage.account_id IS NULL AND fact.account_id IS NULL))
);

部分数据库（如PostgreSQL）支持IS NOT DISTINCT FROM语法，可以简化为：

SELECT stage.*
FROM stage_accounts stage
WHERE NOT EXISTS (
    SELECT 1
    FROM fact_accounts fact
    WHERE stage.date_code IS NOT DISTINCT FROM fact.date_code
      AND stage.account_id IS NOT DISTINCT FROM fact.account_id
);

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