Oracle之分析函数

article/2025/9/18 8:03:26

目录

1. 认识分析函数

1.1 什么是分析函数

1.2 分析函数和聚合函数的不同

1.3 分析函数的形式

2. 理解over()函数

2.1 两个order by 的执行机制

2.2 分析函数中的分组、排序、窗口

2.3 帮助理解over()的实例

3. 常见分析函数

3.1 演示表和数据的生成

3.2 first_value()与last_value():求最值对应的其他属性

3.3 rank()、dense_rank()与row_number() 排序问题

3.4 lag()与lead():求之前或之后的第N行

3.5 rollup()与cube()：排列组合分组

3.6 max()、min()、sum()与avg()：求移动的最值、总和与平均值

3.7 ratio_to_report()：求百分比

-------------------------------------------------------------------------------------------------------------------------------- 

1. 认识分析函数

1.1 什么是分析函数

       分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。

1.2 分析函数和聚合函数的不同

      普通的聚合函数用group by分组，每个分组返回一个统计值；而分析函数采用partition by 分组，并且每组每行都可以返回一个统计值。

1.3 分析函数的形式

       分析函数带有一个开窗函数over(),包含三个分析子句：分组(partition by)，排序(order by),  窗口(rows)，他们的使用形式如下：

over(partition by xxx  order by yyy rows between zzz)
-- 例如在scott.emp表中：xxx为deptno, yyy为sal, 
-- zzz为unbounded preceding and unbounded following

分析函数的例子：

显示各部门员工的工资，并附带显示该部分的最高工资。

SQL如下： 

SELECT DEPTNO, EMPNO, ENAME, SAL, LAST_VALUE(SAL) OVER (PARTITION BY DEPTNO 
ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL 
FROM EMP;

结果为：

注： current row   表示当前行

        unbounded preceding  表示第一行

        unbounded following    表示最后一行

last_value(sal) 的结果与 order by sal 排序有关。如果排序为order by sal desc, 则最终的结果为分组排序后sal的最小值(分组排序后的最后一个值), 当deptno为10时，max_sal为1300。

2. 理解over()函数

2.1 两个order by 的执行机制

       分析函数是在整个SQL查询结束后(SQL语句中的order by 的执行比较特殊)再进行的操作，也就是说SQL语句中的order by也会影响分析函数的执行结果：

  1. 两者一致：如果SQL语句中的order by 满足分析函数分析时要求的排序，那么SQL语句中的排序将先执行，分析函数在分析时就不必再排序。
  2. 两者不一致：如果SQL语句中的order by 不满足分析函数分析时要求的排序，那么SQL语句中的排序将最后在分析函数分析结束后执行排序。

2.2 分析函数中的分组、排序、窗口

       分析函数包含三个分析子句：分组(partition by)、排序(order by)、窗口(rows)。

       窗口就是分析函数分析时要处理的数据范围，就拿sum来说，它是sum窗口中的记录而不是整个分组中的记录。因此我们在想得到某个栏位的累计值时，我们需要把窗口指定到该分组中的第一行数据到当前行，如果你指定该窗口从该分组中的第一行到最后一行，那么该组中的每一个sum值都会一样，即整个组的总和。

窗口子句中我们经常用到指定第一行，当前行，最后一行这样的三个属性：

第一行是       unbounded preceding

当前行是       current row

最后一行是    unbounded following

窗口子句不能单独出现，必须有order by 子句时才能出现，如：

LAST_VALUE(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )

以上示例指定窗口为整个分组.

而出现order by 子句的时候，不一定要有窗口子句，但效果会不一样，此时窗口默认是当前组的第一行到当前行！

SQL语句为：

SELECT DEPTNO, EMPNO, ENAME, SAL,
last_value(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) MAX_SAL FROM EMP;

等价于

SELECT DEPTNO, EMPNO, ENAME, SAL,last_value(SAL) OVER(PARTITION BY DEPTNO 
ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) MAX_SAL FROM EMP;

结果如下图所示：

当省略窗口子句时：

  1. 如果存在order by, 则默认的窗口是 unbounded preceding and current row.
  2. 如果同时省略order by, 则默认的窗口是 unbounded preceding and unbounded following.

如果省略分组，则把全部记录当成一个组：

  1. 如果存在order by 则默认窗口是unbounded preceding and current row
  2. 如果这时省略order by 则窗口默认为 unbounded preceding and unbounded following

2.3 帮助理解over()的实例

例1：关注点：SQL无排序，over()排序子句省略

select deptno, empno, ename, sal, last_value(sal) over(partition by deptno) from emp;

例2：关注点：SQL无排序，over()排序子句有，窗口省略

select deptno, empno, ename, sal, last_value(sal) over(partition by deptno 
order by sal desc) from emp;

  例3：关注点：SQL无排序，over()排序子句有，窗口也有，窗口特意强调全组数据

select deptno, empno, ename, sal, last_value(sal) over(partition by deptno 
order by sal rows between unbounded preceding and unbounded following) max_sal 
from emp;

例4：关注点：SQL有排序（正序），over() 排序子句无，先做SQL排序再进行分析函数运算

select deptno, mgr, ename, sal, hiredate, last_value(sal) over(partition by deptno)
last_value from emp where deptno=30 order by deptno, mgr;

例5：关注点：SQL有排序（倒序），over() 排序子句无，先做SQL排序再进行分析函数运算

select deptno, mgr, ename, sal, hiredate, last_value(sal) over(partition by deptno)
last_value from emp where deptno=30 order by deptno, mgr desc;

例6：关注点：SQL有排序（倒序），over()排序子句有，窗口子句无，此时的运算是：SQL先选数据但是不排序，而后排序子句先排序并进行分析函数处理（窗口默认为第一行到当前行），最后再进行SQL排序

select deptno, mgr, ename, sal, hiredate, min(sal) over(partition by deptno 
order by sal)last_value from emp where deptno=30 order by deptno, mgr desc;

select deptno, mgr, ename, sal, hiredate, min(sal) over(partition by deptno 
order by sal desc) last_value from emp where deptno=30 order by deptno, mgr desc;

3. 常见分析函数

3.1 演示表和数据的生成

建表语句：

create table t(BILL_MONTH VARCHAR2(12),AREA_CODE NUMBER,NET_TYPE VARCHAR(2),LOCAL_FARE NUMBER
);

插入数据：

insert into t values('200405',5761,'G', 7393344.04);
insert into t values('200405',5761,'J', 5667089.85);
insert into t values('200405',5762,'G', 6315075.96);
insert into t values('200405',5762,'J', 6328716.15);
insert into t values('200405',5763,'G', 8861742.59);
insert into t values('200405',5763,'J', 7788036.32);
insert into t values('200405',5764,'G', 6028670.45);
insert into t values('200405',5764,'J', 6459121.49);
insert into t values('200405',5765,'G', 13156065.77);
insert into t values('200405',5765,'J', 11901671.70);
insert into t values('200406',5761,'G', 7614587.96);
insert into t values('200406',5761,'J', 5704343.05);
insert into t values('200406',5762,'G', 6556992.60);
insert into t values('200406',5762,'J', 6238068.05);
insert into t values('200406',5763,'G', 9130055.46);
insert into t values('200406',5763,'J', 7990460.25);
insert into t values('200406',5764,'G', 6387706.01);
insert into t values('200406',5764,'J', 6907481.66);
insert into t values('200406',5765,'G', 13562968.81);
insert into t values('200406',5765,'J', 12495492.50);
insert into t values('200407',5761,'G', 7987050.65);
insert into t values('200407',5761,'J', 5723215.28);
insert into t values('200407',5762,'G', 6833096.68);
insert into t values('200407',5762,'J', 6391201.44);
insert into t values('200407',5763,'G', 9410815.91);
insert into t values('200407',5763,'J', 8076677.41);
insert into t values('200407',5764,'G', 6456433.23);
insert into t values('200407',5764,'J', 6987660.53);
insert into t values('200407',5765,'G', 14000101.20);
insert into t values('200407',5765,'J', 12301780.20);
insert into t values('200408',5761,'G', 8085170.84);
insert into t values('200408',5761,'J', 6050611.37);
insert into t values('200408',5762,'G', 6854584.22);
insert into t values('200408',5762,'J', 6521884.50);
insert into t values('200408',5763,'G', 9468707.65);
insert into t values('200408',5763,'J', 8460049.43);
insert into t values('200408',5764,'G', 6587559.23);
insert into t values('200408',5764,'J', 7342135.86);
insert into t values('200408',5765,'G', 14450586.63);
insert into t values('200408',5765,'J', 12680052.38);
commit;

3.2 first_value()与last_value():求最值对应的其他属性

问题：取出每个月通话费最高和最低的两个地区

思路：先进行group by bill_month, area_code使用聚合函数sum()求解出by bill_month, area_code的local_fare总和, 即sum(local_fare), 然后再运用分析函数进行求解每个月通话费用最高和最低的两个地区。

select bill_month, area_code, sum(local_fare) local_fare,
first_value(area_code) over(partition by bill_month order by sum(local_fare) desc rows between unbounded preceding and unbounded following) firstval,
last_value(area_code) over(partition by bill_month order by sum(local_fare) desc rows between unbounded preceding and unbounded following) lastval
from t group by bill_month, area_code;

3.3 rank()、dense_rank()与row_number() 排序问题

演示数据再Oracle自带的scott用户下

1.rank()值相同时排名相同，其后排名跳跃不连续

select * from (
select deptno, ename, sal,  rank() over(partition by deptno order by sal desc) rw from emp
) where rw < 4;

 2. dense_rank()值相同时排名相同，其后排名连续不跳跃

select * from (
select deptno, ename, sal,  dense_rank() over(partition by deptno order by sal desc) rw from emp
) where rw <= 4;

3. row_number()值相同时排名不相等，其后排名连续不跳跃

select * from (
select deptno, ename, sal,  row_number() over(partition by deptno order by sal desc) rw from emp
) where rw <= 4;

3.4 lag()与lead():求之前或之后的第N行

lag(arg1, arg2, arg3):

arg1：是从其他行返回的表达式

arg2：是希望检索的当前行分区的偏移量。是一个正的偏移量，是一个往回检索以前的行数目

arg3：是在arg2表示的数目超出了分组的范围时返回的值

而lead()与lag()相反

select bill_month, area_code, local_fare cur_local_fare,
lag(local_fare, 1, 0) over(partition by area_code order by bill_month)
last_local_fare,
lead(local_fare, 1, 0) over(partition by area_code order by bill_month) 
next_local_fare
from (select bill_month, area_code, sum(local_fare) local_fare from t group by bill_month, area_code);

3.5 rollup()与cube()：排列组合分组

group by rollup(A, B, C)：

首先会对 (A, B, C) 进行group by,

然后再对 (A, B) 进行group by,

其后再对 (A) 进行group by,

最后对全表进行汇总操作。

group by cube(A, B, C):

则首先会对 (A, B, C) 进行group by,

然后依次是 (A, B), (A, C), (A), (B, C), (B), (C),

最后对全表进行汇总操作。

1.生成演示数据：

create table scott.tt as select * from dba_indexes;

2.普通group by 体验

select owner, index_type, status, count(*) from tt where owner like 'SY%' 
group by owner, index_type, status;

3. group by rollup(A, B, C)：

首先会对 (A, B, C) 进行group by,

然后再对 (A, B) 进行group by,

其后再对 (A) 进行group by,

最后对全表进行汇总操作。

select owner, index_type, status, count(*) from tt where owner like 'SY%' 
group by rollup(owner, index_type, status);

4. group by cube(A, B, C):

则首先会对 (A, B, C) 进行group by,

然后依次是 (A, B), (A, C), (A), (B, C), (B), (C),

最后对全表进行汇总操作。

select owner, index_type, status, count(*) from tt where owner like 'SY%' 
group by cube(owner, index_type, status);

（只截取了部分图）

3.6 max()、min()、sum()与avg()：求移动的最值、总和与平均值

问题：计算出各个地区连续3个月的通话费用的平均数(移动平均值)

select area_code, bill_month, local_fare,
sum(local_fare) over(partition by area_code order by to_number(bill_month) range between 1 preceding and 1 following) month3_sum,
avg(local_fare) over(partition by area_code order by to_number(bill_month) range between 1 preceding and 1 following) month3_avg,
max(local_fare) over(partition by area_code order by to_number(bill_month) range between 1 preceding and 1 following) month3_max,
min(local_fare) over(partition by area_code order by to_number(bill_month) range between 1 preceding and 1 following) month3_min
from (select bill_month, area_code, sum(local_fare) local_fare from t group by area_code, bill_month);

问题：求各地区按月份累加的通话费

select area_code, bill_month, local_fare, sum(local_fare) over(partition by area_code 
order by bill_month asc) last_sum_value
from(select area_code, bill_month, sum(local_fare) local_fare from t 
group by area_code, bill_month) order by area_code, bill_month;

3.7 ratio_to_report()：求百分比

问题：求各地区花费占各月花费的比例

select bill_month, area_code, sum(local_fare) local_fare,
RATIO_TO_REPORT(sum(local_fare)) OVER (partition by bill_month) AS area_pct
from t group by bill_month, area_code;

注：资料从网上找的，自己按照资料进行了学习，如有问题，请联系Me。


http://chatgpt.dhexx.cn/article/VU8naCA6.shtml

相关文章

Oracle中的分析函数

Oracle中的分析函数

目录 1、理解什么是分析函数&#xff1f; 2、分析函数和聚合函数区别&#xff1f; 3、分析函数的语法&#xff1a; 4、常用的分析函数&#xff1a; 5、运用分析函数求相关累计问题 6、运用分析函数求相关排序问题 1. RANK()函数&#xff1a; 2. DENSE_RANK()函数&#…
阅读更多...
常用Oracle分析函数大全

常用Oracle分析函数大全

Oracle的分析函数功能非常强大&#xff0c;工作这些年来经常用到。这次将平时经常使用到的分析函数整理出来&#xff0c;以备日后查看。 我们拿案例来学习&#xff0c;这样理解起来更容易一些。 1、建表 1 2 3 4 5 6 7 8 9 10 create table earnings -- 打工赚钱表 ( ear…
阅读更多...
oracle分析函数技术详解（配上开窗函数over()）

oracle分析函数技术详解（配上开窗函数over()）

一、Oracle分析函数入门 分析函数是什么？分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。 分析函数和聚合函数的不同之处是什么？普通的聚合函数用…
阅读更多...
Python 输出小数点后保留两位有效数字简便方法

Python 输出小数点后保留两位有效数字简便方法

用round(a,n) a代表变量&#xff0c;n代表想保留的小数点的个数&#xff1b;比如&#xff1a;
阅读更多...
金额千位分隔符及保留2位小数

金额千位分隔符及保留2位小数

目录 numberObject.toFixed(num) 正则 $1、$2 正则 {} 正则 ? 正则 ?&#xff1a; 正则 正则 $ 正则 | numberObject.toFixed(num) NumberObject.toFixed(num) 方法把 Number 四舍五入为指定小数位数数字&#xff0c;返回 string 类型num 规定小数位数 0 ~ 20 之间…
阅读更多...
chatgpt赋能python：Python中保留两位小数输出的方法:一个完整的指南

chatgpt赋能python：Python中保留两位小数输出的方法:一个完整的指南

Python中保留两位小数输出的方法: 一个完整的指南 介绍 在Python编程中&#xff0c;有时候我们需要将数字保留到小数点后两位输出。例如&#xff0c;货币单位到分、科学计算等等。本文将介绍多种方法实现这个目标&#xff0c;包括Python的内置函数以及第三方库的函数。 方法…
阅读更多...
c++ 保留2位小数输出

c++ 保留2位小数输出

1.需要头文件<iomanip> 2.结果会经过四舍五入&#xff1b; 3.公式为 cout<<fixed<<setprecision(2)<<“要输出的变量”<<endl; !!!!!!setprecision()&#xff0c;括号中填几&#xff0c;就是保留几位小数&#xff1b;
阅读更多...
python保留2位小数

python保留2位小数

一&#xff1a;使用占位符方法&#xff1a; # 1&#xff1a;%g 科学计数法输出小数&#xff0c;用于舍弃无效的小数 print("%g"%(1.2000004)) print("%.2g"%(1.2000004)) # 2&#xff1a;直接使用%f 输出小数&#xff0c;会出现无效的0&#xff1b;使用%.…
阅读更多...
C语言-------如何打印保留小数点后1,2，..位

C语言-------如何打印保留小数点后1,2，..位

首先你要想有小数&#xff0c;则在数据存储时就要用到浮点型&#xff08;float &#xff0c;double&#xff09; float a 0; double a 0; 其次在打印时和输入时都要用对应的%f&#xff08;float&#xff09;,%lf (double) printf("%.2f",) , printf("%.2lf…
阅读更多...
【postgresql 小数点】pg保留2位小数

【postgresql 小数点】pg保留2位小数

实现方式&#xff1a; cast函数 cast A as B round(cast( A as B),2) SELECT calculate_time,round(CAST("value" as numeric),2) as fcao from arithmetic.dws_tag_today where byt_namehk_fcao and calculate_time > now() - interval 2 hour ORDER BY calcu…
阅读更多...
Excel中常用的函数

Excel中常用的函数

阅读更多...
Excel常见函数用法大全+函数宝典.xls

Excel常见函数用法大全+函数宝典.xls

函数宝典下载地址: 百度网盘 EXACT 作用:使用EXACT函数比较两个字符串是否相同 用法: EXACT(A2,B2) 返回值: true和false LEN 作用: 使用LEN函数计算文本中的字符个数 用法: "还可以输入"&(60-LEN(A2)&"个字符") &符号合并的意思 T 作…
阅读更多...
Excel MATCH函数判断两列包含关系

Excel MATCH函数判断两列包含关系

判断A列是否包含B列 MATCH&#xff08;判断值&#xff0c;查找范围&#xff0c;0&#xff09;0为精确查找
阅读更多...
Excel之COUNTIF函数 判断包含关系

Excel之COUNTIF函数 判断包含关系

COUNTIF&#xff08;查找范围,判断值&#xff09;
阅读更多...
excel中常见函数的用法

excel中常见函数的用法

excel中的函数主要是根据自己的需求对着表格中最上面的A&#xff0c;B&#xff0c;C...列与左边的行数填入函数对应的参数范围即可&#xff0c;这篇文章内容参照的是excelhome的数据进行编写的 目录 1. IF函数条件单条件判断 2. 多条件判断 3. 条件求和 4. 多条件求和 5. …
阅读更多...
excel查询函数

excel查询函数

1&#xff0c;纵向查找函数 VLOOKUP&#xff08;查找目标&#xff0c;查找范围&#xff0c;返回值的列数v&#xff0c;精确OR模糊查找) 例如要查询某位同学的语文成绩&#xff1a; 注意事项&#xff1a;1&#xff0c;查找目标所在的列必须是查找范围的第一列 2&#xff0c;0代…
阅读更多...
【Office】Excel中IF函数的8种用法

【Office】Excel中IF函数的8种用法

今天咱们来学习一下IF函数的几个典型用法&#xff0c;看完你会觉得&#xff0c;原来IF函数竟然也这么高深&#xff01; 1.简单判断 根据成绩是否达到60分判断是否及格。公式&#xff1a;IF(B2>60,"及格","不及格") 2.多重条件判断 男性大于等于60岁时&…
阅读更多...
Excel函数公式大全以及用法

Excel函数公式大全以及用法

首先得说明下excel如何输入函数公式的方法方法是&#xff1a;单击a4单元格&#xff08;a4单元格的位置是第A列&#xff0c;第4行交叉的那个单元格&#xff0c;也即是我们彩色实例的那个位置&#xff09;&#xff0c;在键盘上输入 a1a2a3 &#xff0c;然后按键盘的enter键&#…
阅读更多...
Excel函数公式大全—INDEX函数

Excel函数公式大全—INDEX函数

EXCEL系列文章目录 Excel系列文章是本人亲身经历职场之后萌发的想法&#xff0c;为什么Excel覆盖如此之广&#xff0c;几乎每个公司、学校、家庭都在使用&#xff0c;但是它深藏的宝藏功能却很少被人使用&#xff0c;PQ、BI这些功能同样适用于数据分析&#xff1b;并且在一些需…
阅读更多...
Excel表格常用函数

Excel表格常用函数

常用函数 一、常用函数1、MATCH函数2、INDEX函数3、LOOKUP函数4、MID函数5、if函数6、mod函数7、ABS函数8、MAX&MIN函数9、ROUND函数10、SUM函数11、COUNT函数12、AVERAGE函数13、RANK.EQ函数 一、常用函数 1、MATCH函数 MATCH函数语法为&#xff1a;MATCH&#xff08;lo…
阅读更多...
推荐文章

Copyright @ 2022~2023