vertica分析函数lag/lead--获取当前数据的前/后几条数据

案例：每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people)。 查找至少连续三行记录中的人流量不少于100的日期和相应人流量
建表：

create table public.stadium(
id int,
visit_date varchar(100),
people int
);

插入数据：

insert into public.stadium
      select 1,'2017-01-01',10   
union select 2,'2017-01-02',109  
union select 3,'2017-01-03',150  
union select 4,'2017-01-04',99   
union select 5,'2017-01-05',145  
union select 6,'2017-01-06',1455 
union select 7,'2017-01-07',199  
union select 8,'2017-01-08',188
;

测试数据格式：

解法一：
对于连续几条数据的问题，一般情况都是可以通过多次自关联实现

select 
distinct a.* 
from public.stadium a,
     public.stadium b,
     public.stadium c
where a.people>=100 and 
      b.people>=100 and 
      c.people>=100 and 
     (
     (a.id = b.id-1 and b.id = c.id -1) or
     (a.id = b.id-1 and a.id = c.id +1) or
     (a.id = b.id+1 and b.id = c.id +1)
    ) 
order by a.id

结果 :

注意：1.这里需要做distinct 因为如果有出现超过连续3天人流量大于100的情况，where条件中的or会使结果出现重复的天数
2.如果正式环境的数据量很大，多表自关联会导致查询结果慢甚至直接OOM

解法二：
vertica的分析函数lag/lead : 一般情况下可以代替表的自关联
lag(field,num,defaultvalue) over(partition by A order by B) : 查询当前数据的前几条数据 field取哪个字段的值 num取前面第几行的数据 defaultvalue取不到数据时的默认值 over()取数据的区间
lead(field,num,defaultvalue) over(partition by A order by B) : 查询当前数据的后几条数据 field取哪个字段的值 num取前面第几行的数据 defaultvalue取不到数据时的默认值 over()取数据的区间

select
id,
visit_date,
p3 as people
from
    (select 
    id,
    visit_date,
    lag(people,2,null) over(order by id) as p1,
    lag(people,1,null) over(order by id) as p2,
    people as p3  ,
    lead(people,1,null) over(order by id) as p4,
    lead(people,2,null) over(order by id) as p5
    from public.stadium
     )tmp1
where (p1>=100 and p2>=100 and p3>=100)
or    (p2>=100 and p3>=100 and p4>=100)
or    (p3>=100 and p4>=100 and p5>=100)
order by id

结果：

解法三：
通过vertica的窗口函数row_number() over(partition by )来给数据打标签
由于id是自增且每天一条数据，那么连续出现人流量大于100的id和他对应的标签的差值应该是固定的

select 
id,
visit_date,
people
from 
		    (select 
		    id,
		    visit_date ,
		    people   ,
		    count(1) over( partition by diff ) as cnt --人流量连续大于100的天数
		    from
			        (select
			        id,
			        visit_date ,
			        people   ,
			        (row_number() over(order by id)) - id as diff   --人流量大于100的标签和id的差值
			        from public.stadium
			        where people  > 100
                   )tmp1
		    )tmp2
where cnt >= 3
ORDER BY id

结果：