分析过程---利用sql批量更新部分时间的同比数据
问题：如何将social_kol_tmp表 中的字段cost_ya中日期为201901-201909中的值替换为相同brand和pltform对应18年月份的col_cost字段的数据,其他日期的cost_ya值不变？
假设：social_kol_tmp表 a，social_kol_tmp表b
难点：可以利用join on brand和pltform相等，但是日期如何匹配呢？
思路：通过对18年各月和对应19年的各个月份产生相应的字段，rn1和rn2
注意，理论上结果中同一行两个时间只相差一年
方案一：窗口函数生成rn1和rn2（结果需要看数据情况，原因如下）
1. 代码如下：
select a.brand,a.platform,a.period as adt,b.period as bdt,a.col_cost,b.col_cost as cost_ynew
from (select col_cost,brand,platform,period,dense_rank() over (partition by brand,platform order by period) as rn1
from social_kol_tmp
where period>='201901' and period<'201909'
) a
join
( select col_cost,brand,platform,period,dense_rank() over (partition by brand,platform order by period ) as rn2
from social_kol_tmp
where period>='201801' and period<'201809'
) b
on a.rn1=b.rn2 and a.brand=b.brand and a.platform=b.platform
2. 出现的问题：部分时间不对应
注意，理论上结果中同一行两个时间只相差一年
3 .原因分析：
某个品牌的某一平台的数据不是每个月都有，排序产生的字段rn1和rn2出了问题，未按照对应的月份排序。
方案二：case when 生成rn1和rn2(成功)
1. 代码如下：
select a.brand,a.platform,a.period as adt,b.period as bdt,a.col_cost,b.col_cost as cost_ynew
from (select col_cost,brand,platform,period,
case when period='201901' then '1'
when period='201902' then '2'
when period='201903' then '3'
when period='201904' then '4'
when period='201905' then '5'
else period
end as rn1
from social_kol_tmp
where period>='201901' and period<'201909'
) a
join
( select col_cost,brand,platform,period,
case when period='201801' then '1'
when period='201802' then '2'
when period='201803' then '3'
when period='201804' then '4'
when period='201805' then '5'
else period
end as rn2
from social_kol_tmp
where period>='201801' and period<'201809'
) b
on a.rn1=b.rn2 and a.brand=b.brand and a.platform=b.platform
2.结果如下：
检验：
- adt和bdt的日期月份是对应的
- 表中的数据adt对应的col_cost是以1开头的，bd对应的是以2开头的，故数据校验成功
在sql中实现数据更新的方法
参考如下（sqlsever）：
1.创建一个表，smedia.social_kol_tmp_new ：201901-201909中的kol_cost替换为对应18年月份的数据
create table smedia.social_kol_tmp_new as
select a.brand,a.platform,a.period as adt,b.period as bdt,a.kol_cost,b.kol_cost as cost_ynew
from (select kol_cost,brand,platform,period,
case when period='201901' then '1'
when period='201902' then '2'
when period='201903' then '3'
when period='201904' then '4'
when period='201905' then '5'
when period='201906' then '3'
when period='201907' then '4'
when period='201908' then '5'
when period='201909' then '5'
else period
end as rn1
from media.social_kol_tmp_new
where period>='201901' and period<'201909'
) a
join
( select kol_cost,brand,platform,period,
case when period='201801' then '1'
when period='201802' then '2'
when period='201803' then '3'
when period='201804' then '4'
when period='201805' then '5'
when period='201806' then '6'
when period='201807' then '7'
when period='201808' then '8'
when period='201809' then '9'
else period
end as rn2
from media.social_kol_tmp_new
where period>='201801' and period<'201809'
) b
on a.rn1=b.rn2 and a.brand=b.brand and a.platform=b.platform
2.在smedia.social_kol_tmp 更新新数据
update smedia.social_kol_tmp
set cost_ya= cost_ynew
from smedia.social_kol_tmp a,media.social_kol_tmp_new b
where a.period=b.adt