oracle开窗函数笔记、over()笔记

开窗函数算是中阶sql了(相对于初级sql来说)。

之前一直不太了解，接触过几次觉得有点难，就一直没用起来，随着业务的发展，不会开窗函数确实太受瘪。

开窗函数、组函数、分析函数概念

他们之间的包含关系是：

分析函数>窗口函数>组函数。

开窗函数的理解：
相当于先分组，然后再组内统计，这样就好理解了。

聚合函数和分析函数的区别

聚合函数	分析函数
聚合函数是汇总计算返回一个计算结果	分析函数会根据表的行数，每行返回一个计算结果。
聚合函数使用的时候有很多语法限制	分析函数几乎没有限制
聚合函数分组使用 GROUP BY ，会对分组字段去重	分析函数分组使用 PARTITION BY ，PARTITION BY 没有去重的功能。
聚合函数可以直接放在SQL中当条件使用，比如在 HAVING 后面可以直接用 聚合函数当条件	分析函数不可以直接当条件，必须对分析函数的查询结果 使用子查询套一下 再进行过滤， 否则会报语法错误。
ORDER BY 在聚合函数中是对查询结果排序	在分析函数中有2个作用 排序 和 累计计算
在使用分组 聚合后 ORDER BY 后面只能跟 分组字段 或者 聚合函数	分析函数中 没有这种限制

partition by后面也可以跟多个字段

开窗函数一定要加 聚合函数、或分析函数吗，否则会报错

ORA-00923: 未找到要求的 FROM 关键字

lag()和lead()的用法

lag # 找前一条记录(这两个千万别弄串了)
lead # 找下一条记录

lag和lead实战

建表及造数：

CREATE TABLE T_SALARY(	ID VARCHAR2(100),USER_NAME VARCHAR2(100),PERIOD VARCHAR2(100),AMOUNT NUMBER(20,2),CONSTRAINT T_SALARY_PK PRIMARY KEY (ID));
INSERT INTO T_SALARY (ID,USER_NAME,PERIOD,AMOUNT) VALUES ('1','zhangsan','2024-01',90);
INSERT INTO T_SALARY (ID,USER_NAME,PERIOD,AMOUNT) VALUES ('2','zhangsan','2024-02',95);
INSERT INTO T_SALARY (ID,USER_NAME,PERIOD,AMOUNT) VALUES ('3','zhangsan','2024-03',96);

查询：

环比：
SELECT
period,amount,
amount / LAG(amount) OVER (ORDER BY period) AS cycleDiff
FROM
T_SALARY;错误语句：
SELECT
period,amount,LEAD(amount)/amount OVER (ORDER BY period) AS cycleDiff
FROM
T_SALARY;
报错：
ORA-30484: 丢失的此函数窗口说明
注：这种写法报错，感觉是开窗函数前应该是要有分析函数(例如组函数)。

开窗函数可以和其他函数一起使用吗? TODO

开窗函数中的count(1)是什么意思

当count() over()带有 order by 的时候,分组计的都是到当前order by值的数量,不同于排序函数,但可在特殊情况下使用此特性。

pivot行列转换函数 TODO

unpivot函数 TODO

group_conat

cross apply

mode的用法(也可能不是组函数等，放在这里不太合适?)

其他

这一篇博客不错

https://www.cnblogs.com/xulinforDB/p/17496452.html