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

• MIN()OVER() :不改变表结构的前提下，计算出最小值
• MAX()OVER():不改变表结构的前提下，计算出最大值
• COUNT()OVER():不改变表结构的前提下，计数
• SUM()OVER():不改变表结构的前提下，求和
• AVG()OVER():不改变表结构的前提下，求平均值

• percent_rank() over() 按照数字所在的位置进行百分位分段
• ntile(n)over() 将数字按照大小平均分成n段
• lead(字段名，n)over()把字段数据向前移n个单元格
• lag(字段名，n)over()把字段数据向后移n个单元格

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：新增用户的定义——首次登录的用户，也就是把用户最早登录的那天定义为首次登录日期。
• 坑2：截止当月的单月最大新增用户数：按照月份依次对比每个月的新增用户数的大小取大值用MAX()OVER(order by start_month)
• 坑3：截止当月的累积用户数：按照月份依次累加新增用户数用SUM()OVER(ORDER BY  start_month)

• 先查每一个用户的首次登录日期

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

• 月活用户需要去重COUNT(DISTICT uid) mau

• 对每天的新增用户进行求和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
;

• 使用窗口函数按月依次查找最大每个月最大的新增用户数MAX(month_add_uv)OVER(order by start_month)

• 使用窗口函数按月累加新增用户数SUM(month_add_uv)OVER(order by start_month)

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;