题解 | #每个6/7级用户活跃情况#

每个6/7级用户活跃情况

http://www.nowcoder.com/practice/a32c7c8590324c96950417c57fa6ecd1

又是一道我觉得很烧脑细胞的题目

有看过我之前写的题解思路的话，其实这道题还是一样，对数据表做简化

题目要求是如下：

很快你就发现，这个要求根本用不上examination_info这个考试信息表，跟您说拜拜勒

那么我们针对其他表一一简化下：

首先是用户表user_info，用户这里是筛选6级和7级这两类，因此用一个where条件即可快速实现

select
    uid
from user_info
where `level` between 6 and 7

其实就是exam_record和pratice_record表啦。要求这里有个雷就是要统计用户总活跃月份数。这里并没有明确说2021年，也意味着2021年以外的记录也要统计进去。

因此这里只能做一些小的简化，例如将submit_time拆分成年，月，日期。

select
    distinct
        uid,
        Year(submit_time) as e_year,
        Month(submit_time) as e_mon,
        substring_index(submit_time, ' ', 1) as e_day
from exam_record

select
    distinct
        uid,
        Year(submit_time) as e_year,
        Month(submit_time) as e_mon,
        substring_index(submit_time, ' ', 1) as e_day
from practice_record

剩下的就是组装问题啦，那很多人到了这步，是不是很快就想到了用连接(join)的方式去将这三个SQL关联在一起呢。

我一开始也是如此，于是就写了这个SQL语句以便观察怎么实现接下来的统计：

select
    user_info.uid,
    e_year,
    e_mon,
    e_day,
    p_year,
    p_mon,
    p_day
from user_info

left join

(select
    distinct
        uid,
        Year(submit_time) as e_year,
        Month(submit_time) as e_mon,
        substring_index(submit_time, ' ', 1) as e_day
from exam_record) t using(uid)

left join

(select
    distinct
        uid,
        Year(submit_time) as p_year,
        Month(submit_time) as p_mon,
        substring_index(submit_time, ' ', 1) as p_day
from practice_record) t2 using(uid)
where `level` between 6 and 7

可到了后面发现了一个问题：怎么多字段统计总活跃天数呢？怎么多字段统计活跃月份数呢？又怎么去做去重判断呢？

这里我卡壳了，想过直接用count()相加，但无法去重。也想过多字段distinct看是否行得通，也不行。

最后我想了下，要不将其拆分开，于是就想到了union这个关键字啦

union大概作用是将多个查询结果进行合并，因此这道题我觉得必须得用它

因此重写后的SQL语句如下：

select
    distinct
        uid,
        Year(submit_time) as e_year,
        Month(submit_time) as e_mon,
        substring_index(submit_time, ' ', 1) as e_day
from exam_record

union all

select
    distinct
        uid,
        Year(submit_time) as e_year,
        Month(submit_time) as e_mon,
        substring_index(submit_time, ' ', 1) as e_day
from practice_record

这样到了后续也就有了很清晰的思路啦。BUT 我又遇到了新的难题，就是题目还得要求统计2021年试卷作答活跃天数，2021年答题活跃天数。而现在合在一起了，无法直接统计

那有没有办法去解决这个问题呢？答案肯定是有的，其实只要加一个标记区分就可以轻松实现了，即在exam_record内加'exam'这个tag，然后再pratice_record内加上'pra'这个tag即可（tag名字你可以自由DIY）

最后为最终SQL

select
    user_info.uid,
    count(distinct e_year, e_mon) as act_month_total,
    count(distinct if(e_year = 2021, e_day, null)) as act_days_2021,
    count(distinct if(e_year = 2021 and e_tag = 'exam', e_day, null)) as act_days_2021_exam,
    count(distinct if(e_year = 2021 and e_tag = 'pra', e_day, null)) as act_days_2021_question
from user_info

left join

(select
    distinct
        uid,
        'exam' as e_tag,
        Year(submit_time) as e_year,
        Month(submit_time) as e_mon,
        substring_index(submit_time, ' ', 1) as e_day
from exam_record

union all

select
    distinct
        uid,
        'pra' as e_tag,
        Year(submit_time) as e_year,
        Month(submit_time) as e_mon,
        substring_index(submit_time, ' ', 1) as e_day
from practice_record) t using(uid)
where `level` between 6 and 7
group by user_info.uid
order by act_month_total desc, act_days_2021 desc