1.开窗函数 基本上在查询结果上添加窗口列
1.1 聚合函数开窗
基本格式:
..... 函数() over([partition by 分组列,...][order by 排序列 desc|asc][定位框架])
1，partition by 字段 相当于group by 字段 起到分组作用
2，order by 字段 即根据某个字段进行排序，默认包含该分组的所有行的数据，进行聚合或 排序操作
定位框架必须和ORDER BY 一起使用
定位框架 rows|ranges BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
ROWS 按照行进行累计操作
RANGES 按照值进行累计操作
3，ROWS|RANGE 窗口子句，跟在 order by 子句后面用来限制当前行聚合或排序操作的范围
4，range和rows的区别：
rows 是物理窗口，是哪一行就是哪一行，与当前行的值（order by key的key的值）无
关，只与排序后的行号相关，就是我们常规理解的那样。
range 是逻辑窗口，与当前行的值有关（order by key的key的值）,在key上操作range
范围 (查看示例代码2,3即可理解)
5，窗口子句的几个范围语法的格式：
current row :当前行
unbounded proceding 窗口上边界不设限(即区间的第一行)
unbounded following 窗口下边界不设限(即区间的最后一行)
N proceding 当前行之前的N行，可以是数字也可以是能计算数字的表达式
N following 当前行之后的N行 ，同上
聚合函数 over()可以不强制要求写partition by 和Order by
--创建表格
create table student(name varchar2(20),city varchar2(20),age number,salary number );
--插入数据
INSERT INTO student(name,city,age,salary)
VALUES('Kebi','JiangSu',20,3000);
INSERT INTO student(name,city,age,salary)
VALUES('James','ChengDu',21,4000);
INSERT INTO student(name,city,age,salary)
VALUES('Denglun','BeiJing',22,3500);
INSERT INTO student(name,city,age,salary)
VALUES('Yangmi','London',21,2500);
INSERT INTO student(name,city,age,salary)
VALUES('Nana','NewYork',22,1000);
INSERT INTO student(name,city,age,salary)
VALUES('Sunli','BeiJing',20,3000);
INSERT INTO student(name,city,age,salary)
VALUES('Dengchao','London',22,1500);
INSERT INTO student(name,city,age,salary)
VALUES('Huge','JiangSu',20,2800);
INSERT INTO student(name,city,age,salary)
VALUES('Pengyuyan','BeiJing',24,4500);
INSERT INTO student(name,city,age,salary)
VALUES('Baoluo','London',25,8500);
INSERT INTO student(name,city,age,salary)
VALUES('Huting','ChengDu',25,3000);
INSERT INTO student(name,city,age,salary)
VALUES('Hurenxiang','JiangSu',23,2500);
查询从第一行到当前行的工资总和
select name,
city,
age,
salary,
sum(salary) over(order by salary rows between unbounded preceding and current row) 到当前行工资求和
from student
over后面的括号中的unbounded preceding表示第一行，current row表示当前行。上面这段代码指的是首先将表中的数据按照salary进行排序，如果不指明是升序还是降序，默认的是升序。然后看到rows这个字段，说明计算是按照行进行的。就是计算unbounded preceding（第一行）到current row（当前行）的和。比如第一行的salary为1000，第二行的salary为1500，那么第一行到第二行的和为1000+1500=2500；同理第三行salary为2500，那么从第一行到第三行的和为1000+1500+2500=5000，以此类推......
查询从第一行到当前行的工资总和
select fname,
fcity,
fage,
fsalary,
sum(fsalary) over(order by fsalary range between unbounded preceding and current row) 到当前行工资求和
from t_person
range和rows，rows是按照行进行计算的，而range是按照范围进行计算的。这两种方式的不同点是处理并列数据的情况，上面第三行和第四行出现了两个2500，如果是rows就会在第三行显示1000+1500+2500=5000，第四行显示1000+1500+2500+2500=7500；如果是range就会在第三行显示1000+1500+2500+2500=7500，第四行显示1000+1500+2500+2500=7500，因为第三行和第四行中的salary是一样的，同时又是按照range进行计算的，所以从第一行开始r无法判断并列行中的当前行是哪一行，所以直接将并列的数相加
1.2 2.排名函数
注意：排名函数必须要配合order by一起使用
row_number():忽略值重复的情况，直接现实一个连续的不重复的名次，相当于行号。
rank():相同的值名次一样，但是整个排名是跳跃的，不连续
dense_rank():相同的值相同的名次，并且排名连续。
select name , city ,age , salary,
row_number()over ( order by salary) as row_number, --按薪水依次排名
rank()over ( order by salary) as rank, --按薪水排名，相同薪水并列
dense_rank()over ( order by salary) as dense_rank --按薪水排名，相同薪水隔几个排名
from student
每个员工比自己早入职的5个人和后入职的5个人的工资平均值，看看自己和他们之间的差距。
select EMPNO,job,sal,avg(sal) over (order by HIREDATE rows
between 5 preceding and 5 following ) from EMP e;
查询大于自己部门平均工资的员工信息
select ENAME,DEPTNO,sal,JOB,RN from(
select e.*,avg(sal) over (partition by DEPTNO order by sal desc
rows between unbounded preceding and unbounded following ) RN from EMP e)
where sal>RN;
排序类开窗函数
RANK--序列集排序，相等值的行排名相同
--如果排名中，有相同的值，那么排名并列存在
--有几个并列存在的人，下一个排名就会顺延几个整数
select ENAME,job,sal,rank()over (partition by DEPTNO order by sal desc) RK from EMP e
DENSE_RANK--稠密排名
计算序组中的行的排名，排名从1开始的连续整数
select ENAME,job,sal,dense_rank()over (partition by DEPTNO order by sal desc) RK from EMP e
ROW_NUMBER
--给你已定的排序组中，从1开始为每一行分配一个唯一的数字。
--不会有并列
select ENAME,job,sal,row_number()over (order by sal desc) RK from EMP e
ntile() 平均分组函数
--将30号部门的人，按照工资高低分为三个档
--将30号部门的人，按照工资高低分为三个档
select ENAME,job,sal,ntile(3) over (order by sal desc) RK_level from EMP e
where DEPTNO =30;
1.2..3 连续性问题怎么解决
1.ORACLE查询各个用户最大连续登陆天数
思路:对数据进行去重 ->对去重后的数据编号->计算差值 连续的差值相等 ->使用 Group by
分组 count(*)计算值
SELECT team,rn,COUNT(*) FROM(
SELECT n.*,y-row_number()OVER(PARTITION BY team ORDER BY y ) rn FROM nba n) GROUP BY team,rn
HAVING COUNT(*)>=2
1.3 lag lead 位移函数
注意：必须要配合order by一起使用
1.lag():下移函数
语法：lag(列名,n[,空值替换值]) over(....order by ....)
2.lead():上移函数
语法：lag(列名,n[,空值替换值]) over(....order by ....)
使用场景：计算环比和同比
--将员工的工资进行下移一行
SELECT e.*,LAG(sal,1) OVER(ORDER BY sal) FROM emp e;
--计算每一个月的同比率和环比率
SELECT MONTHS,sell,round((sell-同比)/同比*100,2)||'%' 同比率,
ROUND((sell-环比)/环比*100,2)||'%' 环比率 from(
SELECT s.*,LAG(sell,12) over(ORDER BY MONTHS) 同比,
LAG(sell,1)OVER(ORDER BY MONTHS)环比 FROM sales s);
2.1 行列转换
横表是指多列记录同一类属性的不同取值，而纵表则是将这类属性及其对应的取值展开为多行记录
-- 创建横表 Employee_Projects CSND-小小野猪
CREATE TABLE Employee_Projects (
EmployeeID VARCHAR2(10),
ProjectName VARCHAR2(30),
Score NUMBER
);
-- 插入示例数据 CSND-小小野猪
INSERT INTO Employee_Projects VALUES ('Emp1', 'ProjectA', 85);
INSERT INTO Employee_Projects VALUES ('Emp1', 'ProjectB', 90);
INSERT INTO Employee_Projects VALUES ('Emp2', 'ProjectA', 92);
INSERT INTO Employee_Projects VALUES ('Emp3', 'ProjectA', 95);
INSERT INTO Employee_Projects VALUES ('Emp3', 'ProjectB', 88);
INSERT INTO Employee_Projects VALUES ('Emp3', 'ProjectC', 93);
COMMIT;
Oracle SQL从11g版本开始引入了Pivot关键字，用于将横表转换为纵表。其基本语法如下：
select * from EMPLOYEE_PROJECTS
pivot (max(Score) for ProjectName in ('ProjectA' A,'ProjectB' B,'ProjectC' C));
-- 方法2
select EmployeeID, max(case ProjectName when 'ProjectA' then Score else 0 end) A,
max(case ProjectName when 'ProjectB' then Score else 0 end) B,
max(case ProjectName when 'ProjectC' then Score else 0 end) C
from EMPLOYEE_PROJECTS group by EmployeeID
-- 方法3
select EmployeeID,max(decode(ProjectName,'ProjectA',Score,0)) A,
max(decode(ProjectName,'ProjectB',Score,0)) B,
max(decode(ProjectName,'ProjectB',Score,0)) B
from EMPLOYEE_PROJECTS group by EmployeeID;列转行
with tmp1 as(
select EmployeeID, max(case ProjectName when 'ProjectA' then Score else 0 end) A,
max(case ProjectName when 'ProjectB' then Score else 0 end) B,
max(case ProjectName when 'ProjectC' then Score else 0 end) C
from EMPLOYEE_PROJECTS group by EmployeeID)
select * from tmp1 unpivot (score for Projectname in(A as 'ProjectA',B as 'ProjectB',C
as 'ProjectC'));3.正则
3.1 基本语法
（1）字符匹配
. ：匹配除了换行外的任意一个字符；
\d：匹配任何数字，相当于 [0-9]；
\D：匹配任何非数字字符，相当于 [^0-9]；
\w：匹配任何字母数字字符或下划线，相当于 [a-zA-Z0-9_]；
\W：表示匹配任何非字母数字字符或下划线，相当于 [^a-zA-Z0-9_]。
（2）限定符
*：匹配前一个字符出现0次或多次；
+：匹配前一个字符出现1次或多次；
?：匹配前一个字符出现0次或1次；
{n}：匹配前一个字符出现n次；
{n,}：匹配前一个字符出现n次或更多；
{n,m}：匹配前一个字符出现n~m次。
'|' ：指两项之间的一个选择。
e.g. ^([a-z]+|[0-9]+)$：表示所有小写字母或数字组合成的字符串。
（3）边界匹配
^：匹配开始位置；
$：匹配结束位置；
\b：匹配单词边界，即单词的开头或结尾位置；
\B：匹配非单词边界，即不是单词的开头或结尾位置。
（4）分组和引用
()：分组，标记一个子表达式的开始和结束位置；
\num：引用第num个子表达式，num从1开始。
（5）字符集合
[]：表示一组字符中的任意一个。
（6）转义符
\\：表示转义一个字符。
oracle正则表达式还支持一些高级语法，例如贪婪匹配、非贪婪匹配、零宽断言(zero-width assertion)、后向引用(backreference)、捕获组等。
3.2 POSIX字符类
Oracle数据库中的POSIX字符类是一组特殊的字符类，用于在正则表达式中匹配特定的字符。POSIX字符类以 [: 开头，以 :] 结尾，中间包含一个或多个字符，代表特定的字符集合。POSIX字符类中的字符集合可以是预定义的，也可以是自定义的。
[[:alpha:]] 任何字母，等同于字符集合 [a-zA-Z]；
[[:digit:]] 任何数字，等同于字符集合 [0-9]；
[[:alnum:]] 任何字母和数字，等同于字符集合 [a-zA-Z0-9]；
[[:space:]] 任何白字符；
[[:upper:]] 任何大写字母；
[[:lower:]] 任何小写字母；
[[:punct:]] 任何标点符号；
[[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]。
Oracle数据库中，POSIX字符类可以用于各种正则表达式相关的操作，如模式匹配、替换、分割等。由于Oracle数据库中的POSIX字符类与其他数据库或编程语言中的POSIX字符类可能略有不同，具体使用时需要查看相关文档。
3.3 正则表达式函数
3. 正则表达式函数
Oracle数据库提供了多种正则表达式函数，可以对文本数据进行匹配、替换等操作。
REGEXP_LIKE: 判断字符串是否匹配指定的正则表达式。
e.g. 查询员工名字以"S"开头，以"n"结尾的记录：
SELECT * FROM emp WHERE REGEXP_LIKE(emp_name, '^S.*n$');
REGEXP_REPLACE: 替换字符串中的子串。
e.g. 将字符串"12345"中连续的三个数字替换成星号"*"，输出
“*45”：
SELECT REGEXP_REPLACE('12345', '\d{3}', '*') FROM dual;
REGEXP_SUBSTR: 提取字符串中匹配指定正则表达式的子串。
e.g. 从字符串"abc 123 def"中提取出连续的数字"123"：
SELECT REGEXP_SUBSTR('abc 123 def', '\d+') FROM dual;
REGEXP_INSTR: 返回字符串中匹配指定正则表达式的子串的位置。
e.g. 返回字符串"1ab2cd3ef"中第一个连续数字的起始位置，即1
SELECT REGEXP_INSTR('1ab2cd3ef', '\d+') FROM dual;
正则函数：
1.regexp_like(列名,'正则表达式'):功能类似于like关键字，但是比like功能强大
--查询员工的姓名以S开头的员工信息
SELECT * FROM emp WHERE ename LIKE 'S%';
SELECT * FROM emp WHERE regexp_like(ename,'^S');
--查询姓名以’T‘结尾的员工
SELECT * FROM emp WHERE regexp_like(ename,'T$');
--查询员工的姓名第一个字母必须是O,并且名字的长度必须是5的大写字母。
SELECT * FROM emp WHERE regexp_like(ename,'^[A-Z]O[A-Z]{3}$')
--查询出来由有效手机号的行
SELECT * FROM regexp_t WHERE regexp_like(str,'1[356789]\d{9}');
--过滤出来有效的日期，前后的符号必须要一致
SELECT * FROM REGEXP_t WHERE regexp_like(str,'^\d{4}(-|\.|\/)\d{2}\1\d{2}$')
--过滤出来有效的域名
SELECT * FROM regexp_t WHERE regexp_like(str,'^[a-z]{6}\.[a-z]{3}$');
2.regexp_instr(字符串,'正则表达式'[,i,j]):功能和instr类似，用来在一个字符串中查找正则表达式所描述的子字符串出现的位置
i:代表开始查找的位置
j:代表第几次出现
--查找有效手机号的位置
SELECT str,regexp_instr(str,'1[356789]\d{9}') FROM regexp_t;
--查找数字第二次出现的位置
SELECT regexp_instr('qwqweq223dssdgsdf547hbfb','\d+',1,2) FROM dual;
3.regexp_substr(字符串,'正则表达式'[,i,j]):从字符串中截取正则表达式描述的子字符串
--截取字符串中有效的手机号
SELECT str,regexp_substr(str,'1[356789]\d{9}') FROM regexp_t;
--qqqq,wwww,rrrr,tttt
--截取字符串中以逗号分开的第二部分数据
SELECT regexp_substr('qqqq,wwww,rrrr,tttt','[^,]+',1,2) FROM dual;
4.regexp_replace(字符串,'正则表达式','新值'):将字符串中正则表达式描述的部分使用新的字符串替换。
--将字符串中的空白符替换为*
SELECT regexp_replace('qwe qwe qw ewr erwe wer','\s+','*') FROM dual;
SELECT replace('qwe qwe qw ewr erwe wer',' ','*') FROM dual;
--将字符串中的手机号替换为****
SELECT str,regexp_replace(str,'1[356789]\d{9}','****') FROM regexp_t;
--将字符串中的手机号中间四位替换为****
SELECT str,regexp_replace(str,'(1[356789]\d)(\d{4})(\d{4})','+86 \1****\3') FROM regexp_t;
5.regexp_count(字符串,'正则表达式'):统计正则表达式描述的字符串在字符串中出现的次数
--统计逗号出现的次数
SELECT regexp_count('qqqq,wwww,rrrr,tttt',',') FROM dual;
--统计有效手机号出现的次数
SELECT str,regexp_count(str,'1[356789]\d{9}') FROM regexp_t;
3.4 贪婪匹配 惰性匹配 零宽断言 后向引用 捕获组
贪婪匹配: 通常的行为是（在使整个表达式能得到匹配的前提下）匹配尽可能多的字符。比如这个表达式：a.*b，它将会匹配最长的以a开始，以b结束的字符串。如果用它来搜索aabab的话，它会匹配整个字符串aabab。这被称为贪婪匹配。在正则表达式中，使用量词（如*、+、?、{n,}等）时，默认会匹配尽可能多的字符。
惰性匹配: 也就是匹配尽可能少的字符。在能使整个匹配成功的前提下使用最少的重复，只要在它后面加上一个问号?即可
使用场景 :如果需要捕获的内容前后必须是特定内容，但又不捕获这些特定内容的时候，这个时候就可以使用零宽断言。
零宽断言: 零宽断言是一种零宽度的匹配，它匹配到的内容不会保存到匹配结果中去，最终匹配结果只是一个位置而已。
正则表达式中常用的断言元字符为：^和$，而零宽断言就是其他用正则表达式来定义的功能类似的断言。
后向引用: 指把匹配出来的组引用到表达式本身其它地方，比如，在匹配HTML的标记时，我们匹配出一个＜a＞,我们要把匹配出来的a引用出来，用来找到＜/a＞，这个时候就要用到反向引用。
“H.{4}”表示大写字母H的后面跟随了4个任意字符，其中”.”表示任意单个字符
文章来源：https://www.toymoban.com/news/detail-854977.html
文章来源地址https://www.toymoban.com/news/detail-854977.html
到了这里，关于Oracle 正则,开窗,行列转换的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
