题解 | 统计活跃间隔对用户分级结果 —— W1uSeven7 -- NO.03

看输出描述发现有新字段名，第一反应是作四次查询然后用UNION合并起来：虽然思路不算复杂，但我还是踩了坑，一开始用的是左连接，但写到后面发现逻辑很简单，根本不需要左连接，直接用 IN 和NOT IN就行，代码如下：

SELECT user_grade,ratio FROM
(-- 忠实用户：近七天活跃过且七天前也活跃过
SELECT '忠实用户' AS user_grade,ROUND(COUNT(DISTINCT t1.uid)/(SELECT COUNT(DISTINCT uid)FROM tb_user_log),2) AS ratio
FROM
(SELECT uid
FROM tb_user_log
WHERE in_time BETWEEN DATE_SUB((SELECT MAX(in_time) FROM tb_user_log),INTERVAL 6 DAY) AND (SELECT MAX(in_time) FROM tb_user_log) AND uid IN(SELECT uid FROM tb_user_log
WHERE in_time < DATE_SUB((SELECT MAX(in_time) FROM tb_user_log),INTERVAL 6 DAY))
)t1

UNION 

-- 新晋用户 近七天活跃过且七天前从来没有活跃过：改为NOT IN即可
SELECT '新晋用户' AS user_grade,ROUND(COUNT(t2.uid) / (SELECT COUNT(DISTINCT uid)FROM tb_user_log),2) AS ratio
FROM
(SELECT uid
FROM tb_user_log
WHERE in_time BETWEEN DATE_SUB((SELECT MAX(in_time) FROM tb_user_log),INTERVAL 6 DAY) AND (SELECT MAX(in_time) FROM tb_user_log) AND uid NOT IN(SELECT uid FROM tb_user_log
WHERE in_time < DATE_SUB((SELECT MAX(in_time) FROM tb_user_log),INTERVAL 6 DAY))
)t2

UNION

-- 沉睡用户：近30天活跃过但近七天没有活跃过 = 近30天活跃用户-近七天活跃用户（这有个坑：因为下面还有个分类是流失用户，所以这边的限制条件是要近30天也要活跃过而非只要七天前活跃过就行）
SELECT '沉睡用户' AS user_grade,ROUND(COUNT(DISTINCT t3.uid)/(SELECT COUNT(DISTINCT uid)FROM tb_user_log),2) AS ratio
FROM
(SELECT uid FROM tb_user_log
WHERE in_time >= DATE_SUB((SELECT MAX(in_time) FROM tb_user_log),INTERVAL 29 DAY) AND uid NOT IN (SELECT uid FROM tb_user_log 
WHERE in_time >= DATE_SUB((SELECT MAX(in_time) FROM tb_user_log),INTERVAL 6 DAY))
)t3

UNION

-- 流失用户：全部活跃过的用户 - 近30天活跃过的用户
SELECT '流失用户' AS user_grade,ROUND(COUNT(DISTINCT t4.uid)/(SELECT COUNT(DISTINCT uid)FROM tb_user_log),2) AS ratio
FROM
(SELECT uid
FROM tb_user_log
WHERE uid NOT IN(SELECT uid FROM tb_user_log
WHERE in_time >= DATE_SUB((SELECT MAX(in_time) FROM tb_user_log),INTERVAL 29 DAY)) AND in_time IS NOT NULL
)t4
)t5
ORDER BY ratio DESC;