oracle分析函数

oracle分析函数 -- SQL*PLUS环境
-- 1、GROUP BY子句 

-- CREATE TEST TABLE AND INSERT TEST DATA.
create   table  students
(id  number ( 15 , 0 ),
area  varchar2 ( 10 ),
stu_type  varchar2 ( 2 ),
score  number ( 20 , 2 ));

insert   into  students  values ( 1 ,  ' 111 ' ,  ' g ' ,  80  );
insert   into  students  values ( 1 ,  ' 111 ' ,  ' j ' ,  80  );
insert   into  students  values ( 1 ,  ' 222 ' ,  ' g ' ,  89  );
insert   into  students  values ( 1 ,  ' 222 ' ,  ' g ' ,  68  );
insert   into  students  values ( 2 ,  ' 111 ' ,  ' g ' ,  80  );
insert   into  students  values ( 2 ,  ' 111 ' ,  ' j ' ,  70  );
insert   into  students  values ( 2 ,  ' 222 ' ,  ' g ' ,  60  );
insert   into  students  values ( 2 ,  ' 222 ' ,  ' j ' ,  65  );
insert   into  students  values ( 3 ,  ' 111 ' ,  ' g ' ,  75  );
insert   into  students  values ( 3 ,  ' 111 ' ,  ' j ' ,  58  );
insert   into  students  values ( 3 ,  ' 222 ' ,  ' g ' ,  58  );
insert   into  students  values ( 3 ,  ' 222 ' ,  ' j ' ,  90  );
insert   into  students  values ( 4 ,  ' 111 ' ,  ' g ' ,  89  );
insert   into  students  values ( 4 ,  ' 111 ' ,  ' j ' ,  90  );
insert   into  students  values ( 4 ,  ' 222 ' ,  ' g ' ,  90  );
insert   into  students  values ( 4 ,  ' 222 ' ,  ' j ' ,  89  );
commit ;

col score format  999999999999.99

-- A、GROUPING SETS

select  id,area,stu_type, sum (score) score 
from  students
group   by   grouping  sets((id,area,stu_type),(id,area),id)
order   by  id,area,stu_type;

/**/ /*--------理解grouping sets
select a, b, c, sum( d ) from t
group by grouping sets ( a, b, c )

等效于

select * from (
select a, null, null, sum( d ) from t group by a
union all
select null, b, null, sum( d ) from t group by b 
union all
select null, null, c, sum( d ) from t group by c 
)
*/

-- B、ROLLUP

select  id,area,stu_type, sum (score) score 
from  students
group   by  rollup(id,area,stu_type)
order   by  id,area,stu_type;

/**/ /*--------理解rollup
select a, b, c, sum( d )
from t
group by rollup(a, b, c);

等效于

select * from (
select a, b, c, sum( d ) from t group by a, b, c 
union all
select a, b, null, sum( d ) from t group by a, b
union all
select a, null, null, sum( d ) from t group by a
union all
select null, null, null, sum( d ) from t
)
*/

-- C、CUBE

select  id,area,stu_type, sum (score) score 
from  students
group   by  cube(id,area,stu_type)
order   by  id,area,stu_type;

/**/ /*--------理解cube
select a, b, c, sum( d ) from t
group by cube( a, b, c)

等效于

select a, b, c, sum( d ) from t
group by grouping sets( 
( a, b, c ), 
( a, b ), ( a ), ( b, c ), 
( b ), ( a, c ), ( c ), 
() )
*/

-- D、GROUPING

/**/ /*从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,
如何来区分到底是根据那个字段做的汇总呢,grouping函数判断是否合计列!*/

select  decode( grouping (id), 1 , ' all id ' ,id) id,
decode( grouping (area), 1 , ' all area ' ,to_char(area)) area,
decode( grouping (stu_type), 1 , ' all_stu_type ' ,stu_type) stu_type,
sum (score) score
from  students
group   by  cube(id,area,stu_type)
order   by  id,area,stu_type; 

-- 2、OVER()函数的使用
-- 1、RANK()、DENSE_RANK() 的、ROW_NUMBER()、CUME_DIST()、MAX()、AVG()

break   on  id skip  1
select  id,area,score  from  students  order   by  id,area,score  desc ;

select  id,rank()  over (partition  by  id  order   by  score  desc ) rk,score  from  students;

-- 允许并列名次、名次不间断
select  id,dense_rank()  over (partition  by  id  order   by  score  desc ) rk,score  from  students;

-- 即使SCORE相同，ROW_NUMBER()结果也是不同
select  id,row_number()  over (partition  by  ID  order   by  SCORE  desc ) rn,score  from  students;

select  cume_dist()  over ( order   by  id) a,  -- 该组最大row_number/所有记录row_number 
row_number()  over  ( order   by  id) rn,id,area,score  from  students;

select  id, max (score)  over (partition  by  id  order   by  score  desc )  as  mx,score  from  students;

select  id,area, avg (score)  over (partition  by  id  order   by  area)  as   avg ,score  from  students;  -- 注意有无order by的区别

-- 按照ID求AVG
select  id, avg (score)  over (partition  by  id  order   by  score  desc  rows  between  unbounded preceding 
and  unbounded following )  as  ag,score  from  students;


-- 2、SUM()

select  id,area,score  from  students  order   by  id,area,score  desc ;

select  id,area,score,
sum (score)  over  ( order   by  id,area) 连续求和,  -- 按照OVER后边内容汇总求和
sum (score)  over  () 总和,  --  此处sum(score) over () 等同于sum(score)
100 * round (score / sum (score)  over  (), 4 ) "份额( % )"
from  students;

select  id,area,score,
sum (score)  over  (partition  by  id  order   by  area ) 连id续求和,  -- 按照id内容汇总求和
sum (score)  over  (partition  by  id) id总和,  -- 各id的分数总和
100 * round (score / sum (score)  over  (partition  by  id), 4 ) "id份额( % )",
sum (score)  over  () 总和,  --  此处sum(score) over () 等同于sum(score)
100 * round (score / sum (score)  over  (), 4 ) "份额( % )"
from  students;

-- 4、LAG(COL,n,default)、LEAD(OL,n,default) --取前后边N条数据

select  id,lag(score, 1 , 0 )  over ( order   by  id) lg,score  from  students;

select  id,lead(score, 1 , 0 )  over ( order   by  id) lg,score  from  students;

-- 5、FIRST_VALUE()、LAST_VALUE()

select  id,first_value(score)  over ( order   by  id) fv,score  from  students;

select  id,last_value(score)  over ( order   by  id) fv,score  from  students;

 转自 http://www.itpub.net/showthread.php?s=&threadid=608107&perpage=10&pagenumber=1