oracle connect by 和 分析函数总结Word下载.docx
- 文档编号:7216292
- 上传时间:2023-05-08
- 格式:DOCX
- 页数:17
- 大小:20.57KB
oracle connect by 和 分析函数总结Word下载.docx
《oracle connect by 和 分析函数总结Word下载.docx》由会员分享,可在线阅读,更多相关《oracle connect by 和 分析函数总结Word下载.docx(17页珍藏版)》请在冰点文库上搜索。
例子
原始数据num1为父num2为子
看下面的图
返回当前节点的最顶端节点。
判断是否为叶子节点,是1,不是0。
伪列表示节点深度。
函数显示详细路径,并用“/”分隔。
二、列转行
这个函数使用之前必须先建立一个树,否则无用
sys_connect_by_path(字段名,2个字段之间的连接符号)
with?
tmp_aas(
select'
1'
a,'
0'
pfromdual
unionall?
2'
'
fromdual
3'
4'
5'
6'
)
--子全部显示根-->
子level代表级别
selecta,p,sys_connect_by_path(a,'
--'
),levelfromtmp_a
startwitha=1
connectbyp=priora
--2和2的所有下级去掉根-->
子(开始就要去掉)
)fromtmp_a
startwithp=1anda<
>
'
--2的所有下级都去掉根-->
子(connect时去掉)
connectbyp=prioraandp<
--去掉2的分枝
--2的下一级去掉根-->
子(where中去掉)
wherep<
--显示最长的根-->
子
tmp_tabas(
中国'
s,nullbfromdual?
广东'
s,'
bfromdual?
湖南'
衡阳'
广州'
衡东'
selectmax(sys_connect_by_path(s,'
/'
))fromtmp_tab
startwiths='
connectbypriors=b
2.分析函数总结
一、统计方面:
Sum()Over([Partitionby][Orderby])
Sum()Over([Partitionby][Orderby]?
RowsBetweenPrecedingAndFollowing)
Sum()Over([Partitionby][Orderby]
RowsBetweenPrecedingAndCurrentRow)
RangeBetweenInterval'
Day'
Preceding
AndInterval'
Following)
二、排列方面:
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/Last[Partitionby][Orderby])
四、首记录/末记录查找方面:
First_value/Last_value(Sum()Over([Patitionby][Orderby]
RowsBetweenPrecedingAndFollowing))
五、相邻记录之间比较方面:
Lag(Sum(),1)Over([Patitionby][Orderby])
1.分析函数(OVER)
一.分析函数语法:
FUNCTION_NAME(<
argument>
<
...)
OVER
(<
Partition-Clause>
Order-by-Clause>
WindowingClause>
例:
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子句)
LEAD函数:
可在结果集中向前或向后检索值,为了避免数据的自连接,它们是非常用用的.
VAR_SAMP,STDEV_POPE及线性的衰减函数:
计算任何未排序分区的统计值
2)PARTITION子句
分组
3)ORDERBY子句
分析函数中ORDERBY的存在将添加一个默认的开窗子句,这意味着计算中所使用的行的集合是当前分区中当前行和前面所有行,没有ORDERBY时,默认的窗口是全部的分区。
在Orderby子句后可以添加nullslast,如:
orderbycommdescnullslast表示排序时忽略comm列为空的行.
二、分析函数简单实例:
按区域查找2001年度订单总额占区域订单总额20%以上的客户
【1】测试数据:
SQL>
select*fromorders_tmp;
CUST_NBRREGION_IDSALESPERSON_IDYEARMONTHTOT_ORDERSTOT_SALES
--------------------------------------------------------------------------
1171120017212204
454200110237802
7672001233750
106820011221691
106720012342624
【2】测试语句:
selectcustomer,
2region,
3sumcust_sales,
4sum(sum)over(partitionbyregion_sales
5fromorders_tmpo
6where=2001
7groupby,;
CUSTOMERREGIONCUST_SALESREGION_SALES
------------------------------------------
453780237802
76375068065
1066431568065
1171220412204
3、分析函数OVER解析:
4、
5、请注意上面的绿色高亮部分,groupby的意图很明显:
将数据按区域ID,客户进行分组。
假如我们只需要统计每个区域每个客户的订单总额,那么我们只需要,就够了。
但我们还想在每一行显示该客户所在区域的订单总额,这一点和前面的不同:
需要在前面分组的基础上按区域累加。
groupby和sum是无法做到这一点的(因为聚集操作的级别不一样,前者是对一个客户,后者是对一批客户)。
这就是over函数的作用了!
它的作用是告诉SQL引擎:
按区域对数据进行分区,然后累积每个区域每个客户的订单总额(sum(sum))。
最终语句
selectall_sales.*,
2100*round(cust_sales/region_sales,2)||'
%'
Percent
3from(selectcustomer,
4region,
5sumcust_sales,
6sum(sum)over(partitionbyregion_sales
7fromorders_tmpo
8where=2001
9groupby,all_sales
10where>
*;
CUSTOMERREGIONCUST_SALESREGION_SALESPERCENT
----------------------------------------------------------------------------------
453780237802100%
106643156806594%
117122*********0%
2.分析函数2(Rank,Dense_rank,row_number)
遇到相同的数据时用Rank,Dense_rank,row_number排名策略,他们之间的区别直接看例子
selectregion_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_number
5fromuser_order
6groupbyregion_id,customer_id;
REGION_IDCUSTOMER_IDTOTALRANKDENSE_RANKROW_NUMBER
-------------------------------------------------------------
8181253840111111
521224992121212
9231224992121213
9241224992121214
10301216858151315
3.分析函数3(Top/BottomN、First/Last、NTile)
一、Top/BottomN查询:
找出所有订单总额排名前3的大客户:
select*
from(selectregion_id,
customer_id,
sum(customer_sales)cust_total,
rank()over(orderbysum(customer_sales)descNULLSLAST)rank
fromuser_order
groupbyregion_id,customer_id)
whererank<
=3;
REGION_IDCUSTOMER_IDCUST_TOTALRANK
-----------------------------------------
92522327031
81719442812
71419297743
2、First/Last排名查询:
找出订单总额最多、最少的客户。
selectmin(customer_id)
2keep(dense_rankfirstorderbysum(customer_sales)desc)first,
3min(customer_id)
4keep(dense_ranklastorderbysum(customer_sales)desc)last
6groupbycustomer_id;
FIRSTLAST
--------------------
311
Min:
函数的作用是用于当存在多个First/Last情况下保证返回唯一的记录。
(去掉会出错)
Keep:
从上面的结果我们已经知道Oracle对排名的结果只“保留”2条数据,这就是keep的作用。
告诉Oracle只保留符合keep条件的记录。
dense_rank:
是告诉Oracle排列的策略,first/last则告诉最终筛选的条件。
三、按层次查询(NTile)
找出订单总额排名前1/5的客户。
selectregion_id,
2customer_id,
3ntile(5)over(orderbysum(customer_sales)desc)til
4fromuser_order
5groupbyregion_id,customer_id;
REGION_IDCUSTOMER_IDTILE
-------------------------------
10311
9251
10261
661?
8182
522
9233
693
7113
534
684
8164
675
10295
515
Ntil函数为各个记录在记录集中的排名计算比例,所有的记录分成5个等级,假如只需要前1/5的记录则只需要截取TILE的值为1的记录就可以了。
假如需要排名前25%的记录(也就是1/4)那么我们只需要设置ntile(4)就可以了。
4.窗口函数
需要随着遍历记录集的每一条记录的同时进行统计。
也即是说:
统计不止发生一次,而是发生多次。
统计不至发生在记录集形成后,而是发生在记录集形成的过程中。
一、窗口函数示例-全统计:
需求:
列出每月的订单总额以及全年的订单总额
rowsbetween...precedingand...following。
selectmonth,
2sum(tot_sales)month_sales,
3sum(sum(tot_sales))over(orderbymonth
4rowsbetweenunboundedprecedingandunboundedfollowing)total_sales
5fromorders
6groupbymonth;
MONTHMONTH_SALESTOTAL_SALES
--------------------------------
16106976307766
24286766307766
36370316307766
45411466307766
55929356307766
65014856307766...(后面的剩了)
unboundedprecedingandunbounedfollowing:
针对当前所有记录的前一条、后一条记录,也就是表中的所有记录。
Preceding前面如果是1,不是从第1条记录开始的意思,而是指当前记录的前一条记录。
preceding前面的修饰符是告诉窗口函数执行时参考的记录数,如同unbounded就是告诉oracle不管当前记录是第几条,只要前面有多少条记录,都列入统计的范围。
二、窗口函数进阶-滚动统计(累积/均值):
列出每月的订单总额以及截至到当前月的订单总额。
也就是说3月份要显示当月的订单总额和1,2,3月份订单总额的和,依此类推。
需要在统计第N月的订单总额时,还要再统计这N个月来的订单总额之和。
curreentrow
4rowsbetweenunboundedprecedingandcurrentrow)current_total_sales
MONTHMONTH_SALESCURRENT_TOTAL_SALES
----------------------------------------
1610697610697
24286761039373
36370311676404
三、窗口函数进阶-根据时间范围统计
统计了当天销售额和五天内的评价销售额:
selecttrunc(order_dt)day,
sum(sale_price)daily_sales,
avg(sum(sale_price))over(orderbytrunc(order_dt)
rangebetweeninterval'
daypreceding?
andinterval'
dayfollowing)five_day_avg
fromcust_order
wheresale_priceisnotnull?
andorder_dtbetweento_date('
01-jul-2001'
dd-mon-yyyy'
andto_date('
31-jul-2001'
为了对指定范围进行统计,Oracle使用关键字range、interval来指定一个范围。
上面的例子告诉Oracle查找当前日期的前2天,后2天范围内的记录,并统计其销售平均值。
四、窗口函数进阶-first_value/last_value:
first_value、last_value,用于在窗口记录集中查找第一条记录和最后一条记录。
需要显示当前月、上一个月、后一个月的销售情况,以及每3个月的销售平均值
first_value(sum(tot_sales))over(orderbymonth?
rowsbetween1precedingand1following)prev_month,
sum(tot_sales)monthly_sales,
last_value(sum(tot_sales))over(orderbymonth?
rowsbetween1precedingand1following)next_month,
avg(sum(tot_sales))over(orderbymonth?
rowsbetween1precedingand1following)rolling_avg
fromorders
whereyear=2001?
andregion_id=6
groupbymonth
orderbymonth;
rowsbetween1precedingand1following:
当前记录的前一条、后一条范围内查找并统计,而first_value和last_value在这3条记录中至分别找出第一条、第三条记录
五、窗口函数进阶-比较相邻记录:
我们想每次显示当月的销售额和上个月的销售额,
leg函数类似于preceding和following子句,它能够通过和当前记录的相对位置而被应用,在比较同一个相邻的记录集内两条相邻记录的时候特别有用。
selectmonth,?
lag(sum(tot_sales),1)over(orderbymonth)prev_month_sales
whereyear=2001
5.报表函数
一、报表函数简介
sum(sum(tot_sales))over(orderbymonthrowsbetweenunboundedprecedingandunboundedfollowing)
来统计全年的订单总额,这个函数会在记录集形成的过程中,每检索一条记录就执行一次,它总共执行了12次。
这是非常费时的。
实际上我们还有更简便的方法:
4rowsbetweenunboundedprecedingandunboundedfollowing)win_sales,
5sum(sum(tot_sales))over()rpt_sales
6fromorders
7groupbymonth;
二、RATIO_TO_REPORT函数:
列出上一年度每个月的销售总额、年底销售额以及每个月的销售额占全年总销售额的比例:
方法①:
100*round(cust_sales/region_sales,2)||'
from(selectcustomer,
region,
sumcust_sales,
sum(sum)over(partitionbyregion_sales
fromorders_tmpo
where=2001
groupby,all_sales
where>
方法②:
selectregion_id,salesperson_id,?
sum(tot_sales)sp_sales,
round(sum(tot_sales)/sum(sum(tot_sales))?
over(partitionbyregion_id),2)percent_of_region
groupbyregion_id,salesperson_id
orderbyregion_i
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle connect by 分析函数总结 分析 函数 总结