LAG与LEAD

返回窗口中当前行之前（之后）给定偏移量的输入表达式的值，即在同一张表中实现错位查询（在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列）。它可以避免使用消耗更大的自连接，从而提高查询处理速度。

函数写法：

LAG ( expression[, offset ] [, default ] ) OVER ( partition by... order by... )

expression：字段名；offset：偏移量，上（下）1行或N行；

default：函数取上N/下N个值，当在表中从当前行位置向前数N行已经超出了表的范围时，default作为函数的默认返回值，若无指定默认值，则返回NULL。

示例

原始数据

1.LAG,LEAD

select uucompany_id ,year_quarter ,spend ,lag(spend,1,0) over(partition by uucompany_id order by year_quarter) as last_spend ,lead(spend,1,0) over(partition by uucompany_id order by year_quarter) as next_spend ,spend - lag(spend,1,0) over(partition by uucompany_id order by year_quarter) as spend_diff ,datediff('quarter',lag(year_quarter,1) over(partition by uucompany_id order by year_quarter),year_quarter)-1 as quarter_diff --无消耗季度 from t_quarter_spend

2.自连接

select q1.uucompany_id ,q1.year_quarter ,q1.spend as spend ,q2.spend as last_spend ,q1.spend-q2.spend as spend_diff ,datediff('quarter',q2.year_quarter,q1.year_quarter)-1 as quarter_diff --无消耗季度 from t_quarter_spend q1 left join t_quarter_spend q2 on q1.uucompany_id = q2.uucompany_id --上季度 and q1.rk = q2.rk+1

3.LEAD,LAG应用

根据A,B列实现目标排名（题意：B列从上到下遇到不同的值就加1）

select t.A ,t.B ,sum(decode(t.B=t.B_lead,true,0,1)) over(order by t.A) as 目标值 from ( select t.A , t.B , nvl(lag(t.B) over (order by t.A),0) as B_lead from test.test1 t ) t ;