ORACLE分析函数(5)---其他

商4360

　　1.除了使用数字来指定窗口范围，我们还可以使用日期类型，如：
http://img.blog.csdn.net/20131016104751750?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQveWlkaWFuODE1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast

　　2.lead和lag，返回当前窗口中与当前记录距离为n的记录。lag为向前取记录，lead为向后取记录
　　

SELECT prod_id,
lag(prod_list_price,1) over(order by prod_id) pre_1,
lag(prod_list_price,2) over(order by prod_id) pre_2,
prod_list_price,
lead(prod_list_price,1) over(order by prod_id) lead_1,
lead(prod_list_price,2) over(order by prod_id) lead_2
FROM products;
   PROD_ID      PRE_1      PRE_2 PROD_LIST_PRICE     LEAD_1     LEAD_2
---------- ---------- ---------- --------------- ---------- ----------
13                                899.99     999.99     999.99
14     899.99                     999.99     999.99     299.99
15     999.99     899.99          999.99     299.99    1099.99
16     999.99     999.99          299.99    1099.99    1299.99
17     299.99     999.99         1099.99    1299.99      55.99
18    1099.99     299.99         1299.99      55.99     599.99
19    1299.99    1099.99           55.99     599.99     899.99
20      55.99    1299.99          599.99     899.99      24.99
21     599.99      55.99          899.99      24.99      21.99
22     899.99     599.99           24.99      21.99      45.99
23      24.99     899.99           21.99      45.99     112.99
24      21.99      24.99           45.99     112.99     149.99
25      45.99      21.99          112.99     149.99      44.99
26     112.99      45.99          149.99      44.99     199.99
27     149.99     112.99           44.99     199.99     499.99
28      44.99     149.99          199.99     499.99       9.99
29     199.99      44.99          499.99       9.99       8.99
30     499.99     199.99            9.99       8.99      67.99
31       9.99     499.99            8.99      67.99      44.99
32       8.99       9.99           67.99      44.99      39.99
33      67.99       8.99           44.99      39.99      49.99
34      44.99      67.99           39.99      49.99      44.99
35      39.99      44.99           49.99      44.99      54.99
36      49.99      39.99           44.99      54.99      29.99

3.frist_value last_value返回当前窗口的第一条记录和最后一条记录
SELECT prod_id,
first_value(prod_list_price) over( order by prod_id rows BETWEEN 1 preceding AND 1 following) pre_prod_price,
prod_list_price,
last_value(prod_list_price) over( order by prod_id rows BETWEEN 1 preceding AND 1 following) next_prod_price
FROM products;
   PROD_ID PRE_PROD_PRICE PROD_LIST_PRICE NEXT_PROD_PRICE
---------- -------------- --------------- ---------------
13         899.99          899.99          999.99
14         899.99          999.99          999.99
15         999.99          999.99          299.99
16         999.99          299.99         1099.99
17         299.99         1099.99         1299.99
18        1099.99         1299.99           55.99
19        1299.99           55.99          599.99
20          55.99          599.99          899.99
21         599.99          899.99           24.99
22         899.99           24.99           21.99
23          24.99           21.99           45.99
24          21.99           45.99          112.99
25          45.99          112.99          149.99
26         112.99          149.99           44.99
27         149.99           44.99          199.99
28          44.99          199.99          499.99
29         199.99          499.99            9.99
30         499.99            9.99            8.99
31           9.99            8.99           67.99
32           8.99           67.99           44.99
33          67.99           44.99           39.99
34          44.99           39.99           49.99
35          39.99           49.99           44.99
36          49.99           44.99           54.99
37          44.99           54.99           29.99

4.性能小贴士

如果我们使用sum（col2）over（order by col1 range between unbounded preceding and unbounded following ) ，此时每条记录都会进行统计运算，执行效率不高，而如果使用sum(col2) over()则仅仅统计一次，执行效率就会有所提升。下面的示例显示，效率为1/3　　
　　

SQL> /
COUNT(*)
----------
918843
已用时间:  00: 00: 00.81
SQL> select count(*) from (
select prod_id,sum(amount_sold) over () from sales)
;  2    3  
COUNT(*)
----------
918843
已用时间:  00: 00: 00.27

5.ration_to_report统计当前记录在当前窗口中所占的百分比　　
　　

SQL> select prod_id,prod_list_price,sum(prod_list_price) over() tsum,ratio_to_report(prod_list_price) over() fx from products;
PROD_ID PROD_LIST_PRICE       TSUM         FX
---------- --------------- ---------- ----------
13          899.99   10047.28 .089575487
14          999.99   10047.28  .09952843
15          999.99   10047.28  .09952843
16          299.99   10047.28 .029857832
17         1099.99   10047.28 .109481372
18         1299.99   10047.28 .129387257
19           55.99   10047.28 .005572652
20          599.99   10047.28  .05971666
21          899.99   10047.28 .089575487
22           24.99   10047.28  .00248724
23           21.99   10047.28 .002188652
24           45.99   10047.28 .004577358
25          112.99   10047.28  .01124583
26          149.99   10047.28 .014928418
27           44.99   10047.28 .004477829
28          199.99   10047.28  .01990489
29          499.99   10047.28 .049763717
30            9.99   10047.28 .000994299
31            8.99   10047.28  .00089477
32           67.99   10047.28 .006767006
33           44.99   10047.28 .004477829
34           39.99   10047.28 .003980182
35           49.99   10047.28 .004975476
36           44.99   10047.28 .004477829
37           54.99   10047.28 .005473123
38           29.99   10047.28 .002984887
39           34.99   10047.28 .003482535
40           44.99   10047.28 .004477829
41           44.99   10047.28 .004477829
42           44.99   10047.28 .004477829
43           44.99   10047.28 .004477829
44           44.99   10047.28 .004477829
45           44.99   10047.28 .004477829
46           22.99   10047.28 .002288181
47           28.99   10047.28 .002885358
48           11.99   10047.28 .001193358
113           22.99   10047.28 .002288181
114           18.99   10047.28 .001890064
115            8.99   10047.28  .00089477
116           11.99   10047.28 .001193358
117            8.99   10047.28  .00089477
118            7.99   10047.28  .00079524
119            6.99   10047.28 .000695711
120            6.99   10047.28 .000695711
121           10.99   10047.28 .001093828
122           18.99   10047.28 .001890064
123           49.99   10047.28 .004975476
124           18.99   10047.28 .001890064
125           15.99   10047.28 .001591476
126           28.99   10047.28 .002885358
127           36.99   10047.28 .003681593
128           27.99   10047.28 .002785829
129          192.99   10047.28 .019208184
130           89.99   10047.28 .008956653
131           18.99   10047.28 .001890064
132           24.99   10047.28  .00248724
133           30.99   10047.28 .003084417
134           20.99   10047.28 .002089123
135           49.99   10047.28 .004975476
136           32.99   10047.28 .003283476
137           52.99   10047.28 .005274064
138           69.99   10047.28 .006966064
139           19.99   10047.28 .001989593
140           29.99   10047.28 .002984887
141           29.99   10047.28 .002984887
142           19.99   10047.28 .001989593
143           19.99   10047.28 .001989593
144            7.99   10047.28  .00079524
145           12.99   10047.28 .001292887
146           11.99   10047.28 .001193358
147            7.99   10047.28  .00079524
148           20.99   10047.28 .002089123


　　
　　注意：
　　
　　    部分分析函数在选择列时支持distinct，如果你指定了该参数，则over条件中就只能指定partition子句，而不能再指定orderby子句了