ORACLE分析函数(五)-其他
ORACLE分析函数(5)---其他
3.frist_value last_value返回当前窗口的第一条记录和最后一条记录
4.性能小贴士
如果我们使用sum（col2）over（order by col1 range between unbounded preceding and unbounded following ) ，此时每条记录都会进行统计运算，执行效率不高，而如果使用sum(col2) over()则仅仅统计一次，执行效率就会有所提升。下面的示例显示，效率为1/3
5.ration_to_report统计当前记录在当前窗口中所占的百分比
1.除了使用数字来指定窗口范围，我们还可以使用日期类型，如：
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子句，而不能再指定order by 子句了