SQL练习（3）牛客-数据分析50-59

一、窗口函数（思考过程）

1、SQL58 任意两个连续自然月练题次数大于1的用户

思路：
1、聚合函数取出每个用户每个月的练题次数>1的数据
2、窗口函数、时间相关函数计算用户练题月份差=1

select distinct device_id
from(
    select device_id,ym,
    lag(ym,1)over(partition by device_id order by ym) as last_ym,
    #最后
    timestampdiff(month,concat(lag(ym,1)over(partition by device_id order by ym), '-01'),concat(ym, '-01' )) as monthdiff
    # 窗口函数lag(ym, 1)over(partition by device_id order by ym)求出计算上一个月份last_ym
    # 时间函数timestampdiff(month, concat(lag(ym, 1)over(partition by device_id order by ym), '-01'), concat(ym, '-01'))求出当月ym和上一个月last_ym之间的月份差monthdiff。
    # timestampdiff()函数需要用日期计算时差，而求出的ym和last_ym只有年月没有日期，所以无法计算月份差
    # concat()连接字符串函数将月份和日期连接，每个月取‘01’号即可。
    from(
        select device_id, substring(event_date,1 ,7 ) as ym,
        count(question_id) as question_cnt
        from question_practice_detail
        group by device_id, ym
        having question_cnt > 1
    ) as t1
    # 执行1 取出每个用户一个月练题次数大于1的数据
) as t2
where monthdiff = 1
# 执行2 月份差等于1==连续两个自然月都有练题
order by device_id desc

2、SQL59 截至当月的练题情况

思路：
1、先算每个人当月做题量
2、用窗口函数over聚合partition计算截至当月的总做题数，所有人当月的总做题数，每个人三个月的平均做题数

select device_id,
ym,
sum(question_cnt)over(partition by device_id order by ym) as sum_cnt,
# 按设备聚合，求答题数之和
round(sum(question_cnt)over(partition by device_id order by ym rows 2 preceding)/count(ym)over(partition by device_id 
# 每个人的平均做题数=（每个人做题总和/月份）
# rows 2 procedding语句将当前行和它前面的两行划为一个窗口
# round(sum总题数 / count月份, 2)保留两位小数。
order by ym rows 2 preceding),2) as avg3_cnt,
sum(question_cnt)over(order by ym) as total_cnt
from(
    select distinct device_id,
    substr(event_date,1,7) as ym,
    count(question_id) as question_cnt
    from question_practice_detail
    group by device_id,ym
    # 聚合求每个人的每个月做题数量
)as q1
group by device_id, ym
order by device_id asc,ym asc

二、文本函数

1、SQL57 最后一个匹配上的位置（思考过程）

思路：最后一个匹配=字符逆序reverse后第一个匹配
最后一个匹配索引=字符长度-逆序索引-1

【在str中查找substr的索引值】

1. locate(substr, str, start_pos) ：从起始位置start_pos开始。返回index索引值。
2. instr(str, substr)：与locate()函数的参数位置相反。返回index索引值。
3. position(substr in str)：返回index索引值。
4. 扩展知识：find_in_set(substr, str)：str需要以逗号分隔。返回index索引值。 【根据索引在str中截取substr】
5. substring(str, index)：根据index值截取str中的部分字符串。 【字符串逆序函数】
6. reverse(str)：输出字符串逆序。

代码：

select device_id,
length(nick_name)-locate('cba', reverse(nick_name))-1 as last_idx,
substring(nick_name,(length(nick_name)-locate('cba', reverse(nick_name))-1)) as check_str
from user_submit
where nick_name like '%abc%'
order by device_id desc

2、SQL 52 每月各旬有多少人练题（case、concat、substr用法）

select 
concat(
    concat(substr(event_date,1,4),'年'),
    # concat()连接字符串
    concat(substr(event_date,6,2),'月'),
    # substr()截取字符串
    (case 
        when day(event_date) between 1 and 9 then '上旬'
        when day(event_date) between 10 and 19 then '中旬'
        else '下旬'
    end)
) as dt_range,
count(distinct device_id) as cnt
from question_practice_detail
group by dt_range
order by  substr(dt_range,6,2) desc,cnt desc

3、SQL55 用户名规范化(concat相关用法)

1. concat(str1, str2)连接两个字符串。
2. upper(str)和lower(str)是取大小写字母的两个函数。
3. substr(str, pos, len)截取字符串。从pos位置开始对str截取len长度。
4. substring_index(str, delim, count)截取字符串，delim是所使用的分隔符。count为正数时，对str截取从左往右直到第count个分隔符，count为负数时，对str截取从右往左直到第count个分隔符。

select device_id,
concat(
    upper(substr(substring_index(blog_url,'/',-1),1,1)),
    lower(substr(substring_index(blog_url,'/',-1),2))
) as user_name
from user_submit
order by device_id desc

4、SQL56 博客地址修复（concat、substring_index、replace用法）

1. trim(str)函数用于删除字符串两边的空格。ltrim(str)删除左边空格，rtrim(str)删除右边空格。
2. substring_index(str, delim, count)函数用于截取字符串。delim是分隔符，count的正负代表从左或者从右开始数。
3. concat(str1, str2)将两个字符串拼接。
4. replace(str, old_str, new_str)用于将str中的old_str部分用new_str替换。本题不能用replace()函数，因为blog_url中无论是双斜杠还是单斜杠，始终都存在单斜杠‘http:/’，因此无法判断到底整个字符串是否是单斜杠。

select device_id,
concat(
    'http://',substring_index(trim(blog_url),'/',-2)
    ) as blog_url
from user_submit

三、case用法

1、SQL50 SQL50 各年级学生的平均绩点（case…end 用法）

1、按年级分组
2、case写判别条件
3、计算平均绩点

select 
(case
when graduate_year=2025 then '大一'
when graduate_year=2024 then '大二'
when graduate_year=2023 then '大三'
when graduate_year=2022 then '大四' 
else '其它' end ) as grade,
round(avg(gpa),2) as avg_gpa
from user_profile
group by grade
having grade !='其它'
order by avg_gpa desc

四、时间相关函数

1、SQL53 用户每月最后一周的练题数(时间相关函数date_format、datediff、last_date)

date_format(date, format)函数用于以不同的格式显示日期/时间数据
datediff(datepart, startdate, enddate)函数用于计算两个函数的差值。
本题求最后一天与当前日期之差，小于7即为当月最后7天，用where语句作为筛选条件。
last_day(time)函数返回指定日期对应月份的最后一天。

select 
    device_id,
    date_format(event_date, '20%y年%m月') as ym,
    count(question_id) as cnt
from question_practice_detail
where datediff(last_day(event_date),event_date)<7

group by device_id,ym
order by device_id,ym