百依百随网

首页 » 热门资讯 » 猿创征文｜【第5天】SQL快速入门-必会的常用函数（SQL 小虚竹)

猿创征文｜【第5天】SQL快速入门-必会的常用函数（SQL 小虚竹)

2024-05-08 06:30:22 热门资讯

猿创征文｜【第5天】SQL快速入门-必会的常用函数（SQL 小虚竹)

回城传送–》《32天SQL筑基》

文章目录

  • 零、猿创前言
  • 一、征文练习题目
  • 二、第天SQL思路
    • 条件函数：SQL26 计算25岁以上和以下的速入用户数量
      • 初始化数据
      • 解法
    • 日期函数：SQL28 计算用户8月每天的练题数量
      • 初始化数据
      • 解法
      • 解法二：
      • 解法三：
    • 日期函数：SQL29 计算用户的平均次日留存率
      • 初始化数据
      • 解法
    • 文本函数：SQL32 截取出年龄
      • 初始化数据
      • 解法
    • 窗口函数：SQL33 找出每个学校GPA最低的同学
      • 初始化数据
      • 解法
      • 解法2：
      • 窗口函数总结

零、前言

今天是门必学习 SQL打卡的第 5天，每天我会提供一篇文章供群成员阅读（ 不需要订阅付钱）。函数

希望大家先自己思考，常用如果实在没有想法，小虚竹再看下面的猿创解题思路，自己再实现一遍。征文在小虚竹JAVA社区 中对应的第天 【打卡贴】打卡，今天的速入任务就算完成了，养成每天学习打卡的门必好习惯。

​ 虚竹哥会组织大家一起学习同一篇文章，函数所以有什么问题都可以在群里问，常用群里的小伙伴可以迅速地帮到你，一个人可以走得很快，一群人可以走得很远，有一起学习交流的战友，是多么幸运的事情。

​ 我的学习策略很简单，题海策略+ 费曼学习法。如果能把这些题都认认真真自己实现一遍，那意味着 SQL已经筑基成功了。后面的进阶学习，可以继续跟着我，一起走向架构师之路。

今天的学习内容是：必会的常用函数

一、练习题目

题目链接难度
条件函数：SQL26 计算25岁以上和以下的用户数量★★★☆☆
日期函数：SQL28 计算用户8月每天的练题数量★★★☆☆
日期函数：SQL29 计算用户的平均次日留存率★★★★★
文本函数：SQL32 截取出年龄★★★☆☆
窗口函数：SQL33 找出每个学校GPA最低的同学★★★☆☆

二、SQL思路

条件函数：SQL26 计算25岁以上和以下的用户数量

在这里插入图片描述

初始化数据

drop table if exists `user_profile`;drop table if  exists `question_practice_detail`;CREATE TABLE `user_profile` (`id` int NOT NULL,`device_id` int NOT NULL,`gender` varchar(14) NOT NULL,`age` int ,`university` varchar(32) NOT NULL,`gpa` float,`active_days_within_30` int ,`question_cnt` int ,`answer_cnt` int );CREATE TABLE `question_practice_detail` (`id` int NOT NULL,`device_id` int NOT NULL,`question_id`int NOT NULL,`result` varchar(32) NOT NULL);CREATE TABLE `question_detail` (`id` int NOT NULL,`question_id`int NOT NULL,`difficult_level` varchar(32) NOT NULL);INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong');INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong');INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong');INSERT INTO question_practice_detail VALUES(4,6543,111,'right');INSERT INTO question_practice_detail VALUES(5,2315,115,'right');INSERT INTO question_practice_detail VALUES(6,2315,116,'right');INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong');INSERT INTO question_practice_detail VALUES(8,5432,117,'wrong');INSERT INTO question_practice_detail VALUES(9,5432,112,'wrong');INSERT INTO question_practice_detail VALUES(10,2131,113,'right');INSERT INTO question_practice_detail VALUES(11,5432,113,'wrong');INSERT INTO question_practice_detail VALUES(12,2315,115,'right');INSERT INTO question_practice_detail VALUES(13,2315,116,'right');INSERT INTO question_practice_detail VALUES(14,2315,117,'wrong');INSERT INTO question_practice_detail VALUES(15,5432,117,'wrong');INSERT INTO question_practice_detail VALUES(16,5432,112,'wrong');INSERT INTO question_practice_detail VALUES(17,2131,113,'right');INSERT INTO question_practice_detail VALUES(18,5432,113,'wrong');INSERT INTO question_practice_detail VALUES(19,2315,117,'wrong');INSERT INTO question_practice_detail VALUES(20,5432,117,'wrong');INSERT INTO question_practice_detail VALUES(21,5432,112,'wrong');INSERT INTO question_practice_detail VALUES(22,2131,113,'right');INSERT INTO question_practice_detail VALUES(23,5432,113,'wrong');INSERT INTO question_detail VALUES(1,111,'hard');INSERT INTO question_detail VALUES(2,112,'medium');INSERT INTO question_detail VALUES(3,113,'easy');INSERT INTO question_detail VALUES(4,115,'easy');INSERT INTO question_detail VALUES(5,116,'medium');INSERT INTO question_detail VALUES(6,117,'easy');

解法

要求统计：

  • 将用户划分为25岁以下和25岁及以上两个年龄段
  • 分别查看这两个年龄段用户数量
  • 本题注意：age为null 也记为 25岁以下

分析：

  • 将用户划分为25岁以下和25岁及以上两个年龄段,这里可以用分组：使用关键词：group by

SELECT
case
when age < 25 then ‘25岁以下’
when age >= 25 then ‘25岁及以上’
end age_cut
FROM
user_profile
group by
age_cut

  • 分别查看这两个年龄段用户数量,就是统计计数：使用关键词：count

SELECT
case
when age < 25 then ‘25岁以下’
when age >= 25 then ‘25岁及以上’
end age_cut,
count(1) as number
FROM
user_profile
group by
age_cut

  • age为null 也记为 25岁以下,条件要增加一个：age is null的情况
SELECT  case    when age < 25    or age is null then '25岁以下'    when age >= 25 then '25岁及以上'  end age_cut,  count(1) as numberFROM  user_profilegroup by  age_cut

日期函数：SQL28 计算用户8月每天的练题数量

在这里插入图片描述

初始化数据

drop table if exists `user_profile`;drop table if  exists `question_practice_detail`;drop table if  exists `question_detail`;CREATE TABLE `user_profile` (`id` int NOT NULL,`device_id` int NOT NULL,`gender` varchar(14) NOT NULL,`age` int ,`university` varchar(32) NOT NULL,`gpa` float,`active_days_within_30` int ,`question_cnt` int ,`answer_cnt` int );CREATE TABLE `question_practice_detail` (`id` int NOT NULL,`device_id` int NOT NULL,`question_id`int NOT NULL,`result` varchar(32) NOT NULL,`date` date NOT NULL);CREATE TABLE `question_detail` (`id` int NOT NULL,`question_id`int NOT NULL,`difficult_level` varchar(32) NOT NULL);INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');INSERT INTO question_detail VALUES(1,111,'hard');INSERT INTO question_detail VALUES(2,112,'medium');INSERT INTO question_detail VALUES(3,113,'easy');INSERT INTO question_detail VALUES(4,115,'easy');INSERT INTO question_detail VALUES(5,116,'medium');INSERT INTO question_detail VALUES(6,117,'easy');

解法

要求统计：

  • 2021年8月每天用户练习题目的数量

分析：

  • 2021年8月的题目数据，且是每天的，这里的条件是2021年8月，显示指定日期的数据

SELECT DAY(date) AS day
FROM question_practice_detail
WHERE date LIKE ‘2021-08-%’

  • 每天用户练习题目的数量:这里就是按日期进行分组统计计数：可用函数 count进行计数，使用关键词：**group by ** 对日期进行分组
SELECT DAY(date) AS day , COUNT(date) AS question_cntFROM question_practice_detailWHERE date LIKE '2021-08-%'GROUP BY date

解法二：

只要日期大于等于2021-8-1且小于等于2021-8-31，那查询出来的条件也是符合的，使用日期函数date(),把字符串格式的日期进行转化。

select   day(date) as day,   count(date) as question_cnt from   question_practice_detail where   date('2021-8-31') >= date   and date('2021-8-1') <= date group by   date;

解法三：

只要年份是2021，月份是8，也符合条件：这时可使用函数 YEAR()获取年份，使用函数MONTH()获取月份。

SELECT  DAY(date) AS day,  COUNT(date) AS question_cntFROM  question_practice_detailWHERE  YEAR(date) = 2021  AND MONTH(date) = 08GROUP BY  day

日期函数：SQL29 计算用户的平均次日留存率

在这里插入图片描述
在这里插入图片描述

初始化数据

drop table if exists `user_profile`;drop table if  exists `question_practice_detail`;drop table if  exists `question_detail`;CREATE TABLE `user_profile` (`id` int NOT NULL,`device_id` int NOT NULL,`gender` varchar(14) NOT NULL,`age` int ,`university` varchar(32) NOT NULL,`gpa` float,`active_days_within_30` int ,`question_cnt` int ,`answer_cnt` int );CREATE TABLE `question_practice_detail` (`id` int NOT NULL,`device_id` int NOT NULL,`question_id`int NOT NULL,`result` varchar(32) NOT NULL,`date` date NOT NULL);CREATE TABLE `question_detail` (`id` int NOT NULL,`question_id`int NOT NULL,`difficult_level` varchar(32) NOT NULL);INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');INSERT INTO question_detail VALUES(1,111,'hard');INSERT INTO question_detail VALUES(2,112,'medium');INSERT INTO question_detail VALUES(3,113,'easy');INSERT INTO question_detail VALUES(4,115,'easy');INSERT INTO question_detail VALUES(5,116,'medium');INSERT INTO question_detail VALUES(6,117,'easy');

解法

要求统计：

  • 查看用户在某天刷题后第二天还会再来刷题的平均概率
  • 隐藏需求，返回字段是avg_ret，且保留4位有效小数

分析：

  • 查询条件是用户在某天刷题后第二天还会再来，就是此用户第二天还有答题的记录
  • 因为只有一个question_practice_detail表，所以相当于要把question_practice_detail表和question_practice_detail表（看作是表结构和数据是一样的另一个表）进行关联，关联字段是：device_id
  • 这里有要求，是用户第二天的数据，所以要对日期进行限制：可用关键词：datediff

SELECT DATEDIFF(day,‘2008-12-29’,‘2008-12-30’) AS DiffDate
//结果DiffDate 值为1

SELECT DATEDIFF(day,‘2008-12-30’,‘2008-12-29’) AS DiffDate
//结果DiffDate 值为-1

SELECT
qpd1.*
FROM
question_practice_detail qpd1
LEFT JOIN question_practice_detail qpd2 ON qpd1.device_id = qpd2.device_id
and datediff(qpd1.date, qpd2.date) = 1

  • 用户某天刷题后第二天还会再来刷题的平均概率计算方式：同一用户在某天有刷题，计数加1；分母为某天的计数总和；分子为第二天的计数总和；要对用户和时间进行去重（因为同个用户，会在同一天刷多道题）
  • 去重用关键词：distinct，计数用关键词：COUNT

COUNT(distinct qpd2.device_id, qpd2.date) / COUNT(distinct qpd1.device_id, qpd1.date) as avg_ret

  • 返回字段是avg_ret，且保留4位有效小数，可使用关键函数：round()
SELECT  round(COUNT(distinct qpd2.device_id, qpd2.date) / COUNT(distinct qpd1.device_id, qpd1.date),4) as avg_retFROM  question_practice_detail qpd1  LEFT JOIN question_practice_detail qpd2 ON qpd1.device_id = qpd2.device_id  and datediff(qpd1.date, qpd2.date) = 1

文本函数：SQL32 截取出年龄

在这里插入图片描述
在这里插入图片描述

初始化数据

drop table if exists user_submit;CREATE TABLE `user_submit` (`id` int NOT NULL,`device_id` int NOT NULL,`profile` varchar(100) NOT NULL,`blog_url` varchar(100) NOT NULL);INSERT INTO user_submit VALUES(1,2138,'180cm,75kg,27,male','http:/url/bisdgboy777');INSERT INTO user_submit VALUES(1,3214,'165cm,45kg,26,female','http:/url/dkittycc');INSERT INTO user_submit VALUES(1,6543,'178cm,65kg,25,male','http:/url/tigaer');INSERT INTO user_submit VALUES(1,4321,'171cm,55kg,23,female','http:/url/uhsksd');INSERT INTO user_submit VALUES(1,2131,'168cm,45kg,22,female','http:/url/sysdney');

解法

要求统计：

  • 每个年龄的用户分别有多少参赛者

分析：

  • 年龄是放在字段profile中；要截取文本内容，可使用函数：substring_index

substring_index(str,delim,count)
//说明
str：要处理的字符串
delm：分隔符
count：计数
如果count是正数，那么就是从左往右数

select substring_index(profile, ‘,’, 2)
from user_submit
在这里插入图片描述
如果count是负数，那么就是从右往左数
在这里插入图片描述

结果：

SELECT  substring_index(substring_index(profile, ',', -2), ',', 1) as age,  count(device_id) as numberfrom  user_submitgroup by  age;

窗口函数：SQL33 找出每个学校GPA最低的同学

在这里插入图片描述

初始化数据

drop table if exists user_profile;CREATE TABLE `user_profile` (`id` int NOT NULL,`device_id` int NOT NULL,`gender` varchar(14) NOT NULL,`age` int ,`university` varchar(32) NOT NULL,`gpa` float,`active_days_within_30` int ,`question_cnt` int ,`answer_cnt` int );INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);

解法

要求统计：

  • 找到每个学校gpa最低的同学
  • 并取出每个学校的最低gpa
  • 输出结果按university升序排序

先不用窗口函数实现
分析：

  • 找出每个学校的最低gpa

select university,min(gpa) min_gpa from user_profile group by university

  • 然后通过user_profile 表去关联上面找出来的数据，通过内连接( inner join)去匹配符合university和min(gpa) 的值

select
up1.device_id,
up1.university,
up1.gpa
from
user_profile up1
inner join (
select
university,
min(gpa) min_gpa
from
user_profile
group by
university
) up2 On up1.university = up2.university
and up1.gpa = up2.min_gpa

  • 最后按university升序排序
select  up1.device_id,  up1.university,  up1.gpafrom  user_profile up1  inner join (    select      university,      min(gpa) min_gpa    from      user_profile    group by      university  ) up2 On up1.university = up2.university  and up1.gpa = up2.min_gpa  order by up1.university asc

这种写法是能实现，但有漏洞，你发现了吗？有发现的话，评论区说下

解法2：

分析：

  • 使用窗口函数，先按university字段分组计算，同时按gpa字段排序

说明：MySQL从8.0版本开始支持窗口函数

窗口函数的语法 ：
<窗口函数>over (partition by <用于分组的列名>
order by <用于排序的列名>)

<窗口函数>的位置，可以放以下两种函数：

1） 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。

2） 聚合函数，如sum. avg, count, max, min等

  • 专用窗口函数rank说明:RANK函数返回当前行在分区中的名次。如果存在名次相同的数据，后续的排名将会产生跳跃

如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。

  • 专用窗口函数ROW_NUMBER说明:ROW_NUMBER函数可以为分区中的每行数据分配一个序列号，序列号从1开始。

不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

  • 专用窗口函数dense_rank说明:存在名次相同的数据，后续的排名也是连续值。

比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。

SELECT
*,
ROW_NUMBER() over (
PARTITION BY university
ORDER BY
gpa
) AS rn
FROM
user_profile
在这里插入图片描述

  • 如图所示，这时只要取出rn为1的即可
SELECT  device_id,  university,  gpaFROM  (    SELECT      *,      ROW_NUMBER() over (        PARTITION BY university        ORDER BY          gpa      ) AS rn    FROM      user_profile  ) AS tempWHERE  temp.rn = 1

窗口函数总结

1）同时具有分组（partition by）和排序（order by）的功能

2）不会减少原表的行数

我是虚竹哥，我们明天见~

未经允许不得转载：百依百随网 » 猿创征文｜【第5天】SQL快速入门-必会的常用函数（SQL 小虚竹)

相关推荐

商品推荐