翻转年份时出现 T-SQL 查询相对周数问题

Question

我有一个特定的相对周编号列，称为 WeekNr。源表中的两列（WeekNr 和 CURWEEK）以通常的日历周编号保存：1、2、3...到52。

此视图用于 Excel 文件，企业希望查看从 -12 到 +6 的相对周数：过去 12 周和 6 周的预测，相对于当前周的零周（今天，2015 年 1 月 13 日，这是日历周 2）。

所以，这段代码工作得很好，直到一年过去。52 岁之后，WeekNr - CURWEEK 不再按预期工作。

您对此有某种优雅的解决方案吗？

CREATE VIEW [dbo].[vw_NOSSCE_HealthKPIs_DEV]
 AS 
 (
SELECT 
[Region]
      ,[Country]
      ,[GlobalMaterialCode]
      ,[Material]
      ,[MaterialCode]
      ,[PlantCode]
      ,[Plant]
      ,[Brand]
      ,
      (WeekNr - (SELECT TOP 1 CURWEEK FROM [TO_BDB].[dbo].[TO_BDB_NOSSCE_FACT_NORMALIZED]) ) as 
      WeekNr 
      ,[MEDICAL_NEED]
      ,[TOP_SELLER]
      ,[PHARMABU_CODE]
      ,[PharmaBU]
      ,[SourceLocationCode]
      ,[SourceLocation]
      ,[InventoryTotal]
      ,[MaxStockQtyTotal]
      ,[TotalSafetyStockTotal]
      ,[DangerTreshold]
      ,[FORECAST_FLG]
      ,[DangerZoneDesc]
 FROM [TO_BDB].[dbo].[TO_BDB_NOSSCE_PAST12_HIST] 
 WHERE (WeekNr - (SELECT TOP 1 CURWEEK
 FROM [TO_BDB].[dbo].[TO_BDB_NOSSCE_FACT_NORMALIZED]) )
  BETWEEN -12 and 6
  GROUP BY [Region]
      ,[Country]
      ,[GlobalMaterialCode]
      ,[Material]
      ,[MaterialCode]
      ,[PlantCode]
      ,[Plant]
      ,[Brand]
,WeekNr, [MEDICAL_NEED]
      ,[TOP_SELLER]
      ,[PHARMABU_CODE]
      ,[PharmaBU]
      ,[SourceLocationCode]
      ,[SourceLocation]
      ,[InventoryTotal]
      ,[MaxStockQtyTotal]
      ,[TotalSafetyStockTotal]
      ,[DangerTreshold]
      ,[FORECAST_FLG]
      ,[DangerZoneDesc]
 UNION ALL
 SELECT * FROM [dbo].[vw_NOSSCE_HealthKPIs] WHERE WeekNr > 0
)

更新，我

创建了日历表，为什么它给了我第 53 周？

CREATE TABLE [dbo].[TO_BDB_NOSSCE_CALENDAR](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DATE] [date] NOT NULL,
    [YEAR]  AS (datepart(year,[DATE])) PERSISTED,
    [SEMESTER]  AS (case when datepart(month,[DATE])<(7) then '1' else '2' end) PERSISTED NOT NULL,
    [TRIMESTER]  AS (case when datepart(month,[DATE])<(4) then '1' else case when datepart(month,[DATE])<(7) then '2' else case when datepart(month,[DATE])<(10) then '3' else '4' end end end) PERSISTED NOT NULL,
    [MONTH]  AS (case when len(CONVERT([varchar](2),datepart(month,[DATE]),0))=(1) then '0'+CONVERT([varchar](2),datepart(month,[DATE]),0) else CONVERT([varchar](2),datepart(month,[DATE]),0) end) PERSISTED,
    [WEEK]  AS (case when len(CONVERT([varchar](2),datepart(week,[DATE]),0))=(1) then '0'+CONVERT([varchar](2),datepart(week,[DATE]),0) else CONVERT([varchar](2),datepart(week,[DATE]),0) end),
    [DAY]  AS (case when len(CONVERT([varchar](2),datepart(day,[DATE]),0))=(1) then '0'+CONVERT([varchar](2),datepart(day,[DATE]),0) else CONVERT([varchar](2),datepart(day,[DATE]),0) end) PERSISTED,
    [WEEKNUMBER]  AS (datepart(week,[DATE])),
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Answer 1

首先，如果使用代码生成 Excel 文件，则最好使用 Excel 公式而不是使用 SQL。

其次，周数不能识别周数。要标识一周，您需要年份和周数，或者需要一个日期。这意味着您可能需要更改表来存储除周之外的年份。

在这里，我们使用日历表来简化这样的查询。最小化的简化日历表可能如下所示。我们使用ISO年和周，但不要让它混淆您。您几乎可以使用任何您想要的编号。（为PostgreSQL编写，但SQL Server的原则是相同的。

create table calendar (
  cal_date date primary key,
  iso_year integer not null,
  iso_week integer not null
);
create index on calendar (iso_year, iso_week);

我们使用检查约束（未显示）来保证 iso_year 和 iso_week 的值适用于每个cal_date值。很少（高度信任的）人具有插入和删除行的权限。

在标准SQL中，我可能会写这样的东西来获得我们感兴趣的绝对周数。

select distinct iso_year, iso_week
from calendar
where cal_date between cast(current_date - interval '12 weeks' as date) 
                   and cast(current_date + interval  '6 weeks' as date) 
order by iso_year, iso_week;

iso_year iso_week2014      432014      442014      45...2015      72015      82015      9

它返回 19 行，这似乎是适合您的情况的数字。 （注意我的一个错误。 将其放入公共表表达式或视图中，您可以使用简单的算术对相对周进行编号。（我使用了 CTE，所以这个答案或多或少是独立的。

with absolute_weeks as (
  select distinct iso_year, iso_week
  from calendar
  where cal_date between cast(current_date - interval '12 weeks' as date) 
                     and cast(current_date + interval  '6 weeks' as date) 
)
select *, (row_number() over (order by iso_year, iso_week) - 13) as relative_week
from absolute_weeks 
order by iso_year, iso_week;

iso_year iso_week relative_week--2014      43        -122014      44        -112014      45        -10...2015      7           42015      8           52015      9           6

如果您的数据包含年份和星期，则可以在iso_year和iso_week上将查询加入此数据。如果数据具有日期，则可以将上一个查询移动到 CTE 或视图中，并联接日历表以获取每周的日期。

with absolute_weeks as (
  select distinct iso_year, iso_week
  from calendar
  where cal_date between cast(current_date - interval '12 weeks' as date) 
                     and cast(current_date + interval  '6 weeks' as date) 
), relative_weeks as (
  select *, (row_number() over (order by iso_year, iso_week) - 13) as relative_week
  from absolute_weeks 
)
select c.cal_date, c.iso_year, c.iso_week, r.relative_week
from calendar c
inner join relative_weeks r on c.iso_year = r.iso_year and c.iso_week = r.iso_week
order by c.cal_date;

cal_date iso_year iso_week relative_week--2014-10-20  2014      43        -122014-10-21  2014      43        -122014-10-22  2014      43        -12...2015-01-12  2015       3          02015-01-13  2015       3          02015-01-14  2015       3          0...2015-02-27  2015       9          62015-02-28  2015       9          62015-03-01  2015       9          6

当前周（2015-01-13 这一周）是 ISO 第 3 周，而不是编号的第 2 周。你自己的日历表应该反映你自己的逻辑，而不是我的逻辑。

此查询返回 133 行，这似乎是正确的数字。每个 ISO 周有 7 天，因此 7 * 19 行是基于 ISO 周的日历表的正确数字。您自己的要求可能会给您一个偶尔的星期，该周的天数不同。