题解 | #每月及截止当月的答题情况#

每月及截止当月的答题情况

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
  1. 取累计最大与累计总和,分类标准为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
全部评论

相关推荐

牛客765689665号:没有实习是硬伤,央国企看学历
点赞 评论 收藏
分享
一天代码十万三:实习东西太少了,而且体现不出你业务,3个月不可能就这点产出吧,建议实习多写点,玩具项目面试官都不感兴趣的
点赞 评论 收藏
分享
评论
1
收藏
分享

创作者周榜

更多
牛客网
牛客企业服务