求基于状态聚合统计的SQL查询：统计用户有效活跃应用数

原始数据表 #

期望结果表 #

需求说明 #

统计每个用户拥有的处于active状态的有效应用数量，规则如下：

• 同一app_id若存在inactive状态记录，则该app_id不计入统计
• 仅统计无inactive状态记录的active应用

举例：

• h@gmail.com关联2个app_id（2553和2554），其中app_id=2553存在inactive记录，故不计入；app_id=2554仅有active记录，故计入，最终统计数为1
• b@gmail.com的app_id=2555无inactive记录，app_id=2556也无inactive记录，二者均为active状态，故统计数为2

解决方案SQL #

SELECT 
    user AS email,
    COUNT(DISTINCT app_id) AS count
FROM 
    your_table_name
WHERE 
    app_id NOT IN (
        SELECT app_id 
        FROM your_table_name 
        WHERE enabled_app = 'inactive'
    )
    AND enabled_app = 'active'
GROUP BY 
    user;

思路解释 #

1. 子查询先找出所有存在inactive状态记录的app_id集合
2. 外层查询筛选出不在上述集合中的app_id，同时限定状态为active
3. 按用户分组，统计每个用户符合条件的唯一app_id数量

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