窗口分析函数LAST_VALUE，FIRST_VALUE，lag，lead

目录

FIRST_VALUE和LAST_VALUE详解

lag和lead详解  

FIRST_VALUE和LAST_VALUE详解

with abc as 
(
select 1 as id union all
select 2  union all
select 3 union all
select 4
)
select id,
FIRST_VALUE(id) over(order by id ) as firstid,
LAST_VALUE(id) over(order by id)  as lastid
from abc

FIRST_VALUE 一看就明白了。但last_value 为什么就是当前行的值呢？明明该是4才对啊。

原因在于这两个函数 可以用rows 指定作用域。 而默认的作用域是
RANGE UNBOUNDED PRECEDING AND CURRENT ROW
就是说从窗口的第一行到当前行。 所以last_value 最后一行肯定是当前行了。

知道原因后，只需要改掉行的作用域就可以了。

with abc as 
(
select 1 as id union all
select 2  union all
select 3 uno union all
select 4
)
select id,
FIRST_VALUE(id) over(order by id ) as firstid,
LAST_VALUE(id) over(order by id rows between UNBOUNDED PRECEDING AND UNBOUNDED following )  as lastid
from abc

 

rows 子句的一些关键字：
UNBOUNDED PRECEDING 窗口函数第一行
UNBOUNDED following 窗口函数最后一行
CURRENT ROW 当前行

n PRECEDING 当前行的前几行
n following 当前行的后几行

lag和lead详解

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值

• 第一个参数为列名，
• 第二个参数为往上第n行（可选，默认为1），
• 第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

select field1,field2,LAG(field2,1) over (partition by field1 order by field4) as next_field2,field4
from test1

LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值

• 第一个参数为列名，
• 第二个参数为往下第n行（可选，默认为1），
• 第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

select field1,field2,LEAD(field2,1) over (partition by field1 order by field4) as next_field2,field4
from test1