您的位置: 首页  >  文章  >  ORACLE分析函数

ORACLE分析函数

分类: 文章 • 2024-05-17 20:05:34

1，rollup函数

select class 班级, subject 科目, sex 性别,sum(score) 总分数  
from sailing_class  
group by rollup(class,subject,sex) 
order by class,subject,sex;

解释：
sum(汇总字段) group by rollup(字段A,字段B,字段C)

该函数会先对(A,B,C)三个字段分组，再对(A,B)两个字段分组，再对(A)字段进行分组，之后对整个表分组。
注：会严格按照三个字段的顺序分组，以哪个字段为主，则必须把这个字段放在首位。
ORACLE分析函数

2，cube函数

select class 班级, subject 科目, sex 性别,sum(score) 总分数  
from sailing_class  
group by cube(class,subject,sex) 
order by class,subject,sex;

解释：
sum(汇总字段) group by cube(字段A,字段B,字段C)

该函数的分组顺序如下

  1. （A,B,C）
  2. （A,B）
  3. （A,C）
  4. （A）
  5. （B,C）
  6. （B）
  7. （C）
  8. 全表
    ORACLE分析函数

3，grouping函数

select 	
        class 实际班级,
        grouping(class),
        decode(grouping(class),1,'所有班级',class) 班级,  
        subject 实际科目,
        grouping(subject),
        decode(grouping(subject),1,'全部科目',subject) 科目, 
        sex 实际性别,
        grouping(sex),
        decode(grouping(sex),1,'全部性别',sex) 性别, 
        sum(score) 总分数  
from sailing_class  
group by cube(class,subject,sex)  
order by class,subject nulls last;

解释：
grouping函数用来区分NULL值，这里NULL值有2种情况，一是原本表中的数据就为NULL，二是由rollup、cube、grouping sets生成的NULL值。
当为第一种情况中的空值时，grouping(NULL)返回0；当为第二种情况中的空值时，grouping(NULL)返回1。
可据图对比。
ORACLE分析函数

4，排序（1）—rank()

select class 班级,subject 科目,stu_name 学生姓名,sex 性别,score 分数,   
       rank() over (partition by class,subject order by score desc) 排名  
from sailing_class;

解释：
数据有重复，序号重复并别相同，后续序号中断。
ORACLE分析函数

5，排序（2）—dense_rank()

select class 班级,subject 科目,stu_name 学生姓名,sex 性别,score 分数,   
       dense_rank() over (partition by class,subject order by score desc) 排名  
from sailing_class;

解释：
数据有重复，序号重复并列相同，后续序号不中断。
ORACLE分析函数

6，排序（3）—row_number()

select class 班级,subject 科目,stu_name 学生姓名,sex 性别,score 分数,   
       row_number() over (partition by class,subject order by score desc) 排名  
from sailing_class;

解释：
数据有重复，序号不重复，后续序号始终递增
ORACLE分析函数

使用的数据：

-- create table sailing_class -- 考试成绩表 
-- (  
--   class varchar2(8), -- 班级  
--   subject varchar2(20), -- 科目  
--   stu_no varchar2(10), -- 学生编号  
--   stu_name varchar2(20), -- 学生姓名  
-- 	sex number(1),--性别 
--   score number(10,2) -- 考试分数  
-- )

-- insert into sailing_class values('2017_01','高等数学','100122','李贺',1,78.6);
-- insert into sailing_class values('2017_01','高等数学','100123','刘睿嫣',2,95.1);
-- insert into sailing_class values('2017_01','高等数学','100124','赵宗望',1,92);
-- insert into sailing_class values('2017_01','高等数学','100125','王乐',1,52);
-- insert into sailing_class values('2017_01','高等数学','100126','张启慧',2,68);
-- insert into sailing_class values('2017_01','高等数学','100127','袁牧',1,92);
-- 
-- insert into sailing_class values('2017_01','英语','100122','李贺',1,88.6);
-- insert into sailing_class values('2017_01','英语','100123','刘睿嫣',2,75.1);
-- insert into sailing_class values('2017_01','英语','100124','赵宗望',1,58);
-- insert into sailing_class values('2017_01','英语','100125','王乐',1,82);
-- insert into sailing_class values('2017_01','英语','100126','张启慧',2,78);
-- insert into sailing_class values('2017_01','英语','100127','袁牧',1,80.4);
-- 
-- insert into sailing_class values('2017_02','高等数学','100242','卿泽明',1,98.6);
-- insert into sailing_class values('2017_02','高等数学','100243','史明利',1,75.1);
-- insert into sailing_class values('2017_02','高等数学','100244','赵卿焰',2,92);
-- insert into sailing_class values('2017_02','高等数学','100245','演绎木',1,62);
-- insert into sailing_class values('2017_02','高等数学','100246','卿木燕',2,78);
-- insert into sailing_class values('2017_02','高等数学','100247','成亚瑟',1,54);
-- 
-- insert into sailing_class values('2017_02','英语','100242','卿泽明',1,78.6);
-- insert into sailing_class values('2017_02','英语','100243','史明利',1,85.1);
-- insert into sailing_class values('2017_02','英语','100244','赵卿焰',2,88);
-- insert into sailing_class values('2017_02','英语','100245','演绎木',1,69);
-- insert into sailing_class values('2017_02','英语','100246','卿木燕',2,88);
-- insert into sailing_class values('2017_02','英语','100247','成亚瑟',1,56);

相关推荐