zhouwf0726 | 25 七月, 2006 12:51

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;