oracle connect by 和 分析函数总结Word文件下载.docx
《oracle connect by 和 分析函数总结Word文件下载.docx》由会员分享，可在线阅读，更多相关《oracle connect by 和 分析函数总结Word文件下载.docx（15页珍藏版）》请在冰豆网上搜索。
二、列转行sys_connect_by_path（）
这个函数使用之前必须先建立一个树，否则无用
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
（[Partition
by
]
[Order
]）
Rows
Between
Preceding
And
Following）
]
Current
Row）
Range
Interval
'
Day'
Preceding
Following
二、排列方面：
Rank（）
[Nulls
First/Last]）
Dense_rank（）
（[Patition
Row_number（）
（[Partitionby
Ntile（）
三、最大值/最小值查找方面：
Min（）/Max（）
Keep
（Dense_rank
First/Last
[Partition
四、首记录/末记录查找方面：
First_value
/
Last_value（Sum（）
））
五、相邻记录之间比较方面：
Lag（Sum（）,
1）
Over（[Patition
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子句）
4.LAG,LEAD函数:
可在结果集中向前或向后检索值,为了避免数据的自连接,它们是非常用用的.
5.VAR_POP,VAR_SAMP,STDEV_POPE及线性的衰减函数:
计算任何未排序分区的统计值
2）PARTITION子句
分组
3）ORDERBY子句
分析函数中ORDER
BY的存在将添加一个默认的开窗子句,这意味着计算中所使用的行的集合是当前分区中当前行和前面所有行,没有ORDERBY时,默认的窗口是全部的分区。
在Orderby子句后可以添加nullslast,如:
orderbycommdescnullslast表示排序时忽略comm列为空的行.
二、分析函数简单实例：
按区域查找2001年度订单总额占区域订单总额20%以上的客户
【1】测试数据：
SQL>
select
*
from
orders_tmp;
CUST_NBR
REGION_ID
SALESPERSON_ID
YEAR
MONTH
TOT_ORDERS
TOT_SALES
----------
--------------
----------
11
7
2001
2
12204
4
5
10
37802
6
3
3750
8
1
21691
42624
【2】测试语句：
o.cust_nbr
customer,
o.region_id
region,
sum（o.tot_sales）
cust_sales,
sum（sum（o.tot_sales））
over（partition
o.region_id）
region_sales
orders_tmp
o
where
o.year
=
2001
group
o.region_id,
o.cust_nbr;
CUSTOMER
REGION
CUST_SALES
REGION_SALES
------------
37802
3750
68065
64315
12204
三、分析函数OVER解析：
请注意上面的绿色高亮部分，groupby的意图很明显：
将数据按区域ID，客户进行分组。
假如我们只需要统计每个区域每个客户的订单总额，那么我们只需要groupbyo.region_id,o.cust_nbr就够了。
但我们还想在每一行显示该客户所在区域的订单总额，这一点和前面的不同：
需要在前面分组的基础上按区域累加。
groupby和sum是无法做到这一点的（因为聚集操作的级别不一样，前者是对一个客户，后者是对一批客户）。
这就是over函数的作用了！
它的作用是告诉SQL引擎：
按区域对数据进行分区，然后累积每个区域每个客户的订单总额（sum（sum（o.tot_sales）））。
最终语句
all_sales.*,
100
round（cust_sales
region_sales,
2）
||
%'
Percent
（select
9
o.cust_nbr）
all_sales
all_sales.cust_sales
all_sales.region_sales
0.2;
REGION_SALES
PERCENT
------------
----------------------------------------
100%
68065
94%
2.分析函数2（Rank,Dense_rank,row_number）
遇到相同的数据时用Rank,Dense_rank,row_number排名策略，他们之间的区别直接看例子
region_id,
customer_id,
sum（customer_sales）
total,
rank（）
over（order
desc）
rank,
dense_rank（）
dense_rank,
row_number（）
row_number
user_order
customer_id;
CUSTOMER_ID
TOTAL
RANK
DENSE_RANK
ROW_NUMBER
-----------
18
1253840
11
1224992
12
12
23
13
24
14
30
1216858
15
13
3.分析函数3（Top/BottomN、First/Last、NTile）
一、Top/BottomN查询：
找出所有订单总额排名前3的大客户：
*
region_id,
customer_id,
cust_total,
desc
NULLS
LAST）
rank
customer_id）
rank
3;
CUST_TOTAL
RANK
25
2232703
1
17
1944281
2
14
1929774
3
二、First/Last排名查询：
找出订单总额最多、最少的客户。
min（customer_id）
keep
（dense_rank
first
order
first,
last
last
FIRST
LAST
31
Min：
函数的作用是用于当存在多个First/Last情况下保证返回唯一的记录。
（去掉会出错）
Keep：
从上面的结果我们已经知道Oracle对排名的结果只“保留”2条数据，这就是keep的作用。
告诉Oracle只保留符合keep条件的记录。
dense_rank：
是告诉Oracle排列的策略，first/last则告诉最终筛选的条件。
三、按层次查询（NTile）
找出订单总额排名前1/5的客户。
ntile（5）
til
TILE
26
4
16
5
29
Ntil函数为各个记录在记录集中的排名计算比例，所有的记录分成5个等级，假如只需要前1/5的记录则只需要截取TILE的值为1的记录就可以了。
假如需要排名前25%的记录（也就是1/4）那么我们只需要设置ntile（4）就可以了。
4.窗口函数
需要随着遍历记录集的每一条记录的同时进行统计。
也即是说：
统计不止发生一次，而是发生多次。
统计不至发生在记录集形成后，而是发生在记录集形成的过程中。
一、窗口函数示例－全统计：
需求：
列出每月的订单总额以及全年的订单总额
rowsbetween...precedingand...following。
month,
sum（tot_sales）
month_sales,
sum（sum（tot_sales））
over
（order
month
rows
between
unbounded
preceding
and
following）
total_sales
orders
month;
MONTH_SALES
TOTAL_SALES
-----------
610697
6307766
428676
6370
升级会员 