如何根据 T-SQL 中前几个月的数据确定缺失月份的值

Question

我有一组在特定时间点发生的事务：

CREATE TABLE Transactions (
    TransactionDate Date NOT NULL,
    TransactionValue Integer NOT NULL
)

数据可能是：

INSERT INTO Transactions (TransactionDate, TransactionValue)
VALUES ('1/1/2009', 1)
INSERT INTO Transactions (TransactionDate, TransactionValue)
VALUES ('3/1/2009', 2)
INSERT INTO Transactions (TransactionDate, TransactionValue)
VALUES ('6/1/2009', 3)

假设 TransactionValue 设置某种级别，我需要知道事务之间的级别是什么。 我需要在一组 T-SQL 查询的上下文中执行此操作，因此最好能够获得如下结果集：

Month   Value
1/2009  1
2/2009  1
3/2009  2
4/2009  2
5/2009  2
6/2009  3

请注意，对于每个月，我们要么获取事务中指定的值，要么获取最近的非空值。

我的问题是我不知道该怎么做！ 我只是一个“中级”级别的 SQL 开发人员，我不记得以前见过这样的事情。 当然，我可以在程序中或使用游标创建我想要的数据，但我想知道是否有更好的、面向集合的方法来做到这一点。

我正在使用 SQL Server 2008，因此如果有任何新功能有帮助，我很想听听。

PS如果有人能想出更好的方式来陈述这个问题，甚至更好的主题行，我将不胜感激。 我花了很长时间才决定“传播”虽然很蹩脚，但却是我能想到的最好的办法。 “涂抹”听起来更糟糕。

Answer 1

约翰·吉布（John Gibb）发布了一个很好的答案，已经被接受，但我想对其进行一些扩展：

• 消除一年的限制，
• 以更广泛的方式公开日期范围
  明确的方式，并
• 消除了单独的需要
  数字表。

这个细微的变化使用递归公用表表达式来建立一组表示 DateRange 中定义的起始日期和截止日期或之后的每个月的第一天的日期。 请注意使用 MAXRECURSION 选项来防止堆栈溢出（！）； 根据需要进行调整以适应预期的最大月数。 另外，请考虑添加替代日期组装逻辑以支持周、季度甚至每天。

with 
DateRange(FromDate, ToDate) as (
  select 
    Cast('11/1/2008' as DateTime), 
    Cast('2/15/2010' as DateTime)
),
Dates(Date) as (
  select 
    Case Day(FromDate) 
      When 1 Then FromDate
      Else DateAdd(month, 1, DateAdd(month, ((Year(FromDate)-1900)*12)+Month(FromDate)-1, 0))
    End
  from DateRange
  union all
  select DateAdd(month, 1, Date)
  from Dates
  where Date < (select ToDate from DateRange)
)
select 
  d.Date, t.TransactionValue
from Dates d
outer apply (
  select top 1 TransactionValue
  from Transactions
  where TransactionDate <= d.Date
  order by TransactionDate desc
) t
option (maxrecursion 120);

Answer 2

如果您经常进行此类分析，您可能会对我为此目的而组合的 SQL Server 函数感兴趣：

if exists (select * from dbo.sysobjects where name = 'fn_daterange') drop function fn_daterange;
go

create function fn_daterange
   (
   @MinDate as datetime,
   @MaxDate as datetime,
   @intval  as datetime
   )
returns table
--**************************************************************************
-- Procedure: fn_daterange()
--    Author: Ron Savage
--      Date: 12/16/2008
--
-- Description:
-- This function takes a starting and ending date and an interval, then
-- returns a table of all the dates in that range at the specified interval.
--
-- Change History:
-- Date        Init. Description
-- 12/16/2008  RS    Created.
-- **************************************************************************
as
return
   WITH times (startdate, enddate, intervl) AS
      (
      SELECT @MinDate as startdate, @MinDate + @intval - .0000001 as enddate, @intval as intervl
         UNION ALL
      SELECT startdate + intervl as startdate, enddate + intervl as enddate, intervl as intervl
      FROM times
      WHERE startdate + intervl <= @MaxDate
      )
   select startdate, enddate from times;

go

这是对此的答案 问题，其中也有一些示例输出。

Answer 3

我无法从手机访问 BOL，因此这是一个粗略指南...

首先，您需要生成没有数据的月份的缺失行。 您可以使用 OUTER 联接到具有所需时间跨度的固定表或临时表，或者使用以编程方式创建的数据集（存储过程等）。

其次，您应该查看新的 SQL 2008“分析”函数，例如 MAX(value ) OVER (分区子句) 获取先前的值。

（我知道 Oracle 可以做到这一点，因为我需要它来计算交易日期之间的复利计算 - 确实有同样的问题）

希望这为您指明了正确的方向...

（避免将其扔到临时表中并将光标放在它上面。太原油！！！）

Answer 4

-----替代方式------

select 
    d.firstOfMonth,
    MONTH(d.firstOfMonth) as Mon,
    YEAR(d.firstOfMonth) as Yr, 
    t.TransactionValue
from (
    select 
        dateadd( month, inMonths - 1, '1/1/2009') as firstOfMonth 
        from (
            values (1), (2), (3), (4), (5), (7), (8), (9), (10), (11), (12)
        ) Dates(inMonths)
) d
outer apply (
    select top 1 TransactionValue
    from Transactions
    where TransactionDate <= d.firstOfMonth
    order by TransactionDate desc
) t

Answer 5

我首先构建一个数字表，其中包含从 1 到一百万左右的连续整数。 一旦你掌握了它的窍门，它们就会非常方便。

例如，以下是如何获取 2008 年每个月的 1 号：

select firstOfMonth = dateadd( month, n - 1, '1/1/2008')
from Numbers
where n <= 12;

现在，您可以使用 OUTER APPLY 将其放在一起，以查找每个日期的最新交易，如下所示：

with Dates as (
    select firstOfMonth = dateadd( month, n - 1, '1/1/2008')
    from Numbers
    where n <= 12
)
select d.firstOfMonth, t.TransactionValue
from Dates d
outer apply (
    select top 1 TransactionValue
    from Transactions
    where TransactionDate <= d.firstOfMonth
    order by TransactionDate desc
) t;

这应该会为您提供所需的内容，但是您可能需要谷歌一下才能找到创建 Numbers 表的最佳方法。

Answer 6

这就是我想出的

declare @Transactions table (TransactionDate datetime, TransactionValue int)

declare @MinDate datetime
declare @MaxDate datetime
declare @iDate datetime
declare @Month int
declare @count int
declare @i int
declare @PrevLvl int

insert into @Transactions (TransactionDate, TransactionValue)
select '1/1/09',1

insert into @Transactions (TransactionDate, TransactionValue)
select '3/1/09',2

insert into @Transactions (TransactionDate, TransactionValue)
select '5/1/09',3


select @MinDate = min(TransactionDate) from @Transactions
select @MaxDate = max(TransactionDate) from @Transactions

set @count=datediff(mm,@MinDate,@MaxDate)
set @i=1
set @iDate=@MinDate


while (@i<=@count)
begin

    set @iDate=dateadd(mm,1,@iDate)

    if (select count(*) from @Transactions where TransactionDate=@iDate) < 1
    begin

        select @PrevLvl = TransactionValue from @Transactions where TransactionDate=dateadd(mm,-1,@iDate)

        insert into @Transactions (TransactionDate, TransactionValue)
        select @iDate, @prevLvl

    end


    set @i=@i+1
end

select *
from @Transactions
order by TransactionDate

Answer 7

要以基于集合的方式完成此操作，您需要所有数据或信息的集合。 在这种情况下，存在被忽视的数据“有哪些月份？” 在数据库中拥有“日历”表和“数字”表作为实用程序表非常有用。

这是使用其中一种方法的解决方案。 第一段代码设置您的日历表。 您可以使用光标或手动或其他方式填写它，并且可以将其限制为您的业务所需的任何日期范围（回到 1900-01-01 或只是回到 1970-01-01 以及尽可能远的未来）想）。 您还可以添加对您的业务有用的任何其他列。

CREATE TABLE dbo.Calendar
(
     date           DATETIME     NOT NULL,
     is_holiday     BIT          NOT NULL,
     CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED (date)
)

INSERT INTO dbo.Calendar (date, is_holiday) VALUES ('2009-01-01', 1)  -- New Year
INSERT INTO dbo.Calendar (date, is_holiday) VALUES ('2009-01-02', 1)
...

现在，使用这张表你的问题就变得微不足道了：

SELECT
     CAST(MONTH(date) AS VARCHAR) + '/' + CAST(YEAR(date) AS VARCHAR) AS [Month],
     T1.TransactionValue AS [Value]
FROM
     dbo.Calendar C
LEFT OUTER JOIN dbo.Transactions T1 ON
     T1.TransactionDate <= C.date
LEFT OUTER JOIN dbo.Transactions T2 ON
     T2.TransactionDate > T1.TransactionDate AND
     T2.TransactionDate <= C.date
WHERE
     DAY(C.date) = 1 AND
     T2.TransactionDate IS NULL AND
     C.date BETWEEN '2009-01-01' AND '2009-12-31'  -- You can use whatever range you want