数据库的高阶知识

数据库的基本知识
数据库的高阶知识

一、case when

  在实际工作中，经常会涉及以下两类问题：

• 数据的映射处理：比如将离散的数字变成有意义的业务说明，或是将连续的数值映射到离散的区间带
• 有针对性的筛选计算：将细颗粒单位转换为粗颗粒单位的聚合运算

  这两类问题，我们通常使用case when语句来解决，case语句其实就是mysql中的控制流语句，类似于其他很多编程工具中的if …then…的分支判断逻辑。
  关于case when的表达方式有两种：“简单case函数法”和“case搜索函数法”。这两种方法各有优点，比如“简单case函数法”的语法更加简洁，但是只能处理等式的问题，“case搜索函数法”更加灵活好用，可以处理等式问题也可以处理不等式问题。

# 简单case函数法
case <name>when <when_condition> then <result>[when <when_condition> then <result>][......][else <else_result>]
end
#case搜索函数法
case when <bool_condition> then <result>[when <bool_condition> then <result>][.......][else <else_result>]
end

  下面以电商数据为例学习CASE WHEN语法在数据查询中几种常见用法，该数据一共包含7个字段和1000条样本，7个字段分别是用户ID，用户出生日期、下单时间、订单ID、支付方式、支付金额和是否享受折扣。数据链接：https://pan.quark.cn/s/af980c28a2d0

-- 离散数值与实际业务含义一一对应
select *,case when is_discount = 1 then "享受折扣"else "无折扣"end discount_new,casewhen pay_type = 1 then "支付宝支付"when pay_type = 2 then "微信支付"else "银行卡支付"end pay_type_new
from goods_orders#根据用户的出生日期将用户分为70后，80后，90后,00后
select *,casewhen year(birthday) between 1970 and 1979 then '70后'when year(birthday) between 1980 and 1989 then '80后'when year(birthday) between 1990 and 1999 then '90后'else '00后'end AGE_GROUP
from goods_orders#长形统计表转换成宽形统计表
select month(order_date) as imonth,sum(case when pay_type = 1 then pay_amt end) as 'ali_pay',sum(case when pay_type = 2 then pay_amt end) as 'wechat',sum(case when pay_type = 3 then pay_amt end) as 'bank_card'
from goods_orders		
where year(order_date) = 2023
group by imonth
order by imonth

二、几种常见的嵌套查询

  嵌套查询，也称为子查询，是实际工作中经常用到的一种查询方式。子查询其实就是在已有的查询语句中的where后面再嵌套一层查询语句，也就是把内层查询结果当做外层查询参照的数据表来使用。
  在工作中，经常会遇见4种子查询，即含有比较运算符(>、>=、<、<=、=、!=)、ANY/ALL关键词、IN关键词以及EXISTS关键词的嵌套查询。数据链接 ：https://pan.quark.cn/s/223ade8607a3

2.1 比较运算符

  比较运算符用于比较两个值，返回布尔值（TRUE 或 FALSE）。

#创建学生信息表
CREATE TABLE stu_info (id INT AUTO_INCREMENT PRIMARY KEY, -- 学生ID，自增主键iname VARCHAR(20),                 -- 学生姓名gender CHAR(1),                    -- 性别（M: 男, F: 女）department VARCHAR(10),            -- 所属院系age INT,                           -- 年龄province VARCHAR(10),              -- 省份email VARCHAR(50),                 -- 邮箱mobilephone CHAR(11)               -- 手机号（11位）
);INSERT INTO stu_info (iname, gender, department, age, province, email, mobilephone)
VALUES
('张三', 'M', '计算机', 20, '北京', 'zhangsan@example.com', '13800138000'),
('李四', 'M', '数学', 21, '上海', 'lisi@example.com', '13800138001'),
('王五', 'F', '物理', 22, '广东', 'wangwu@example.com', '13800138002'),
('赵六', 'M', '化学', 19, '江苏', 'zhaoliu@example.com', '13800138003'),
('孙七', 'F', '生物', 20, '浙江', 'sunqi@example.com', '13800138004'),
('周八', 'M', '计算机', 21, '北京', 'zhouba@example.com', '13800138005'),
......
('武勇', 'M', '生物', 21, '浙江', 'wuyong@example.com', '13800138049');#学生成绩表
CREATE TABLE stu_score (id INT,          -- 学生IDChinese TINYINT, -- 语文成绩（0-100）Math TINYINT,    -- 数学成绩（0-100）English TINYINT  -- 英语成绩（0-100）
);INSERT INTO stu_score (id, Chinese, Math, English)
VALUES
(1, 87, 90, 83),
(2, 78, 85, 88),
(3, 92, 76, 81),
......
(50, 87, 80, 83);# 1.查询年龄超过所有学员平均年龄的学员信息
#第一步先计算学员平均年龄
select avg(age) from stu_info#筛选超过平均年龄的学员信息
select * from stu_info
where age >= 20.5#子查询
select * from stu_info
where age >= (select avg(age) from stu_info)# 2.查询年龄不低于所属系平均年龄的学员信息
select * from stu_info as t1
where age >=(
select avg(age) from stu_info as t2
where t1.department = t2.department)

2.2 ANY/ALL 关键词

  对于含比较运算符的嵌套查询来说，嵌套部分的查询语句只能返回一个值。那如果子查询返回多个值，就需要用到ANY或者ALL关键词了。通常，ANY/ALL关键词经常和比较运算符连用，下面是6种比较运算符与ANY/ALL 关键词的搭配结果:

组合	含义	组合	含义
>ANY	大于子查询结果中的某个值	>ALL	大于子查询结果中的所有值
>=ANY	大于等于子查询结果中的某个值	>=ALL	大于等于子查询结果中的所有值
<ANY	小于子查询结果中的某个值	<ALL	小于子查询结果中的所有值
<=ANY	小于等于子查询结果中的某个值	<=ALL	小于等于子查询结果中的所有值
=ANY	等于子查询结果中的某个值	=ALL	等于子查询结果中的所有值
!=ANY	不等于子查询结果中的某个值	!=ALL	不等于子查询结果中的所有值

# 2.查询非物理系中比物理系任意一个学员年龄小的学员信息
select * from stu_info 
where department != '物理' and
age < any(
select distinct age 
from stu_info
where department = '物理'
)#3.查询非物理系中比物理系所有学员年龄大的学员信息
select * from stu_info 
where department != '物理' and
age > all(
select distinct age 
from stu_info
where department = '物理'
)

2.3 in 关键词

  IN 用于判断某个值是否在指定的值列表中。它可以替代多个 OR 条件，使查询更简洁。

# 查询计算机和物理系的学员信息
select * from stu_info
where department in ("计算机","物理")#查询和张三、王五同一个系的学员信息
select * from stu_info
where department in (
select department from stu_info
where iname in("张三","王五"))#查询语文成绩大于85分的学员信息
select * from stu_info where id in (select id from stu_score
where Chinese >85)

2.4 EXISTS关键词

  EXISTS 用于检查子查询是否返回任何行。如果子查询返回至少一行，则 EXISTS 返回 TRUE，否则返回 FALSE。

#查询语文成绩大于85分的学员信息
select * from stu_info where exists (select * from stu_score
where stu_score.id = stu_info.id and Chinese >85)

2.5 in和exists的异同点

• 使用场景对比
    in适合用于静态值列表或子查询结果集较小的情况。

-- 查询数学成绩为 85、90 或 95 的学生
SELECT * FROM stu_score
WHERE Math IN (85, 90, 95);-- 查询在某个班级的学生
SELECT * FROM stu_info
WHERE class_id IN (SELECT class_id FROM class WHERE class_name = '计算机');

  exists适合用于动态子查询或子查询结果集较大的情况。

-- 查询有成绩记录的学生
SELECT * FROM stu_info
WHERE EXISTS (SELECT 1 FROM stu_scoreWHERE stu_score.id = stu_info.id
);-- 查询没有成绩记录的学生
SELECT * FROM stu_info
WHERE NOT EXISTS (SELECT 1 FROM stu_scoreWHERE stu_score.id = stu_info.id
);

• 性能对比
  IN 的性能：
  当子查询结果集较小时，IN 的性能较好，因为子查询的结果会被缓存，外部查询只需要与缓存的结果进行比较。
  当子查询结果集较大时，IN 的性能较差，因为缓存大量数据会占用内存，且比较操作较慢。
  EXISTS 的性能：
  当子查询结果集较大时，EXISTS 的性能较好，因为它不需要缓存结果，且可以在找到第一个匹配项时立即返回 TRUE。
  当子查询结果集较小时，EXISTS 的性能可能略低于 IN，因为需要为外部查询的每一行执行一次子查询。

• NULL值处理区别
  in：如果子查询的结果中包含 NULL，IN 会忽略 NULL，不会将其与外部查询的值进行比较。

SELECT * FROM stu_score
WHERE Math IN (85, 90, NULL);
#如果 Math 是 NULL，上述查询不会返回该行。

EXISTS：不关心子查询的具体值，只关心是否存在匹配的行。

SELECT * FROM stu_info
WHERE EXISTS (SELECT 1 FROM stu_scoreWHERE stu_score.id = stu_info.id AND Math IS NULL
);

三、开窗函数

  结合order by关键词和limit关键词是可以解决很多的topN问题，比如从二手房数据集中查询出某个地区的最贵的10套房，从电商交易数据集中查询出实付金额最高的5笔交易，从学员信息表中查询出年龄最小的3个学员等。但是，如果需求变成从二手房数据集中查询出各个地区最贵的10套房，从电商数据集中查询出每月实付金额最高的5笔交易，从学员信息表中查询出各个科系下年龄最小的3个学员，该如何解决呢?
  其实这类问题的核心就是，筛选出组内的topN，而不是从全部数据集中挑选出topN。遇到这种既需要分组也需要排序的问题，直接上开窗函数就能解决了。
（1）开窗函数定义
  开窗函数是 SQL 中一种特殊的函数，用于在查询结果集的“窗口”（即一组相关行）上执行计算。与普通的聚合函数（如 SUM、AVG）不同，开窗函数不会将多行合并为一行，而是为每一行返回一个计算结果，同时保留原始行的详细信息。 注：MySQL 8.0 及以上版本支持开窗函数，低于版本不支持。
  开窗函数分为两部分，一部分是函数名称，开窗函数的数量比较少，总共才11个开窗函数+聚合函数（所有聚合函数都可以用作开窗函数），根据函数的性质，有的需要写参数，有的不需要写参数。
  另一部分为over语句，over()语句必须要写的，里面的参数都是非必须参数，可以根据需求有选择的使用。

函数名(字段) OVER ([PARTITION BY 分组字段] #含义是根据此字段将数据集分为多份[ORDER BY 排序字段] #每个窗口的数据依据此字段进行升序或降序排列[ROWS/RANGE 窗口范围]
)函数名：开窗函数的名称，如 ROW_NUMBER()、RANK()、SUM() 等。
OVER：定义窗口的范围和规则。
PARTITION BY：将数据分组，类似于 GROUP BY，但不会合并行。
ORDER BY：指定窗口内的排序规则。
ROWS/RANGE：定义窗口的物理或逻辑范围。

#创建虚拟的业务员销售数据
create table Sales(idate date,iname char(5),sales int)
#向表中插入数据
insert into Sales values('2024-01-01', '张三', 200)-- 插入50行模拟数据
INSERT INTO Sales (idate, iname, sales)
VALUES
('2024-01-01', '张三', 200),
('2024-01-02', '张三', 650),
('2024-01-03', '王五', 200),
('2024-01-04', '赵六', 250),
('2024-01-05', '王五', 300),
......
('2024-02-18', '苏勇', 2500);# 一、查询各月中销售业绩最差的业务员
select * from(
select month(idate),iname,sales,row_number() over(partition by month(idate) order by sales) as sales_order from sales) as t  #易错点：在 SQL 中，所有的派生表必须有一个别名，没有指定别名，MySQL 报错
where sales_order= 1# row_number()、rank()、dense_rank()的区别
select * from(
select month(idate),iname,sales,row_number() over(partition by month(idate) order by sales) as row_order,rank() over(partition by month(idate) order by sales) as rank_order,dense_rank() over(partition by month(idate) order by sales) as dense_order from sales) as t# 连续登录的问题：创建虚拟的用户登录表
create table user_login(user_id varchar(100),login_time datetime);
# 二、查看每位用户连续登录的情况
#1、时间戳格式修改为时间格式
create table user_login_date (
select distinct user_id,date(login_time) login_date
from user_login)# login_date - irank 相等表示连续登录
-- select * from user_login_date#2、对用户登录数据进行排序
create table user_login_data_1 (select *,rank() over(partition by user_id order by login_date) irankfrom user_login_date)-- select * from user_login_data_1# 3、date_sub 从指定的日期减去指定的时间间隔
create table user_login_data_2(
select *,date_sub(login_date,interval irank day) idate from user_login_data_1);-- select * from user_login_data_2
# 4、计算连续登录的用户情况select user_id,min(login_date) as start_date,max(login_date) as end_date,count(login_date) as days
from user_login_data_2
group by user_id,idate#整合代码
select user_id,min(login_date) as start_date,max(login_date) as end_date,count(login_date) as days
from (select *,date_sub(login_date,interval irank day) idate from (select *,rank() over(partition by user_id order by login_date) irankfrom (select distinct user_id,date(login_time) login_date
from user_login) as c) as b) as a 
group by user_id,idate# 三、查看每位用户最大连续登录的天数
select user_id,max(days) from(select user_id,min(login_date) as start_date,max(login_date) as end_date,count(login_date) as days
from (select *,date_sub(login_date,interval irank day) idate from (select *,rank() over(partition by user_id order by login_date) irankfrom (select distinct user_id,date(login_time) login_date
from user_login) as c) as b) as a 
group by user_id,idate) as d
group by user_id# 四、查看在这段时间内连续登录天数>=5天的用户
#方案1：
select distinct user_id from (select user_id,min(login_date) as start_date,max(login_date) as end_date,count(login_date) as days
from (select *,date_sub(login_date,interval irank day) idate from (select *,rank() over(partition by user_id order by login_date) irankfrom (select distinct user_id,date(login_time) login_date
from user_login) as c) as b) as a 
group by user_id,idate
having days >=5) d #方案2：
#lead()函数：静态窗口函数
select *,lead(login_date,4) over (partition by user_id order by login_date ) as idate5
from user_login_date#计算第5次登录与当天的差值
select *,datediff(idate5,login_date)+1 days
from (select *,lead(login_date,4) over (partition by user_id order by login_date ) as idate5
from user_login_date) as a #查找相差天数为5的记录
select distinct user_id from (select *,datediff(idate5,login_date)+1 days
from (select *,lead(login_date,4) over (partition by user_id order by login_date ) as idate5
from user_login_date) as a )as b 
where days = 5

  DATE_SUB 是 SQL 中用于从日期中减去指定时间间隔的函数。它通常用于计算过去某个时间点的日期。以下是 DATE_SUB 函数的详细用法及解释：

DATE_SUB(date, INTERVAL value unit)
# date：要操作的日期或日期时间值。
# INTERVAL：表示时间间隔的关键字。
# value：要减去的时间间隔值，必须是一个整数。
# unit：时间间隔的单位，支持以下值：秒、分钟、小时、天、周、月、季度、年
示例：
SELECT DATE_SUB('2023-10-01', INTERVAL 5 DAY); # 从 2023-10-01 减去 5 天：结果：2023-09-26

  LEAD() 是 SQL 中的一种 窗口函数（Window Function），用于获取当前行之后的某一行数据。它通常用于分析时间序列数据或计算相邻行之间的差异。

LEAD(column_name, offset, default_value) OVER ([PARTITION BY partition_expression][ORDER BY sort_expression]
)
# column_name：需要获取值的列名。
# offset（可选）：指定要获取的行数偏移量。默认值为 1，表示获取下一行。
# default_value（可选）：如果偏移量超出范围（如最后一行没有下一行），则返回的默认值。如果未指定，默认返回 NULL。
# PARTITION BY（可选）：将数据分组，LEAD() 函数会在每个分组内单独计算。
# ORDER BY（可选）：指定窗口内的排序规则。

  LEAD() 函数的作用：获取当前行之后的某一行数据；常用于计算相邻行之间的差异，或分析时间序列数据。