over partition by order by rows|range（3）之lead和lag

lead函数用于提取当前行前某行的数据

lag函数用于提取当前行后某行的数据

语法如下：

lead(expression,offset,default) over(partition by ... order by ...)

lag(expression,offset,default) over(partition by ... order by ... )

例如提取前一周和后一周的数据，如下：

select year,week,sale,
lead(sale,1,NULL)
over(--前一周sale
partition by product,country,region
order by year,week
) lead_week_sale,
lag(sale,1,NULL)
over(--后一周sale
partition by product,country,region
order by year,week
) lag_week_sale
from sales_fact a
where a.country='country1' and a.product='product1' and region='region1'
order by product,country,year,week