oracle connect by 和 分析函数总结.docx
《oracle connect by 和 分析函数总结.docx》由会员分享，可在线阅读，更多相关《oracle connect by 和 分析函数总结.docx（15页珍藏版）》请在冰豆网上搜索。
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,如:
orderbycommdescnullslast表示排序时忽略comm列为空的行.
二、分析函数简单实例：
按区域查找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
3、分析函数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） rank
SQL> 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
2、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
升级会员 