[大数据]牛客SQL----非技术快速入门

SQL1 查询多列

select device_id,gender,age,university
from user_profile;

SQL2 查询所有列

SELECT *
from user_profile; 

SQL3 查询结果去重

SELECT DISTINCT university
from user_profile; 

SQL4 查询结果限制返回行数

SELECT device_id
FROM user_profile
limit 0,2;

SQL5 将查询后的列重新命名

SELECT device_id  user_infos_exanple
FROM user_profile
limit 0,2;

SQL6 查找学校是北大的学生信息

SELECT device_id,university
from user_profile
where university="北京大学"

SQL7 查找年龄大于24岁的用户信息

SELECT device_id,gender,age,university
FROM user_profile
WHERE age>24

SQL8 查找某个年龄段的用户信息

SELECT device_id,gender,age
FROM user_profile
where age BETWEEN 20 and 23

SQL9 查找除复旦大学的用户信息

SELECT device_id,gender,age,university
from user_profile
where university <> '复旦大学'

SQL10 用where过滤空值练习

SELECT device_id,gender,age,university
FROM user_profile
WHERE age is not NULL

SQL11 高级操作符练习(1)

SELECT device_id,gender,age,university,gpa
FROM user_profile
where gender="male" and gpa > 3.5

SQL12 高级操作符练习(2）

SELECT device_id,gender,age,university,gpa
FROM user_profile
where university="北京大学" or gpa > 3.7

SQL13 Where in 和Not in

SELECT device_id,gender,age,university,gpa
FROM user_profile
where university="北京大学" or university="复旦大学" or university="山东大学"

SQL14 操作符混合运用

SELECT device_id,gender,age,university,gpa
FROM user_profile
where (university="山东大学" and gpa>3.5) or (university="复旦大学" and gpa>3.8)

SQL15 查看学校名称中含北京的用户

SELECT device_id,age,university
FROM user_profile
where university like "%北京%"

SQL16 查找GPA最高值

SELECT max(gpa)
FROM user_profile
where university="复旦大学"

SQL17 计算男生人数以及平均GPA

SELECT count(1) male_num,avg(gpa) avg_gpa
FROM user_profile
where gender = 'male'

SQL18 分组计算练习题

SELECT gender,university,count(1) user_num,round(avg(active_days_within_30),1) avg_active_day ,round(avg(question_cnt),1) avg_question_cnt
FROM user_profile
group by gender,university

SQL19 分组过滤练习题

SELECT university,avg(question_cnt) avg_question_cnt,AVG(answer_cnt) avg_question_cnt
from user_profile
GROUP by university
HAVING avg(question_cnt)<5 or AVG(answer_cnt)<20

SQL20 分组排序练习题

SELECT university,avg(question_cnt) avg_question_cnt
from user_profile
GROUP by university
order by avg_question_cnt

SQL21 浙江大学用户题目回答情况

select device_id,question_id,result
from question_practice_detail
where device_id = (select device_id 
                  from user_profile
                  where university='浙江大学')
order by question_id asc

SQL22 统计每个学校的答过题的用户的平均答题数

select u.university university,round(count(q.question_id)/count(distinct q.device_id),4) avg_answer_cnt
from user_profile u join question_practice_detail q on u.device_id = q.device_id
group by u.university
order by university asc

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

SQL24 统计每个用户的平均刷题数

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`
where u.`university`="山东大学"
group by u.`university`,d.`difficult_level`

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

select u.`device_id`,u.`gender`,u.`age`,u.`gpa`
from `user_profile` u
where u.`university`="山东大学"
union all
select u.`device_id`,u.`gender`,u.`age`,u.`gpa`
from `user_profile` u
where u.`gender`="male"

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"