高级查询

最新推荐文章于 2023-09-11 21:10:07 发布

dgiz88951

最新推荐文章于 2023-09-11 21:10:07 发布

阅读量58

点赞数

原文链接：http://www.cnblogs.com/heibaitan/p/4266584.html

版权

高级查询

一、connect by

drop table test___;

create table test___(pers varchar2(10),pers_ varchar2(10));
insert into test___ 
       select '太爷','爷爷' from dual
       union all
       select '太爷','外爷' from dual
       union all
       select '爷爷','伯父' from dual
       union all
       select '爷爷','爸爸' from dual
       union all
       select '爷爷','姑姑' from dual
       union all
       select '爸爸','儿子' from dual
       union all
       select '爸爸','女儿' from dual
       union all
       select '儿子','婴儿' from dual
       union all
       select '伯父','堂弟' from dual;
commit;

select * from test___;

--向下遍历【将 prior 放在表示下级的字段旁】
select level,pers,pers_ from test___ start with pers='太爷' connect by pers=prior pers_;
--向上遍历【将 prior 放在表示上级的字段旁】
select level,pers,pers_ from test___ start with pers_='婴儿' connect by prior pers=pers_;


--向下遍历时，想排除某一个关系分支
--写法一【只是为了展示格式，实际中没有必要】
select level,pers,pers_ from test___ a 
start with pers='太爷'
connect by pers=prior pers_ and not exists (select 1 from test___ b where b.pers_='伯父' and a.pers=b.pers and a.pers_=b.pers_);
--写法二
select level,pers,pers_ from test___ a 
start with pers='太爷'
connect by pers=prior pers_ and pers_<>'伯父';
  

--向下遍历时，想排除某一个关系节点
select level,pers,pers_ from test___ a 
where pers_<>'伯父'
start with pers='太爷'
connect by pers=prior pers_;

-- sys_connect_by_path( , )【】 当前关系分支的某一列转为行
--connect_by_root ** 根节点
--connect_by_isleaf 当前关系分支是否遍历到最后，1表示是，0表示否

--向上遍历
select 
   level
   ,pers
   ,ltrim(sys_connect_by_path(pers_,'/'),'/') 关系
   ,connect_by_root pers 上级
   ,connect_by_root pers_ 下级
   ,connect_by_isleaf 是否叶子
from test___ ddf
start with pers_='婴儿'
connect by prior pers=pers_;

--向下遍历
select level,pers,pers_
   ,ltrim(sys_connect_by_path(pers_,'/'),'/') 关系
   ,connect_by_root pers 上级
   ,connect_by_root pers_ 下级
   ,connect_by_isleaf 是否叶子
from test___ a 
where pers_<>'伯父'
start with pers='太爷'
connect by pers=prior pers_;

View Code

二、over（）

drop table test__;

create table test__(name varchar2(10),class varchar2(10),code number(10));

insert into test__
       select 'a','语文',34 from dual
       union all
       select 'a','数学',54 from dual
       union all
       select 'a','英语',64 from dual
       union all
       select 'a','化学',43 from dual
       union all       
       select 'b','语文',65 from dual
       union all
       select 'b','数学',12 from dual
       union all
       select 'b','英语',98 from dual
       union all
       select 'b','化学',76 from dual
       union all       
       select 'c','语文',65 from dual
       union all
       select 'c','数学',65 from dual
       union all
       select 'c','英语',45 from dual
       union all
       select 'c','化学',32 from dual
       union all       
       select 'd','语文',65 from dual
       union all
       select 'd','数学',56 from dual
       union all
       select 'd','英语',45 from dual
       union all
       select 'd','化学',65 from dual;
commit;

select * from test__;


select 
       class
       ,name
       ,code
       ,row_number() over(partition by class order by code desc) 排序_123 
       ,rank() over(partition by class order by code desc) 排序_113
       ,dense_rank() over(partition by class order by code desc) 排序_112
       ,first_value(code) over(partition by class order by code desc) 第一个_1
       ,first_value(code) over(order by code desc) 第一个_2
       ,last_value(code) over(partition by class) 最后一个_1
       ,last_value(code) over() 最后一个_2
       ,sum(code) over(partition by class order by code desc) 分组求和_叠加
       ,sum(code) over(partition by class) 分组求和_不叠加
       ,to_char(round(code/(sum(code) over(partition by class)),4)*100)||'%' 百分比
       ,sum(code) over(partition by class order by code desc rows between 1 preceding and 1 following) 上下2行 
       ,sum(code) over(partition by class order by code desc range between 45 preceding and 45 following) 前后45
       ,lag(code) over(partition by class order by code desc) 上一个_1
       ,lead(code) over(order by code desc) 上一个_2
       ,to_char(round(ratio_to_report(code) over(),4)*100)||'%' 占比_1
       ,to_char(round(ratio_to_report(code) over(partition by class),4)*100)||'%' 占比_2
       ,percent_rank() over(partition by class order by code desc) aa_1
       ,cume_dist() over(partition by class order by code desc) aa_2
       ,percentile_cont(0.6) within group(order by code desc) over(partition by class) aa_3
       ,percentile_disc(.07) within group(order by code desc) over(partition by class) aa_4
from 
       test__ a;



sum(code) over(partition by class)
   表示按class分组，并求总和，用group by可简单实现
sum(code) over(partition by class order by code desc)
   表示按class分组，然后按code递降，并将其依次叠加

first_value(code) over(partition by class order by code desc)
   得到的值和code原来的值一致
first_value(code) over(order by code desc)
   得到的值为所有code中的最大值
last_value(code) over(partition by class)
   得到的值为各个分组中的code的最小值
last_value(code) over() 
   得到的值为所有code中的最小值 
      
range between 5 preceding and 5 following
   窗口范围为当前行数据幅度减5加5后的范围内的
rows between 5 preceding and 5 following
   窗口范围为当前行前后各移动5行
   
lag()和lead() 的 over()中必须包含 order by ，因为只有排序了才有上一个和下一个

ratio_to_report(code) over(partition by class)
   表示code在所有code中的占比 
ratio_to_report(code) over(partition by class)
   表示code在各个class分组的占比        
   
percent_rank()
   所在组排名序号-1除以该组所有的行数-1

cume_dist()
   所在组排名序号除以该组所有的行数，但是如果存在并列情况，则需加上并列的个数-1

percentile_cont()
   输入一个百分比（该百分比就是按照percent_rank函数计算的值），返回该百分比位置的平均值

percentile_disc()
   返回一个与输入的分布百分比值相对应的数据值，分布百分比的计算方法见函数CUME_DIST，如果没有正好对应的数据值，就取大于该分布值的下一个值。        

--一个排序的实例【按成绩横向排序】
select 
       class
       ,max(case pai when 1 then tt else null end) 第一名
       ,max(case pai when 2 then tt else null end) 第二名
       ,max(case pai when 3 then tt else null end) 第三名
from 
       (select class,name||','||code tt,row_number() over(partition by class order by code desc) pai from test__ a)
group by class;

View Code

三、grouping

drop table test_;
create table test_(a varchar2(10),b varchar2(10),c number(10));
insert into test_ 
       select 'a','a',1 from dual
       union all
       select 'a','b',3 from dual
       union all
       select 'a','c',6 from dual
       union all
       select 'a','a',2 from dual
       union all
       select 'b','a',1 from dual
       union all
       select 'b','b',1 from dual
       union all
       select 'b','c',1 from dual
       union all
       select 'c','a',2 from dual
       union all
       select 'c','b',2 from dual
       union all
       select 'c','c',2 from dual
commit;

select * from test_;


select a,b,sum(c),grouping(a),grouping(b) 
from test_ 
group by rollup(a,b);

select a,b,sum(c),grouping(a),grouping(b) 
from test_ 
group by cube(a,b);

select a,b,sum(c),grouping(a),grouping(b) 
from test_ 
group by grouping sets((a,b),a,b,null);

select a,b,sum(c),grouping(a),grouping(b) 
from test_ 
group by a,b;

grouping() 
   只有两个值：0（表示参与了统计），1（表示未参与统计）
group by rollup(a,b)【排序规则】
   表示 group by a,b + group by a + group by null;【9+3+1=13】
group by cube(a,b)【组合规则】
   表示 group by null + group by b + group by a + group by a,b ;【1+3+3+9=16】
group by grouping sets((a,b),a,b,null)【可选择的自由组合】 
   表示 group by a,b + group by a + group by b + group by null;【9+3+3+1=16】
   
group by null
   表示直接 select sum(c) from dual;