统计tableA未在tableB的accountNumber 两SQL结果差异原因

现有两张数据库表，结构定义如下：

CREATE TABLE tableA 
(
    accountNumber, -- 值之间包含冒号，关联时需要移除
    type, -- 取值范围1-10
    status1,
    status2
);
CREATE TABLE tableB 
(
    accountNumber,
    usage, -- 取值范围[0, +∞)
    day_id
);

需求为：统计符合筛选条件、存在于tableA但不存在于tableB的去重accountNumber数量，结果按type维度分组。
编写的两种SQL实现返回结果不一致，需要排查逻辑疏漏。

两种写法结果不一致，是因为分别存在不同的逻辑bug，既没有对齐需求，也没有互相对齐计算逻辑：

方式1的错误 #

• 定义了做账号格式清洗、预筛选的cteA和cteB，但后续关联逻辑完全没有引用这两个CTE，直接使用原始tableA和tableB做关联，属于无效代码。
• 关联时没有对tableA的accountNumber做移除冒号、转大写的格式统一，带冒号的账号无法和tableB中无冒号的账号匹配，会导致"不存在于B表"的统计值虚高。
• 最终统计使用count(*)，没有对账号去重，如果同一个accountNumber在tableA中存在多条符合筛选条件的记录，会被重复计数，不符合"统计accountNumber数量"的需求。

方式2的错误 #

• 统计A表总账号数时使用原始带冒号的accountNumber做去重，匹配B表时使用清洗后的账号，两者统计基数不一致，差值计算本身就有误差。
• 差值逻辑默认前提是「同一个账号在A表只对应一个type」，如果同一个清洗后的账号在A表中对应多个type值，会被重复统计到多个type分组下，和方式1的错误场景类似但触发条件不同。

优先使用NOT EXISTS写法，关联时统一账号格式，同时显式对账号去重，避免重复计数：

SELECT
    a.type,
    COUNT(DISTINCT UPPER(REPLACE(a.accountNumber, ':', ''))) AS account_count
FROM tableA a
WHERE
    a.status1 = 'Active'
    AND a.status2 = 'Active'
    AND a.type IN ('1','2','3','4')
    AND NOT EXISTS (
        SELECT 1
        FROM tableB b
        WHERE
            b.usage > 0
            AND b.day_id > '2022-01-01'
            AND b.accountNumber = UPPER(REPLACE(a.accountNumber, ':', ''))
    )
GROUP BY a.type
ORDER BY account_count DESC;

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