案例:利用sql批量更新部分时间的同比数据

问题:如何将social_kol_tmp表 中的字段cost_YA中日期为201901-201909中的值替换为相同brand和pltform对应18年月份的col_cost字段的数据,其他日期的cost_Y值不变?

假设: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 生成rn1rn2(成功)

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

相关文章
相关标签/搜索