牛客sql_非技术快速入门

SQL23 统计每个学校各难度的用户平均刷题数

• 解法：

SELECT u.`university`,d.`difficult_level`,COUNT(1)/COUNT(DISTINCT u.`device_id`)
FROM user_profile u
LEFT JOIN question_practice_detail q ON u.`device_id`=q.`device_id`
LEFT JOIN question_detail d ON q.`question_id`=d.`question_id`
GROUP BY u.`university`,d.`difficult_level`
HAVING difficult_level IS NOT  NULL

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

• 解法一：

select (case 
	when age<25 or age is null then "25岁以下"
	when age>=25  then "25岁及以上"
	end) as age_cut,count(1) number
from user_profile
group by age_cut;

• 解法二：

select "25岁以下" age_cut,count(1) number
from user_profile
where age<25 or age is null
union all 
select "25岁及以上" age_cut,count(1) number
from user_profile
where age>=25;

SQL27 查看不同年龄段的用户明细

• 解法一：

select 
    device_id
    ,gender
    ,case
    when age<20 then "20岁以下"
    when age>=20 and age <=24 then "20-24岁"
    when age>=25 then "25岁及以上"
    else "其他"
    end as age_cut
from user_profile;

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

• 问题分解：
  限定条件：2021年8月，写法有很多种，比如用year/month函数的year(date)=2021 and month(date)=8，比如用date_format函数的date_format(date, “%Y-%m”)=“202108”
  每天：按天分组group by date
  题目数量：count(question_id)

select day(date) as day ,count(device_id)
from question_practice_detail
where date_format(date,"%Y-%m")="2021-08"
group by day

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

• 问题分解：
  两张表关联，用总的第二天还来的人数量/每个日期用户的刷题登录的数量
  DATE_SUB() 函数从日期减去指定的时间间隔。
  distinct 对相同的设备id和日期去重

select avg(if(b.device_id is not null,1,0)) as avg_ret
from 
(select distinct device_id,date
from question_practice_detail) a
left join 
(select distinct device_id,date_sub(date,interval 1 day) as date
from question_practice_detail) b
on a.device_id=b.device_id and a.date=b.date;

SQL30 统计每种性别的人数

• 问题分解：
  substring_index(str, delimiter, count)
  返回一个 str 的子字符串，在 delimiter 出现 count 次的位置截取。
  如果 count > 0，从则左边数起，且返回位置前的子串；
  如果 count < 0，从则右边数起，且返回位置后的子串。

delimiter 是大小写敏感，且是多字节安全的。

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

SQL31 提取博客URL中的用户名

• 问题分解：
  substring_index(str, delimiter, count)
  返回一个 str 的子字符串，在 delimiter 出现 count 次的位置截取。
  如果 count > 0，从则左边数起，且返回位置前的子串；
  如果 count < 0，从则右边数起，且返回位置后的子串。

delimiter 是大小写敏感，且是多字节安全的。

select device_id,substring_index(blog_url,'/',-1) as user_name
from user_submit

SQL32 截取出年龄

• 问题分解：
  substring_index(str, delimiter, count)
  返回一个 str 的子字符串，在 delimiter 出现 count 次的位置截取。
  如果 count > 0，从则左边数起，且返回位置前的子串；
  如果 count < 0，从则右边数起，且返回位置后的子串。

delimiter 是大小写敏感，且是多字节安全的。

select substring_index(substring_index(profile,",",-2),",",1) as age,count(1) 
from user_submit
group by age;

SQL33 找出每个学校GPA最低的同学

• 问题分解：
  使用窗口函数row_number()

select     
    a.device_id
    ,a.university
    ,a.gpa
from(
select 
    device_id
    ,university
    ,gpa
    ,row_number() over(partition by university order by gpa asc) as rn
from user_profile) a
where a.rn=1

SQL34 统计复旦用户8月练题情况

• 问题分解：分布处理，按照每一次处理后的结果进行下一次的处理

select 
	u.device_id
	,u.university
	,sum(case when q.id is null then 0 else 1 end) as question_cnt
	,sum(case when q.result="wrong" then 0 else 1 end) as right_question_cnt
from user_profile u
left join question_practice_detail q on u.device_id=q.device_id
where u.university="复旦大学" and (q.date is null or month(date)=8)
group by u.device_id

SQL35 浙大不同难度题目的正确率

• 问题分解：分布处理，按照每一次处理后的结果进行下一次的处理

select 
    d.difficult_level
    ,avg(case when q.result="right" then 1 else 0 end) as correct_rate
from(select device_id
from user_profile
where university="浙江大学") u 
left join question_practice_detail q on u.device_id =q.device_id
left join question_detail d on q.question_id = d.question_id
group by d.difficult_level
having d.difficult_level is not null
order by correct_rate asc;

SQL36 查找后排序

select device_id,age
from user_profile
order by age;

SQL37 查找后多列排序

select device_id,gpa,age
from user_profile
order by gpa asc,age asc;

SQL38 查找后降序排列

select device_id,gpa,age
from user_profile
order by gpa desc,age desc;

SQL39 21年8月份练题总数

select 
    count(distinct device_id) as did_cnt
    ,count(1) as question_cnt
from question_practice_detail
where date_format(date,"%Y-%m")="2021-08"