注:参考公众号“猴子数据分析”中的相关教程。
题目一:
"课程订单表”里记录了某在线教育App的用户购买课程的信息(部分数据截图)。
请使用sql将购买记录表中的信息,提取为下表(复购分析表)的格式。并用一条sql语句写出。
复购用户:如果一个用户的首次购买日期是a,且该用户在a之后的第n月内,也有购买行为,这个用户被算做第n月复购用户。
代码:
select a.购买时间, count(distinct a.用户id),
count(distinct case when timestampdiff(month, a.购买时间,b.购买时间)<1 then a.用户id else null end) as 此月复购用户数,
count(distinct case when timestampdiff(month, a.购买时间,b.购买时间)=3 then a.用户id else null end) as 第三个月复购用户数
from table a
left join table b on a.用户id=b.用户id
where a.购买时间<>b.购买时间 and a.课程类型=2
group by a.购买时间
题目二:
“薪水表”中记录了员工发放的薪水。包含雇员编号,薪水、起始日期、结束日期。
其中,薪水是指该雇员在起始日期到结束日期这段时间内的薪水。当前员工是指结束日期 = '9999-01-01’的员工。
业务问题:
按照雇员编号升序排列,查找薪水的累计和(累计薪水)。其中累计薪水是前N个当前员工( 结束日期 = ‘9999-01-01’)的薪水的累计和,其他以此类推。
注意:累计薪水的含义如下图示例所示:
第1行的累计薪水为雇员编号(10001)的薪水,
第2行的累计薪水为雇员编号(10001)、雇员编号(10002)的薪水之和,
第3行的累计薪水为雇员编号(10001)、雇员编号(10002)、雇员编号(10003)的薪水之和
依次类推…
解题思路:
使用聚合函数作为窗口函数的方法,如下:
--格式
<窗口函数> over (partition by <用于分组的列名>
order by <用于排序的列名>)
示例如下:
--累计求和
sum(列名) over (partition by <用于分组的列名>
order by <用于排序的列名>)
--累计求平均值
avg(列名) over (partition by <用于分组的列名>
order by <用于排序的列名>)
代码:
select 雇员编号,薪水,
sum(薪水) over (order by 雇员编号) as 累计薪水
from 薪水表
where 结束日期 = '9999-01-01'
order by 雇员编号
查询结果: