题解 | #每月及截止当月的答题情况#
每月及截止当月的答题情况
http://www.nowcoder.com/practice/1ce93d5cec5c4243930fc5e8efaaca1e
思路一：新增月活判断标准：本月=首月
思路二：新增月活判断标准：本月在月份中的排序=1
方法一
分析
- 两次聚合窗口函数的分类标准不一致
新增月活判断标准：本月=首月——以uid分类，min()over
累计最大和累计总和：以start_month分类，max()over,sum()over()
- 去重+窗口会报错
- 分层，中层放distinct和group by，内外不g
实现
- 1.取uid，本月月份和首月月份，分类标准为uid
select uid,date_format(start_time,'%Y%m') as start_month,
min(date_format(start_time,'%Y%m')) over (partition by uid)
as first_month
from exam_record
- 2.取每月月活及新增月活，分类标准为start_month
select start_month,count(distinct uid) as mau,
count(distinct if(first_month=start_month,uid,null)) as month_add_uv
from
(select uid,date_format(start_time,'%Y%m') as start_month,
min(date_format(start_time,'%Y%m')) over (partition by uid)
as first_month
from exam_record
) as q1
group by start_month
- 取累计最大与累计总和，分类标准为start_month
select start_month,mau,month_add_uv,
max(month_add_uv) over (order by start_month) as max_month_add_uv,
sum(month_add_uv) over (order by start_month) as cum_sum_uv
from
(select start_month,count(distinct uid) as mau,
count(distinct if(first_month=start_month,uid,null)) as month_add_uv
from
(select uid,date_format(start_time,'%Y%m') as start_month,
min(date_format(start_time,'%Y%m')) over (partition by uid)
as first_month
from exam_record
) as q1
group by start_month
) as q2
方法二
分析——内层排序，外层聚合+聚合窗口
区别于方法一，排序=1无需考虑去重问题
实现
select start_month,count(distinct uid) as mau,
count(if(rn=1, uid, null)) as month_add_uv,
max(count(if(rn=1, uid, null))) over(order by start_month)
as max_month_add_uv,
sum(count(if(rn=1, uid, null))) over(order by start_month)
as cum_sum_uv
from
(select uid, date_format(start_time, '%Y%m') as start_month,
row_number() over(partition by uid order by start_time) as rn
from exam_record) as q
group by start_month
