如何对同表按不同过滤条件统计各分类应用数并合并为多列结果

你之前的条件聚合写法出错的核心原因是未对app做去重，COUNT统计的是符合条件的行的总数，同个app的多条下载、更新记录会被重复计数。

直接修改为在COUNT中加入DISTINCT即可实现需求，通用SQL写法如下：

SELECT
  id,
  COUNT(DISTINCT CASE WHEN app IN ('Clash of Clans', 'Valorant', 'PUBG') THEN app ELSE NULL END) AS gaming_apps,
  COUNT(DISTINCT CASE WHEN app IN ('Telegram', 'WhatsApp', 'Signal', 'FBMessenger') THEN app ELSE NULL END) AS msg_apps,
  COUNT(DISTINCT CASE WHEN app IN ('Yahoo Finance', 'Robinhood') THEN app ELSE NULL END) AS fin_apps
FROM apps_table
GROUP BY id

写法说明 #

• CASE WHEN判断应用所属类别，匹配成功返回app名称，匹配失败返回NULL
• COUNT函数会自动忽略NULL值，搭配DISTINCT即可统计每个id对应类别下的不同app数量
• 该写法仅需扫描一次数据表，无需嵌套子查询或多表JOIN，执行效率和可维护性都更高

如果你使用的是支持IF函数的SQL方言（如MySQL），也可以简化为更短的写法：

SELECT
  id,
  COUNT(DISTINCT IF(app IN ('Clash of Clans', 'Valorant', 'PUBG'), app, NULL)) AS gaming_apps,
  COUNT(DISTINCT IF(app IN ('Telegram', 'WhatsApp', 'Signal', 'FBMessenger'), app, NULL)) AS msg_apps,
  COUNT(DISTINCT IF(app IN ('Yahoo Finance', 'Robinhood'), app, NULL)) AS fin_apps
FROM apps_table
GROUP BY id

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