sql案例练习

注：参考公众号“猴子数据分析”中的相关教程。

题目一：
"课程订单表”里记录了某在线教育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 雇员编号

查询结果：