【题目】
表名为“购买记录表”里记录某在线教育平台的用户购买记录，包含字段：用户id、购买时间、课程类型、消费金额。
问题：分析出每日首次购买用户的次月、第三月、第四月复购情况如何？
【解题思路】
1. 群组分析方法
这类复购问题的取数方式是群组分析方法经常使用的。
群组分析方法：根据用户初始行为的发生时间，将用户划分为不同的组，进而分析每个组的行为如何随时间变化而变化。
上面说的初始行为包括：登录、购买等。以购买行为举例，首先每天圈定“第一次发生购买行为”的用户作为初始用户群，然后观察这部分用户在后面每一天发生购买行为的情况（如下图）。
本题就是圈定“第一次发生购买行为”的用户群，然后统计这部分用户在次月、第三月、第四月依然购买的人数。
2. 窗口函数
群组分析方法对应到SQL里常用窗口函数来实现。也就是从某些维度对数据分组（partition by），然后同样也可以对每个组进行统计运算。
窗口函数使用形式如下：
窗口函数名(字段) over(partition by 字段 order by 字段)其中，row_number() 就是窗口函数的一种，用于排名。
首先要获取“当日首次购买用户量”，也就是获取每个用户的第一次购买的日期（也就是对用户按购买时间排名，排名第1的就是第一次购买的日期）。
select 用户id,date(购买时间) as 日期,row_number() over(partition by 用户id order by 购买时间) as 购买顺序from 购买记录表;
查询结果：
“购买顺序”为1时，即该用户首次购买的日期。
select 用户id,日期,购买顺序from (select 用户id,date(购买时间) as 日期,row_number() over(partition by 用户id order by 购买时间) as 购买顺序from 购买记录表) as t0where 购买顺序 = 1;
查询结果：
3. 多表联结
接下来获取这些用户在次月、第三月、第四月的购买记录，使之与原始“购买记录表”进行左联结即可。
此处为了分别获取每个用户在次月、第三月、第四月的购买记录，需要与“购买记录表”联结三次，并且在最终按日期进行汇总分析。
select t1.日期,count(distinct t1.用户id) as 当日首次购买用户量,count(distinct t2.用户id) as 次月复购用户量,count(distinct t3.用户id) as 第三月复购用户量,count(distinct t4.用户id) as 第四月复购用户量from (select 用户id,日期,购买顺序from (select 用户id,date(购买时间) as 日期,row_number() over(partition by 用户id order by 购买时间) as 购买顺序from 购买记录表) as t0where 购买顺序 = 1) as t1left join 购买记录表 as t2 on timestampdiff(month, t1.日期, date(t2.购买时间)) = 1 and t1.用户id = t2.用户idleft join 购买记录表 as t3 on timestampdiff(month, t1.日期, date(t3.购买时间)) = 2 and t1.用户id = t3.用户idleft join 购买记录表 as t4 on timestampdiff(month, t1.日期, date(t4.购买时间)) = 3 and t1.用户id = t4.用户idgroup by t1.日期order by t1.日期;
【本题考点】
1.遇到复购、留存、流失这类业务问题，要想到用群组分析方法
2.群组分析方法对应在SQL里用窗口函数实现
3.SQL常用函数的使用，包括：count、date、timestampdiff、distinct。
原文作者：猴子数据分析；如有侵权，请联系管理员删除。
