oracle lead/lag 函数 ignore nulls 功能在11g以下版本的实现

oracle在11g中允许对lead/lag分析函数使用ignore nulls 语法以排除空值。这是一个很实用的语法，然而在低版本中却不能使用。本文将介绍几种可以在较低版本中实现类似功能的sql写法。

建立测试表

create table nayi_test_180515 as
select level ord, decode(level, 5, null, 6, null, level) num
  from dual
connect by level <= 8;

select*from nayi_test_180515;

ORD	NUM
1	1
2	2
3	3
4	4
5
6
7	7
8	8

对测试数据做了简化。其中ORD列视为按指定排序后获得的递增的唯一序列，NUM列视为数据列。

11g中的写法

select t1.ord, t1.num, 
       lag(t1.num) over(order by t1.ord) lag_,
       lag(t1.num) ignore nulls over(order by t1.ord) lag_ignore_,
       lead(t1.num) over(order by t1.ord) lead_,
       lead(t1.num ignore nulls) over(order by t1.ord) lead_ignore_
  from nayi_test_180515 t1
 order by t1.ord;

ORD	NUM	LAG_	LAG_IGNORE_	LEAD_	LEAD_IGNORE_
1	1			2	2
2	2	1	1	3	3
3	3	2	2	4	4
4	4	3	3		7
5		4	4		7
6			4	7	7
7	7		4	8	8
8	8	7	7

model写法

select*from nayi_test_180515 t1
model
dimension by(row_number() over(order by t1.ord) rn)
measures(ord, num, num lag_, num lag_ignore_, num lead_, num lead_ignore_)
rules(
lag_[rn] = num[cv() - 1],
lag_ignore_[rn] = nvl(num[cv() - 1], lag_ignore_[cv() - 1]),
lead_[rn] = num[cv() + 1],
lead_ignore_[rn] order by rn desc = nvl(num[cv() + 1], lead_ignore_[cv() + 1])
)
;

RN	ORD	NUM	LAG_	LAG_IGNORE_	LEAD_	LEAD_IGNORE_
1	1	1			2	2
2	2	2	1	1	3	3
3	3	3	2	2	4	4
4	4	4	3	3		7
5	5		4	4		7
6	6			4	7	7
7	7	7		4	8	8
8	8	8	7	7

使用first_value/last_value+ignore的方法

select t1.*, 
       first_value(t1.num) over(order by t1.ord rows between 1 following and unbounded following) first_lead,
       first_value(t1.num ignore nulls) over(order by t1.ord rows between 1 following and unbounded following) first_ignore,
       last_value(t1.num) over(order by t1.ord rows between unbounded preceding and 1 preceding) last_lag,
       last_value(t1.num ignore nulls) over(order by t1.ord rows between unbounded preceding and 1 preceding) last_ignore
  from nayi_test_180515 t1
 order by ord
;

ORD	NUM	FIRST_LEAD	FIRST_IGNORE	LAST_LAG	LAST_IGNORE
1	1	2	2
2	2	3	3	1	1
3	3	4	4	2	2
4	4		7	3	3
5			7	4	4
6		7	7		4
7	7	8	8		4
8	8			7	7

自连接

select t1.ord, t1.num, t3.num lag_ignore_
  from nayi_test_180515 t1,
       (select t1.ord, max(t2.ord) t2_ord
          from (select * from nayi_test_180515 t1) t1,
               (select * from nayi_test_180515 t2 where t2.num is not null) t2
         where 1 = 1
           and t1.ord > t2.ord
         group by t1.ord) t2,
       nayi_test_180515 t3
 where 1 = 1
   and t1.ord = t2.ord(+)
   and t2.t2_ord = t3.ord(+)
 order by t1.ord;

ORD	NUM	LAG_IGNORE_
1	1
2	2	1
3	3	2
4	4	3
5		4
6		4
7	7	4
8	8	7

因为实现起来过于复杂并且效率非常低，只实现了lag ignore nulls 。

其他

方法肯定不只这些，欢迎交流。