如何使用SQL统计新用户与回访用户的数量比例
问题拆解与实现方案
原有代码的核心问题
- 代码占位符未替换:原CTE里的
from 1、from b是未替换为真实业务表名的占位内容,直接运行会触发语法错误 - 语法细节遗漏:连续定义的两个CTE之间缺少逗号分隔,同样会导致执行失败
- 思路误区:新用户和回访用户是完全互斥的两个用户集合,不存在可关联的匹配字段,之前尝试做表关联的操作完全多余
推荐实现逻辑
不需要拆分两个独立CTE再做关联,单轮用户粒度聚合即可完成统计,效率更高也更简洁:
- 先按
user_id分组,计算每个用户在表中的出现次数,按规则标记用户类型 - 对标记后的用户类型做计数统计,直接拼接为可读的比例格式
对应可运行的SQL(将answer_records替换为实际业务表名即可):
with user_level_stats as ( select user_id, case when count(answer_id) = 1 then 'new_user' else 'returning_user' end as user_tag from answer_records group by user_id ) select sum(case when user_tag = 'new_user' then 1 else 0 end) as new_user_total, sum(case when user_tag = 'returning_user' then 1 else 0 end) as returning_user_total, concat( sum(case when user_tag = 'new_user' then 1 else 0 end), ':', sum(case when user_tag = 'returning_user' then 1 else 0 end) ) as user_ratio from user_level_stats
适配原拆分CTE思路的实现
如果坚持沿用之前拆分两个CTE的结构,不需要做表关联,直接分别统计两个CTE的行数即可:
with table_a as( select user_id from answer_records group by user_id having count(answer_id) > 1 -- 回访用户集合 ), table_b as ( select user_id from answer_records group by user_id having count(answer_id) < 2 -- 新用户集合 ) select (select count(user_id) from table_b) as new_user_total, (select count(user_id) from table_a) as returning_user_total, concat((select count(user_id) from table_b), ':', (select count(user_id) from table_a)) as user_ratio
示例数据验证
按给出的示例数据计算:
- 新用户(仅出现1次):user_id=22、user_id=78,共2个
- 回访用户(出现次数>1):user_id=33,共1个
最终输出的比例为2:1,和预期结果完全匹配。
内容的提问来源于stack exchange,提问作者Eli
相关产品推荐
相关产品推荐

