sql - 如何在 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，所以如果有任何新功能对我有帮助，我想听听。

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

Answer 1

我首先构建一个 Numbers 表，其中包含从 1 到一百万左右的连续整数。一旦您掌握了窍门，它们就会派上用场。

例如，这里是如何获取 2008 年每个月的第一天:

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 表的最佳方法。