[转帖]分析函数_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 5922 | 回复: 0   主题: [转帖]分析函数        下一篇 
wangxilu
注册用户
等级:少校
经验:850
发帖:73
精华:3
注册:2013-4-10
状态:离线
发送短消息息给wangxilu 加好友    发送短消息息给wangxilu 发消息
发表于: IP:您无权察看 2013-4-23 13:44:43 | [全部帖] [楼主帖] 楼主

分析函数语法:

function_name(<argument>,<argument>……)
over
(<partition-clause><order-by-clause><windowing clause>)


例:

sum(sal)
over
(partition by deptno order by ename) new_alias


说明:

sum就是函数名

(sal)是分析函数的参数,每个函数有0~3个参数,参数可以是表达式:sum(sal+comm)

over是一个关键字,用于标识分析函数,否则查询分析器不能区别sum()是聚合函数还是分析函数。

1.function子句

Oracle提供了26个分析函数,按功能分5类:

等级(ranking)函数:用于寻找前N种查询

开窗(windowing)函数:用于计算不同的累积,如sum,count,avg,min,max等,作用于数据的一个窗口上。

制表(reporting)函数:与开窗函数同名,作用于一个分区或一组上的所有列

开窗函数和制表函数的关键不同之处在于over语句上缺少一个order by子句。

lag,lead函数:这类函数允许在结果集中向前或向后检索值,为了避免数据的自连接,它们是非常有用的。

var_pop,var_samp,stdev_pope及线性的衰减函数:计算任何未排序分区的统计值

2.partition子句

按照表达式分区(就是分组),如果省略了分区子句,则全部的结果集被看做是一个单一的组。

3.order by子句

分析函数中order by的存在将添加一个默认的开窗子句,这意味着计算中所使用的行的集合是当前分区中当前行和前面所有行,没有order by时,默认的窗口是全部的分区,在order by子句后可以添加nulls last,如:order by comm desc null last表示排序时忽略comm列为空的行。

4.windowing子句

用于定义分析函数将在其上操作的行的集合

windowing子句给出了一个定义变化或固定的数据窗口的方法,分析函数将对这些数据进行操作

默认的窗口是一个固定的窗口,仅仅在一组的第一行开始,一直继续到当前行,要使用窗口,必须使用order by子句

根据2个标准可以建立窗口:数据值的范围(ranges)或与当前行的行偏移量

1)Range窗口:

Range 5 preceding将产生一个滑动窗口,它在组中拥有当前行的前5行集合。

Range窗口仅对numbers和dates起作用,因为不可能从varchar2中增加或减去N个单元

另外的限制是order by中只能有一列,因而范围实际上是一维的,不能在N维空间中

例:avg(t.sal) over(order by t.hiredate asc range 100 preceding)   --统计前100天的平均工资

2)Row窗口

利用Row分区,就没有Range分区那样的限制了,数据可以是任何类型,且order by可以包括很多列

3)Specifying窗口

unbounded preceding:这个窗口从当前分区的每一行开始,并结束于正在处理的当前行

current row:该窗口从当前行开始(并结束)

numeric expression preceding:对该窗口从当前行之前的数据表达式的行开始,对range来说,从行序值小于数字表达式的当前行的值开始

numeric expression following:对该窗口从当前行之后的数据表达式的行终止(或开始),对range来说,从行序值大于数字表达式的当前行的方位开始(或终止)

range between 100 preceding and 100 following


等级函数:

例:按区域查找上一年度订单总额占区域订单总额20%以上的客户

select all_sales.*,
100 * round(cust_sales / region_sales, 2) || '%' Percent
from (select o.cust_nbr customer,
o.region_id region,
sum(o.tot_sales) cust_sales,
sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
from orders_tmp o
where o.year = 2001
group by o.region_id, o.cust_nbr) all_sales
where all_sales.cust_sales > all_sales.region_sales * 0.2;
①ROW_NUMBER:


Row_number函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。

②DENSE_RANK:

Dense_rank函数返回一个唯一的值,除非当碰到相同数据时,此时所有相同数据的排名都是一样的。

③RANK:

Rank函数返回一个唯一的值,除非遇到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

REGION_ID CUSTOMER_ID CUSTOMER_SALES
---------- ----------- --------------
5           1           151162
10          29         903383
6           7           971585
10          28         986964
9           21         1020541
9           22         1036146
8           16         1068467
6           8           1141638
5           3           1161286
5           5           1169926
8           19         1174421
7           12         1182275
7           11         1190421
6           10         1196748
6           9           1208959
10          30         1216858
5           2           1224992
9           24         1224992
9           23         1224992
8           18         1253840
7           15         1255591
7           13         1310434
10          27         1322747
8           20         1413722
6           6           1788836
10          26         1808949
5           4           1878275
7           14         1929774
8           17         1944281
9           25         2232703
SQL> select region_id,
customer_id,
sum(customer_sales) total,
rank() over(order by sum(customer_sales) desc) rank,
dense_rank() over(order by sum(customer_sales) desc) dense_rank,
row_number() over(order by sum(customer_sales) desc) row_number
from user_order
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
.......
SQL> select region_id,
customer_id,
sum(customer_sales) total,
rank() over(partition by region_id order by sum(customer_sales) desc) rank,
dense_rank() over(partition by region_id order by sum(customer_sales) desc) dense_rank,
row_number() over(partition by region_id order by sum(customer_sales) desc) row_number
from user_order
group by region_id, customer_id;
REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER
---------- ----------- ---------- ---------- ---------- ----------
......
5           4    1878275          1          1          1
5           2    1224992          2          2          2
5           5    1169926          3          3          3
6           6    1788836          1          1          1
6           9    1208959          2          2          2
6         10    1196748          3          3          3
......
SQL> select region_id,
customer_id,
sum(customer_sales) cust_sales,
sum(sum(customer_sales)) over(partition by region_id) ran_total,
rank() over(partition by region_id order by sum(customer_sales) desc) rank
from user_order
group by region_id, customer_id;
REGION_ID CUSTOMER_ID CUST_SALES  RAN_TOTAL       RANK
---------- ----------- ---------- ---------- ----------
10          31                      6238901          1
10          26    1808949    6238901          2
10          27    1322747    6238901          3
10          30    1216858    6238901          4
10          28     986964     6238901          5
10          29     903383     6238901          6


这里有一条记录的CUST_TOTAL字段值为NULL,居然排在第一名,显然是不符合情理。

SQL> select region_id,
customer_id,
sum(customer_sales) cust_total,
sum(sum(customer_sales)) over(partition by region_id) reg_total,
rank() over(partition by region_id order by sum(customer_sales) desc NULLS LAST) rank
from user_order
group by region_id, customer_id;
REGION_ID CUSTOMER_ID CUST_TOTAL  REG_TOTAL       RANK
---------- ----------- ---------- ---------- ----------
10          26    1808949    6238901          1
10          27    1322747    6238901          2
10          30    1216858    6238901          3
10          28     986964     6238901          4
10          29     903383     6238901          5
10          31                      6238901          6


例:找出订单总额最多、最少的客户

SQL> select min(customer_id) keep (dense_rank first order by sum(customer_sales) desc) first,
min(customer_id) keep (dense_rank last  order by sum(customer_sales) desc) last
from user_order
group by customer_id;
FIRST       LAST
---------- ----------
31          1


min函数的作用是用于当存在多个First/Last情况下保证返回唯一的记录。

keep告诉Oracle只保留符合条件的记录。

dense_rank告诉Oracle排列的策略。

first/last告诉Oracle最终筛选的条件。

例:找出订单总额排名前1/5的客户

SQL> select region_id,
customer_id,
ntile(5) over(order by sum(customer_sales) desc) til
from user_order
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的记录就可以了。

窗口函数:

例:列出每月的订单总额以及截至到当前月的订单总额

SQL> select month,
sum(tot_sales) month_sales,
sum(sum(tot_sales)) over(order by month rows between unbounded preceding and current row) current_total_sales,
sum(sum(tot_sales)) over(order by month rows between unbounded preceding and unbounded following) total_sales
from orders
group by month;
MONTH MONTH_SALES CURRENT_TOTAL_SALES TOTAL_SALES
---------- ----------- ------------------- -----------
1      610697               610697     6307766
2      428676             1039373     6307766
3      637031             1676404     6307766
4      541146             2217550     6307766
5      592935             2810485     6307766
6      501485             3311970     6307766
7      606914             3918884     6307766
8      460520             4379404     6307766
9      392898             4772302     6307766
10     510117             5282419     6307766
11     532889             5815308     6307766
12     492458             6307766     6307766


例:当天销售额和五天内的平均销售额

SQL> select trunc(order_dt) day,
sum(sale_price) daily_sales,
avg(sum(sale_price)) over (order by trunc(order_dt) range between interval '2' day preceding and interval '2' day following) five_day_avg
from cust_order
where sale_price is not null
and order_dt between to_date('01-jul-2001','dd-mon-yyyy')
and to_date('31-jul-2001','dd-mon-yyyy')


例:显示当前月、上一个月、后一个月的销售情况,以及每3个月的销售平均值

SQL> select month,
first_value(sum(tot_sales)) over (order by month rows between 1 preceding and 1 following) prev_month,
sum(tot_sales)  monthly_sales,
last_value (sum(tot_sales)) over (order by month rows between 1 preceding and 1 following) next_month,
avg(sum(tot_sales)) over (order by month rows between 1 preceding and 1 following) rolling_avg
from orders
where year = 2001
and region_id = 6
group by month
order by month;


例:每次显示当月的销售额和上个月的销售额

SQL> select month,
sum(tot_sales) monthly_sales,
lag(sum(tot_sales), 1) over (order by month) prev_month_sales
from orders
where year = 2001
and region_id = 6
group by month
order by month;


lag(sum(tot_sales),1)中的1表示以1月为基准。

制表函数:

SQL> select month,
sum(tot_sales) month_sales,
sum(sum(tot_sales)) over(order by month rows between unbounded preceding and unbounded following) win_sales,
sum(sum(tot_sales)) over() rpt_sales
from orders
group by month;
MONTH MONTH_SALES WINDOW_SALES REPORT_SALES
---------- ----------- ------------ ------------
1      610697      6307766      6307766
2      428676      6307766      6307766
3      637031      6307766      6307766
4      541146      6307766      6307766
5      592935      6307766      6307766
6      501485      6307766      6307766
7      606914      6307766      6307766
8      460520      6307766      6307766
9      392898      6307766      6307766
10     510117      6307766      6307766
11     532889      6307766      6307766
12     492458      6307766      6307766


over函数的空括号表示该记录集的所有记录都应该被列入统计的范围,如果使用了partition by则先分区,再依次统计各个分区。

SQL> select region_id,
salesperson_id,
sum(tot_sales) sp_sales,
round(ratio_to_report(sum(tot_sales)) over (partition by region_id), 2) sp_ratio
from orders
where year = 2001
group by region_id, salesperson_id
order by region_id, salesperson_id;


Oracle提供的Ratio_to_report函数允许我们计算每条记录在其对应记录集或其子集中所占的比例。




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论