思路:
# 根据uid分组找到最小登录月
select uid,
date_format(min(start_time), '%Y%m') f_month
from exam_record
group by uid
上述也可用窗口函数构造单独的一列, 当找到最小登录月之后, 再根据月份分组, 就能知道每个月份的新增用户数
select f_month,
count(1) month_add_uv
from (
select uid,
date_format(min(start_time), '%Y%m') f_month
from exam_record
group by uid
) t1
group by f_month
最后再查月度活跃用户数
select date_format(start_time, '%Y%m') month,
count(distinct uid) mau
from exam_record
group by month
最后根据月份进行左连接, 使用窗口函数max() over() 即可的出结果
select t1.month,
t1.mau,
ifnull(month_add_uv, 0),
max(month_add_uv) over(order by t1.month),
sum(month_add_uv) over(order by t1.month)
from (
select date_format(start_time, '%Y%m') month,
count(distinct uid) mau
from exam_record
group by month
) t1
left join (
select f_month,
count(1) month_add_uv
from (
select uid,
date_format(min(start_time), '%Y%m') f_month
from exam_record
group by uid
) t1
group by f_month
) t2 on t1.month=t2.f_month