oracle connect by 和 分析函数总结.docx
《oracle connect by 和 分析函数总结.docx》由会员分享,可在线阅读,更多相关《oracle connect by 和 分析函数总结.docx(12页珍藏版)》请在冰豆网上搜索。
oracleconnectby和分析函数总结
1.connectby用法总结
一、树查询(递归查询)
1.作用
对于oracle进展简单树查询(递归查询)
列转行
2.根本语法
select...from
where:
过滤条件,用于对返回的所有记录进展过滤。
startwith:
查询结果重起始根结点的限定条件。
connectby;:
连接条件
1)例子:
selectnum1,num2
fromtable
startwithnum2=1008
connectby num2=priornum1 ;
2)解释:
startwith:
用来标识哪个节点作为查找树型构造的根节点。
假设该子句被省略,那么表示所有满足查询条件的行作为根节点。
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_aas(
select'1'a,'0'pfromdual
unionall
select'2','1'fromdual
unionall
select'3','1'fromdual
unionall
select'4','3'fromdual
unionall
select'5','2'fromdual
unionall
select'6','5'fromdual
)
--子全部显示根-->子 level代表级别selecta,p,sys_connect_by_path(a,'--'),levelfromtmp_a
startwitha=1
connectbyp=priora
--2和2的所有下级去掉根-->子(开场就要去掉)
selecta,p,sys_connect_by_path(a,'--')fromtmp_a
startwithp=1anda<>'2'
connectbyp=priora
--2的所有下级都去掉根-->子(connect时去掉)
selecta,p,sys_connect_by_path(a,'--')fromtmp_a
startwitha=1
connectbyp=prioraandp<>'2'--去掉2的分枝
--2的下一级去掉根-->子(where中去掉)
selecta,p,sys_connect_by_path(a,'--')fromtmp_a
wherep<>'2'
startwitha=1
connectbyp=priora
--显示最长的根-->子with
tmp_tabas(
select'中国's,nullbfromdual
unionall
select''s,'中国'bfromdual
unionall
select''s,'中国'bfromdual
unionall
select''s,''bfromdual
unionall
select''s,''bfromdual
unionall
select'衡东's,''bfromdual
)
selectmax(sys_connect_by_path(s,'/'))fromtmp_tab
startwiths=''
connectbypriors=b
2.分析函数总结
一、统计方面:
Sum() Over ([Partition by ] [Order by ])
Sum() Over ([Partition by ] [Order by ]
Rows Between Preceding And Following)
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(partitionbydeptnoorderbyename)new_alias
sum:
函数名
(sal):
参数0~3个参数可以是表达式
Over:
关键字
partitionby:
〔可选〕分区
orderby:
〔可选〕LAG和LEAD需,AVG不需要,如果使用排序的开窗函数时,必须加
1)FUNCTION子句26个分析函数,按功能分5类分析函数分类1.等级(ranking)函数:
用于寻找前N种查询
2.开窗(windowing)函数:
用于计算不同的累计,如SUM,COUNT,AVG,MIN,MAX等,作用于数据的一个窗口上
3.制表(reporting)函数:
与开窗函数同名,作用于一个分区或一组上的所有列〔制表与开窗的区别:
制表的OVER语句上少一个ORDERBY子句〕4.LAG,LEAD函数:
可在结果集中向前或向后检索值,为了防止数据的自连接,它们是非常用用的.
5.VAR_POP,VAR_SAMP,STDEV_POPE及线性的衰减函数:
计算任何未排序分区的统计值
2)PARTITION子句分组
3)ORDERBY子句分析函数中ORDER BY的存在将添加一个默认的开窗子句,这意味着计算中所使用的行的集合是当前分区中当前行和前面所有行,没有ORDERBY时,默认的窗口是全部的分区。
在Orderby子句后可以添加nullslast,如:
orderbymdescnullslast表示排序时忽略m列为空的行.
二、分析函数简单实例:
按区域查找2001年度订单总额占区域订单总额20%以上的客户
【1】测试数据:
SQL> select * from orders_tmp;
CUST_NBR REGION_ID SALESPERSON_ID YEAR MONTH TOT_ORDERS TOT_SALES
---------- ---------- -------------- ---------- ---------- ---------- ----------
11 7 11 2001 7 2 12204
4 5 4 2001 10 2 37802
7 6 7 2001 2 3 3750
10 6 8 2001 1 2 21691
10 6 7 2001 2 3 42624
【2】测试语句:
SQL> select o.cust_nbr customer,
2 o.region_id region,
3 sum(o.tot_sales) cust_sales,
4 sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
5 from orders_tmp o
6 where o.year = 2001
7 group by o.region_id, o.cust_nbr;
CUSTOMER REGION CUST_SALES REGION_SALES
---------- ---------- ---------- ------------
4 5 37802 37802
7 6 3750 68065
10 6 64315 68065
11 7 12204 12204
三、分析函数OVER解析:
请注意上面的绿色高亮局部,groupby的意图很明显:
将数据按区域ID,客户进展分组。
假设我们只需要统计每个区域每个客户的订单总额,那么我们只需要groupbyo.region_id,o.cust_nbr就够了。
但我们还想在每一行显示该客户所在区域的订单总额,这一点和前面的不同:
需要在前面分组的根底上按区域累加。
groupby和sum是无法做到这一点的(因为聚集操作的级别不一样,前者是对一个客户,后者是对一批客户)。
这就是over函数的作用了!
它的作用是告诉SQL引擎:
按区域对数据进展分区,然后累积每个区域每个客户的订单总额(sum(sum(o.tot_sales)))。
最终语句
SQL> select all_sales.*,
2 100 * round(cust_sales / region_sales, 2) || '%' Percent 3 from (select o.cust_nbr customer,
4 o.region_id region,
5 sum(o.tot_sales) cust_sales,
6 sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
7 from orders_tmp o
8 where o.year = 2001
9 group by o.region_id, o.cust_nbr) all_sales
10 where all_sales.cust_sales > all_sales.region_sales * 0.2;
CUSTOMER REGION CUST_SALES REGION_SALES PERCENT---------- ---------- ---------- ------------ ----------------------------------------
4 5 37802 37802 100%
10 6 64315 68065 94%
11 7 12204 12204 100%
2.分析函数2(Rank,Dense_rank,row_number)
遇到一样的数据时用Rank,Dense_rank,row_number排名策略,他们之间的区别直接看例子
SQL> select region_id, customer_id, sum(customer_sales) total,
2 rank() over(order by sum(customer_sales) desc) rank,
3 dense_rank() over(order by sum(customer_sales) desc) dense_rank,
4 row_number() over(order by sum(customer_sales) desc) row_number
5 from user_order
6 group by region_id, customer_id;
REGION_ID CUSTOMER_ID TOTAL RANK DENSE_RANK ROW_NUMBER
---------- ----------- ---------- ---------- ---------- ----------
8 18 1253840 11 11 11
5 2 1224992 12 12 12
9 23 1224992 12 12 13
9 24 1224992 12 12 14
10 30 1216858 15 13 15
3.分析函数3(Top/BottomN、First/Last、NTile)
一、Top/BottomN查询:
找出所有订单总额排名前3的大客户:
SQL> select *
SQL> from (select region_id,
SQL> customer_id,
SQL> sum(customer_sales) cust_total,
SQL> rank() over(order by sum(customer_sales) desc NULLS LAST) rankSQL> from user_order
SQL> group by region_id, customer_id)
SQL> where rank <= 3;
REGION_ID CUSTOMER_ID CUST_TOTAL RANK
---------- ----------- ---------- ----------
9 25 2232703 1
8 17 1944281 2
7 14 1929774 3
二、First/Last排名查询:
找出订单总额最多、最少的客户。
SQL> select min(customer_id)
2 keep (dense_rank first order by sum(customer_sales) desc) first,
3 min(customer_id)
4 keep (dense_rank last order by sum(customer_sales) desc) last
5 from user_order
6 group by customer_id;
FIRST LAST
---------- ----------
31 1
Min:
函数的作用是用于当存在多个First/Last情况下保证返回唯一的记录。
〔去掉会出错〕
Keep:
从上面的结果我们已经知道Oracle对排名的结果只“保存〞2条数据,这就是keep的作用。
告诉Oracle只保存符合keep条件的记录。
dense_rank:
是告诉Oracle排列的策略,first/last那么告诉最终筛选的条件。
三、按层次查询〔NTile〕
找出订单总额排名前1/5的客户。
SQL> select region_id,
2 customer_id,
3 ntile(5) over(order by sum(customer_sales) desc) til
4 from user_order
5 group by region_id, customer_id;
REGION_ID CUSTOMER_ID TILE
---------- ----------- ----------
10 31 1
9 25 1
10 26 1
6 6 1
8 18 2
5 2 2
9 23 3
6 9 3
7 11 3
5 3 4
6 8 4
8 16 4
6 7 5
10 29 5
5 1 5
Ntil函数为各个记录在记录集中的排名计算比例,所有的记录分成5个等级,假设只需要前1/5的记录那么只需要截取TILE的值为1的记录就可以了。
假设需要排名前25%的记录(也就是1/4)那么我们只需要设置ntile(4)就可以了。
4.窗口函数
需要随着遍历记录集的每一条记录的同时进展统计。
也即是说:
统计不止发生一次,而是发生屡次。
统计不至发生在记录集形成后,而是发生在记录集形成的过程中。
一、窗口函数例如-全统计:
需求:
列出每月的订单总额以及全年的订单总额
rowsbetween...precedingand...following。
SQL> select month,
2 sum(tot_sales) month_sales,
3 sum(sum(tot_sales)) over (order by month
4 rows between unbounded preceding and unbounded following) total_sales
5 from orders
6 group by month;
MONTH MONTH_SALES TOTAL_SALES
---------- ----------- -----------
1 610697 6307766
2 428676 6307766
3 637031 6307766
4 541146 6307766