Snowflake SQL需求：显示指定实体的计数（含0值行）

要实现你的需求，核心是先构建包含所有需要显示的实体的基准列表，再和统计结果做左连接，确保所有实体行都被保留，同时修正Others的计数逻辑。以下是适配Snowflake环境的SQL代码：

WITH target_entities AS (
    -- 定义所有需要显示的目标实体
    SELECT 'AAA' AS target_entity UNION ALL
    SELECT 'Sol-BBB' UNION ALL
    SELECT 'MU-CCC' UNION ALL
    SELECT 'MU-DDD' UNION ALL
    SELECT 'Others'
),
entity_stats AS (
    -- 先计算每个目标分类的正确计数值
    SELECT 
        CASE 
            WHEN Entity LIKE '%AAA%' THEN 'AAA'
            WHEN Entity LIKE '%BBB%' THEN 'Sol-BBB'
            WHEN Entity LIKE '%CCC%' THEN 'MU-CCC'
            WHEN Entity = 'DDD' THEN 'MU-DDD'
            ELSE 'Others' 
        END AS target_entity,
        COUNT(DISTINCT CASE WHEN SEVERITY_ID='X' THEN CMDBHASH_ASSET_CVE_COMBINE END) AS numerator
    FROM cw.cw_cns.CW_VW_MS_VN_SU_HIS
    GROUP BY target_entity
)
-- 关联基准实体列表和统计结果，确保所有实体都显示，空值补0
SELECT 
    ADD_MONTHS(LAST_DAY((SELECT date FROM cw.cw_cs.cw_vw_cw_us_date)), -1) AS "DATE",
    'MT09' AS "Metric ID",
    'Number of Asset' AS "Metric Name",
    te.target_entity AS "Legal Entity",
    COALESCE(es.numerator, 0) AS "NUMERATOR"
FROM target_entities te
LEFT JOIN entity_stats es ON te.target_entity = es.target_entity
ORDER BY te.target_entity;

关键逻辑说明 #

• 目标实体列表（target_entities）：用UNION ALL构建所有需要显示的实体行，确保即使没有对应数据也能输出该行。
• 统计子查询（entity_stats）：先对原始数据做分类映射，再按目标分类分组统计，保证Others的计数是所有不符合前四类的实体的正确统计值。
• 左连接+COALESCE：将基准实体列表和统计结果关联，用COALESCE把空的计数值替换为0，满足计数为0时显示0的要求。

验证结果 #

执行后会输出你期望的所有实体行：

DATE        Metric ID   Metric Name Legal Entity    NUMERATOR
7/31/2024   MT09        Number of Asset AAA          0
7/31/2024   MT09        Number of Asset MU-CCC       5
7/31/2024   MT09        Number of Asset Sol-BBB      0
7/31/2024   MT09        Number of Asset MU-DDD       0
7/31/2024   MT09        Number of Asset Others       2

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