oracle分析函数

oracle分析函数（3、6、8、9、10、11页有些例子）

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;

http://zhouwf0726.itpub.net/post/9689/158090

 

再次理解分析函数！

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

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);


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);

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
;
看看这两句的差别！

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

              ID         FV                  SCORE
---------------- ---------- ----------------------
               1         68                  68.00
               1         68                  80.00
               1         68                  80.00
               1         68                  89.00
               2         68                  60.00
               2         68                  65.00
               2         68                  70.00
               2         68                  80.00
               3         68                  58.00
               3         68                  58.00
               3         68                  75.00
               3         68                  90.00
               4         68                  89.00
               4         68                  89.00
               4         68                  90.00
               4         68                  90.00

16 rows selected

SQL> select id,first_value(score) over(PARTITION BY ID order by id) fv,score from students;

              ID         FV                  SCORE
---------------- ---------- ----------------------
               1         68                  68.00
               1         68                  80.00
               1         68                  80.00
               1         68                  89.00
               2         60                  60.00
               2         60                  65.00
               2         60                  70.00
               2         60                  80.00
               3         58                  58.00
               3         58                  58.00
               3         58                  75.00
               3         58                  90.00
               4         89                  89.00
               4         89                  89.00
               4         89                  90.00
               4         89                  90.00

16 rows selected

QUOTE:

---

最初由 zjp8310 发布
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,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;

这两段看不懂，有谁可以帮忙解释一下吗？

---

CUME_DIST 功能描述：<0CUME_DIST<=1，该组最大row_number/所有记录row_number。

SQL> select cume_dist() over(order by id) a,id,row_number() over(order by id) rn from test;

         A ID                           RN
---------- -------------------- ----------
       0.4 1                             1
       0.4 1                             2
         1 2                             3
         1 2                             4
         1 2                             5

id=1,该组最大row_number=2,最大row_number=5,所以2/5=0.4
id=2,该组最大row_number=5,最大row_number=5,所以5/5=1

SQL> select cume_dist() over(partition by id order by id) a,id,row_number() over(partition by id order by id) rn from test;

         A ID                           RN
---------- -------------------- ----------
         1 1                             1
         1 1                             2
         1 2                             1
         1 2                             2
         1 2                             3
id=1,该组最大row_number=2,最大row_number=2,所以2/1=1
id=2,该组最大row_number=3,最大row_number=3,所以3/3=1


SQL> select cume_dist() over(order by rownum) a,id,rownum,row_number() over(order by rownum) rn from test;

         A ID                       ROWNUM         RN
---------- -------------------- ---------- ----------
       0.2 1                             1          1
       0.4 1                             2          2
       0.6 2                             3          3
       0.8 2                             4          4
         1 2                             5          5
         
rownum=1,该组最大row_number=1,最大row_number=5,所以1/5=0.2
rownum=2,该组最大row_number=2,最大row_number=5,所以2/5=0.4
rownum=3,该组最大row_number=3,最大row_number=5,所以4/5=0.6
rownum=4,该组最大row_number=4,最大row_number=5,所以5/5=0.8
rownum=5,该组最大row_number=5,最大row_number=5,所以5/5=1

注意：上述该组row_number和最大row_number条件必须是一样的。我们在查询的时候可以没有row_number() over()这个列,oracle会隐含算出这个列在和该组row_number去做比值。






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

              ID AREA                        SCORE   连续求和       总和    份额(%)
---------------- ---------- ---------------------- ---------- ---------- ----------
               1 111                         80.00        160       1231        6.5
               1 111                         80.00        160       1231        6.5
               1 222                         89.00        317       1231       7.23
               1 222                         68.00        317       1231       5.52
               2 111                         80.00        467       1231        6.5
               2 111                         70.00        467       1231       5.69
               2 222                         60.00        592       1231       4.87
               2 222                         65.00        592       1231       5.28
               3 111                         75.00        725       1231       6.09
               3 111                         58.00        725       1231       4.71
               3 222                         58.00        873       1231       4.71
               3 222                         90.00        873       1231       7.31
               4 111                         89.00       1052       1231       7.23
               4 111                         90.00       1052       1231       7.31
               4 222                         90.00       1231       1231       7.31
               4 222                         89.00       1231       1231       7.23

看看这个结果是比较好理解的，连续求和就是本组数据求和，总和就不用说了吧，所有的求和，比重就是该条数据和总和的比值。

看到很多人对于keep不理解，这里解释一下！

Returns the row ranked first using DENSE_RANK
2种取值：
DENSE_RANK FIRST
DENSE_RANK LAST

在keep (DENSE_RANK first ORDER BY sl) 结果集中再取max、min的例子。


SQL> select * from test;

ID MC SL
-------------------- -------------------- -------------------
1 111 1
1 222 1
1 333 2
1 555 3
1 666 3
2 111 1
2 222 1
2 333 2
2 555 2

9 rows selected

SQL>
SQL> select id,mc,sl,
2 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
3 max(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id)
4 from test
5 ;

ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)KEEP(DENSE_RANKLASTORDE
-------------------- -------------------- ------------------- ------------------------------ ------------------------------
1 111 1 111 666
1 222 1 111 666
1 333 2 111 666
1 555 3 111 666
1 666 3 111 666
2 111 1 111 555
2 222 1 111 555
2 333 2 111 555
2 555 2 111 555

9 rows selected

SQL>

不要混淆keep内（first、last）外（min、max或者其他）：
min是可以对应last的
max是可以对应first的

SQL> select id,mc,sl,
2 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
3 max(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
4 min(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id),
5 max(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id)
6 from test
7 ;

ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)KEEP(DENSE_RANKFIRSTORD MIN(MC)KEEP(DENSE_RANKLASTORDE MAX(MC)KEEP(DENSE_RANKLASTORDE
-------------------- -------------------- ------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
1 111 1 111 222 555 666
1 222 1 111 222 555 666
1 333 2 111 222 555 666
1 555 3 111 222 555 666
1 666 3 111 222 555 666
2 111 1 111 222 333 555
2 222 1 111 222 333 555
2 333 2 111 222 333 555
2 555 2 111 222 333 555

9 rows selected


SQL> select id,mc,sl,
2 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
3 max(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
4 min(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id),
5 max(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id)
6 from test
7 ;

ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)KEEP(DENSE_RANKFIRSTORD MIN(MC)KEEP(DENSE_RANKLASTORDE MAX(MC)KEEP(DENSE_RANKLASTORDE
-------------------- -------------------- ------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
1 111 1 111 222 555 666
1 222 1 111 222 555 666
1 333 2 111 222 555 666
1 555 3 111 222 555 666
1 666 3 111 222 555 666

2 111 1 111 222 333 555
2 222 1 111 222 333 555
2 333 2 111 222 333 555
2 555 2 111 222 333 555

min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id)：id等于1的数量最小的（DENSE_RANK first ）为
1 111 1
1 222 1
在这个结果中取min(mc) 就是111
max(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id)
取max(mc) 就是222；
min(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id)：id等于1的数量最大的（DENSE_RANK first ）为
1 555 3
1 666 3
在这个结果中取min(mc) 就是222，取max(mc)就是666

////

看到有些同志对oracle分析函数中select over(partition by col1 order by col2) from test order by ...关于partition by 和 组内order by以及最后的order by的执行顺序存在疑惑，这里解释一下。

http://www.itpub.net/showthread. ... 10&pagenumber=1



over 中的partition为分组， order by是视窗内排序, 先执行 partition 然后order by 如 partition by col_a order by col_b 的执行排序效果类似于order by col_a, col_b 这样的排序效果，如果再在最后加order by，是在前边分组排序的结果基础上进行排序。

SQL> create table test(id varchar2(20));

Table created

SQL> insert into test values('1');

1 row inserted

SQL> insert into test values('1');

1 row inserted

SQL> insert into test values('8');

1 row inserted

SQL> insert into test values('5');

1 row inserted

SQL> insert into test values('5');

1 row inserted

SQL> commit;

Commit complete



SQL> select * from test;

ID
--------------------
1
1
8
5
5

1.按照id排序:

SQL> select row_number() over(order by id),id,rownum from test;

ROW_NUMBER()OVER(ORDERBYID) ID ROWNUM
--------------------------- -------------------- ----------
1 1 1
2 1 2
3 5 5
4 5 4
5 8 3

2.组内(没有分组就是所有数据1组)按照id排序,最后order by在组内排序基础上按照rownum排序:

SQL> select row_number() over(order by id),id,rownum from test order by rownum;

ROW_NUMBER()OVER(ORDERBYID) ID ROWNUM
--------------------------- -------------------- ----------
1 1 1
2 1 2
5 8 3
4 5 4
3 5 5


3.按照rownum排序:

SQL> select row_number() over(order by rownum),id,rownum from test;

ROW_NUMBER()OVER(ORDERBYROWNUM ID ROWNUM
------------------------------ -------------------- ----------
1 1 1
2 1 2
3 8 3
4 5 4
5 5 5

4.按照id分组,组内按照id排序

SQL> select row_number() over(partition by id order by id),id,rownum from test;

ROW_NUMBER()OVER(PARTITIONBYID ID ROWNUM
------------------------------ -------------------- ----------
1 1 1
2 1 2
1 5 5
2 5 4
1 8 3

5.按照id分组,组内按照rownum(这个是早已经出来的结构)排序:

SQL> select row_number() over(partition by id order by rownum),id,rownum from test;

ROW_NUMBER()OVER(PARTITIONBYID ID ROWNUM
------------------------------ -------------------- ----------
1 1 1
2 1 2
1 5 4
2 5 5
1 8 3

总结：oracle在提取数据库的时候是按over(partition by ... order by ...)这个里边的order by后边的字段的一个个distinct值取出数据的。

SQL> select * from t;

A B C D
---------- ---------- ---------- ----------
1 111 G 87
1 111 G 87
1 222 G 85
1 222 G 86
2 111 G 80
2 111 G 80
2 222 G 81
2 222 G 80

8 rows selected

只有partition by a，distinct a有2个值1和2：分2次提取数据
为1的提取一次，4条a值相同，4条平均86.25
为2的提取一次，4条a值相同，4条平均80.25

SQL> select a,b,c,avg(d) over(partition by a ),d from t;

A B C AVG(D)OVER(PARTITIONBYA) D
---------- ---------- ---------- ------------------------ ----------
1 111 G 86.25 87
1 111 G 86.25 87
1 222 G 86.25 85
1 222 G 86.25 86
2 111 G 80.25 80
2 111 G 80.25 80
2 222 G 80.25 81
2 222 G 80.25 80

8 rows selected

partition by a，order by b，distinct a，b有4个值：
1---111
1---222
2---111
2---222
分四次提取数据：
1---111：取出2条，a=1的2条取平均87
1---222：取出2条，a=1的4条取平均86.25
2---111：取出2条，a=2的2条取平均80
2---222：取出2条，a=2的4条取平均80.25

SQL> select a,b,c,avg(d) over(partition by a order by b ),d from t;

A B C AVG(D)OVER(PARTITIONBYAORDERBY D
---------- ---------- ---------- ------------------------------ ----------
1 111 G 87 87
1 111 G 87 87
1 222 G 86.25 85
1 222 G 86.25 86
2 111 G 80 80
2 111 G 80 80
2 222 G 80.25 81
2 222 G 80.25 80

8 rows selected

今天利用分析函数解答了一个朋友的sql问题，自己也做个记录：

http://www.itpub.net/showthread. ... 7237412#post7237412

行列拆分问题

表A数据
起始id 终止ID 面额
890001 890009 20
891001 891007 30
.......


插入B表
ID 面额
890001 20
890002 20
890003 20
890004 20
890005 20
890006 20
890007 20
890008 20
890009 20
891001 30
891002 30
891003 30
891004 30
891005 30
891006 30
891007 30
........

我现在是通过pl/sql过程实现,有没有简便的办法,一条sql语句解决?

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

SQL> create table test(s_id varchar2(20),e_id varchar2(20),je number(18));

Table created

SQL> insert into test values('890001','890009',20);

1 row inserted

SQL> insert into test values('891001','891007',30);

1 row inserted

SQL> insert into test values('892001','892022',50);

1 row inserted

SQL> insert into test values('893001','893008',60);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test;

S_ID                 E_ID                                  JE
-------------------- -------------------- -------------------
890001               890009                                20
891001               891007                                30
892001               892022                                50
893001               893008                                60

SQL>
SQL> SELECT S_ID+ROWNUM-weight,JE FROM (
  2  select S_ID,RN,E_RN,JE,lag(E_RN,1,0) over(order by rownum)+1 weight from(
  3  SELECT S_ID,rownum rn,sum(E_ID-S_ID+1) over(order by rownum) E_RN,JE FROM TEST
  4  )
  5  )
  6  start with rn=1 CONNECT BY ROWNUM<=e_rn;

S_ID+ROWNUM-WEIGHT                  JE
------------------ -------------------
            890001                  20
            890002                  20
            890003                  20
            890004                  20
            890005                  20
            890006                  20
            890007                  20
            890008                  20
            890009                  20
            891001                  30
            891002                  30
            891003                  30
            891004                  30
            891005                  30
            891006                  30
            891007                  30
            892001                  50
            892002                  50
            892003                  50
            892004                  50

S_ID+ROWNUM-WEIGHT                  JE
------------------ -------------------
            892005                  50
            892006                  50
            892007                  50
            892008                  50
            892009                  50
            892010                  50
            892011                  50
            892012                  50
            892013                  50
            892014                  50
            892015                  50
            892016                  50
            892017                  50
            892018                  50
            892019                  50
            892020                  50
            892021                  50
            892022                  50
            893001                  60
            893002                  60
            893003                  60

S_ID+ROWNUM-WEIGHT                  JE
------------------ -------------------
            893004                  60
            893005                  60
            893006                  60
            893007                  60
            893008                  60

46 rows selected

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7194105/viewspace-675133/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7194105/viewspace-675133/