题解 | #每月及截止当月的答题情况#
每月及截止当月的答题情况
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