newkid 发表于 2016-3-14 22:38
正如OO所指出的,必须指定ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING窗口。
newkid, 如下的是测试目前用lag+ignore nulls和last_value + ignore nulls的测试结果, lag_new_current_value和 last_value_new_current_value得到的结果是一致的, 是不是你看错了, 呵呵
SQL> with t as
2 (select '2E73F17A8D2648E4AED9E0DCFE454263' eq_guid, to_date('2016/2/29 6:30:00','yyyy-mm-dd hh24:mi:ss') cycle_time,105777 current_value,105772 lately_value from dual union all
3 select '2E73F17A8D2648E4AED9E0DCFE454263' eq_guid,to_date('2016/2/29 6:45:00','yyyy-mm-dd hh24:mi:ss') cycle_time,105781 current_value,105777 lately_value from dual union all
4 select '2E73F17A8D2648E4AED9E0DCFE454263' eq_guid,to_date('2016/2/29 7:00:00','yyyy-mm-dd hh24:mi:ss') cycle_time,105782 current_value,105781 lately_value from dual union all
select '2E73F17A8D2648E4AED9E0DCFE454263' eq_guid,to_date('2016/2/29 7:15:00','yyyy-mm-dd hh24:mi:ss') cycle_time,null current_value,null lately_value from dual union all
5 6 select '2E73F17A8D2648E4AED9E0DCFE454263' eq_guid,to_date('2016/2/29 7:30:00','yyyy-mm-dd hh24:mi:ss') cycle_time,null current_value,null lately_value from dual union all
7 select '2E73F17A8D2648E4AED9E0DCFE454263' eq_guid,to_date('2016/2/29 7:45:00','yyyy-mm-dd hh24:mi:ss') cycle_time,null current_value,null lately_value from dual union all
8 select '2E73F17A8D2648E4AED9E0DCFE454263' eq_guid,to_date('2016/2/29 8:00:00','yyyy-mm-dd hh24:mi:ss') cycle_time,105824 current_value,105824 lately_value from dual union all
9 select '2E73F17A8D2648E4AED9E0DCFE454263' eq_guid,to_date('2016/2/29 8:15:00','yyyy-mm-dd hh24:mi:ss') cycle_time,105832 current_value,105824 lately_value from dual union all
10 select '2E73F17A8D2648E4AED9E0DCFE454263' eq_guid,to_date('2016/2/29 8:30:00','yyyy-mm-dd hh24:mi:ss') cycle_time,105841 current_value,105832 lately_value from dual union all
11 select '2E73F17A8D2648E4AED9E0DCFE454263' eq_guid,to_date('2016/2/29 8:45:00','yyyy-mm-dd hh24:mi:ss') cycle_time,105850 current_value,105841 lately_value from dual)
12 select t.*,
13 nvl(t.current_value,lag(t.current_value ignore nulls) over(order by t.cycle_time)) lag_new_current_value,
14 last_value(t.current_value ignore nulls) over(order by t.cycle_time) last_value_new_current_value
15 from t;
EQ_GUID CYCLE_TIM CURRENT_VALUE LATELY_VALUE LAG_NEW_CURRENT_VALUE LAST_VALUE_NEW_CURRENT_VALUE
-------------------------------- --------- ------------- ------------ --------------------- ----------------------------
2E73F17A8D2648E4AED9E0DCFE454263 29-FEB-16 105777 105772 105777 105777
2E73F17A8D2648E4AED9E0DCFE454263 29-FEB-16 105781 105777 105781 105781
2E73F17A8D2648E4AED9E0DCFE454263 29-FEB-16 105782 105781 105782 105782
2E73F17A8D2648E4AED9E0DCFE454263 29-FEB-16 105782 105782
2E73F17A8D2648E4AED9E0DCFE454263 29-FEB-16 105782 105782
2E73F17A8D2648E4AED9E0DCFE454263 29-FEB-16 105782 105782
2E73F17A8D2648E4AED9E0DCFE454263 29-FEB-16 105824 105824 105824 105824
2E73F17A8D2648E4AED9E0DCFE454263 29-FEB-16 105832 105824 105832 105832
2E73F17A8D2648E4AED9E0DCFE454263 29-FEB-16 105841 105832 105841 105841
2E73F17A8D2648E4AED9E0DCFE454263 29-FEB-16 105850 105841 105850 105850
10 rows selected.