oracleconnectby和分析函数总结
1. connect by ⽤法总结 (2)
⼀、树查询(递归查询) (2)
⼆、列转⾏sys_connect_by_path() (4)
2.分析函数总结 (6)
1.分析函数(OVER) (6)
2.分析函数2(Rank, Dense_rank, row_number) (9)
3.分析函数3(Top/Bottom N、First/Last、NTile) (9)
4.窗⼝函数 (11)
5.报表函数 (14)
⼀、树查询(递归查询)
1.作⽤
对于oracle进⾏简单树查询(递归查询)
列转⾏
2.基本语法
select ... from
where ：过滤条件，⽤于对返回的所有记录进⾏过滤。
start with ：查询结果重起始根结点的限定条件。
connect by ; ：连接条件
1)例⼦：
select num1,num2
from table
start with num2 = 1008
connect by num2 = prior num1 ;
2)解释:
start with：⽤来标识哪个节点作为查树型结构的根节点。若该⼦句被省略，则表⽰所有满⾜查询条件的⾏作为根节点。prior: 位置很重要（⾃我总结，和⽗在⼀起则⾃底向上，即查⽗和⼦在⼀起则⾃顶向下查⼦）
例⼦
原始数据 num1 为⽗ num2 为⼦
看下⾯的图
1. CONNECT_BY_ROOT 返回当前节点的最顶端节点。
2. CONNECT_BY_ISLEAF 判断是否为叶⼦节点，是1，不是0。
3. LEVEL 伪列表⽰节点深度。
4. SYS_CONNECT_BY_PATH函数显⽰详细路径，并⽤“/”分隔。
⼆、列转⾏sys_connect_by_path()
这个函数使⽤之前必须先建⽴⼀个树，否则⽆⽤
sys_connect_by_path(字段名, 2个字段之间的连接符号)
with
tmp_a as (
select '1' a,'0' p from dual
union all
select '2','1' from dual
union all
select '3','1' from dual
union all
select '4','3' from dual
union all
select '5','2' from dual
union all
select '6','5' from dual
)
-- ⼦全部显⽰根-->⼦ level代表级别
select a,p,sys_connect_by_path(a,'--'),level from tmp_a start with a = 1
connect by p = prior a
-- 2和2的所有下级去掉根-->⼦ (开始就要去掉)
select a,p,sys_connect_by_path(a,'--') from tmp_a start with p = 1 and a <> '2'
connect by p = prior a
-
- 2的所有下级都去掉根-->⼦ (connect 时去掉) select a,p,sys_connect_by_path(a,'--') from tmp_a start with a = 1
connect by p = prior a and p <> '2' --去掉2的分枝-- 2的下⼀级去掉根-->⼦ (where 中去掉) select a,p,sys_connect_by_path(a,'--') from tmp_a where p <> '2'
start with a = 1
connect by p = prior a
--显⽰最长的根-->⼦
with
tmp_tab as (
select '中国' s,null b from dual
union all
select '⼴东' s,'中国' b from dual
union all
select '湖南' s,'中国' b from dual
union all
select '衡阳' s,'湖南' b from dual
union all
select '⼴州' s,'⼴东' b from dual
union all
select '衡东' s,'衡阳' b from dual
)
select max(sys_connect_by_path(s,'/')) from tmp_tab start with s = '湖南'
connect by prior s = b
2.分析函数总结
⼀、统计⽅⾯：
Sum( ) Over ([Partition by ][Order by ])
Sum( ) Over ([Partition by ][Order by ]
Rows Between Preceding And Following)connect to和connect with的区别
Sum( ) Over ([Partition by ][Order by ]
Rows Between Preceding And Current Row)
Sum( ) Over ([Partition by ][Order by ]
Range Between Interval ' ''Day' Preceding
And Interval ' ''Day' Following )
⼆、排列⽅⾯：
Rank() Over ([Partition by ][Order by ][Nulls First/Last])
Dense_rank() Over ([Patition by ][Order by ][Nulls First/Last])
Row_number() Over ([Partitionby ][Order by ][Nulls First/Last])
Ntile( ) Over ([Partition by ][Order by ])
三、最⼤值/最⼩值查⽅⾯：
Min( )/Max( ) Keep (Dense_rank First/Last [Partition by ][Order by ])
四、⾸记录/末记录查⽅⾯：
First_value / Last_value(Sum( ) Over ([Patition by ][Order by ]
Rows Between Preceding And Following ))
五、相邻记录之间⽐较⽅⾯：
Lag(Sum( ), 1) Over([Patition by ][Order by ])
1.分析函数(OVER)
⼀.分析函数语法:
FUNCTION_NAME(,...)
OVER
()
例:
sum(sal)over (partition by deptno order by ename) new_alias
sum：函数名
(sal)：参数 0~3个参数可以是表达式
Over：关键字
partition by ：（可选）分区
order by ：（可选）LAG和LEAD 需，AVG不需要，如果使⽤排序的开窗函数时，必须加
1)FUNCTION⼦句
26个分析函数，按功能分5类
分析函数分类
1.等级(ranking)函数: ⽤于寻前N种查询
2.开窗(windowing)函数:⽤于计算不同的累计，如SUM,COUNT,AVG,MIN,MAX等，作⽤于数据的⼀
个窗⼝上
3.制表(reporting)函数：与开窗函数同名，作⽤于⼀个分区或⼀组上的所有列
（制表与开窗的区别：制表的OVER语句上少⼀个ORDER BY⼦句）
www.doczj/doc/c7eac628e0bd960590c69ec3d5bbfd0a7956d55d.html G,LEAD函数: 可在结果集中向前或向后检索值，为了避免数据的⾃连接，它们是⾮常⽤⽤的.
5.VAR_POP,VAR_SAMP,STDEV_POPE及线性的衰减函数：计算任何未排序分区的统计值
2)PARTITION⼦句
分组
3)ORDER BY⼦句
分析函数中ORDER BY的存在将添加⼀个默认的开窗⼦句，这意味着计算中所使⽤的⾏的集合是
当前分区中当前⾏和前⾯所有⾏，没有ORDER BY时，默认的窗⼝是全部的分区。在Order by⼦句后可以添加nulls last，如:order by comm desc nulls last表⽰排序时忽略comm列为空的⾏.
⼆、分析函数简单实例：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。