--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;
文章目录leadlagfirst_valuelast_valuenth_valuerank & dense_rank(denserank)row_number(rownumber)lead--lead: 当前行向上剔除行数( 0-剔除当前行 )with tmp(day) as ( values ('20200708'),('20200709'),('20200710'))select day,lead(day,1) over (order by day asc) one_row.