SQL经典用法：开窗函数之LEAD() OVER()和ROW_NUMBER() OVER()
案例需求：求哪些店铺的电子类产品连续3个月增长
shop month dz fz sp
a,2019-01,3000,5000,2000
a,2019-02,6000,4000,3000
b,2019-01,3000,5000,2000
b,2019-02,6000,4000,3000
b,2019-03,3000,3500,3000
b,2019-04,3200,3300,2500
b,2019-05,4000,3000,3000
a,2019-03,3200,3500,3000
a,2019-04,3600,3000,3300
a,2019-05,3900,3500,4000
c,2019-01,3200,3500,3000
c,2019-02,3330,2900,3000
c,2019-03,3360,3200,3100
c,2019-04,3500,3100,2800
第一步：查找出每一个店铺电子类产品下个月的销量：LEAD() OVER()
--按照店铺分组，月份升序排序
--假设已加载数据建表，表名DEMO_SALE
select shop,
month,
dz,
lead(dz,1,null) over(partition by shop oreder by month) as next_dz --lead()参数1：目标字段;参数2：步长（是取下1个还是下2个）;参数3：取不到给NULL
from demo_sale
第二步：用下月销量-当前月销量>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个月增长的店铺：ROW_NUMBER()
select shop,substr(month,6,2) - rn as inc2_flag --截取月份-排序值，如果值相等就是连续增长的
from
(select shop,month,row_number() over(partition by shop order by month ) as rn
from
(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
) t3
) t4
--第四步：按照shop、inc2_flag聚合group by 数量>=3的店铺就是符合条件的,注意去重。
select distinct t5.shop
from
(select shop,substr(month,6,2) - rn as inc2_flag --截取月份-排序值，如果值相等就是连续增长的
from
(select shop,month,row_number() over(partition by shop order by month ) as rn
from
(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
) t3
) t4
) t5
group by t5.shop.t5.inc2_flag
having count(1)>=3
