SQL 窗口函数之lead() over(partition by ) 和 lag() over(partition by )

          lag() over() 与 lead() over() 函数是跟偏移量相关的两个分析函数，通过这两个函数可以在一次查询中取出同一字段的前 N 行的数据 (lag) 和后 N 行的数据 (lead) 作为独立的列, 从而更方便地进行进行数据过滤。这种操作可以代替表的自联接，并且 LAG 和 LEAD 有更高的效率。

语法解析

over() 表示 lag() 与 lead() 操作的数据都在 over() 的范围内，他里面可以使用 partition by 语句（用于分组） order by 语句（用于排序）。partition by a order by b 表示以 a 字段进行分组，再 以 b 字段进行排序，对数据进行查询。

lead(expression,offset,default) over(partition by ... order by ...)lag(expression,offset,default) over(partition by ... order by ... )

lead(expression,offset,default) expression需要查找的字段，offset往后查找的 offset行的数据（即偏移量），defaultvalue 没有符合条件的默认值

示例

求哪些店铺的电子类产品连续3个月增长

1、查找出每一个店铺电子类产品下个月的销量：LEAD()  OVER()

select  shop,month,dz,lead(dz,1,null)  over(partition by shop oreder by month)  as next_dz   --lead()参数1：目标字段;参数2：步长（是取下1个还是下2个）;参数3：取不到给NULLfrom demo_sale

2、用下月销量-当前月销量>0标记为1否则标记为0,我们要取标记为1的条件过滤

select shop,month  from (select shop, month,case when  (next_dz - dz) > 0 then 1 else 0 end as  inc_flag  from (select shop,month,dz,lead(dz,1,null)  over(partition by shop oreder by month)  as next_dz   from demo_sale)  t) t2  where t2.inc_flag =  1

3、求连续3个月增长的店铺：ROW_NUMBER() 

# 截取月份-排序值，如果值相等就是连续增长的，因为rn是以1为差值的等差序列，如果月份-rn得到的值一样，那么就是相邻月份
select　shop,substr(month,6,2) - rn  as inc2_flag  # 按照店铺分区，月份升序排序from (select  shop,month,row_number() over(partition by shop  order by month )  as rnfrom (select shop,month from # 计算相邻两个月的销量值(select shop,month,case when  (next_dz - dz) > 0 then 1 else 0 end as  inc_flag from # 数据往下偏移1行，获取下个月的销量(select  shop,month,dz,lead(dz,1,null)  over(partition by shop oreder by month)  as next_dz   from demo_sale)  t) t2 
# 筛选出连续两个月销量增长的店铺 
where t2.inc_flag =  1) t3 ) t4

4、按照shop、inc2_flag聚合group by 数量>=3的店铺就是符合条件的,注意去重。

select  distinct t5.shop from # 因为排序得到的rn是按照以1为差值的等差序列，如果有的月份店铺销量不是增长，那么就会被去掉，导致月份之间的增长不一定是按照1为差值增长的等差序列，
那么得到的inc2_flag就有可能不一样，但是一样的一定是连续增长的月份(select shop,substr(month,6,2) - rn  as inc2_flag   --截取月份-排序值，如果值相等就是连续增长的from (select  shop,month,row_number() over(partition by shop  order by month )  as rnfrom (select shop,month from (select shop,month,case when  (next_dz - dz) > 0 then 1 else 0 end as  inc_flagfrom (select  shop,month,dz,lead(dz,1,null)  over(partition by shop oreder by month)  as next_dz from demo_sale)  t) t2where t2.inc_flag =  1) t3) t4) t5 group by  t5.shop.t5.inc2_flag having count(1)>=3