Mysql刷题记录

SQL25 查找山东大学或者性别为男生的信息

要求：分别查看 且 结果不去重
分别查看表明了不能使用 or。or的话会合并既是山东大学也是性别为男的数据，无法做到分别查看。故考虑union 或者union all。
由于要求是结果不去重
union 和 union all的区别：前者对结果去重，后者不去重
因此 使用union all。

select 
    device_id, gender, age, gpa
from user_profile
where university='山东大学'

union all

select 
    device_id, gender, age, gpa
from user_profile
where gender='male'

SQL26 计算25岁以上和以下的用户数量

知识点：case函数的使用

Q：说一下mysql 中的case函数
case函数是一种多分支函数，可以根据条件列表的值返回多个可能的结果表达式中的一个。
分为 ：简单case函数、搜索case函数
区分：case后是否跟了测试表达式，如果跟了测试表达式，则比对该表达式和when后的表达式。如果没跟测试表达式，则直接判断when后的布尔表达式。
简单case函数：

case 测试表达式
when 简单表达式1 then 结果表达式1
when 简单表达式2 then 结果表达式2
else 简单表达式3
end 

eg.

case 性别
when 0 then '男'
when 1 then '女'
else '不男不女'
end

搜索case函数：

case
when 布尔表达式1 then 结果表达式1
when 布尔表达式2 then 结果表达式2
else 结果表达式3
end 

eg.

case 
when sex = 0 then '男'
when sex = 1 then '女'
else '不男不女'
end as sexx

本题答案：

SELECT CASE WHEN age < 25 OR age IS NULL THEN '25岁以下' 
            WHEN age >= 25 THEN '25岁及以上'
            END age_cut,COUNT(*)number
FROM user_profile
GROUP BY age_cut

SQL28 计算用户8月每天的练题数量

知识点：year(date字段) month(date字段) day(date字段) 函数
eg. date = 2021-08-13
year(date) = 2021, month(date) = 8, day(date) = 13

select
    day(date) as day,
    count(question_id) as question_cnt
from question_practice_detail
where month(date)=8 and year(date)=2021
group by date

SQL29 计算用户的平均次日留存率

平均次日留存率
题目：用户再某天刷题后第二天再来刷题的平均概率
思路分析：整理数据，获取该用户第二天来刷题的结果，第二天刷题/第一天刷题数
知识点：
partition by 用法
partition by 字段 ：根据某个字段将数据分块，然后可以对该分块数据再做查询。通常使用场景用于整理数据，分块后对该块数据进行order by 排序。结合lead获取该块中的某行数据，结合row_number() 、rank() over、dense_rank()获取行数
如果是group by ，select语句中只能是分组的字段或者是某个聚合函数。
通常结合row_number() over使用，对分组后的数据进行顺序连续排序 比如 123456
eg.
select province, city,persons, row_number() over(partition by province order by persons)
rank() over 跳跃排序，如果又并列第一个会直接跳到第三个 比如1224
dense_rank() 连续排序，如果又两个第一级别任然从第二级别开始 比如 1223

lead 用法
lead(字段名称，行数，默认值) 第一个参数就是取该字段的值，第二个参数是要取后面的第几行数据（一般会结合partition by 使用，分区排序以后 通过lead取某区需要的行），该字段可以省略，省略就等同于1，也就是取后面一行的数据；第三个参数是指如果已经到最后一行了，需要给一个默认值，如果该字段省略了，就等同于null。

date_add用法
给日期增加指定的时间间隔
eg. date_add(date, interval 1 day)
datediff用法
返回两个日期之间的天数
eg. datediff(date1,date2)
SELECT DATEDIFF(‘2008-12-30’,‘2008-12-29’) AS DiffDate 结果就是1

本题答案：
解法1

select
    count(date2) / count(date1) as avg_ret
from
    (
        select distinct
            t1.device_id,
            t1.date as date1,
            t2.date as date2
        from
            question_practice_detail as t1
            left join (
                select distinct
                    device_id,
                    date
                from
                    question_practice_detail
            ) as t2 on t1.device_id = t2.device_id
            and date_add(t1.date, interval 1 day) = t2.date
    ) as tt

解法2

select
    avg(if (datediff (t2.date2, t2.date1) = 1, 1, 0)) as avg_ret
from
    (
        select distinct
            device_id,
            date as date1,
            lead (date, 1, null) over (
                partition by
                    device_id
                order by
                    date
            ) as date2
        from
            (
                select distinct
                    device_id,
                    date
                from
                    question_practice_detail
            ) t1
    ) as t2

SQL30 统计每种性别的人数

知识点：字符串截取 substring_index(str,delim,count)
str：要处理的字符串
delim：分隔符
count：计数（正数是从左到右，负数是从右到左）

本题答案

select substring_index(profile,',',-1) as gender, count(*) 
from user_submit group by gender 

各个连接：