分析函数

最新推荐文章于 2024-06-17 18:06:00 发布

神临物语

最新推荐文章于 2024-06-17 18:06:00 发布

阅读量145

点赞数 1

分类专栏：数据库学习笔记文章标签： Oracle分析函数

本文链接：https://blog.csdn.net/u011332300/article/details/58603411

版权

数据库学习笔记专栏收录该内容

1 篇文章 0 订阅

订阅专栏

分析函数

--创建测试表
create table test(
       name varchar2(10),
       month varchar2(6),
       income number
)
--插入测试数据
insert into test(name,month,income) values ('Jack','201701',5148);
insert into test(name,month,income) values ('Jack','201702',5218);
insert into test(name,month,income) values ('Sam','201701',5148);
insert into test(name,month,income) values ('Sam','201702',5218);
insert into test(name,month,income) values ('Tom','201701',5242);
insert into test(name,month,income) values ('Tom','201702',5242);
insert into test(name,month,income) values ('Ben','201701',6214);
insert into test(name,month,income) values ('Ben','201702',6231);

1. 自动汇总函数rollup,cube

Rollup()
Group by ROLLUP(A, B, C)，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。

select name,month,sum(income) from test group by rollup(name,month)

rollup查询结果

cube()
GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作

select name,month,sum(income) from test group by cube(name,month) order by name,month nulls last;

cube查询结果

Grouping()
grouping()是与rollup和cube配套使用的，用于判断括号内的列名是否被汇总了，汇总列返回1，未汇总列则返回0。我们可以利用grouping的这一特性来填补rollup和cube汇总时候出现的空白。

select name,month,sum(income),grouping(name),grouping(month) from test group by cube(name,month) order by name,month nulls last;

grouping查询结果

select case
         when grouping(name) = 1 and month is null then
          '总计'
         when grouping(name) = 1 and month is not null then
          '按月统计'
         else
          name
       end as name,
       case
         when grouping(month) = 1 and name is not null then
          '按人统计'
         else
          month
       end as month,
       sum(income)
  from test
 group by cube(name, month)
 order by name, month nulls last

grouping查询结果

2. Rank函数介绍

Rank()为跳跃排序，有同样值的行得到同样的数字序号，若两行序数为1，则没有序数2，序列将给组中的下一行分配值3。后面的over为开窗函数，其中partition by代表开窗范围，order by为此窗口内的排序。

select name,month,income,rank() over(partition by month order by income) income_rank from test

rank查询结果

dense_rank()为顺序排序，有同样值的行得到同样的数字序号，若两行序数为1，序列将给组中的下一行分配值2。

select name,month,income,dense_rank() over(partition by month order by income) income_rank from test

dense_rank查询结果

Row_number()返回一组有序序列，即使是有同样值的行得到也是不一样的序号。

select name,month,income,row_number() over(partition by month order by income) income_rank from test

row_number查询结果

3. lag/lead函数介绍

功能介绍：可以访问结果集中的其它行而不用进行自连接。在给定组中可参考当前行之前或者之后的行进行比较。
参数介绍： lag/lead(value_expression [,offset] [,default]) value_expression代表返回返回值，可以是一个字段或一个内建函数；offset是一个正整数，默认为1，代表返回对应的前几列（lead）或者后几列（lag）；default代表超出范围时的返回值。

select name,
       month,
       income,
       lag(income, 1, 0) over(partition by month order by income) lag1,  --分组内前一行的income字段
       lag(income, 2, 0) over(partition by month order by income) lag2,  --分组内前两行的income字段
       lead(income, 1, 0) over(partition by month order by income) lead1,--分组内后一行的income字段
       lead(income, 2, 0) over(partition by month order by income) lead2 --分组内后两行的income字段
  from test

lag/lead查询结果

4. sum/avg/max/min函数

sum/avg/max/min后面接over开窗函数表示为分析函数，而不是普通的聚集函数，功能分别为获取分组内的和/平均值/最大值/最小值。

select name,
       month,
       income,
       sum(income) over(partition by month order by month) sum,
       avg(income) over(partition by month order by month) avg,
       max(income) over(partition by month order by month) max,
       min(income) over(partition by month order by month) min
  from test;