思路一：新增月活判断标准：本月=首月

思路二：新增月活判断标准：本月在月份中的排序=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

3. 取累计最大与累计总和，分类标准为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