SQL同列双条件计数:统计activity表中同时含SEM及其他渠道的用户数
统计同时关联SEM渠道与至少一个其他渠道的用户数实现
这类跨同一字段、按用户维度做多条件匹配的统计,核心是要把判断粒度从单条记录抬升到用户维度,单条记录不可能同时满足两个互斥渠道判断,直接在单行上做两个渠道值的等值匹配永远得不到正确结果。
实现方案
方案1:通用分组聚合写法(兼容所有SQL引擎)
通过分组后做条件计数,搭配HAVING子句过滤符合要求的用户,无语法兼容问题:
SELECT COUNT(user_ID) AS qualified_user_total FROM ( SELECT user_ID FROM activity GROUP BY user_ID HAVING -- 判断用户是否存在SEM渠道关联 COUNT(DISTINCT CASE WHEN contact_channel_ID = 'SEM' THEN contact_channel_ID END) > 0 -- 判断用户是否存在除SEM外的其他渠道关联 AND COUNT(DISTINCT CASE WHEN contact_channel_ID != 'SEM' THEN contact_channel_ID END) > 0 ) AS qualified_user
方案2:布尔聚合简化写法(适配支持bool_or函数的引擎,如SparkSQL、PostgreSQL)
如果使用的SQL引擎支持布尔聚合函数,可以简化条件判断逻辑,减少代码冗余:
SELECT COUNT(user_ID) AS qualified_user_total FROM activity GROUP BY user_ID HAVING bool_or(contact_channel_ID = 'SEM') AND bool_or(contact_channel_ID != 'SEM')
结果校验
按照给出的activity表示例数据逐用户匹配规则:
- user_ID 123456:仅关联email渠道,无SEM渠道关联记录,不符合要求
- user_ID 555555:仅关联SEM渠道,无其他渠道关联记录,不符合要求
- user_ID 995959:同时关联SEM、phone两个渠道,满足双条件,符合要求
- user_ID 696969:仅关联email渠道,无SEM渠道关联记录,不符合要求
最终统计得到的符合规则的用户总数为1,与逻辑预期一致。
常见易错点
- 不要将两个互斥的渠道判断写在WHERE过滤条件中,比如
WHERE contact_channel_ID = 'SEM' AND contact_channel_ID != 'SEM',单条记录不可能同时匹配两个冲突值,这种写法会直接返回空结果 - 统计用户数时不要直接计算原始表的行数,需要在用户维度做去重,避免同一用户的重复渠道记录被多次计数
- 判断渠道存在性时,用
COUNT(DISTINCT 渠道值)做判断比单纯计数行数更稳妥,就算存在重复的渠道关联记录,也不会干扰条件判断
内容的提问来源于stack exchange,提问作者VRumay
相关产品推荐
相关产品推荐

