oracleconnectby和分析函数总结.doc

1. connect by 用法总结2一、树查询(递归查询)2二、列转行sys_connect_by_path()42.分析函数总结61.分析函数(OVER)72.分析函数2(Rank, Dense_rank, row_number)93.分析函数3(Top/Bottom N、First/Last、NTile)94.窗口函数115.报表函数14151. 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：用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。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个字段之间的连接符号)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 = prior 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_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)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 RangeBetweenInterval 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/LastPartitionby 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个参数 可以是表达式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函数: 可在结果集中向前或向后检索值,为了避免数据的自连接,它们是非常用用的.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年度订单总额占区域订单总额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.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就够了。但我们还想在每一行显示该客户所在区域的订单总额，这一点和前面的不同：需要在前面分组的基础上按区域累加。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,5sum(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%1171220412204100%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_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_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(customer_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)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.窗口函数需要随着遍历记录集的每一条记录的同时进行统计。也即是说：统计不止发生一次，而是发生多次。统计不至发生在记录集形成后，而是发生在记录集形成的过程中。一、窗口函数示例全统计：需求：列出每月的订单总额以及全年的订单总额rows between . preceding and . following。SQLselectmonth,2sum(tot_sales)month_sales,3sum(sum(tot_sales)over(orderbymonth4rowsbetweenunboundedprecedingandunboundedfollowing)total_sales5fromorders6groupbymonth;MONTHMONTH_SALESTOTAL_SALES-161069763077662428676630776636370316307766454114663077665592935630776665014856307766 .（后面的剩了）unbounded preceding and unbouned following：针对当前所有记录的前一条、后一条记录，也就是表中的所有记录。Preceding前面如果是1，不是从第1条记录开始的意思，而是指当前记录的前一条记录。preceding前面的修饰符是告诉窗口函数执行时参考的记录数，如同unbounded就是告诉oracle不管当前记录是第几条，只要前面有多少条记录，都列入统计的范围。二、窗口函数进阶滚动统计(累积/均值)：需求：列出每月的订单总额以及截至到当前月的订单总额。也就是说3月份要显示当月的订单总额和1,2,3月份订单总额的和，依此类推。需要在统计第N月的订单总额时，还要再统计这N个月来的订单总额之和。curreent rowSQLselectmonth,2sum(tot_sales)month_sales,3sum(sum(tot_sales)over(orderbymonth4rowsbetweenunboundedprecedingandcurrentrow)current_total_sales5fromorders6groupbymonth;MONTHMONTH_SALESCURRENT_TOTAL_SALES-16106976106972428676103937336370311676404三、窗口函数进阶根据时间范围统计统计了当天销售额和五天内的评价销售额：selecttrunc(order_dt)day,sum(sale_price)daily_sales,avg(sum(sale_price)over(orderbytrunc(order_dt)rangebetweeninterval2dayprecedingandinterval2dayfollowing)five_day_avgfromcust_orderwheresale_priceisnotnullandorder_dtbetweento_date(01-jul-2001,dd-mon-yyyy)andto_date(31-jul-2001,dd-mon-yyyy)为了对指定范围进行统计，Oracle使用关键字range、interval来指定一个范围。上面的例子告诉Oracle查找当前日期的前2天，后2天范围内的记录，并统计其销售平均值。四、窗口函数进阶first_value/last_value：first_value、last_value，用于在窗口记录集中查找第一条记录和最后一条记录。需要显示当前月、上一个月、后一个月的销售情况，以及每3个月的销售平均值selectmonth,first_value(sum(tot_sales)over(orderbymonthrowsbetween1precedingand1following)prev_month,sum(tot_sales)monthly_sales,last_value(sum(tot_sales)over(orderbymonthrowsbetween1precedingand1following)next_month,avg(sum(tot_sales)over(orderbymonthrowsbetween1precedingand1following)rolling_avgfromorderswhereyear=2001andregion_id=6groupbymonthorderbymonth;rows between 1 preceding and 1 following:当前记录的前一条、后一条范围内查找并统计，而first_value和last_value在这3条记录中至分别找出第一条、第三条记录五、窗口函数进阶比较相邻记录：我们想每次显示当月的销售额和上个月的销售额，leg函数类似于preceding和following子句，它能够通过和当前记录的相对位置而被应用，在比较同一个相邻的记录集内两条相邻记录的时候特别有用。selectmonth,sum(tot_sales)monthly_sales,lag(sum(tot_sales),1)over(orderbymonth)prev_month_salesfromorderswhereyear=2001andregion_id=6groupbymonthorderbymonth;5.报表函数一、报表函数简介sum(sum(tot_sales)over(orderbymonthrowsbetweenunboundedprecedingandunboundedfollowing)来统计全年的订单总额，这个函数会在记录集形成的过程中，每检索一条记录就执行一次，它总共执行了12次。这是非常费时的。实际上我们还有更简便的方法：SQLselectmonth,2sum(tot_sales)month_sales,3sum(sum(tot_sales)over(orderbymonth4rowsbetweenunboundedprecedingandunboundedfollowing)win_sales,5sum(sum