当前位置： 首页 > news >正文

MySQL---控制流函数、窗口函数（序号函数、开窗聚合函数、分布函数、前后函数、头尾函数、其他函数）

news 2025/7/29 20:38:02

1. 控制流函数

格式

解释

案例

IF(expr,v1,v2)

如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。

SELECT IF(1 > 0,'正确','错误')   

->正确

IFNULL(v1,v2）

如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。

SELECT IFNULL(null,'Hello Word')

->Hello Word

ISNULL(expression)

判断表达式是否为 NULL

SELECT ISNULL(NULL);

->1

NULLIF(expr1, expr2)

比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1

SELECT NULLIF(25, 25);

->

格式

解释

操作

CASE expression

    WHEN condition1 THEN result1

    WHEN condition2 THEN result2

   ...

    WHEN conditionN THEN resultN

    ELSE result

END

CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。

select case 100 when 50 then 'tom'

when 100 then 'mary'else 'tim' end ;

select case when 1=2 then 'tom'

when 2=2 then 'mary' else'tim' end ;

use mydb4; 
-- 创建订单表
create table orders(oid int primary key, -- 订单idprice double, -- 订单价格payType int -- 支付类型(1:微信支付 2:支付宝支付 3:银行卡支付 4：其他)
);insert into orders values(1,1200,1);
insert into orders values(2,1000,2);
insert into orders values(3,200,3);
insert into orders values(4,3000,1);
insert into orders values(5,1500,2);-- 方式1
select 
*  ,
case when payType=1 then '微信支付' when payType=2 then '支付宝支付' when payType=3 then '银行卡支付' else '其他支付方式' 
end  as payTypeStr
from orders;-- 方式2
select 
*  ,
case payTypewhen 1 then '微信支付' when 2 then '支付宝支付' when 3 then '银行卡支付' else '其他支付方式' 
end  as payTypeStr
from orders;

2. 窗口函数

非聚合窗口函数是相对于聚合函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。

-- 语法：
window_function ( expr ) OVER ( PARTITION BY ... ORDER BY ... frame_clause 
)

其中，window_function 是窗口函数的名称；expr 是参数，有些函数不需要参数；OVER子句包

含三个选项：

分区（PARTITION BY）：

PARTITION BY选项用于将数据行拆分成多个分区（组），它的作用类似于GROUP BY分组。如

果省略了 PARTITION BY，所有的数据作为一个组进行计算

排序（ORDER BY）：

OVER 子句中的ORDER BY选项用于指定分区内的排序方式，与 ORDER BY 子句的作用类似

以及窗口大小（frame_clause）：

frame_clause选项用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集。

数据准备：

use mydb4; 
create table employee( dname varchar(20), -- 部门名 eid varchar(20), ename varchar(20), hiredate date, -- 入职日期 salary double -- 薪资
); 
insert into employee values('研发部','1001','刘备','2021-11-01',3000);
insert into employee values('研发部','1002','关羽','2021-11-02',5000);
insert into employee values('研发部','1003','张飞','2021-11-03',7000);
insert into employee values('研发部','1004','赵云','2021-11-04',7000);
insert into employee values('研发部','1005','马超','2021-11-05',4000);
insert into employee values('研发部','1006','黄忠','2021-11-06',4000);insert into employee values('销售部','1007','曹操','2021-11-01',2000);
insert into employee values('销售部','1008','许褚','2021-11-02',3000);
insert into employee values('销售部','1009','典韦','2021-11-03',5000);
insert into employee values('销售部','1010','张辽','2021-11-04',6000);
insert into employee values('销售部','1011','徐晃','2021-11-05',9000);
insert into employee values('销售部','1012','曹洪','2021-11-06',6000);

 2.1 序号函数

序号函数有三个：ROW_NUMBER()、RANK()、DENSE_RANK()，可以用来实现分组排序，并添

加序号。

-- 语法：
row_number()|rank()|dense_rank() over ( partition by ... order by ... 
) 

-- 对每个部门的员工按照薪资排序，并给出排名
select 
dname,
ename,
salary,
row_number() over(partition by dname order by salary desc) as rn 
from employee;

-- 对每个部门的员工按照薪资排序，并给出排名 rank
select 
dname,
ename,
salary,
rank() over(partition by dname order by salary desc) as rn 
from employee;

-- 对每个部门的员工按照薪资排序，并给出排名 dense-rank
select 
dname,
ename,
salary,
dense_rank() over(partition by dname order by salary desc) as rn 
from employee;

--求出每个部门薪资排在前三名的员工- 分组求TOPN
select 
* 
from 
(select dname,ename,salary,dense_rank() over(partition by dname order by salary desc)  as rnfrom employee
)t
where t.rn <= 3

-- 对所有员工进行全局排序（不分组）
-- 不加partition by表示全局排序
select dname,ename,salary,dense_rank() over( order by salary desc)  as rn
from employee;

2.2 开窗聚合函数

select  dname,ename,salary,sum(salary) over(partition by dname order by hiredate) as pv1 
from employee;select cookieid,createtime,pv,
sum(pv) over(partition by cookieid) as pv3
from itcast_t1;  -- 如果没有order  by排序语句  默认把分组内的所有数据进行sum操作

select  dname,ename,salary,sum(salary) over(partition by dname order by hiredate  rows between unbounded preceding and current row) as c1 
from employee;select  dname,ename,salary,sum(salary) over(partition by dname order by hiredate   rows between 3 preceding and current row) as c1 
from employee;

select  dname,ename,salary,sum(salary) over(partition by dname order by hiredate   rows between 3 preceding and 1 following) as c1 
from employee;
select  dname,ename,salary,sum(salary) over(partition by dname order by hiredate   rows between current row and unbounded following) as c1 
from employee;

2.3 分布函数

CUME_DIST：

用途：分组内小于、等于当前rank值的行数 / 分组内总行数

应用场景：查询小于等于当前薪资（salary）的比例

select  dname,ename,salary,cume_dist() over(order by salary) as rn1, -- 没有partition语句 所有的数据位于一组cume_dist() over(partition by dept order by salary) as rn2 
from employee;/*
rn1: 没有partition,所有数据均为1组，总行数为12，第一行：小于等于3000的行数为3，因此，3/12=0.25第二行：小于等于4000的行数为5，因此，5/12=0.4166666666666667
rn2: 按照部门分组，dname='研发部'的行数为6,第一行：研发部小于等于3000的行数为1，因此，1/6=0.16666666666666666
*/

PERCENT_RANK ：

每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗

口的记录总行数

select dname,ename,salary,rank() over(partition by dname order by salary desc ) as rn,percent_rank() over(partition by dname order by salary desc ) as rn2
from employee;/*rn2:第一行: (1 - 1) / (6 - 1) = 0第二行: (1 - 1) / (6 - 1) = 0第三行: (3 - 1) / (6 - 1) = 0.4
*/

2.4 前后函数

返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值

用途：查询前1名同学的成绩和当前同学成绩的差值

-- lag的用法
select dname,ename,hiredate,salary,lag(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time,lag(hiredate,2) over(partition by dname order by hiredate) as last_2_time 
from employee;/*
last_1_time: 指定了往上第1行的值，default为'2000-01-01'  第一行，往上1行为null,因此取默认值 '2000-01-01'第二行，往上1行值为第一行值，2021-11-01 第三行，往上1行值为第二行值，2021-11-02 
last_2_time: 指定了往上第2行的值，为指定默认值第一行，往上2行为null第二行，往上2行为null第四行，往上2行为第二行值，2021-11-01 第七行，往上2行为第五行值，2021-11-02 
*/

-- lead的用法
select dname,ename,hiredate,salary,lead(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time,lead(hiredate,2) over(partition by dname order by hiredate) as last_2_time 
from employee;

2.5 头尾函数

返回第一个（FIRST_VALUE(expr)）或最后一个（LAST_VALUE(expr)）expr的值

应用场景：截止到当前，按照日期排序查询第1个入职和最后1个入职员工的薪资

-- 注意,  如果不指定ORDER BY，则进行排序混乱，会出现错误的结果
selectdname,ename,hiredate,salary,first_value(salary) over(partition by dname order by hiredate) as first,last_value(salary) over(partition by dname order by  hiredate) as last 
from  employee;

2.6 其他函数

NTH_VALUE(expr,n)：

返回窗口中第n个expr的值。expr可以是表达式，也可以是列名

应用场景：截止到当前薪资，显示每个员工的薪资中排名第2或者第3的薪资

-- 查询每个部门截止目前薪资排在第二和第三的员工信息
select dname,ename,hiredate,salary,nth_value(salary,2) over(partition by dname order by hiredate) as second_score,nth_value(salary,3) over(partition by dname order by hiredate) as third_score
from employee

NTILE： 

将分区中的有序数据分为n个等级，记录等级数

应用场景：将每个部门员工按照入职日期分成3组

-- 根据入职日期将每个部门的员工分成3组
select dname,ename,hiredate,salary,
ntile(3) over(partition by dname order by  hiredate  ) as rn 
from employee;

-- 取出每个部门的第一组员工
select
*
from
(SELECT dname,ename,hiredate,salary,NTILE(3) OVER(PARTITION BY dname ORDER BY  hiredate  ) AS rn FROM employee
)t
where t.rn = 1;

 

（日常美图时间）

 

http://www.lryc.cn/news/67078.html

相关文章：

  • 一心报国的西工大网安人走出新手村
  • 如何安装oracle的sample schema
  • ChatGPT ：国内免费可用 ChatGPT +Midjourney绘图
  • 女孩子转数据分析难吗？难在哪里？
  • 基于常用设计模式的业务框架
  • ubuntu重启ssh服务
  • 【19】SCI易中期刊推荐——计算机 | 人工智能领域（中科院2区）
  • Vue.js条件、循环语句
  • Go语言学习查缺补漏ing Day4
  • 说服审稿人，只需牢记这 8 大返修套路！
  • Java 责任链模式详解
  • 使用MASA全家桶从零开始搭建IoT平台（三）管理设备的连接状态
  • 我的新书上架了！
  • 语言与专业的奇迹：如何利用ChatGPT优化跨国贸易
  • 云服务器安装宝塔Linux面板命令脚本大全
  • zed2i相机中imu内参的标定及外参标定
  • Java中的JUnit是什么？如何使用JUnit进行单元测试
  • 【seata的部署和集成】
  • uniapp学习日记之request自定义请求头
  • 【Rust】速度入门---打印个螃蟹先
  • 《Linux 内核设计与实现》12. 内存管理
  • 公司新来个卷王，让人崩溃...
  • Docker 安全及日志管理
  • 大厂面试必备 - MAC 地址 和 IP 地址分别有什么作用？
  • 【sqlite】联查Join更新
  • asp.net+C#德育课程分数统计管理系统
  • Figma中文网？比Figma更懂你的设计网站！
  • Nacos-01-Nacos基本介绍
  • SpringBoot集成Dubbo启用gRPC协议
  • Kali HTTrack演示-渗透测试察打一体(1)