Window analysis function last_ VALUE，FIRST_ VALUE，lag，lead

Catalog

FIRST_VALUE and LAST_VALUE Detailed explanation

lag and lead Detailed explanation   

FIRST_VALUE and LAST_VALUE Detailed explanation

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 It's easy to see . but last_value Why is it the value of the current row ？ It should be 4 That's right .

The reason is that these two functions It can be used rows Specify scope . The default scope is
RANGE UNBOUNDED PRECEDING AND CURRENT ROW
That is, from the first line of the window to the current line . therefore last_value The last line must be the current line .

When you know why , Just change the scope of the line .

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 Some keywords of clause ：
UNBOUNDED PRECEDING The first line of the window function
UNBOUNDED following The last line of the window function
CURRENT ROW Current row

n PRECEDING The first few lines of the current line
n following The last few lines of the current line

lag and lead Detailed explanation

LAG(col,n,DEFAULT) It is used to count up the n Row value

• The first parameter is the column name ,
• The second parameter is upward n That's ok （ Optional , The default is 1）,
• The third parameter is the default value （ When you go up n Behavior NULL When , Take the default value , If not specified , Then for NULL）

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

LEAD(col,n,DEFAULT) Used for the next... In the statistics window n Row value

• The first parameter is the column name ,
• The second parameter is next n That's ok （ Optional , The default is 1）,
• The third parameter is the default value （ When you go down n Behavior NULL When , Take the default value , If not specified , Then for NULL）

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