- lag(统计窗口内往下第n行值)
lag(expr [, offset] [, default]) OVER ([partition_by_clause] order_by_clause)
第一个参数:列名
第二个参数:往下偏移n行(可选,默认为1)
第三个参数:默认值
SELECT
building_name,
organization_id ,
row_number() OVER(PARTITION BY building_name,organization_id ORDER BY ins_piont_total desc ) as rm ,
lag(ins_piont_total,2) OVER(PARTITION BY building_name,organization_id ORDER BY ins_piont_total desc) as lag
from dwd_building_room_point where dt='2022-04-28'
- lead(统计窗口内往上第n行值)
lead(expr [, offset] [, default]) OVER([partition_by_clause] order_by_clause)
第一个参数:列名
第二个参数:往上偏移n行(可选,默认为1)
第三个参数:默认值