如何按组和实体统计事件数？SQL计数结果异常排查

你的原查询逻辑存在问题，导致计数结果只能是0或1，无法正确统计每组对应的事件总数：

• 原查询里的CASE WHEN仅判断组ID是否存在于事件表中，而非统计该组匹配的事件条数。每个分组后，这个CASE的结果要么是1要么是NULL，COUNT只会统计非NULL值的数量，因此每组最多得到1。

修正方案一：直接关联事件表后统计 #

通过LEFT JOIN关联两个事件表，直接统计匹配的事件记录数：

SELECT 
    pg."name" AS "Groupe", 
    e."displaylabel" AS "Entity",
    COUNT(i1.id) AS "Number of incident 1 month",
    COUNT(i2.id) AS "Number of incident 2 month"
FROM 
    "persongroup" pg
LEFT JOIN 
    "entity" e ON pg."entity_c" = e."id"
LEFT JOIN incident_1month i1 ON pg."id" = i1.new_group
LEFT JOIN incident_2month i2 ON pg."id" = i2.new_group
GROUP BY pg."name", e."displaylabel"
ORDER BY pg."name";

修正方案二：预统计事件表的分组计数（适合大数据量场景） #

先对两个事件表按new_group分组统计事件数，再关联到组和实体表，避免关联时产生过多中间数据：

SELECT 
    pg."name" AS "Groupe", 
    e."displaylabel" AS "Entity",
    COALESCE(i1.count_1month, 0) AS "Number of incident 1 month",
    COALESCE(i2.count_2month, 0) AS "Number of incident 2 month"
FROM 
    "persongroup" pg
LEFT JOIN 
    "entity" e ON pg."entity_c" = e."id"
LEFT JOIN (
    SELECT new_group, COUNT(*) AS count_1month
    FROM incident_1month
    GROUP BY new_group
) i1 ON pg."id" = i1.new_group
LEFT JOIN (
    SELECT new_group, COUNT(*) AS count_2month
    FROM incident_2month
    GROUP BY new_group
) i2 ON pg."id" = i2.new_group
GROUP BY pg."name", e."displaylabel", i1.count_1month, i2.count_2month
ORDER BY pg."name";

两种方案都能正确统计每组在两个事件表中的事件总数，COALESCE函数用于将NULL（无匹配事件的组）转换为0，保证结果更直观。

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