【场景】：截止某个时间段的统计结果、每月新增用户数 【分类】：聚合窗口函数 分析思路 难点： 1.如何求每月新增用户 2.截至当月的答题情况 （1）统计每个人的首次登陆月份 [使用]：min() （2）统计每月的月活和新增用户数 先得到每个人的首次登陆月份，再对首次登陆月份分组求和是该月份的新增人数, 例如： ​ 1月有用户1002；2月有用户1002、1003、1004；新增用户数为2； ​ 1月有用户1001；2月由用户1002、1003、1004；新增用户数为3。 求每月的月活根据exam_record表中的用户；求每月的新增用户根据上面的表中的用户 [使用]：...

【场景】：min-max归一化 【分类】：聚合函数、嵌套子查询 分析思路 （1）统计每个试卷的min(score)和max(score)-min(score) [条件]：where difficulty = hard [使用]：group by （2）统计执行min-max归一化后的数据 如果分组之后最大值等于最小值，即为某个试卷作答记录中只有一个得分 [条件]：where score is not null （3）最终根据uid,exam_id分组求均值得到new_score_avg，按照试卷ID升序、归一化分数降序输出 [使用]：group by uid,exam_id； or...

【场景】：等级值百分比 【分类】：专用窗口函数、多表连接 分析思路 难点： 长整型的数据类型要求不能有负号产生，用cast函数转换数据类型为signed。 （1）统计2020年上半年各类试卷的做完次数 [条件]：where date(start_time) between '20200101' and '20200630' [使用]：group by （2）统计2021年上半年各类试卷的做完次数 [条件]：where date(start_time) between '20210101' and '20210630' [使用]：group by （3）统计2020上半年做完次数排名...

【场景】：等级值百分比、近几个月 【分类】：专用窗口函数、嵌套子查询 分析思路 难点： 统计SQL试卷用户未完成率中的百分比排位使用percent_rank() （1）统计SQL试卷未完成率 [条件]：tag = 'SQL' [使用]：join ;count(); if() （2）统计SQL试卷用户未完成率中的百分比排位 [使用]：percent_rank() （3）统计SQL试卷用户未完成率中的百分比排位中大于50%，并且是6级和7级用户有试卷作答记录的近三个月 [使用]：dense_rank()；date_format(start_time,'%Y%m') （4）最终统计每...

【场景】：时间窗 【分类】：专用窗口函数、嵌套子查询 分析思路 难点： 1.如何求窗口期：使用lag()或者lead()，再分组求最大值 2.作答试卷不去重 （1）统计2021年至少有两天作答过试卷的人 [条件]：where year(start_time) = 2021 group by uid having count(date(start_time)) >= 2 [使用]：count()、max()、min() （2）得到平均每天作答试卷数、当前行日期和当前行的前一行日期 [使用]：timestampdiff(day,min_date,max_date); 当前...

【场景】：存在空值 【分类】：空值处理、if 函数、select条件语句 分析思路 难点： 1.查询结果一个有条件限制一个没有条件限制，怎么查询？两种方法 一是使用where条件分别查询，最后做表连接（列合并）； 二使用select条件语句，在select查询结果中进行条件判断。 （1）统计作答记录 [使用]：count ... group by exam_id （2）统计作答未完成记录 [条件]：score is null （3）统计有未完成状态的试卷的未完成数incomplete_cnt和未完成率incomplete_rate，并按exam_id升序 [使用]： incom...

【场景】：与某行数据的差 【分类】：专用窗口函数、嵌套子查询 分析思路 难点： 1.查询第二慢和第二快的（一个正序排名第二，一个倒序第二），nth_value(expr,n)可以取expr列第n行的数两者求差，另一种方法是排序之后向下平移一位再做差。 2.换算成秒来计算作答时间 （1）统计试卷每次作答的时间（秒） ​ [条件]：submit_time is not null ​ [使用]：timestampdiff(second,start_time,submit_time) （2）查询第二慢和第二快的用时记录（一个正序排名第二，一个倒序第二） ​ [使用]：nth_value(se...

【场景】：近一个月 【分类】：聚合函数、日期函数、限量查询、date_sub 分析思路 难点： 1.近一个月的数据变化情况 （1）计算当天日期和近一个月的开始日期 [条件]：当天就是最大日期 [使用]：date(end_time)；date(date_sub(end_time,interval 29 day))；limit 1 （2）统计被播放次数，完成播放次数，被点赞次数，评论次数，转发次数，最近无播放天数 [条件]：release_time >= date_min [使用]：count；sum；min （3）按照公式查询找出近一个月发布的视频中热度最高的t...