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

【场景】：截止某个时间段的统计结果、每月新增用户数

【分类】：聚合窗口函数

分析思路

难点：

1.如何求每月新增用户

2.截至当月的答题情况

（1）统计每个人的首次登陆月份

• [使用]：min()

（2）统计每月的月活和新增用户数

先得到每个人的首次登陆月份，再对首次登陆月份分组求和是该月份的新增人数, 例如：

​ 1月有用户1002；2月有用户1002、1003、1004；新增用户数为2；

​ 1月有用户1001；2月由用户1002、1003、1004；新增用户数为3。

求每月的月活根据exam_record表中的用户；求每月的新增用户根据上面的表中的用户

• [使用]：group by start_month；count(distinct a.uid)

（3）统计截止当月的单月最大新增用户数、截止当月的累积用户数 ，最终按照按月份升序输出

• [使用]：窗口函数 max(新增) over(order by 月份）
• [使用]：窗口函数 sum(新增) over(order by 月份)

求解代码

方法一

with子句

with 
    main as(
        #统计每个人的首次登陆月份
        select 
            uid,
            min(date_format(start_time,'%Y%m'))as first_month 
        from  exam_record
        group by uid
    )
    ,main1 as(
        #统计每月的月活和新增用户数
        select 
            date_format(a.start_time,'%Y%m') as start_month,
            count(distinct a.uid) as mau,
            count(distinct b.uid) as month_add_uv
        from exam_record a
        left join main b on date_format(a.start_time,'%Y%m') = b.first_month
        group by start_month
    )

#截止当月的单月最大新增用户数、截止当月的累积用户数，按月份升序输出
select
    start_month,
    mau,
    month_add_uv,
    max(month_add_uv) over(order by start_month),
    sum(month_add_uv) over(order by start_month)
from main1
order by start_month

main 表输出结果

1	1001|202001	
2	1002|202001	
3	1003|202002	
4	1004|202002	

main1 表输出结果

1	202001|2|2
2	202002|4|2
3	202003|3|0
4	202005|1|0	

方法二

from嵌套子查询

#截止当月的单月最大新增用户数、截止当月的累积用户数，按月份升序输出
select
    start_month,
    mau,
    month_add_uv,
    max(month_add_uv) over(order by start_month),
    sum(month_add_uv) over(order by start_month)
from(
    #统计每月的月活和新增用户数
    select 
        date_format(a.start_time,'%Y%m') as start_month,
        count(distinct a.uid) as mau,
        count(distinct b.uid) as month_add_uv
    from exam_record a
    left join(
        #统计每个人的首次登陆月份
        select 
            uid,
            min(date_format(start_time,'%Y%m'))as first_month 
        from  exam_record
        group by uid
    ) b on date_format(a.start_time,'%Y%m') = b.first_month
    group by start_month
) main
order by start_month