爱站程序员基地通过学习数据分析案例整理 – APP用户活跃+红包领取数据分析，练习MqSQL中Join, group by, case when 语句。
数据框架如下：
活跃用户表：（是新用户列，1：新用户；0：老用户）
领取红包表：
问题：
- 计算2019年6月1日至今，每日DAU（活跃用户是指有登陆的用户）
- 分析每天领取红包的用户数、人均领取金额、人均领取次数、要区分用户属性：新用户、老用户、领取红包但未登录用户
- 分析每个月红包领取天数，每个月领取红包用户数、人均领取金额、人均领取次数
- 分析每个月领过红包用户和未领红包用户的数量
解答：
- 问题解析：按天统计每天的登录用户数量，注意去重，以及筛选日期范围
SELECT 登录日期, COUNT(DISTINCT 用户ID) AS DAUFROM `活跃用户表`WHERE 登录日期 > 20190601GROUP BY 登录日期;
结果：
- 问题解析：
每天 – 按天分组，分别计算对应字段
划分用户属性：需要关联 活跃用户表 与领取红包表，并且根据匹配的结果对用户分类
2.1) 关联两个表，并划分用户属性，并将该结果作为新的查询表格
注意：两个表都有 用户ID字段, 建议将其中一个重新命名，以免报错: duplicate fields 用户ID
SELECT a.*, b.登录日期, b.用户ID AS 用户IDlogin, b.是新用户,CASE b.是新用户WHEN 1 THEN \'新用户\'WHEN 0 THEN \'老用户\'ELSE \'未登录用户\'END as \'领红包用户属性\'FROM `领取红包表` aLEFT JOIN `活跃用户表` b ON a.抢红包日期 = b.登录日期 AND b.用户ID = a.用户ID
2.2) 每天领取红包的用户数
SELECT COUNT(DISTINCT 用户ID) FROM 新表 GROUP BY 抢红包日期
2.3) 人均领取金额
SELECT SUM(金额)/COUNT(DISTINCT 用户ID) FROM 新表 group by 抢红包日期
2.4) 人均领取次数
SELECT COUNT(*)/COUNT(DISTINCT 用户ID) FROM 新表 GROUP BY 抢红包日期
2.5) 将以上结果合并
SELECT r.抢红包日期, r.领红包用户属性,COUNT(DISTINCT r.用户ID) as 用户数量,ROUND(SUM(r.金额) / COUNT(DISTINCT r.用户ID),2) as 人均领取金额,COUNT(*)/COUNT(DISTINCT r.用户ID) as 人均领取次数FROM(SELECT a.*, b.登录日期, b.用户ID AS 用户IDlogin, b.是新用户,CASE b.是新用户WHEN 1 THEN \'新用户\'WHEN 0 THEN \'老用户\'ELSE \'未登录用户\'END as \'领红包用户属性\'FROM `领取红包表` aLEFT JOIN `活跃用户表` b ON a.抢红包日期 = b.登录日期 AND b.用户ID = a.用户ID) rGROUP BY r.抢红包日期,r.领红包用户属性
结果如下：
- 问题解析：
每月 – 计算月份，按月分组，由于只有2019年数据，因此直接使用month函数
其他指标与第2问一致
SELECT month(抢红包日期) 月份, COUNT(抢红包日期) as 抢红包天数,COUNT(DISTINCT 用户ID) as 领取红包人数,ROUND(SUM(金额)/COUNT(DISTINCT 用户ID),2) AS 人均领取金额,COUNT(*)/ COUNT(DISTINCT 用户ID) AS 人均领取次数FROM `领取红包表`GROUP BY month(抢红包日期)
- 问题解析：
每月 – 按月分组
领过红包：领取红包表中的每个用户都是领取用户
未领过红包：活跃用户表中有但是领取红包表中没有的用户
领过红包 VS 未领过红包：活跃用户表 LEFT JOIN 领红包表进行查看
4.1) 链接两个表, 作为最终查询的表基础
SELECT *FROM 活跃用户表 aLEFT JOIN `领取红包表` bON a.用户ID = b.用户ID AND a.登录日期 = b.抢红包日期
4.2) 两个链接表，当领取红包表对应的用户ID为空，则说明没有领取红包，不为空则说明领取了红包，按照此条件使用CASE WHEN进行分类，后续分组查询
SELECT month(登录日期) 活跃月份,CASE WHEN b.用户ID is null THEN \'没有领取红包用户\' ELSE \'领取红包用户\' END as 是否领取红包,COUNT( DISTINCT a.用户ID) 用户数FROM 活跃用户表 aLEFT JOIN `领取红包表` bON a.用户ID = b.用户ID AND a.登录日期 = b.抢红包日期GROUP BY 活跃月份, 是否领取红包;
结果为：
