一、知识点汇总与拓展

把有用的知识写在前面，以方便自个儿复习观看😊

1）查看每一个用户的情况，一般用GROUP BY 配合聚类函数进行查询。

a.group by的常见搭配：常和以下聚合函数搭配 

  •     avg（）-- 求平均值
  •     count（）-- 计数
  •     sum（）-- 求和
  •     max（） -- 最大值
  •     min（）-- 最小值

b.group by 的进阶用法，和with rollup一起使用。 

2）常用的判断语句
a.简单条件判断语句
case when...（条件1）then...（结果A）else...(结果B) end 
if(条件1，结果A，结果B)
满足条件1则返回结果A，否则返回结果B
b.搜索式条件判断语句
case when 条件1 then 结果1
         when 条件2 then 结果2
         when 条件3 then 结果3
         ...
 else 结果n end
每个条件依次返回不同的结果

3）常用的时间函数

a.常用的截取时间的函数有：year（），month（），day（）
b.常用的时间差函数：timestampdiff(时间格式，开始时间，结束时间)
  • timestampdiff (minute,start_time,submit_time)<5
c.截取年月日等 date_format(时间字段，‘%年-%月-%日’)
  • date_format(submit_time,'%Y-%m-%d')
d.时间加减date_add(时间字段，interval n 时间格式)
  • date_add(start_time,interval 1 day) '加1天'

4)常用的连接
左连接：表1 left join 表2 on 表1.字段=表2.字段  （以表1为准，表2进行匹配）
右连接：表1 right join 表2 on 表1.字段=表2.字段  （以表2为准，表1进行匹配）
全连接：表1 union all 表2 （表1 和表2的列数必须一样多，union 去除重复项，union all 不剔除重复项）
内连接：表1 inner join 表2（取表1和表2相交部分）
外连接：表1 full outer join 表2 （取表1和表2不相交的部分）
ps：MYSQL 不支持外连接，可以用左右连接后再全连接代替

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

1、题目解读

题目：请统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数。按照总活跃月份数、2021年活跃天数降序排序。

坑1：总活跃月份数的字段需求是历史全量数据，其他的皆为2021年
坑2：要查看每一个6/7级用户的活跃情况，哪怕数据为空的数据也需要体现。

examination_info（试卷信息表）
user_info（用户信息表）
exam_record（试卷作答记录表）
practice_record（题目练习记录表）

2、解题步骤拆分

求6/7级用户的：总活跃月份数（不做年份限制），2021年活跃天数，2021年试卷作答活跃天数，2021年答题活跃天数

因为既要查看试卷作答表又要查看题目练习表，故而选择把试卷作答表和题目练习表进行全连接。
依次拆分每一个字段的需求
1）用户的总活跃月份数
     COUNT(DISTINCT mon_time) act_total -- 对每个用户的活跃月份数据进行去重后计数
2）2021年活跃天数
    a.截取‘%年-%月-%日’字段：exam_record表 date_format(start_time,'%Y-%m-%d') as day_time； practice_record表 date_format(submit_time,'%Y-%m-%d') as day_time
    b.year(day_time)=2021
3）2021年试卷作答活跃天数
    对试卷进行定义'exam' as type
4）2021年答题活跃天数
    对练习题进行定义'practice' as type
5）6/7级用户
    user_info表：uid IN（‘6’,‘7’）
6）按照总活跃月份数、2021年活跃天数降序排序。
总活跃月份数DESC、2021年活跃天数降序DESC

三、步骤代码

STEP1：拼接exam_record 和 practice_record

SELECT uid,date_format(start_time,'%Y-%m-%d') day_time,date_format(start_time,'%Y-%m') mon_time,'exam' as type
FROM exam_record
UNION ALL
SELECT uid,date_format(submit_time,'%Y-%m-%d') day_time,date_format(submit_time,'%Y-%m') mon_time,'practice' as type
FROM practice_record

组装后的表格

STEP2:各个字段定义代码

查看每个用户的数据情况，故而以uid为关键字段进行聚类查看。
SELECT uid，字段1,字段2,... FROM 表1 GROUP BY uid

1）用户的总活跃月份数

2）2021年活跃天数
COUNT( DISTINCT (CASE WHEN YEAR(day_time)='2021' then day_time ELSE NULL END)) act_2021 -- 对每个用户在2021年的活跃日数据进行去重后计数（比字段1多了个日期限制）
3）2021年试卷作答活跃天数
COUNT( DISTINCT (CASE WHEN YEAR(day_time)='2021' AND type='exam' then day_time ELSE NULL END)) act_exam -- 对每个用户在2021年的作答类型为试卷的活跃日数据进行去重统计（比字段2多了个类型限制）
4）2021年答题活跃天数
COUNT( DISTINCT (CASE WHEN YEAR(day_time)='2021' AND type='practice' then day_time ELSE NULL END)) act_practice -- 对每个用户在2021年的作答类型为答题的活跃日数据进行去重统计（和字段3相似）
5）6/7级用户
SELECT uid FROM user_info WHERE uid IN('6','7')

四、完整代码组装 

WITH t1 AS (
SELECT uid,date_format(start_time,'%Y-%m-%d') day_time,date_format(start_time,'%Y-%m') mon_time,'exam' as type
FROM exam_record
union all
SELECT uid,date_format(submit_time,'%Y-%m-%d') day_time,date_format(submit_time,'%Y-%m') mon_time,'practice' as type
FROM practice_record
)
/* 全连接 exam_record表和practice_record表，并重命名为t1*/
SELECT t2.uid,
	COUNT(DISTINCT mon_time) act_total,
    COUNT( DISTINCT (CASE WHEN YEAR(day_time)='2021' then day_time ELSE NULL END)) act_2021,
    COUNT( DISTINCT (CASE WHEN YEAR(day_time)='2021' AND type='exam' then day_time ELSE NULL END)) act_exam,
    COUNT( DISTINCT (CASE WHEN YEAR(day_time)='2021' AND type='practice' then day_time ELSE NULL END)) act_practice
FROM t1
RIGHT JOIN user_info  t2 ON t1.uid=t2.uid
/*因为要查看每一个6/7级用户的情况，故而右连user_info表*/
WHERE t2.level IN('6','7')
GROUP BY uid
ORDER BY act_total DESC,act_2021 DESC
/*按照总活跃月份数、2021年活跃天数降序排序*/
;