牛客-sql基础篇

题目一 （日期题目）

sql-39 题目： 现在运营想要了解2021年8月份所有练习过题目的总用户数和练习过题目的总次数，请取出相应结果

解法一

select
    count(distinct device_id) as did_cnt,
    count(question_id) as question_cnt
from question_practice_detail
where date like "2021-08%";

解法二

SELECT COUNT(DISTINCT device_id) AS did_cnt,COUNT(device_id) AS question_cnt\
FROM question_practice_detail\
WHERE YEAR(date)=2021 AND MONTH(date)=8

解法三

select
    count(distinct device_id) as did_cnt,
    count(question_id) as question_cnt
from question_practice_detail
where
date between '2021-08-01' and '2021-08-30'

解法四

-- 2021年8月份所有练习过题目的总用户数和练习过题目的总次数
-- 总用户数 device_id去重  总次数
SELECT 
    COUNT(DISTINCT (device_id)) user_cnt, COUNT(1) question_cnt
FROM
    question_practice_detail
WHERE
    date >= STR_TO_DATE('2021-08-01', '%Y-%m-%d')
        AND date <= STR_TO_DATE('2021-08-31', '%Y-%m-%d');

解法五 （推荐）

-- 2021年8月份所有练习过题目的总用户数和练习过题目的总次数
-- 总用户数 device_id去重  总次数
SELECT 
    COUNT(DISTINCT (device_id)) user_cnt, COUNT(1) question_cnt
FROM
    question_practice_detail
WHERE
    date >= date_format('2021-08-01', '%Y-%m-%d')
        AND date <= date_format('2021-08-31', '%Y-%m-%d');

解法六

-- 2021年8月份所有练习过题目的总用户数和练习过题目的总次数
-- 总用户数 device_id去重  总次数
SELECT 
    COUNT(DISTINCT (device_id)) user_cnt, COUNT(1) question_cnt
FROM
    question_practice_detail
WHERE
    date >= date('2021-08-01')
        AND date <= date('2021-08-31');

知识总结

1、DATE() 函数：返回日期或日期时间[表达式]的日期部分；

2、str_to_date()函数：按照指定日期或时间显示格式 将[字符串]转换为日期或日期时间类型；

3、date_format()函数：按照指定日期或时间显示格式 输出日期或日期时间；


1、date(datestring)

datestring是合法的日期表达式

如：SELECT date(‘2017-02-09 15:25:46.635’) FROM dual; -->‘2017-02-09’


2、date_format(datestring，format)

datestring参数是合法的日期。format 规定日期/时间的输出格式。

如：

SELECT STR_TO_DATE(‘2017-02-09 15:25:46.635’,’%Y-%m-%d’)
FROM dual;
–>‘2017-02-09’

SELECT STR_TO_DATE(‘2017-02-09 15:25:46’,’%Y-%m-%d %H:%i:%s’)
FROM DUAL;
–>‘2017-02-09 15:25:46’

SELECT STR_TO_DATE(‘2017-02-09 15:25’,’%Y-%m-%d %k:%i’)
FROM DUAL;
–>‘2017-02-09 15:25:00’


3、date_format(datestring，format)

datestring参数是合法的日期。format 规定日期/时间的输出格式。

如：

当前时间按月-日 时:分:秒显示：

SELECT DATE_FORMAT(NOW(),’%m-%d %h:%i %p’)
FROM dual;
–>‘02-09 06:00 PM’

当前时间按 年-月-日 时:分:秒 AM/PM显示：

SELECT DATE_FORMAT(NOW(),’%Y-%m-%d %h:%i:%s %p’) FROM dual; -->‘2017-02-09 06:00:35’

当前时间按 年 周 日 时:分:秒显示：

SELECT DATE_FORMAT(NOW(),’%Y %b %d %T’) FROM dual; -->‘2017 Feb 09 18:04:13’

可以使用的格式有：

格式 描述\
%a 缩写星期名\
%b 缩写月名\
%c 月，数值\
%D 带有英文前缀的月中的天\
%d 月的天，数值(00-31)\
%e 月的天，数值(0-31)\
%f 微秒\
%H 小时 (00-23)\
%h 小时 (01-12)\
%I 小时 (01-12)\
%i 分钟，数值(00-59)\
%j 年的天 (001-366)\
%k 小时 (0-23)\
%l 小时 (1-12)\
%M 月名\
%m 月，数值(00-12)\
%p AM 或 PM\
%r 时间，12-小时（hh:mm:ss AM 或 PM）\
%S 秒(00-59)\
%s 秒(00-59)\
%T 时间, 24-小时 (hh:mm:ss)\
%U 周 (00-53) 星期日是一周的第一天\
%u 周 (00-53) 星期一是一周的第一天\
%V 周 (01-53) 星期日是一周的第一天，与 %X 使用\
%v 周 (01-53) 星期一是一周的第一天，与 %x 使用\
%W 星期名\
%w 周的天 （0=星期日, 6=星期六）\
%X 年，其中的星期日是周的第一天，4 位，与 %V 使用\
%x 年，其中的星期一是周的第一天，4 位，与 %v 使用\
%Y 年，4 位\
%y 年，2 位

题目二（not in）

题目：现在运营想要查看除复旦大学以外的所有用户明细，请你取出相应数据

解法一(推荐)

SELECT device_id, gender,age,university FROM user_profile WHERE university NOT IN ("复旦大学")

解法二

select device_id, gender, age, university
from user_profile
where university != '复旦大学'

题目三 用where过滤空值练习

题目：现在运营想要对用户的年龄分布开展分析，在分析时想要剔除没有获取到年龄的用户，请你取出所有年龄值不为空的用户的设备ID，性别，年龄，学校的信息。

解法一

select
device_id,
gender,
age,
university
from user_profile
WHERE
age is not null

题目四 Where in 和Not in

题目：现在运营想要找到学校为北大、复旦和山大的同学进行调研，请你取出相关数据。

解法一

select device_id,gender,age,university,gpa from user_profile
where university in('北京大学','复旦大学','山东大学');

题目五 操作符混合运用

题目：现在运营想要找到gpa在3.5以上(不包括3.5)的山东大学用户 或 gpa在3.8以上(不包括3.8)的复旦大学同学进行用户调研，请你取出相应数据

解法一

and的优先级大于or 我这里写大括号方便区分,表示两个条件或两个条件 可以省略括号
select device_id,gender,age,university,gpa from user_profile
where (university='山东大学' and gpa>3.5 )
or (university="复旦大学" and gpa>3.8);

题目六

题目：现在运营想查看所有大学中带有北京的用户的信息，请你取出相应数据。

解法一

题目：现在运营想查看所有大学中带有北京的用户的信息，请你取出相应数据。

SELECT device_id,age,university FROM user_profile
WHERE university LIKE '%北京%'

题目七 查找GPA最高值

题目：运营想要知道复旦大学学生gpa最高值是多少，请你取出相应数据

解法一（推荐）

select max(gpa) as gpa from user_profile where university='复旦大学'

解法2

select gpa from user_profile where university='复旦大学' order by gpa desc limit 1

题目八 计算男生人数以及平均GPA

题目：现在运营想要看一下男性用户有多少人以及他们的平均gpa是多少，用以辅助设计相关活动，请你取出相应数据。

解法一

select count(gender) as male_num, round(avg(gpa), 1) as avg_gpa from user_profile where gender="male";

round函数

在mysql中，round函数用于数据的四舍五入，它有两种形式：

1、round(x,d)  ，x指要处理的数，d是指保留几位小数

这里有个值得注意的地方是，d可以是负数，这时是指定小数点左边的d位整数位为0,同时小数位均为0；

2、round(x)  ,其实就是round(x,0),也就是默认d为0；

下面是几个实例

1、查询: select round(1123.26723,2);

     结果：1123.27

2、查询: select round(1123.26723,1);
     结果: 1123.3

3、查询: select round(1123.26723,0);

     结果：1123

4、查询: select round(1123.26723,-1);
     结果: 1120

5、查询: select round(1123.26723,-2);

     结果：1100

5、查询: select round(1123.26723);

     结果：1123

题目八 分组计算练习题

题目：现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

解法一

select
    gender, university,
    count(device_id) as user_num,
    avg(active_days_within_30) as avg_active_days,
    avg(question_cnt) as avg_question_cnt
from user_profile
group by gender, university

题目九 分组过滤练习题

题目：现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。

select
    university,
    avg(question_cnt) as avg_question_cnt,
    avg(answer_cnt) as avg_answer_cnt
from user_profile
group by university
having avg_question_cnt<5 or avg_answer_cnt<20

题目十 分组排序练习题

题目：现在运营想要查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列，请你取出相应数据。

分组排序练习题

select
university,
avg(question_cnt) AS avg_question_cnt 
from user_profile
group by university 
order by avg_question_cnt

题目十一 统计每个学校的答过题的用户的平均答题数

运营想要了解每个学校答过题的用户平均答题数量情况，请你取出数据。

select university,
    count(question_id) / count(distinct qpd.device_id) as avg_answer_cnt
from question_practice_detail as qpd
inner join user_profile as up
on qpd.device_id=up.device_id
group by university

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

题目：运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据

select 
    university,
    difficult_level,
    round(count(qpd.question_id) / count(distinct qpd.device_id), 4) as avg_answer_cnt
from question_practice_detail as qpd

left join user_profile as up
on up.device_id=qpd.device_id

left join question_detail as qd
on qd.question_id=qpd.question_id

group by university, difficult_level

题目十三 查找山东大学或者性别为男生的信息

题目：现在运营想要分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重。

解法一

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'

union 和union all

1: 什么时候用union和union all ?

　　　   我们经常会碰到这样的应用，两个表的数据按照一定的查询条件查询出来以后，需要将结果合并到一起显示出来，这个时候

就需要用到union和union all关键字来实现这样的功能，union和union all的主要区别是union all是把结果集直接合并在一起，而

union 是将union all后的结果镜像一次distinct，去除重复的记录后的结果。

题目十四 计算25岁以上和以下的用户数量

题目：现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量

题解一

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

case函数

CASE函数
是一种多分支的函数，可以根据条件列表的值返回多个可能的结果表达式中的一个。
可用在任何允许使用表达式的地方，但不能单独作为一个语句执行。
分为：
简单CASE函数
搜索CASE函数


1.简单case函数:
case name 
    when 'xiaomi' then '国产'
    when 'huawei' then '国产'
    when 'iphone' then '进口'
else '其他' end
2.搜索case函数:
case  
    when name = 'xiaomi' then '国产'
    when name = 'huawei' then '国产'
    when name = 'iphone' then '进口'
else '其他' end
二者都可实现功能,不过推荐使用case搜索函数.
注意:case只返回第一个符合条件的值,剩下的case 部分会被忽略.

case函数的功能很强大,介绍一个实用的:

我要分组统计进口手机国产手机的总量count:

题目十五 条件函数 查看不同年龄段的用户明细

题目：现在运营想要将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况，请取出相应数据。（注：若年龄为空请返回其他。）

解法一

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

题目十六 日期函数 计算用户8月每天的练题数量

题目：现在运营想要计算出2021年8月每天用户练习题目的数量，请取出相应数据。

解法一

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

sql日期

day(date):获取指定时间的天数

month(date):获取指定时间的月份

year(date):获取指定时间的年份

题目十七 文本函数 统计每种性别的人数

解法一

SELECT SUBSTRING_INDEX(profile,",",-1) gender,COUNT(*) number
FROM user_submit
GROUP BY gender;

解法二

SELECT IF(profile LIKE '%female','female','male') gender,COUNT(*) number
FROM user_submit
GROUP BY gender;

substring(str,delim,count)

str:要处理的字符串 delim:分隔符 count:计数

题目十八 文本函数 提取博客URL中的用户名

题目：对于申请参与比赛的用户，blog_url字段中url字符后的字符串为用户个人博客的用户名，现在运营想要把用户的个人博客用户字段提取出单独记录为一个新的字段，请取出所需数据。

解法一

SELECT device_id,
       SUBSTRING_INDEX(blog_url,"/",-1) AS user_name
FROM user_submit;

解法二

SELECT device_id,
       replace(blog_url,'http:/url/','') as user_name
FROM user_submit;

切割、截取、删除、替换

select 
-- 替换法 replace(string, '被替换部分','替换后的结果')
-- device_id, replace(blog_url,'http:/url/','') as user_name

-- 截取法 substr(string, start_point, length*可选参数*)
-- device_id, substr(blog_url,11,length(blog_url)-10) as user_nam

-- 删除法 trim('被删除字段' from 列名)
-- device_id, trim('http:/url/' from blog_url) as user_name

-- 字段切割法 substring_index(string, '切割标志', 位置数（负号：从后面开始）)
device_id, substring_index(blog_url,'/',-1) as user_name

from user_submit;

题目十九 窗口函数

题目：现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。

解法一

select device_id,university,gpa
from user_profile
where (university,gpa) in (select university,min(gpa) from user_profile group by university)
order by university

题目二十 统计复旦用户8月练题情况

题目： 现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0.

解法一

select up.device_id, '复旦大学',
    count(question_id) as question_cnt,
    sum(if(qpd.result='right', 1, 0)) as right_question_cnt
from user_profile as up

left join question_practice_detail as qpd
  on qpd.device_id = up.device_id and month(qpd.date) = 8

where up.university = '复旦大学'
group by up.device_id