select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOBField)) from TableName;
出处：http://zhouwf0726.itpub.net/post/9689/158090
oracle分析函数
===========================================================
作者: zhouwf0726(http://zhouwf0726.itpub.net)
发表于:2006.07.25 12:51
分类: oracle开发
出处：http://zhouwf0726.itpub.net/post/9689/158090
---------------------------------------------------------------
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;
看到很多人对于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
详细讲述看这些地址：
http://zhouwf0726.itpub.net/post/9689/247171
http://zhouwf0726.itpub.net/post/9689/247175
更多讨论看以下地址：
http://www.itpub.net/showthread.php?s=&threadid=608107&perpage=10&pagenumber=1
/*****************分析函数的计算顺序问题*************/
有些人对oracle分析函数中select over(partition by col1 order by col2) from test order by ...关于partition by 和 组内order by以及最后的order by的执行顺序产生疑惑。
http://www.itpub.net/showthread.php?s=&threadid=732957&perpage=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.php?s=&postid=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>
===========================================================
作者: zhouwf0726(http://zhouwf0726.itpub.net)
发表于:2006.07.25 12:51
分类: oracle开发
出处：http://zhouwf0726.itpub.net/post/9689/158090
---------------------------------------------------------------
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;
看到很多人对于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
详细讲述看这些地址：
http://zhouwf0726.itpub.net/post/9689/247171
http://zhouwf0726.itpub.net/post/9689/247175
更多讨论看以下地址：
http://www.itpub.net/showthread.php?s=&threadid=608107&perpage=10&pagenumber=1
/*****************分析函数的计算顺序问题*************/
有些人对oracle分析函数中select over(partition by col1 order by col2) from test order by ...关于partition by 和 组内order by以及最后的order by的执行顺序产生疑惑。
http://www.itpub.net/showthread.php?s=&threadid=732957&perpage=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.php?s=&postid=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>
扩展GROUP BY研究 http://www.itpub.net/viewthread.php?tid=998611&extra=page%3D2%26amp%3Bfilter%3Ddigest
0.概述
本文主要介绍查询中扩展group by子句的基本用法，同时简要解析工作的原理。
主要包括如下内容：
(1). 准备
(2). GROUP BY
(3). ROLLUP
(4). CUBE
(5). GROUPING SETS
(6). GROUPING()函数
(7). grouping_id()函数
(8). group_id()函数
1. 准备
创建一个查询样例表，下面的例子都基于此表。
CREATE TABLE egb_tab(
c1 VARCHAR2(10),
c2 VARCHAR2(10),
c3 VARCHAR2(10),
c4 VARCHAR2(10));
INSERT INTO egb_tab VALUES('x','a','1','xa1');
INSERT INTO egb_tab VALUES('x','a','2','xa2');
INSERT INTO egb_tab VALUES('x','a','2','xa2');
INSERT INTO egb_tab VALUES('x','a','3','xa3');
INSERT INTO egb_tab VALUES('x','a','3','xa3');
INSERT INTO egb_tab VALUES('x','a','3','xa3');
INSERT INTO egb_tab VALUES('x','b','4','xb4');
INSERT INTO egb_tab VALUES('x','b','4','xb4');
INSERT INTO egb_tab VALUES('x','b','5','xb4');
INSERT INTO egb_tab VALUES('x','c','6','xc6');
INSERT INTO egb_tab VALUES('x','c','6','xc6');
--------------------------------------------------------
INSERT INTO egb_tab VALUES('y','a','1','xa1');
INSERT INTO egb_tab VALUES('y','a','1','xa1');
INSERT INTO egb_tab VALUES('y','a','2','xa1');
INSERT INTO egb_tab VALUES('y','a','2','xa1');
INSERT INTO egb_tab VALUES('y','a','3','xa1');
INSERT INTO egb_tab VALUES('y','b','4','xb2');
INSERT INTO egb_tab VALUES('y','b','4','xb2');
INSERT INTO egb_tab VALUES('y','b','5','xb2');
INSERT INTO egb_tab VALUES('y','c','6','xa1');
INSERT INTO egb_tab VALUES('y','c','7','xb2');
INSERT INTO egb_tab VALUES('y','c','7','xb2');
INSERT INTO egb_tab VALUES('y','d','8','xb2');
INSERT INTO egb_tab VALUES('y','d','9','xa1');
--------------------------------------------------------
INSERT INTO egb_tab VALUES('z','a','1','xa5');
INSERT INTO egb_tab VALUES('z','a','2','xa5');
INSERT INTO egb_tab VALUES('z','f','6','xa5');
INSERT INTO egb_tab VALUES('z','f','6','xa3');
INSERT INTO egb_tab VALUES('z','f','7','xa4');
COMMIT;
SELECT * FROM egb_tab;
C1 C2 C3 C4
------------
x a 1 xa1
x a 2 xa2
x a 2 xa2
x a 3 xa3
x a 3 xa3
x a 3 xa3
x b 4 xb4
x b 4 xb4
x b 5 xb4
x c 6 xc6
x c 6 xc6
y a 1 xa1
y a 1 xa1
y a 2 xa1
y a 2 xa1
y a 3 xa1
y b 4 xb2
y b 4 xb2
y b 5 xb2
y c 6 xa1
y c 7 xb2
y c 7 xb2
y d 8 xb2
y d 9 xa1
z a 1 xa5
z a 2 xa5
z f 6 xa5
z f 6 xa3
z f 7 xa4
2. GROUP BY
单独使用group by统计方式只有一种，即按group by后的所有列进行一次统计。
注意： GROUP BY，以及 ROLLUP,CUBE,GROUPING SETS 后面的参数，
不必都出现在查询列中，只要保证查询列中的参数是其子集便可。
比如下面的例子中，SELECT 中不必选择c1，c2，只选择c3是可以的。
SELECT c1, c2, c3, COUNT(c4) cnt
FROM egb_tab
GROUP BY c1, c2, c3
ORDER BY c1, c2, c3;
C1 C2 C3 CNT
------------
x a 1 1
x a 2 2
x a 3 3
x b 4 2
x b 5 1
x c 6 2
y a 1 2
y a 2 2
y a 3 1
y b 4 2
y b 5 1
y c 6 1
y c 7 2
y d 8 1
y d 9 1
z a 1 1
z a 2 1
z f 6 2
z f 7 1
3. ROLLUP
若rollup的参数个数为n,则统计的方式有n+1种。
假设 ROLLUP(p1,p2,...,p(n-1),p(n))，则分别按
p1,p2,..,p(n-1),p(n)
p1,p2,...p(n-1)
... ...
p1
NULL
分组统计，其中 NULL 表示不分组。
例子：
SELECT c1, c2, c3, COUNT(c4) cnt
FROM egb_tab
GROUP BY ROLLUP(c1, c2, c3)
ORDER BY c1, c2, c3;
C1 C2 C3 CNT
------------
x a 1 1
x a 2 2
x a 3 3
x a 6
x b 4 2
x b 5 1
x b 3
x c 6 2
x c 2
x 11
y a 1 2
y a 2 2
y a 3 1
y a 5
y b 4 2
y b 5 1
y b 3
y c 6 1
y c 7 2
y c 3
y d 8 1
y d 9 1
y d 2
y 13
z a 1 1
z a 2 1
z a 2
z f 6 2
z f 7 1
z f 3
z 5
29
<=> 以下查询union all(需要剔除中间的order by)
(1)按c1,c2,c3分组统计
SELECT c1, c2, c3, COUNT(c4) cnt
FROM egb_tab
GROUP BY c1, c2, c3
ORDER BY c1, c2, c3;
C1 C2 C3 CNT
------------
x a 1 1
x a 2 2
x a 3 3
x b 4 2
x b 5 1
x c 6 2
y a 1 2
y a 2 2
y a 3 1
y b 4 2
y b 5 1
y c 6 1
y c 7 2
y d 8 1
y d 9 1
z a 1 1
z a 2 1
z f 6 2
z f 7 1
(2)按c1,c2分组统计
SELECT c1, c2, NULL c3, COUNT(c4) cnt
FROM egb_tab
GROUP BY c1, c2, NULL
ORDER BY c1, c2, NULL;
C1 C2 C3 CNT
------------
x a 6
x b 3
x c 2
y a 5
y b 3
y c 3
y d 2
z a 2
z f 3
(3)按c1分组统计
SELECT c1, NULL c2, NULL c3, COUNT(c4) cnt
FROM egb_tab
GROUP BY c1, NULL, NULL
ORDER BY c1, NULL, NULL;
C1 C2 C3 CNT
-------------
x 11
y 13
z 5
(4)不分组统计
SELECT NULL c1, NULL c2, NULL c3, COUNT(c4) cnt
FROM egb_tab
GROUP BY NULL, NULL, NULL
ORDER BY NULL, NULL, NULL;
C1 C2 C3 CNT
-------------
29
4. CUBE
若cube参数的个数为n,则统计的方式有2^n种(表示2的n次方)。
假设 CUBE(p1,p2,...,p(n-1),p(n))，则分别按：
从p1,p2,...,p(n-1),p(n)中:
选1个分组，组合为C(n,1),
选2个分组，组合为C(n,2),
...
选n-1个分组，组合为C(n,n-1),
选n个分组，组合为C(n,n)
不分组，相当于C(n,0)
进行统计，故整个统计种数为：C(n,0) + C(n,1) + C(n,2) + ... + C(n,n-1) + C(n,n) = 2^n。
例子：
SELECT c1,c2,c3,COUNT(c4) cnt
FROM egb_tab
GROUP BY CUBE(c1,c2,c3)
ORDER BY c1,c2,c3;
<=>以下查询union all(需要剔除中间的order by)
(1)不分组统计
SELECT NULL c1, NULL c2, NULL c3, COUNT(c4)
FROM egb_tab
GROUP BY NULL, NULL, NULL
ORDER BY NULL, NULL, NULL
C1 C2 C3 CNT
-------------
29
(2)按c1分组统计
SELECT c1, NULL c2, NULL c3, COUNT(c4) cnt
FROM egb_tab
GROUP BY c1, NULL, NULL
ORDER BY c1, NULL, NULL
C1 C2 C3 CNT
---------------
x 11
y 13
z 5
(3)按c2分组统计
SELECT NULL c1, c2, NULL c3, COUNT(c4) cnt
FROM egb_tab
GROUP BY NULL, c2, NULL
ORDER BY NULL, c2, NULL
C1 C2 C3 CNT
----------------
a 13
b 6
c 5
d 2
f 3
(4)按c3分组统计
SELECT NULL c1, NULL c2, c3, COUNT(c4) cnt
FROM egb_tab
GROUP BY NULL, NULL, c3
ORDER BY NULL, NULL, c3
C1 C2 C3 CNT
本文主要介绍查询中扩展group by子句的基本用法，同时简要解析工作的原理。
主要包括如下内容：
(1). 准备
(2). GROUP BY
(3). ROLLUP
(4). CUBE
(5). GROUPING SETS
(6). GROUPING()函数
(7). grouping_id()函数
(8). group_id()函数
1. 准备
创建一个查询样例表，下面的例子都基于此表。
CREATE TABLE egb_tab(
c1 VARCHAR2(10),
c2 VARCHAR2(10),
c3 VARCHAR2(10),
c4 VARCHAR2(10));
INSERT INTO egb_tab VALUES('x','a','1','xa1');
INSERT INTO egb_tab VALUES('x','a','2','xa2');
INSERT INTO egb_tab VALUES('x','a','2','xa2');
INSERT INTO egb_tab VALUES('x','a','3','xa3');
INSERT INTO egb_tab VALUES('x','a','3','xa3');
INSERT INTO egb_tab VALUES('x','a','3','xa3');
INSERT INTO egb_tab VALUES('x','b','4','xb4');
INSERT INTO egb_tab VALUES('x','b','4','xb4');
INSERT INTO egb_tab VALUES('x','b','5','xb4');
INSERT INTO egb_tab VALUES('x','c','6','xc6');
INSERT INTO egb_tab VALUES('x','c','6','xc6');
--------------------------------------------------------
INSERT INTO egb_tab VALUES('y','a','1','xa1');
INSERT INTO egb_tab VALUES('y','a','1','xa1');
INSERT INTO egb_tab VALUES('y','a','2','xa1');
INSERT INTO egb_tab VALUES('y','a','2','xa1');
INSERT INTO egb_tab VALUES('y','a','3','xa1');
INSERT INTO egb_tab VALUES('y','b','4','xb2');
INSERT INTO egb_tab VALUES('y','b','4','xb2');
INSERT INTO egb_tab VALUES('y','b','5','xb2');
INSERT INTO egb_tab VALUES('y','c','6','xa1');
INSERT INTO egb_tab VALUES('y','c','7','xb2');
INSERT INTO egb_tab VALUES('y','c','7','xb2');
INSERT INTO egb_tab VALUES('y','d','8','xb2');
INSERT INTO egb_tab VALUES('y','d','9','xa1');
--------------------------------------------------------
INSERT INTO egb_tab VALUES('z','a','1','xa5');
INSERT INTO egb_tab VALUES('z','a','2','xa5');
INSERT INTO egb_tab VALUES('z','f','6','xa5');
INSERT INTO egb_tab VALUES('z','f','6','xa3');
INSERT INTO egb_tab VALUES('z','f','7','xa4');
COMMIT;
SELECT * FROM egb_tab;
C1 C2 C3 C4
------------
x a 1 xa1
x a 2 xa2
x a 2 xa2
x a 3 xa3
x a 3 xa3
x a 3 xa3
x b 4 xb4
x b 4 xb4
x b 5 xb4
x c 6 xc6
x c 6 xc6
y a 1 xa1
y a 1 xa1
y a 2 xa1
y a 2 xa1
y a 3 xa1
y b 4 xb2
y b 4 xb2
y b 5 xb2
y c 6 xa1
y c 7 xb2
y c 7 xb2
y d 8 xb2
y d 9 xa1
z a 1 xa5
z a 2 xa5
z f 6 xa5
z f 6 xa3
z f 7 xa4
2. GROUP BY
单独使用group by统计方式只有一种，即按group by后的所有列进行一次统计。
注意： GROUP BY，以及 ROLLUP,CUBE,GROUPING SETS 后面的参数，
不必都出现在查询列中，只要保证查询列中的参数是其子集便可。
比如下面的例子中，SELECT 中不必选择c1，c2，只选择c3是可以的。
SELECT c1, c2, c3, COUNT(c4) cnt
FROM egb_tab
GROUP BY c1, c2, c3
ORDER BY c1, c2, c3;
C1 C2 C3 CNT
------------
x a 1 1
x a 2 2
x a 3 3
x b 4 2
x b 5 1
x c 6 2
y a 1 2
y a 2 2
y a 3 1
y b 4 2
y b 5 1
y c 6 1
y c 7 2
y d 8 1
y d 9 1
z a 1 1
z a 2 1
z f 6 2
z f 7 1
3. ROLLUP
若rollup的参数个数为n,则统计的方式有n+1种。
假设 ROLLUP(p1,p2,...,p(n-1),p(n))，则分别按
p1,p2,..,p(n-1),p(n)
p1,p2,...p(n-1)
... ...
p1
NULL
分组统计，其中 NULL 表示不分组。
例子：
SELECT c1, c2, c3, COUNT(c4) cnt
FROM egb_tab
GROUP BY ROLLUP(c1, c2, c3)
ORDER BY c1, c2, c3;
C1 C2 C3 CNT
------------
x a 1 1
x a 2 2
x a 3 3
x a 6
x b 4 2
x b 5 1
x b 3
x c 6 2
x c 2
x 11
y a 1 2
y a 2 2
y a 3 1
y a 5
y b 4 2
y b 5 1
y b 3
y c 6 1
y c 7 2
y c 3
y d 8 1
y d 9 1
y d 2
y 13
z a 1 1
z a 2 1
z a 2
z f 6 2
z f 7 1
z f 3
z 5
29
<=> 以下查询union all(需要剔除中间的order by)
(1)按c1,c2,c3分组统计
SELECT c1, c2, c3, COUNT(c4) cnt
FROM egb_tab
GROUP BY c1, c2, c3
ORDER BY c1, c2, c3;
C1 C2 C3 CNT
------------
x a 1 1
x a 2 2
x a 3 3
x b 4 2
x b 5 1
x c 6 2
y a 1 2
y a 2 2
y a 3 1
y b 4 2
y b 5 1
y c 6 1
y c 7 2
y d 8 1
y d 9 1
z a 1 1
z a 2 1
z f 6 2
z f 7 1
(2)按c1,c2分组统计
SELECT c1, c2, NULL c3, COUNT(c4) cnt
FROM egb_tab
GROUP BY c1, c2, NULL
ORDER BY c1, c2, NULL;
C1 C2 C3 CNT
------------
x a 6
x b 3
x c 2
y a 5
y b 3
y c 3
y d 2
z a 2
z f 3
(3)按c1分组统计
SELECT c1, NULL c2, NULL c3, COUNT(c4) cnt
FROM egb_tab
GROUP BY c1, NULL, NULL
ORDER BY c1, NULL, NULL;
C1 C2 C3 CNT
-------------
x 11
y 13
z 5
(4)不分组统计
SELECT NULL c1, NULL c2, NULL c3, COUNT(c4) cnt
FROM egb_tab
GROUP BY NULL, NULL, NULL
ORDER BY NULL, NULL, NULL;
C1 C2 C3 CNT
-------------
29
4. CUBE
若cube参数的个数为n,则统计的方式有2^n种(表示2的n次方)。
假设 CUBE(p1,p2,...,p(n-1),p(n))，则分别按：
从p1,p2,...,p(n-1),p(n)中:
选1个分组，组合为C(n,1),
选2个分组，组合为C(n,2),
...
选n-1个分组，组合为C(n,n-1),
选n个分组，组合为C(n,n)
不分组，相当于C(n,0)
进行统计，故整个统计种数为：C(n,0) + C(n,1) + C(n,2) + ... + C(n,n-1) + C(n,n) = 2^n。
例子：
SELECT c1,c2,c3,COUNT(c4) cnt
FROM egb_tab
GROUP BY CUBE(c1,c2,c3)
ORDER BY c1,c2,c3;
<=>以下查询union all(需要剔除中间的order by)
(1)不分组统计
SELECT NULL c1, NULL c2, NULL c3, COUNT(c4)
FROM egb_tab
GROUP BY NULL, NULL, NULL
ORDER BY NULL, NULL, NULL
C1 C2 C3 CNT
-------------
29
(2)按c1分组统计
SELECT c1, NULL c2, NULL c3, COUNT(c4) cnt
FROM egb_tab
GROUP BY c1, NULL, NULL
ORDER BY c1, NULL, NULL
C1 C2 C3 CNT
---------------
x 11
y 13
z 5
(3)按c2分组统计
SELECT NULL c1, c2, NULL c3, COUNT(c4) cnt
FROM egb_tab
GROUP BY NULL, c2, NULL
ORDER BY NULL, c2, NULL
C1 C2 C3 CNT
----------------
a 13
b 6
c 5
d 2
f 3
(4)按c3分组统计
SELECT NULL c1, NULL c2, c3, COUNT(c4) cnt
FROM egb_tab
GROUP BY NULL, NULL, c3
ORDER BY NULL, NULL, c3
C1 C2 C3 CNT