- 浏览: 5139077 次
- 性别:
- 来自: 北京
-
文章分类
最新评论
-
silence19841230:
先拿走看看
SpringBoot2.0开发WebSocket应用完整示例 -
wallimn:
masuweng 写道发下源码下载地址吧!三个相关文件打了个包 ...
SpringBoot2.0开发WebSocket应用完整示例 -
masuweng:
发下源码下载地址吧!
SpringBoot2.0开发WebSocket应用完整示例 -
masuweng:
SpringBoot2.0开发WebSocket应用完整示例 -
wallimn:
水淼火 写道你好,我使用以后,图标不显示,应该怎么引用呢,谢谢 ...
前端框架iviewui使用示例之菜单+多Tab页布局
===========================================================
作者: 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;
相当于
select id,area,stu_type,score from (
select id,area,stu_type,sum(score) score
from students
group by id,area,stu_type
union all
select id,area,null stu_type,sum(score) score
from students
group by id,area
union all
select id,null area,null stu_type,sum(score) score
from students
group by id
)order by id,area,stu_type;
函数分析说明grouping sets((a),(b),(c))
分别按条件a,b,c进行聚合操作
/*--------理解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;
相当于
select id,area,stu_type,score from (
select id,area,stu_type,sum(score) score
from students
group by id,area,stu_type
union all
select id,area,null stu_type,sum(score) score
from students
group by id,area
union all
select id,null area,null stu_type,sum(score) score
from students
group by id
union all
select null id,null area,null stu_type,sum(score) score
from students
)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 area续求和, --按照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;
相当于
select id,area,stu_type,score from (
select id,area,stu_type,sum(score) score
from students
group by id,area,stu_type
union all
select id,area,null stu_type,sum(score) score
from students
group by id,area
union all
select id,null area,null stu_type,sum(score) score
from students
group by id
)order by id,area,stu_type;
函数分析说明grouping sets((a),(b),(c))
分别按条件a,b,c进行聚合操作
/*--------理解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;
相当于
select id,area,stu_type,score from (
select id,area,stu_type,sum(score) score
from students
group by id,area,stu_type
union all
select id,area,null stu_type,sum(score) score
from students
group by id,area
union all
select id,null area,null stu_type,sum(score) score
from students
group by id
union all
select null id,null area,null stu_type,sum(score) score
from students
)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 area续求和, --按照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>
发表评论
-
Oracle连接故障的排除
昨天 24Oracle版本为11G,操作系统为Windows Ser ... -
Oracle数据库相关系统突然提示“SQLException:违反协议”
2024-02-19 15:50 764SQLException:违反协议这个异常可能由很多的 ... -
CentOS在Docker中安装Oracle
2024-02-06 12:13 10081.拉取Oracle镜像,并检 ... -
Windows Server安装oracle数据库一直停在82%
2023-02-04 12:01 498网上有个说法:服务器超过一定数量的CPU后,将不能正常安装 ... -
ORA-04030错误处理
2023-02-04 11:52 2483【错误描述】 错误信息如下: ORA-04030:在尝 ... -
ORA-04030错误处理
2023-02-04 11:45 403【错误描述】 错误信息如下: ORA-04030:在尝 ... -
Linux安装MySQL数据库
2019-06-10 22:27 17041.进入安装包所在目录,解压: tar zxvf mysql- ... -
确定MySQL在Linux系统中配置文件的位置
2019-04-14 19:30 27151.通过which mysql命令来查看mysql的安装位置。 ... -
mysql set names 命令和 mysql 字符编码问题
2019-04-12 00:34 1080转自:https://www.cnblogs.com/digd ... -
MYSQL中取当前周/月/季/年的第一天与最后一天
2018-11-17 23:16 2160转自:https://blog.csdn.net/ ... -
Oracle删除大量数据的实践
2016-11-07 18:03 5761一、引言 从来没有 ... -
Oracle 数据库简明教程 V0.1
2016-03-23 21:01 1988供初学者入门学习使用,以开发者常见、常用的知识为主,基本上 ... -
Oracle拆分字符串函数
2016-03-23 10:58 3297create or replace type string ... -
Oracle数据库远程连接无响应
2016-03-21 10:20 4242故障现象: 服务器本机使用sqlplus / as s ... -
Oracle PGA详解
2015-10-21 15:34 11387转自:http://yanguz123.iteye.com/b ... -
Oracle12C导入dmp数据
2015-10-08 23:43 20463Oracle12C,发生了较大的变化。以前熟悉的东西变得陌 ... -
SQLLDR数据导入小结
2015-07-25 22:06 74521.创建数据表 CREATE TABLE ... -
Window7安装Oracle10
2015-03-06 12:14 1551每次安装都要百度,转到自己的博客上,找起来方便,还能增加访 ... -
Oracle SQL Developer 连接 Mysql 数据库
2015-02-25 19:36 3590下载JDBC包,解压缩这里只要mysql-connector- ... -
Mysql数据备份与恢复
2015-02-25 19:15 1292备份/恢复策略 1. 要定期做 mysql备份,并考虑系统可以 ...
相关推荐
Oracle分析函数是数据库管理系统Oracle中的一个强大特性,它允许用户在SQL查询中执行复杂的分析操作。分析函数在处理报表和数据迁移任务时尤其有用,因为它们可以基于分组计算聚合值,并为每个分组返回多行,而不...
Oracle 分析函数(用法+实例) Oracle 分析函数是 Oracle 8.1.6 版本中引入的高级应用,属于 Oracle 的一大亮点。分析函数可以分为四大类:排名函数、聚合函数、行比较函数和统计函数。下面将对分析函数的原理、...
Oracle 分析函数是一种强大的SQL工具,它允许你在处理数据时执行复杂的分析操作,而不像聚合...参考书籍如Tom Kyte的《Expert One-on-One》和Oracle 9i SQL Reference等,都是深入学习和理解Oracle分析函数的宝贵资源。
Oracle 分析函数是一种高级SQL功能,它允许在单个查询中对数据集进行复杂的分析,无需额外的编程或多次数据库交互。分析函数处理的结果通常基于数据的分组、排序或特定窗口,为统计汇总和复杂的数据分析提供了便利。...
Oracle分析函数是数据库管理系统Oracle中的一种高级SQL特性,它允许用户在单个查询中对一组行进行计算,而无需使用子查询或自连接。这些函数极大地增强了数据分析和报告的能力,提高了查询性能。以下是对Oracle分析...
oracle 分析函数 开发必备 数据库开发工程师
### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具,它自Oracle 8.1.6版本开始引入,并在后续版本中不断完善和发展。这类函数的主要用途在于能够针对一组数据执行复杂的聚合计算,并且不同于...
Oracle 分析函数是数据库查询中的强大工具,它们允许在单个SQL语句中对结果集进行复杂的计算和分组操作,极大地简化了数据分析的过程。在Oracle 8i版本之后引入,分析函数为处理大量数据提供了高效的方法,避免了...
ORACLE分析函数 ORACLE分析函数是数据库管理系统中的一种功能强大且灵活的分析工具,能够对数据进行复杂的分析和处理。通过使用分析函数,开发者可以更加方便地实现业务逻辑,提高查询效率和数据处理速度。 在本...
### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具,它允许用户对分组数据执行复杂的计算,并且能够返回多个结果行。这与传统的聚合函数(如`SUM`、`COUNT`等)形成鲜明对比,后者通常只针对...
Oracle分析函数是数据库管理系统Oracle中一组强大的工具,用于处理集合数据,特别是在复杂的报表和数据分析场景中。它们允许用户在单个SQL查询中执行聚合操作,同时保持行的原始顺序,这是传统的GROUP BY函数无法...
Oracle分析函数是数据库管理系统Oracle中的一个重要特性,自8.1.6版本开始引入,它们用于执行基于组的聚合计算,并且为每个组返回多行结果,而不仅仅是单行。这使得分析函数在数据分析和报表生成方面非常有用,能够...