ORACLE分析函数(5)---其他
1.除了使用数字来指定窗口范围，我们还可以使用日期类型，如：
2.lead和lag，返回当前窗口中与当前记录距离为n的记录。lag为向前取记录，lead为向后取记录
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 子句了
