基于CTE的SQL多Flag组合唯一ID统计方法咨询

首先先修正你原SQL里的几个明显错误（这些错误会导致Flag计算或关联出错）：

1. Joint CTE中，SELECT r.ID应该改为i.ID，因为主表是ids i
2. 所有JOIN条件里的RAID应该改为ID，因为各个子查询定义的列名都是ID
3. R Flag的CASE判断逻辑写反了，应该是WHEN r.ID IS NOT NULL THEN 1 ELSE 0
4. RR Flag里的rr.raid应该改为rr.ID

接下来，要统计各种组合的ID数量，确实可以用SUM(CASE ...)来实现——本质上是对符合组合条件的行计数，每满足条件就记1，否则0，最后求和就是符合条件的ID总数。

修改后的完整代码如下：

WITH ids AS (
    SELECT DISTINCT LOWER(r.entry_id) AS ID
    FROM id_user AS r
    UNION
    SELECT DISTINCT LOWER(identifiervalue) AS ID
    FROM account AS a
),
PPP as (
    SELECT DISTINCT 
        LOWER(accountid) as "ID"
    FROM ppp
    WHERE date >= '2022-11-21'
),
R as (
    SELECT DISTINCT 
        LOWER(account_id) as "ID"
    FROM "user"  -- 注意user是关键字，最好加引号避免语法错误
),
RR as (
    SELECT DISTINCT
        LOWER(id) AS "ID"
    FROM program_member
),
Joint as (
    SELECT
        i.ID,
        CASE WHEN ppp.ID IS NOT NULL THEN 1 ELSE 0 END AS "PPP Flag",
        CASE WHEN r.ID IS NOT NULL THEN 1 ELSE 0 END AS "R Flag",
        CASE WHEN rr.ID IS NOT NULL THEN 1 ELSE 0 END AS "RR Flag"
    FROM ids i
    LEFT JOIN PPP ppp ON i.ID = ppp.ID
    LEFT JOIN R r ON i.ID = r.ID
    LEFT JOIN RR rr ON i.ID = rr.ID
)
SELECT
    COUNT(ID) AS "ID Count",
    SUM("PPP Flag") AS "PPP Users",
    SUM("R Flag") AS "R Accounts",
    SUM("RR Flag") AS "RR Users",
    -- 统计同时属于全部3种Flag的ID数量
    SUM(CASE WHEN "PPP Flag" = 1 AND "R Flag" = 1 AND "RR Flag" = 1 THEN 1 ELSE 0 END) AS "All 3 Flags",
    -- 统计同时属于PPP和R的ID数量
    SUM(CASE WHEN "PPP Flag" = 1 AND "R Flag" = 1 THEN 1 ELSE 0 END) AS "PPP + R",
    -- 统计同时属于PPP和RR的ID数量
    SUM(CASE WHEN "PPP Flag" = 1 AND "RR Flag" = 1 THEN 1 ELSE 0 END) AS "PPP + RR",
    -- 统计同时属于R和RR的ID数量
    SUM(CASE WHEN "R Flag" = 1 AND "RR Flag" = 1 THEN 1 ELSE 0 END) AS "R + RR"
FROM Joint;

关键说明： #

• 每个组合统计列都用SUM(CASE ...)实现：当某行的多个Flag同时为1时，该行就被计入对应组合的计数，最终求和得到符合条件的ID总数
• 注意user是SQL关键字，不同数据库可能需要用引号/反引号包裹避免语法错误
• 所有关联条件统一用ID，保证关联逻辑正确

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