SCD 缓慢变化维按月保存 lag lead 实现

最新推荐文章于 2022-02-19 15:40:35 发布

你冻了我的冬天

最新推荐文章于 2022-02-19 15:40:35 发布

阅读量260

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/u012071918/article/details/80735313

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

需求
将上表变为下表


*year_month nestle_outlet_code  UserCode*
201801  I00002N15052359710625   TSRB41CGAB013
201802  I00002N15052359710625   TSRB41CGAB013
201803  I00002N15052359710625   TSRB41CGAB013
201804  I00002N15052359710625   TSRB41CGAB006
201805  I00002N15052359710625   TSRB41CG7B073
201806  I00002N15052359710625   TSRB41CG7B073

nestle_outlet_code     UserCode     start_month   end_month
I00002N15052359710625   TSRB41CGAB013   201801  201803
I00002N15052359710625   TSRB41CGAB006   201804  201804
I00002N15052359710625   TSRB41CG7B073   201805  999912

代码
建表

CREATE TABLE [dbo].[A_Before](
    [year_month] [int] NULL,
    [nestle_outlet_code] [nvarchar](50) NULL,
    [UserCode] [nvarchar](50) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[A_Before] ([year_month], [nestle_outlet_code], [UserCode]) VALUES (201801, N'I00002N15052359710625', N'TSRB41CGAB013')
INSERT [dbo].[A_Before] ([year_month], [nestle_outlet_code], [UserCode]) VALUES (201802, N'I00002N15052359710625', N'TSRB41CGAB013')
INSERT [dbo].[A_Before] ([year_month], [nestle_outlet_code], [UserCode]) VALUES (201803, N'I00002N15052359710625', N'TSRB41CGAB013')
INSERT [dbo].[A_Before] ([year_month], [nestle_outlet_code], [UserCode]) VALUES (201804, N'I00002N15052359710625', N'TSRB41CGAB006')
INSERT [dbo].[A_Before] ([year_month], [nestle_outlet_code], [UserCode]) VALUES (201805, N'I00002N15052359710625', N'TSRB41CG7B073')
INSERT [dbo].[A_Before] ([year_month], [nestle_outlet_code], [UserCode]) VALUES (201806, N'I00002N15052359710625', N'TSRB41CG7B073')

实现代码

with temp_a as (
select aa.year_month,aa.nestle_outlet_code,aa.UserCode  from (
SELECT T.*,
                CASE
                  WHEN convert(varchar(6),dateadd(m,-1,cast(cast(year_month as varchar(10))+'01'  as date)),112)                          
                  = lag(year_month) OVER(partition by nestle_outlet_code,UserCode ORDER BY year_month)
                    THEN
                   'delete'
                  ELSE
                   'No delete'
                END AS year_month_flag
         FROM   A_Before T
         )  aa   where 
        year_month_flag='No delete'
         ) 
select  nestle_outlet_code,UserCode
,year_month as start_month
,lead(convert(varchar(6),dateadd(m,-1,cast(cast(year_month as varchar(10))+'01'  as date)),112),1,'999912') 
over(partition by nestle_outlet_code order by year_month) as end_month 
from temp_a