创建测试表空间及用户

create tablespace LIUC
logging 
datafile 'E:\ORACLE\APP\ORACLE\ORADATA\XE\LIUC.DBF' 
size 1500m 
autoextend on 
next 100m maxsize 10000m;

create user LIUC identified by LIUC DEFAULT TABLESPACE LIUC;

GRANT dba to LIUC;

插入测试数据

insert into earnings values('200912','北平','511601','大魁',11,30,11*30);  
insert into earnings values('200912','北平','511602','大凯',8,25,8*25);  
insert into earnings values('200912','北平','511603','小东',30,6.25,30*6.25);  
insert into earnings values('200912','北平','511604','大亮',16,8.25,16*8.25);  
insert into earnings values('200912','北平','511605','贱敬',30,11,30*11);  
insert into earnings values('200912','金陵','511301','小玉',15,12.25,15*12.25);  
insert into earnings values('200912','金陵','511302','小凡',27,16.67,27*16.67);  
insert into earnings values('200912','金陵','511303','小妮',7,33.33,7*33.33);  
insert into earnings values('200912','金陵','511304','小俐',0,18,0);  
insert into earnings values('200912','金陵','511305','雪儿',11,9.88,11*9.88);  
insert into earnings values('201001','北平','511601','大魁',0,30,0);  
insert into earnings values('201001','北平','511602','大凯',14,25,14*25);  
insert into earnings values('201001','北平','511603','小东',19,6.25,19*6.25);  
insert into earnings values('201001','北平','511604','大亮',7,8.25,7*8.25);  
insert into earnings values('201001','北平','511605','贱敬',21,11,21*11);  
insert into earnings values('201001','金陵','511301','小玉',6,12.25,6*12.25);  
insert into earnings values('201001','金陵','511302','小凡',17,16.67,17*16.67);  
insert into earnings values('201001','金陵','511303','小妮',27,33.33,27*33.33);  
insert into earnings values('201001','金陵','511304','小俐',16,18,16*18);  
insert into earnings values('201001','金陵','511305','雪儿',11,9.88,11*9.88);  
commit;

分析函数的使用

  • SUM() 分组求和聚合函数
    select e.earnmonth,e.area,sum(e.personincome) from earnings e group by e.earnmonth,e.area;
    
  • SUM() ROLLUP() 分组求和并且按照第一个字段再次分组求和
    select e.earnmonth,e.area,sum(e.personincome) from earnings e group by rollup(e.earnmonth,e.area);	
    select e.area,e.earnmonth,sum(e.personincome) from earnings e group by rollup(e.area,e.earnmonth);
    
  • SUM() CUBE() 分组求和并且按照每个字段再次分组求和
    select e.earnmonth,e.area,sum(e.personincome) from earnings e group by cube(e.earnmonth,e.area) 
    order by e.earnmonth,e.area nulls last;
    
    
  • rollup和cube区别

	如果是ROLLUP(A, B, C)的话,GROUP BY顺序

	(A、B、C)

	(A、B)
	(A)
	最后对全表进行GROUP BY操作。
	
	如果是GROUP BY CUBE(A, B, C),GROUP BY顺序
	(A、B、C)
	(A、B)
	(A、C)
	(A)
	(B、C)
	(B)
	(C)
	最后对全表进行GROUP BY操作

  • GROUPING()函数
    rollup和cube函数都会对结果集产生null,这时候可用grouping函数来确认
    该记录是由哪个字段得出来的
    select decode(grouping(earnmonth),1,'所有月份',earnmonth) 月份,  
        decode(grouping(area),1,'全部地区',area) 地区, sum(personincome) 总金额  
    from earnings  
    group by cube(earnmonth,area)  
    order by earnmonth,area nulls last;
    
  • rank() over() 开窗函数 组内排序,生成组内排序编号,排名会跳跃
  • dense_rank() over() 排名不会跳跃
    select e.earnmonth,e.area,e.sname,e.personincome,
    rank() over (partition by e.earnmonth,e.area order by e.personincome desc) 
    from earnings e
    
  • row_number() over() 开窗函数 组内排序,生成组内排序编号 即使相同也会有不一样的排名
    select e.earnmonth,e.area,e.sname,e.personincome,
    row_number() over (partition by e.earnmonth,e.area order by e.personincome desc) 
    from earnings e
    
  • sum() over() 分组求和的分析函数
     select e.earnmonth,e.area,e.sname,
     sum(e.personincome) over(partition by e.earnmonth,e.area order by e.personincome) from earnings e
    
  • max() over() min() over() avg() over() count() over() 分组分析函数
    select distinct e.earnmonth,e.area,
     max(e.personincome) over(partition by e.earnmonth,e.area),
     min(e.personincome) over(partition by e.earnmonth,e.area),
     avg(e.personincome) over(partition by e.earnmonth,e.area),
     sum(e.personincome) over(partition by e.earnmonth,e.area),
     count(distinct e.sname) over(partition by e.earnmonth,e.area)
     from earnings e
    

行列转换

CREATE TABLE SCORES  
  (Student NVARCHAR2(2),Course NVARCHAR2(2),Score INT  
  );  
  
INSERT into SCORES   
select N'张三',N'语文',78 from dual union all  
select N'张三',N'数学',87 from dual union all  
select N'张三',N'英语',82 from dual union all  
select N'张三',N'物理',90 from dual union all  
select N'李四',N'语文',65 from dual union all  
select N'李四',N'数学',77 from dual union all  
select N'李四',N'英语',65 from dual union all  
select N'李四',N'物理',85 from dual ;  
commit;  
  • 行转列

    分组判断
    
    select s.student,
    max(decode(s.course,'语文',s.score,null)) as "语文",
    max(decode(s.course,'数学',s.score,null)) as "数学",
    max(decode(s.course,'英语',s.score,null)) as "英语",
    max(decode(s.course,'物理',s.score,null)) as "物理",
    SUM(s.score) TOTAL  
     from scores s
    group by s.student
    
    pivot函数
    
    select *
      from scores
    pivot(max(score)
       for course in('语文' as "语文",
                     '数学' as "数学",
                     '英语' as "英语",
                     '物理' as "物理"))
    
  • 单字段拆分成多行

CREATE TABLE T_STU  
  (STUID NVARCHAR2(2),STUNAME NVARCHAR2(50),STUSEX INT  
  );  
  
INSERT into T_STU   
select N'1',N'张三,李四,王五',1 from dual union all  
select N'2',N'莉莉,末末,玲玲',0 from dual;
commit; 
使用REGEXP_SUBSTR()函数
SELECT DISTINCT T.STUID,
                REGEXP_SUBSTR(T.STUNAME, '[^,]+', 1, LEVEL) AS STUNAME,
                T.STUSEX
  FROM T_STU T
CONNECT BY LEVEL <=
           LENGTH(T.STUNAME) - LENGTH(REPLACE(T.STUNAME, ',', '')) + 1
 ORDER BY T.STUID
  • 多行合成单个字段
CREATE OR REPLACE VIEW ROWSTOCOLUMN AS
SELECT DISTINCT T.STUID,REGEXP_SUBSTR(T.STUNAME, '[^,]+', 1, LEVEL) as STUNAME, T.STUSEX
  FROM T_STU T
CONNECT BY LEVEL <=
           LENGTH(T.STUNAME) - LENGTH(REPLACE(T.STUNAME, ',', '')) + 1
 ORDER BY T.STUID;
使用LISTAGG() WITHIN GROUP(ORDER BY )  多行按成一定规则拼接成一个字段
SELECT STUID,
       LISTAGG(STUNAME, ',') WITHIN GROUP(ORDER BY STUNAME) AS STUNAME
  FROM ROWSTOCOLUMN
 GROUP BY STUID

标题:oracle分析函数的使用
作者:upaths
地址:http://noyil.com/analytic