【场景】：平均活跃天数和月活人数 【分类】：分组查询、count（distinct 2个字段） 分析思路 难点： 1.如何计算活跃天数？对日期、用户去重 如何计算平均活跃天数 可能存在的情况： 一、2个不同用户在同一天有记录，活跃天数为2，活跃人数为2。 二、2个不同用户在不同天各有一条记录，活跃天数为2，活跃人数为2。 三、1个用户在同一天有2条记录，活跃天数为1，活跃人数为1。 使用count(distinct day(submi_time))计算活跃天数，在情况一中得到活跃天数为1，与题目要求不符； 使用count(day(submi_time))计算活跃天数，在情况三中得到活跃天...

【场景】：每个学校不同性别的用户 【分类】：分组计算 分析思路 select 查询结果 [性别;学校;count(设备ID) as 用户数;avg(30天内活跃记录) as 平均活跃天数;avg(发帖记录) as 平均发帖数] from 从哪张表中查找数据 [user_profile] group by 分组 [学校;性别] 求解代码 方法一： select gender, university, count(device_id) as user_num, avg(active_days_within_30) as avg_active_day, av...

【场景】：每个学校用户的平均 【分类】：分组过滤 分析思路 select 查询结果 [学校,avg(发帖) as 平均发帖数;avg(回帖) as 平均回帖数] from 从哪张表中查找数据 [user_profile] group by 分组 [学校] having 对分组结果指定条件 [平均发帖数小于5或者平均回帖数小于20] 求解代码 方法一： select university, avg(question_cnt) as avg_question_cnt, avg(answer_cnt) as avg_answer_cnt from user_profile ...

【场景】：不同大学的用户平均发帖情况 【分类】：分组排序 分析思路 select 查询结果 [学校;avg(答题记录) as avg_question_cnt] from 从哪张表中查询数据[user_profile] group by 分组条件[学校] order by 对查询结果排序[平均发帖情况升序] 求解代码 方法一： select university, avg(question_cnt) as avg_question_cnt from user_profile group by university order by avg_question_cnt

【场景】：某个条件下做统计 【分类】：where 数据 > select 条件 分析思路 难点： 1.理解题目要求的是什么 （1）得到SQL试卷的平均分 [条件]：where tag = 'SQL' [条件]：avg(score) （2）找到得分不小于平均分的最低分 [条件]：score >= score_avg [使用]：min(score) 或者 正序取第一条数据 求解代码 方法一： with子句 with main as( #得到SQL试卷的平均分 select avg(score) ...

【场景】：不同条件下做统计 【分类】：select if、多表连接 分析思路 难点： 1.在不同的条件下使用count做统计 在不同的条件下做统计 方法一： where 中写条件，每次只能得到一列，分别得到每列，然后用join做连接 方法二： 使用select if，把条件写在select里面 作答次数 [条件]： count(idid) 已完成的作答次数 [条件]： count(if(submit_time is not null and score is not null, 1, null)) 已完成的试卷份数 [条件]：count(distinct if(submi...

【场景】：截断平均值 【分类】：可能存在多个最值的情况下，如何只去掉一个最值、窗口函数rank()；去掉正序排和倒序排的第一行 分析思路 难点： 1.如何去掉最大值和最小值(去掉一个最大值和一个最小值后的平均值) 如何去掉最大值和最小值 [使用]：在where 中使用 select max 和min，如果有多个最大值和最小值，这种方法计算均值的时候去掉的不止一个最大值和最小值。所以此方法不适用。 [使用]：窗口函数rank()；去掉正序排和倒序排的第一行。 判断不等于 [使用]：<> 或者 != 求解代码 错误代码 使用 where select select ...

【分类】：分组查询、字符串截取函数 分析思路 难点： 1.如何截取出性别的字段 如何截取出性别的字段 [使用]：substring_index（按照分隔符截取子字符串） [使用]：like；因为性别只有2个数，不是 female 就是 male，相当于定值，如果是年龄这种连续的字段，是不定值，不能用like 扩展 前往查看：MySQL 字符串截取 最终结果 select 查询结果 [substring_index(字段) as 性别;count(设备ID) as 人数] from 从哪张表中查询数据[user_submit] group by 分组条件[性别] 求解代码 方法一： 使...

【分类】：字符串截取函数 分析思路 难点： 1.如何截取出url中的用户名 如何截取出url中的用户名 [使用]：substring_index（按照分隔符截取子字符串） 扩展 前往查看：MySQL 字符串截取 最终结果 select 查询结果 [设备ID;substring_index(字段) as 用户名称] from 从哪张表中查询数据[user_submit] 求解代码 select device_id, substring_index(blog_url, '/', -1) as user_name from user_submit

【分类】：分组查询、字符串截取函数 分析思路 难点： 1.如何截取出年龄的字段 如何截取出年龄的字段 [使用]：substring_index（按照分隔符截取子字符串）；**注意：**需要多次截取 扩展 前往查看：MySQL 字符串截取 最终结果 select 查询结果 [substring_index(字段) as 年龄;count(设备ID) as 人数] from 从哪张表中查询数据[user_submit] group by 分组条件[年龄] 求解代码 方法一： with子句 + 一步步拆解 select distinct substring_index(substr...

【场景】：近几天活跃 【分类】：条件函数、日期函数、datediff 分析思路 难点： 1.这个题有一个坑题目中没有说清楚，沉睡用户(近7天未活跃但更早前活跃过)，根据定义它是包含流失用户(近30天未活跃但更早前活跃过)，也就是说只要是流失用户就是沉睡用户！实际上答案并不包括。所以沉睡用户应该这样定义：近7天未活跃但更早前活跃过且非流失用户。 2.case when 判断使每个类型的用户不会有重合。而且先判断哪个后判断哪个尤其重要，顺序不能换！ 新学到： 1.用户最近一次活跃时间：max(date)；一直没有想到，其实在之前是知道求用户第一次活跃时间是用：min(date)。如果使用这个会省...

【场景】：次日留存率 【分类】：多表连接、date_sub(dt, interval 1 day) = min_dt 分析思路 难点： 1.怎么得到第一天的用户第二天又有浏览记录 2.第一天有用户活跃第二天没有用户活跃，怎么处理？使用 right join ,以有新增用户的表为主。 新学到： 1.统计2021年11月： like '2021-11%' （1）统计用户的活跃记录 如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过 [使用]：union把两个活跃记录合并 （2）统计新增用户 新用户：第一次进入时间(天)就是该时间新增用户 [使用]：...

【场景】：同一时刻在线人数 【分类】：窗口函数、tag标记、嵌套子查询 分析思路 难点： 1.同一时刻的在线人数 区别于 某个时刻之前的在线人数 （1）用tag标记增加还是减少 进入增加1，出去减少1 [使用]：union；1 as tag；-1 as tag （2）统计每个时刻的在看人数 如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，所以在窗口函数中使用sum()需要order by dt,tag desc [使用]：sum(tag) over (partition by artical_id order by dt,tag desc) （3）求在看人数的最大值 [...

【场景】：每天人均 【分类】：嵌套子查询、日期函数、month、year、date、date_format 分析思路 难点： 1.难点：计算人均浏览时长时，可能一个人当天浏览多条记录，所以需要对用户去重后统计个数 （1）统计2021年11月每天的人均浏览文章时长（秒数），结果保留1位小数，并按时长由短到长排序。 ​ [条件]：where artical_id <> '0' and month(in_time) = 11 and year(in_time) = 2021 ​ [使用]：group by dt；order by avg_viiew_len_sec；年月日使用da...