Trafodion之LEAD和LAG窗口函数

Oracle支持很多窗口函数，包括LEAD和LAG窗口函数，用于获取下一条或上一条记录对应字段信息。Trafodion也一样，它也支持很多的窗口函数，同样也支持LEAD和LAG窗口函数，有关LEAD和LAG窗口函数的详细语法，可以参考Apache Trafodion官网SQL手册之http://trafodion.apache.org/docs/sql_reference/index.html#lead_window_function及http://trafodion.apache.org/docs/sql_reference/index.html#lag_window_function。

基本语法与Oracle相同，如下，

LAG(expression, offset, default-value) OVER(PARTITION BY ... ORDER BY ..)
LEAD(expression, offset, default-value) OVER(PARTITION BY ... ORDER BY ..)

其中，expression可以是表中的某一个字段或者一个表达式，offset表示偏移量，表示前面/后面第几条记录对应的值，default-value表示当用LAG或LEAD取不到相应值的时候给定一个默认值。

下面我们创建一个测试表并插入测试数据，

drop table test_lead_lag;
create table test_lead_lag(
  id varchar(10) not null,
  cphm varchar(10) not null,
  create_date date not null, 
  primary key (id)
);
insert into test_lead_lag values ('1000001', 'AB7477', to_date('2015-11-30 10:18:12','YYYY-MM-DD HH24:mi:ss'));
insert into test_lead_lag values ('1000002', 'AB7477', to_date('2015-11-30 10:22:12','YYYY-MM-DD HH24:mi:ss'));
insert into test_lead_lag values ('1000003', 'AB7477', to_date('2015-11-30 10:28:12','YYYY-MM-DD HH24:mi:ss'));
insert into test_lead_lag values ('1000004', 'AB7477', to_date('2015-11-30 10:29:12','YYYY-MM-DD HH24:mi:ss'));
insert into test_lead_lag values ('1000005', 'AB7477', to_date('2015-11-30 10:39:13','YYYY-MM-DD HH24:mi:ss'));
insert into test_lead_lag values ('1000006', 'AB7477', to_date('2015-11-30 10:45:12','YYYY-MM-DD HH24:mi:ss'));
insert into test_lead_lag values ('1000007', 'AB7477', to_date('2015-11-30 10:56:12','YYYY-MM-DD HH24:mi:ss'));
insert into test_lead_lag values ('1000008', 'AB7477', to_date('2015-11-30 10:57:12','YYYY-MM-DD HH24:mi:ss'));
insert into test_lead_lag values ('1000009', 'AB3808', to_date('2015-11-30 11:00:12','YYYY-MM-DD HH24:mi:ss'));
insert into test_lead_lag values ('1000010', 'AB3808', to_date('2015-11-30 11:10:13','YYYY-MM-DD HH24:mi:ss'));
insert into test_lead_lag values ('1000011', 'AB3808', to_date('2015-11-30 11:15:12','YYYY-MM-DD HH24:mi:ss'));
insert into test_lead_lag values ('1000012', 'AB3808', to_date('2015-11-30 11:26:12','YYYY-MM-DD HH24:mi:ss'));
insert into test_lead_lag values ('1000013', 'AB3808', to_date('2015-11-30 11:30:12','YYYY-MM-DD HH24:mi:ss'));

现在我们来测试LEAD和LAG函数，其中LEAD表示后面一条记录对应的值，LAG表示前面一条记录对应的值，

SQL>select t.id id, 
+>       lead(t.id, 1, '') over (order by t.id) next_record_id, 
+>   lag(t.id, 1, '') over (order by t.id) last_record_id,
+>       t.cphm 
+>from test_lead_lag t order by t.id asc;

ID         NEXT_RECORD_ID LAST_RECORD_ID CPHM      
---------- -------------- -------------- ----------
1000001    1000002                       AB7477    
1000002    1000003        1000001        AB7477    
1000003    1000004        1000002        AB7477    
1000004    1000005        1000003        AB7477    
1000005    1000006        1000004        AB7477    
1000006    1000007        1000005        AB7477    
1000007    1000008        1000006        AB7477    
1000008    1000009        1000007        AB7477    
1000009    1000010        1000008        AB3808    
1000010    1000011        1000009        AB3808    
1000011    1000012        1000010        AB3808    
1000012    1000013        1000011        AB3808    
1000013                   1000012        AB3808   

根据以上结果，我们可以发现，由于ID=1000001的上一条记录不存在，因此ID=1000001对应lag函数的值为”，而ID=1000013的下一条记录不存在，因此ID=1000013对应lead函数的值也为”。