IT数码 购物 网址 头条 软件 日历 阅读 图书馆
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓ 		图片批量下载器
↓批量下载图片,美女图库↓ 		图片自动播放器
↓图片自动播放器↓ 		一键清除垃圾
↓轻轻一点,清除系统垃圾↓
开发: C++知识库 Java知识库 JavaScript Python PHP知识库 人工智能 区块链 大数据 移动开发 嵌入式 开发工具 数据结构与算法 开发测试 游戏开发 网络协议 系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁
 
   -> 大数据 -> 牛客sql快速入门 -> 正文阅读

[大数据]牛客sql快速入门

notion原文地址
SQL数据库实战题_面试必刷+解析_牛客题霸_牛客网

基础练习

1. 常用字段

distinct()去重
MAX()
avg()求平均值
union all将多个查询结果合在一起，不去重
union将多个查询结果合在一起 去重
SUBSTRING_INDEX(profile, ‘,’, -1)截取最后一个，如果是 -2 就是截取最后两个

2. 小技巧

  • 取表中的前几条数据

    // sql 取前两条数据
select  device_id from user_profile limit 0, 2;

// oracle
select * from user_profile where rownum <= 2

  • 某个字段不为空

    // is not null
SELECT device_id, gender, age, university from user_profile where age is not null

  • 字段中包含某些文字

    // 包含北京
SELECT device_id, age, university
from user_profile
where university LIKE '%北京%'

  • 多列分组

    // group by 列1， 列2
SELECT gender, university, 
    COUNT(1) user_num, 
    AVG(active_days_within_30) avg_active_day, 
    AVG(question_cnt) avg_question_cnt
from user_profile
group by  gender, university

  • Group by 跟 where， having 联用

    1. where 是在分分组前对数据进行过滤，不能包含聚合函数
    2. have 是筛选出符合条件的组，是在分组后
    SELECT university,
    AVG(question_cnt) avg_question_cnt,
    AVG(answer_cnt) avg_answer_cnt
from user_profile
where age > 18
GROUP by university  
HAVING AVG(question_cnt) < 5
    or AVG(answer_cnt) < 20

  • case 函数

    https://blog.nowcoder.net/n/693e3098b4844ba4931020347cb7c87a?f=comment

    知识

    CASE函数

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

    简单 CASE函数

    SELECT 班号 ,班名,
CASE 系号
WHEN 1 THEN '软件工程系'
WHEN 2 THEN '计算机系'
WHEN 3 THEN '物联网系'
END AS 系号,班主任号
FROM 班级表

    计算测试表达式，按从上到下的书写顺序将测试表达式的值与每个WHEN子句的简单表达式进行比较。如果某个简单表达式的值与测试表达式的值相等，则返回第一个与之匹配的WHEN子句所对应的结果表达式的值。如果所有简单表达式的值与测试表达式的值都不相等，若指定了ELSE子句,则返回ELSE子句中指定的结果表达式的值；若没有指定ELSE子句，则返回NULL。

    例48. 查询班级表中的学生的班号、班名、系号和班主任号，并对系号作如下处理：当系号为1时，显示 “计算机系”；当系号为2时，显示 “软件工程系”；当系号为3时，显示 “物联网系”。

    SELECT 班号 ,班名,
CASE 系号
WHEN 1 THEN '软件工程系'
WHEN 2 THEN '计算机系'
WHEN 3 THEN '物联网系'
END AS 系号,班主任号
FROM 班级表

    搜索CASE函数

    CASE
WHEN 布尔表达式1 THEN 结果表达式1
WHEN 布尔表达式2 THEN 结果表达式2 …
WHEN 布尔表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END

    按从上到下的书写顺序计算每个WHEN子句的布尔表达式。返回第一个取值为TRUE的布尔表达式所对应的结果表达式的值。如果没有取值为TRUE的布尔表达式，则当指定了ELSE子句时,返回ELSE子句中指定的结果；如果没有指定ELSE子句，则返回NULL。

    例48用搜索CASE来做：

    SELECT 班号 ,班名,
CASE
WHEN 系号=1 THEN '软件工程系'
WHEN 系号=2 THEN '计算机系'
WHEN 系号=3 THEN '物联网系'
END AS 系号,班主任号
FROM 班级表

    例49.查询“M01F011”号课程的考试情况，列出学号、课程号和成绩，同时将百分制成绩显示为等级。

    SELECT 学号,课程号,
CASE
WHEN 成绩 >= 90 THEN '优'
WHEN 成绩 BETWEEN 80 AND 89 THEN '良'
WHEN 成绩 BETWEEN 70 AND 79 THEN '中'
WHEN 成绩 BETWEEN 60 AND 69 THEN '及格'
WHEN 成绩 <60 THEN '不及格'
END 成绩
FROM 成绩表
WHERE 课程号 = 'M01F011'

    CASE函数(续)

    例50.统计每个班男生和女生的数量各是多少，统计结果的表头为，班号，男生数量，女生数量。

    SELECT 班号,
COUNT(CASE WHEN 性别=‘男’ THEN ‘男’ END) 男生数,
COUNT(CASE WHEN 性别=‘女’ THEN ‘女’ END) 女生数
FROM 学生表 GROUP BY 班号

    例51.判断成绩的等级，85-100为“优”，70-84为“良”，60-69为“及格”，60以下为“不及格”，并统计每一等级的人数。

    SELECT
CASE
WHEN GRADE BETWEEN 85 AND 100 THEN '优'
WHEN GRADE BETWEEN 70 AND 84 THEN '良'
WHEN GRADE BETWEEN 60 AND 69 THEN '及格'
ELSE '不及格'
END 等级, COUNT(*) 人数
FROM SC
GROUP BY
CASE
WHEN GRADE BETWEEN 85 AND 100 THEN '优'
WHEN GRADE BETWEEN 70 AND 84 THEN '良'
WHEN GRADE BETWEEN 60 AND 69 THEN '及格'
ELSE '不及格'
END

    题解

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

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

    示例：user_profile

    https://uploadfiles.nowcoder.com/images/20210928/999991344_1632828974694/7219DA906D0C14B655E857C7349E6017

    根据示例，你的查询应返回以下结果：

    https://uploadfiles.nowcoder.com/images/20210928/999991344_1632831829937/75D997B10141AD2740283AC28486E68D

  • 日期函数

    DateOfWeek(‘1997-06-19’)这天是周几：0是周一，6是周日
    WeekDay(‘1997-06-19 14:19:00’)当前时间是周几：0周一
    DayOfMonth(‘1997-06-19’)这天是几号 19
    DayOfYear(‘1997-06-19’)这天是一年中的第几天
    Year(date)获取年份
    Month(date)获取月份
    DayName(date)返回星期的英文名
    MonthName(date)返回月份的英文名
    Quarter(date)返回季度 1~4
    Week(date)返回这一周是一年中的第几周 0~52
    Week(date,first)返回这一周是一年中的第几周，但是可以指定从周几开始，first = 0 从周日开始， first = 1 从周一开始
    Hour(time)返回当前时间是第几小时 0~23
    Minute(time)0~59
    Second(time)0~59
    Period_add(P,N)增加N个月到阶段P(以格式YYMM或YYYYMM)。以格式YYYYMM返回值。注意阶段参数P不是日期值。

    例如：若返回 199706
    select PERIOD_ADD(9704,2) |
    | Period_diff(P1,P2) | 返回在时期P1和P2之间月数，P1和P2应该以格式YYMM或YYYYMM。注意，时期参数P1和P2不是日期值。 且p1 > p2返回是正
    例如：求97年6月到 00年8月之间有几个月
    select PERIOD_DIFF(200008, 9706); |

  • 窗口函数

    1. 与group by 的区别

      group by 分组后会去重，减少原表的行数
      而窗口函数可以分组但不会·去重，不会改变原表的行数；并且可以排序

    2. 窗口函数只能写在select后面

    3. 窗口函数分类

      1. rank
      2. dense_rank
      3. row_number

      区别：

      学号分数班级
      1991
      2981
      3992
      4972
      5991
      61002
      /*  
		partition by 后面跟分组的列名  
		order by 后跟排序的列名 
*/
select *,
   rank() over (partition by 班级 order by 分数 desc) as ranking,
   dense_rank() over (partition by 班级 order by 分数desc) as dese_rank,
   row_number() over (partition by 班级 order by 分数desc) as row_num
from table_name

      结果：

      学号分数班级rankingdese_rankrow_num
      6991111
      1991112
      3981323
      51002111
      21002112
      4972323

  • 连接查询

    1. 内连接：结果只包含符合条件的行

      1. 显示内连接

        select difficult_level,
#    avg(if(qpd.result='right', 1, 0)) as correct_rate
#    sum(if(qpd.result='right', 1, 0)) / count(qpd.question_id) as correct_rate
    count(if(qpd.result='right', 1, null)) / count(qpd.question_id) as correct_rate
from user_profile as up

inner join question_practice_detail as qpd
    on up.device_id = qpd.device_id

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

where up.university = '浙江大学'
group by qd.difficult_level
order by correct_rate asc;

      2. 隐式内连接

        select difficult_level,
    avg(if(qpd.result='right', 1, 0)) as correct_rate
#    sum(if(qpd.result='right', 1, 0)) / count(qpd.question_id) as correct_rate
#    count(if(qpd.result='right', 1, null)) / count(qpd.question_id) as correct_rate
from user_profile as up, question_practice_detail as qpd, question_detail as qd
where up.university = '浙江大学'
	and up.device_id = qpd.device_id
	and qd.question_id = qpd.question_id
group by qd.difficult_level
order by correct_rate asc;

    2. 外连接：连接结果包含符合条件的行，也包括不符合条件的行

      1. 左外连接：以左表为基准，左表全部展示，右表符合条件的展示，不符合条件的为null

        SELECT COUNT(date2) / COUNT(date1) AS avg_ret
FROM 
    (SELECT DISTINCT device_id, date AS date1
     FROM question_practice_detail) AS qpd1
LEFT join 
    (SELECT DISTINCT device_id, date AS date2
     FROM question_practice_detail) AS qpd2
ON qpd1.device_id = qpd2.device_id 
    and date2 = DATE_ADD(date1, interval 1 day)

      2. 右外连接: 以右表为基准，右表全部展示，左表符合条件的展示，不符合条件的为null

        SELECT COUNT(date2) / COUNT(date1) AS avg_ret
FROM 
		(SELECT DISTINCT device_id, date AS date2
     FROM question_practice_detail) AS qpd2

right join 
		(SELECT DISTINCT device_id, date AS date1
     FROM question_practice_detail) AS qpd1
ON qpd1.device_id = qpd2.device_id 
    and date2 = DATE_ADD(date1, interval 1 day)

3. 例题
  大数据 最新文章
实现Kafka至少消费一次
亚马逊云科技：还在苦于ETL？Zero ETL的时代
初探MapReduce
【SpringBoot框架篇】32.基于注解+redis实现
Elasticsearch：如何减少 Elasticsearch 集
Go redis操作
Redis面试题
专题五 Redis高并发场景
基于GBase8s和Calcite的多数据源查询
Redis——底层数据结构原理
上一篇文章      下一篇文章      查看所有文章
加:2021-11-29 16:23:14  更:2021-11-29 16:24:43 
 
开发: C++知识库 Java知识库 JavaScript Python PHP知识库 人工智能 区块链 大数据 移动开发 嵌入式 开发工具 数据结构与算法 开发测试 游戏开发 网络协议 系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁

360图书馆 购物 三丰科技 阅读网 日历 万年历 2024年11日历 -2024/11/24 9:42:30-

图片自动播放器
↓图片自动播放器↓ 		TxT小说阅读器
↓语音阅读,小说下载,古典文学↓ 		一键清除垃圾
↓轻轻一点,清除系统垃圾↓ 		图片批量下载器
↓批量下载图片,美女图库↓
  网站联系: qq:121756557 email:121756557@qq.com  IT数码