Oracle 正则,开窗,行列转换

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 ROWROWS 按照行进行累计操作 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 rowsbetween 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 descrows 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号部门的人，按照工资高低分为三个档

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,rnHAVING 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) Cfrom 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)) Bfrom 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) Cfrom EMPLOYEE_PROJECTS group by EmployeeID)
select * from tmp1 unpivot (score for Projectname in(A as 'ProjectA',B as 'ProjectB',Cas '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"中第一个连续数字的起始位置，即1SELECT 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,}等）时，默认会匹配尽可能多的字符。