Oracle-分析函数--last_value

1： last_value 和first_value获取第一行和最后一行的数据。

 

其中ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 是在指定了order 后的范围窗口

 

select gather_time,bd_usage_rate,last_value(bd_usage_rate) over (partition by gather_time1 order by bd_usage_rate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as ord
from (
select to_char(gather_time-1/24/4,'yyyy-mm-dd HH24:mi:ss') as gather_time,to_char(gather_time-1/24/4,'yyyy-mm-dd') as gather_time1,
s_isp_name,
d_isp_name,
sum(ip_flow) ip_flow,
sum(t.llink_rbandwidth) as llink_rbandwidth,
sum(t.IP_FLOW) * 8 / sum(t.llink_rbandwidth) / 15 / 60 as bd_usage_rate
from psv_llink_traffic t
where gather_time > to_date('2016-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and gather_time <= to_date('2016-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
group by gather_time, s_isp_name, d_isp_name
)aa

 

2：max() over(parition)

select gather_time,bd_usage_rate,max(bd_usage_rate) over (partition by gather_time1) as ord
from (
select to_char(gather_time-1/24/4,'yyyy-mm-dd HH24:mi:ss') as gather_time,to_char(gather_time-1/24/4,'yyyy-mm-dd') as gather_time1,
s_isp_name,
d_isp_name,
sum(ip_flow) ip_flow,
sum(t.llink_rbandwidth) as llink_rbandwidth,
sum(t.IP_FLOW) * 8 / sum(t.llink_rbandwidth) / 15 / 60 as bd_usage_rate
from psv_llink_traffic t
where gather_time > to_date('2016-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and gather_time <= to_date('2016-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
group by gather_time, s_isp_name, d_isp_name
)aa

3: ratio_to_report   不会和order by 连用

select ratio_to_report(ip_flow1) over(partition by gather_time1)as aa,gather_time1,ip_flow1
from (select gather_time1, sum(ip_flow) as ip_flow1
from
(
select to_char(gather_time-1/24/4,'yyyy-mm-dd') as gather_time1,
s_isp_name,
d_isp_name,
ip_flow
from psv_llink_traffic t
where gather_time > to_date('2016-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and gather_time <= to_date('2016-10-15 00:00:00','yyyy-mm-dd hh24:mi:ss')
)aa
group by gather_time1
)tt;