oracle分析函数求总,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;

/*而对于last_value() over(order by id),结果是有问题的，因为我们没有按照id分区，所以应该出来的效果应该全部是90(最后一条)。

再看个例子就明白了：*/

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

/*ID LAST_VALUE(SCORE)OVER(ORDERBYR SCORE

---------------- ------------------------------ ----------------------

1 80 80.00

1 80 80.00

1 89 89.00

1 68 68.00

2 80 80.00

2 70 70.00

2 60 60.00

2 65 65.00

3 75 75.00

3 58 58.00

3 58 58.00

3 90 90.00

4 89 89.00

4 90 90.00

4 90 90.00

4 89 89.00

16 rows selected当使用last_value分析函数的时候，缺省的WINDOWING范围是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，在进行比较的时候从当前行向前进行比较，所以会出现上边的结果。加上如下的参数，结果就正常了。呵呵。默认窗口范围为所有处理结果。*/

select id,last_value(score) over(order by rownum RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),score from students;

/*

ID LAST_VALUE(SCORE)OVER(ORDERBYR SCORE

---------------- ------------------------------ ----------------------

1 89 80.00

1 89 80.00

1 89 89.00

1 89 68.00

2 89 80.00

2 89 70.00

2 89 60.00

2 89 65.00

3 89 75.00

3 89 58.00

3 89 58.00

3 89 90.00

4 89 89.00

4 89 90.00

4 89 90.00

4 89 89.00

16 rows selected

*/

--给出一个例子再次理解分析函数

/*********************************************************************************************

http://www.itpub.net/620932.html

问题提出：

一个高级SQL语句问题

假设有一张表，A和B字段都是NUMBER，

A B

1 2

2 3

3 4

4

有这样一些数据

现在想用一条SQL语句，查询出这样的数据

1－》2－》3—》4

就是说，A和B的数据表示一种连接的关系，现在想通过A的一个值，去查询A所对应的B值，直到B为NULL为止，

不知道这个SQL语句怎么写？请教高手！谢谢

*********************************************************************************************/

--以下是利用分析函数的一个简单解答：

--start with connect by可以参考http://www.itpub.net/620427.html

CREATE TABLE TEST(COL1 NUMBER(18,0),COL2 NUMBER(18,0));

INSERT INTO TEST VALUES(1,2);

INSERT INTO TEST VALUES(2,3);

INSERT INTO TEST VALUES(3,4);

INSERT INTO TEST VALUES(4,NULL);

INSERT INTO TEST VALUES(5,6);

INSERT INTO TEST VALUES(6,7);

INSERT INTO TEST VALUES(7,8);

INSERT INTO TEST VALUES(8,NULL);

INSERT INTO TEST VALUES(9,10);

INSERT INTO TEST VALUES(10,NULL);

INSERT INTO TEST VALUES(11,12);

INSERT INTO TEST VALUES(12,13);

INSERT INTO TEST VALUES(13,14);

INSERT INTO TEST VALUES(14,NULL);

select max(col) from(

select SUBSTR(col,1,CASE WHEN INSTR(col,'->')>0 THEN INSTR(col,'->') - 1 ELSE LENGTH(col) END) FLAG,col from(

select ltrim(sys_connect_by_path(col1,'->'),'->') col from (

select col1,col2,CASE WHEN LAG(COL2,1,NULL) OVER(ORDER BY ROWNUM) IS NULL THEN 1 ELSE 0 END FLAG

from test

)

start with flag=1 connect by col1=prior col2

)

)

group by flag

;

--再次给出一个例子：

--查找重复记录的方法，除了用count(*)，还可以用row_number()等函数实现

create table test(xm varchar2(20),sfzhm varchar2(20));

insert into test values('1','11111');

insert into test values('1','11111');

insert into test values('2','22222');

insert into test values('2','22222');

insert into test values('2','22222');

insert into test values('3','33333');

insert into test values('3','33333');

insert into test values('3','33333');

commit;

select * from test a,(

select xm,sfzhm from test

group by xm,sfzhm

having count(*)>2

) b

where a.xm=b.xm and a.sfzhm=b.sfzhm

select * from (select xm,sfzhm,count(*) over(partition by xm,sfzhm) sl from test) where sl>2;