通用-分析函数

lead

--lead: 当前行向上剔除行数( 0-剔除当前行 )
with tmp(day) as (
    values ('20200708'),('20200709'),('20200710')
)
select day,lead(day,1) over (order by day asc) one_row_offset_up
from tmp;

lag

--lag: 当前行向下剔除行数( 0-剔除当前行 )
with tmp(day) as (
    values ('20200708'),('20200709'),('20200710')
)
select day,lag(day,1) over (order by day asc) one_row_offset_down 
from tmp;

first_value

--first_value : 取第一行记录
with tmp(day) as (
    values ('20200708'),('20200709'),('20200710')
)
select day,first_value(day) over () first_row_value
from tmp;

last_value

--last_value : 取尾行记录
with tmp(day) as (
    values ('20200708'),('20200709'),('20200710')
)
select day,last_value(day) over () last_row_value
from tmp;

nth_value

--nth_value : 取第N行记录(从上往下)
with tmp(day) as (
    values ('20200708'),('20200709'),('20200710'),('20200711')
)
select day,nth_value(day,2) from first over () nth_row_value_from_first
from tmp;

--nth_value : 取第N行记录(从下往上)
with tmp(day) as (
    values ('20200708'),('20200709'),('20200710'),('20200711')
)
select day,nth_value(day,2) from last over ()  nth_row_value_from_last
from tmp;

rank & dense_rank(denserank)

--rank: 排名，相同值则排名相同并跳过对应行号

--dense_rank or denserank: 排名，相同值则排名相同,下一排名序号延续上一排名序号    
with tmp(name, class, score) as (
    values ('Lilith', 'Math', 100),
           ('Jordan', 'Math', 20),
           ('Martha', 'Math', 60),
           ('Mars', 'Math', 60),
           ('Zeus', 'Math', 80)
)

select distinct name,
                class,
                score,
                rank() over (order by score desc)       as rank,
                dense_rank() over (order by score desc) as dense_rank,
                denserank() over (order by score desc)  as denserank
from tmp;

row_number(rownumber)

--row_number or rownumber 连续行号，start with 1
with tmp(name, class, score) as (
    values ('Lilith', 'Math', 100),
           ('Jordan', 'Math', 20),
           ('Martha', 'Math', 60),
           ('Mars', 'Math', 60),
           ('Zeus', 'Math', 80)
)

select distinct name,
                class,
                score,
                row_number() over () as row_number,
                rownumber() over ()  as rownumber
from tmp;