请教分析函数first_value与min,last_value与max
select a.zhh,a.rq,a.zdl,
first_value(zdl)over(partition by a.rq order by zhh ) first_value,
min(zdl)over(partition by a.rq order by zhh ) min,
last_value(zdl)over(partition by a.rq order by zhh ) last_value,
max(zdl)over(partition by a.rq order by zhh ) max
from R_JLDDL a where zdl0
and to_char(rq,'yyyymmdd')='20100819'order by rq,zhh
) t order by rq
结果:
ZHH
RQ
ZDLFIRST_VALUEMINLAST_VALUEMAX
101501648422010/8/19 0.310.310.311983.21983.2
201501648422010/8/19 0.310.310.311983.21983.2
301501648422010/8/19 0.530.310.311983.21983.2
401501648422010/8/19 0.530.310.311983.21983.2
501501648422010/8/19 4.990.310.311983.21983.2
601501648422010/8/19 8.390.310.311983.21983.2
701501648422010/8/19 770.50 0.310.311983.21983.2
801501648422010/8/19 1983.20 0.310.311983.21983.2
904000091192010/8/19 0.040.310.041730.771983.2
1004000091192010/8/190.050.310.041730.771983.2
1104000091192010/8/19576.770.310.041730.771983.2
1204000091192010/8/19576.810.310.041730.771983.2
1304000091192010/8/19756.980.310.041730.771983.2
1404000091192010/8/19757.030.310.041730.771983.2
1504000091192010/8/191315.610.310.041730.771983.2
1604000091192010/8/191730.770.310.041730.771983.2
我并没有对字段zhh限制,为什么first_value与last_value都会在字段rq下,又按照字段zhh来查找最前一条和最后一条记录?
|