一、考总结拓展&完整代码

 题设的考点通篇围绕这几个聚合窗口函数以及结合GROUP BY函数的使用。

1） 聚合窗口函数
  • MIN()OVER() :不改变表结构的前提下，计算出最小值
  • MAX()OVER():不改变表结构的前提下，计算出最大值
  • COUNT()OVER():不改变表结构的前提下，计数
  • SUM()OVER():不改变表结构的前提下，求和
  • AVG()OVER():不改变表结构的前提下，求平均值
2）排序窗口函数
  • percent_rank() over() 按照数字所在的位置进行百分位分段
  • ntile(n)over() 将数字按照大小平均分成n段
  • lead(字段名，n)over()把字段数据向前移n个单元格
  • lag(字段名，n)over()把字段数据向后移n个单元格
3）完整代码 
SELECT start_month ,#每个月     COUNT(DISTINCT uid) mau, #月活用户数   
    SUM(new_day) month_add_uv, #新增用户
    MAX(SUM(new_day))OVER(ORDER BY start_month) max_month_add_uv, #截止当月的单月最大新增用户数
    SUM(SUM(new_day))OVER(ORDER BY start_month) cum_sum_uv
FROM (
    SELECT *,DATE_FORMAT(start_time,'%Y%m') start_month,     IF(start_time=MIN(start_time)OVER(PARTITION BY uid),1,0) new_day
    FROM exam_record)t1
GROUP BY start_month;

二、题目解读与解题步骤拆分

1、题目解读

求：请输出自从有用户作答记录以来，每月的试卷作答记录中月活用户数、新增用户数、截止当月的单月最大新增用户数、截止当月的累积用户数。结果按月份升序输出

题目中隐藏的坑&难点
  • 坑1：新增用户的定义——首次登录的用户，也就是把用户最早登录的那天定义为首次登录日期。
  • 坑2：截止当月的单月最大新增用户数：按照月份依次对比每个月的新增用户数的大小取大值用MAX()OVER(order by start_month)
  • 坑3：截止当月的累积用户数：按照月份依次累加新增用户数用SUM()OVER(ORDER BY  start_month)

需求字段：


  • start_month ：月份
  • mau ：月活用户数
  • month_add_uv ：新增用户数
  • max_month_add_uv：截止当月的单月最大新增用户数
  • cum_sum_uv：截止当月的累积用户数


2、步骤拆分

思路比较简单，就是先处理新用户数据，再用窗口函数进行各种查询。
先基于原表，新增一列对新用户的定义列：定义新增用户“首次登录日取1，其他日期取0”，建立子表t1。再基于t1的基础上查询月活用户数，新增用户数，截止当月的单月最大新增用户数，截止当月的累积用户数。

三、步骤代码

1）定义新用户
  • 先查每一个用户的首次登录日期 
MIN(start_time)OVER(PARTITION BY uid) AS new_user_day #第一天登录
  • 定义新用户 
IF(start_time = new_user_day,1,0) AS new_day # 若用户的登录时间等于首次登录时间则定义为新用户，新用户被标记为1，其他标记为0。
IF(start_time=MIN(start_time)OVER(PARTITION BY uid),1,0) AS new_day #完整的表达
  • 新用户被定义出来后运行该代码可得下表 
SELECT *,DATE_FORMAT(start_time,'%Y%m') start_month,
	IF(start_time=MIN(start_time)OVER(PARTITION BY uid),1,0) new_day
FROM exam_record
#子表命名为t1


2）统计月活用户
  • 月活用户需要去重COUNT(DISTICT uid) mau
3）统计月新增用户
  • 对每天的新增用户进行求和SUM(new_day) month_add_uv 
SELECT start_month ,#每个月
	COUNT(DISTINCT uid) mau, #月活用户数   
    SUM(new_day) month_add_uv #新增用户
FROM (
    SELECT *,DATE_FORMAT(start_time,'%Y%m') start_month,
	IF(start_time=MIN(start_time)OVER(PARTITION BY uid),1,0) new_day
    FROM exam_record)t1
GROUP BY start_month
;

4）查找截止当月的单月最大新增用户数
  • 使用窗口函数按月依次查找最大每个月最大的新增用户数MAX(month_add_uv)OVER(order by start_month)
5）统计截止当月的累积用户数
  • 使用窗口函数按月累加新增用户数SUM(month_add_uv)OVER(order by start_month)
6）按月份升序输出&完整代码
SELECT start_month ,#每个月     COUNT(DISTINCT uid) mau, #月活用户数   
    SUM(new_day) month_add_uv, #新增用户
    MAX(SUM(new_day))OVER(ORDER BY start_month) max_month_add_uv, #截止当月的单月最大新增用户数
    SUM(SUM(new_day))OVER(ORDER BY start_month) cum_sum_uv
FROM (
    SELECT *,DATE_FORMAT(start_time,'%Y%m') start_month,     IF(start_time=MIN(start_time)OVER(PARTITION BY uid),1,0) new_day
    FROM exam_record)t1
GROUP BY start_month;