oracle connect by 和 分析函数总结.docx

1、oracle connect by 和 分析函数总结1.connect by 用法总结一、树查询(递归查询)1.作用对于oracle进行简单树查询(递归查询)列转行2.基本语法select . from where ：过滤条件，用于对返回的所有记录进行过滤。 start with ：查询结果重起始根结点的限定条件。 connect by ; ：连接条件1)例子：select num1,num2from tablestart with num2 = 1008connect by num2 = prior num1;2)解释:start with：用来标识哪个节点作为查找树型结构的根节点。若该子句

2、被省略，则表示所有满足查询条件的行作为根节点。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

3、个字段之间的连接符号)withtmp_a as (select 1 a,0 p from dualunion allselect 2,1 from dualunion allselect 3,1 from dualunion allselect 4,3 from dualunion allselect 5,2 from dualunion allselect 6,5 from dual)- 子全部显示 根-子 level代表级别select a,p,sys_connect_by_path(a,-),level from tmp_astart with a = 1connect by p = p

4、rior a- 2和2的所有下级去掉 根-子 (开始就要去掉)select a,p,sys_connect_by_path(a,-) from tmp_astart with p = 1 and a 2connect by p = prior a- 2的所有下级都去掉 根-子 (connect 时去掉)select a,p,sys_connect_by_path(a,-) from tmp_astart with a = 1connect by p = prior a and p 2 -去掉2的分枝- 2的下一级去掉 根-子 (where 中去掉)select a,p,sys_connect_

5、by_path(a,-) from tmp_awhere p 2 start with a = 1connect by p = prior a-显示最长的 根-子withtmp_tab as (select 中国 s,null b from dualunion allselect 广东 s,中国 b from dualunion allselect 湖南 s,中国 b from dualunion allselect 衡阳 s,湖南 b from dualunion allselect 广州 s,广东 b from dualunion allselect 衡东 s,衡阳 b from dual

6、)select max(sys_connect_by_path(s,/) from tmp_tabstart with s = 湖南connect by prior s = b 2.分析函数总结一、统计方面：Sum( )Over(Partitionby Orderby )Sum( )Over(Partitionby Orderby RowsBetween PrecedingAnd Following)Sum( )Over(Partitionby Orderby RowsBetween PrecedingAndCurrentRow)Sum( )Over(Partitionby Orderby R

7、angeBetweenInterval DayPrecedingAndInterval DayFollowing)二、排列方面：Rank()Over(Partitionby Orderby NullsFirst/Last)Dense_rank()Over(Patitionby Orderby NullsFirst/Last)Row_number()Over(Partitionby Orderby NullsFirst/Last)Ntile( )Over(Partitionby Orderby )三、最大值/最小值查找方面：Min( )/Max( )Keep(Dense_rankFirst/La

8、stPartitionby Orderby )四、首记录/末记录查找方面：First_value/Last_value(Sum( )Over(Patitionby Orderby RowsBetween PrecedingAnd Following)五、相邻记录之间比较方面：Lag(Sum( ),1)Over(Patitionby Orderby )1.分析函数(OVER)一.分析函数语法:FUNCTION_NAME(,.)OVER()例:sum(sal) over (partition by deptno order by ename) new_aliassum：函数名(sal)：参数 03

9、个参数 可以是表达式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子句）4.LAG,LEAD函数: 可在

10、结果集中向前或向后检索值,为了避免数据的自连接,它们是非常用用的.5.VAR_POP,VAR_SAMP,STDEV_POPE及线性的衰减函数:计算任何未排序分区的统计值2)PARTITION子句分组3)ORDER BY子句分析函数中ORDERBY的存在将添加一个默认的开窗子句,这意味着计算中所使用的行的集合是当前分区中当前行和前面所有行,没有ORDER BY时,默认的窗口是全部的分区。在Order by子句后可以添加nulls last,如:order by comm desc nulls last表示排序时忽略comm列为空的行.二、分析函数简单实例：按区域查找2001年度订单总额占区域订单

11、总额20%以上的客户【1】测试数据：SQLselect*fromorders_tmp;CUST_NBRREGION_IDSALESPERSON_IDYEARMONTHTOT_ORDERSTOT_SALES-11711 200172122044542001102378027672001233750106820011221691106720012342624【2】测试语句：SQLselecto.cust_nbrcustomer,2o.region_idregion,3sum(o.tot_sales)cust_sales,4sum(sum(o.tot_sales)over(partitionbyo.

12、region_id)region_sales5fromorders_tmpo6whereo.year=20017groupbyo.region_id,o.cust_nbr;CUSTOMERREGIONCUST_SALESREGION_SALES-45378023780276375068065106643156806511712204122043、分析函数OVER解析：请注意上面的绿色高亮部分，group by的意图很明显：将数据按区域ID，客户进行分组。假如我们只需要统计每个区域每个客户的订单总额，那么我们只需要group by o.region_id,o.cust_nbr就够了。但我们还想在

13、每一行显示该客户所在区域的订单总额，这一点和前面的不同：需要在前面分组的基础上按区域累加。group by和sum是无法做到这一点的(因为聚集操作的级别不一样，前者是对一个客户，后者是对一批客户)。这就是over函数的作用了！它的作用是告诉SQL引擎：按区域对数据进行分区，然后累积每个区域每个客户的订单总额(sum(sum(o.tot_sales)。最终语句SQLselectall_sales.*,2100*round(cust_sales/region_sales,2)|%Percent3from(selecto.cust_nbrcustomer,4o.region_idregion,5su

14、m(o.tot_sales)cust_sales,6sum(sum(o.tot_sales)over(partitionbyo.region_id)region_sales7fromorders_tmpo8whereo.year=20019groupbyo.region_id,o.cust_nbr)all_sales10whereall_sales.cust_salesall_sales.region_sales*0.2;CUSTOMERREGIONCUST_SALESREGION_SALESPERCENT-453780237802100%106643156806594%11712204122

15、04100%2.分析函数2(Rank, Dense_rank, row_number)遇到相同的数据时用Rank, Dense_rank, row_number排名策略，他们之间的区别 直接看例子SQLselectregion_id,customer_id,sum(customer_sales)total,2rank()over(orderbysum(customer_sales)desc)rank,3dense_rank()over(orderbysum(customer_sales)desc)dense_rank,4row_number()over(orderbysum(customer_

16、sales)desc)row_number5fromuser_order6groupbyregion_id,customer_id;REGION_IDCUSTOMER_IDTOTALRANKDENSE_RANKROW_NUMBER- 818125384011111152122499212121292312249921212139241224992121214103012168581513 153.分析函数3(Top/Bottom N、First/Last、NTile)一、Top/Bottom N查询：找出所有订单总额排名前3的大客户：SQLselect*SQLfrom(selectregion

17、_id,SQLcustomer_id,SQLsum(customer_sales)cust_total,SQLrank()over(orderbysum(customer_sales)descNULLSLAST)rankSQLfromuser_orderSQLgroupbyregion_id,customer_id)SQLwhererankselectmin(customer_id)2keep(dense_rankfirstorderbysum(customer_sales)desc)first,3min(customer_id)4keep(dense_ranklastorderbysum(c

18、ustomer_sales)desc)last5fromuser_order6groupbycustomer_id;FIRSTLAST-311Min：函?的作用是用于?存在多?First/Last情?下保?返回唯一的?。（去掉?出?）Keep：?上面的?果我?已?知道Oracle?排名的?果只“保留”2?据，?就是keep的作用。告?Oracle只保留符合keep?件的?。dense_rank：是告?Oracle排列的策略，first/last?告?最?的?件。三、按?次查?（NTile）找出?排名前1/5的客?。SQLselectregion_id,2customer_id,3ntile(5

19、)over(orderbysum(customer_sales)desc)til4fromuser_order5groupbyregion_id,customer_id;REGION_IDCUSTOMER_IDTILE-10311925110261661818252292336937113534684816467510295515Ntil函?各?在?集中的排名?算比例，所有的?分成5?等?，假如只需要前1/5的?只需要截取TILE的值?1的?就可以了。假如需要排名前25%的?(也就是1/4)那么我?只需要?置ntile(4)就可以了。4.窗口函?需要?遍?集的每一?的同?行?。也即是?：?不止

20、?生一次，而是?生多次。?不至?生在?集形成后，而是?生在?集形成的?程中。一、窗口函?示例全?：需求：列出每月的?以及全年的?rows between . preceding and . following。SQLselectmonth,2sum(tot_sales)month_sales,3sum(sum(tot_sales)over(orderbymonth4rowsbetweenunboundedprecedingandunboundedfollowing)total_sales5fromorders6groupbymonth;MONTHMONTH_SALESTOTAL_SALES-161069763077662