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

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

news 2025/7/8 20:59:31

文章目录

- - 开窗函数、组函数、分析函数概念
  - - 聚合函数和分析函数的区别
    - partition by后面也可以跟多个字段
  - 开窗函数一定要加聚合函数、或分析函数吗，否则会报错
  - lag()和lead()的用法
  - - lag和lead实战
    - 开窗函数可以和其他函数一起使用吗? TODO
    - 开窗函数中的count(1)是什么意思
    - pivot行列转换函数 TODO
    - unpivot函数 TODO
    - group_conat
    - cross apply
    - mode的用法(也可能不是组函数等，放在这里不太合适?)
- 其他
- - 这一篇博客不错

开窗函数算是中阶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: 丢失的此函数窗口说明
注：这种写法报错，感觉是开窗函数前应该是要有分析函数(例如组函数)。