如何在T-SQL中基于上个月的数据确定丢失月份的值 [英] How to Determine Values for Missing Months based on Data of Previous Months in T-SQL

问题描述

我在特定的时间点发生了一组交易:

I have a set of transactions occurring at specific points in time:

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查询的上下文中使用它，因此，最好能得到如下结果集:

Assuming that the TransactionValue sets some kind of level, I need to know what the level was between the transactions. I need this in the context of a set of T-SQL queries, so it would be best if I could get a result set like this:

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

请注意，如何每月获取交易中指定的值，或者获取最新的非空值.

Note how, for each month, we either get the value specified in the transaction, or we get the most recent non-null value.

我的问题是我不知道该怎么做！我只是一个中级"级别的SQL Developer，而且我不记得以前见过类似的东西.自然，我可以在程序中或使用游标创建所需的数据，但是我想知道是否有更好的面向集合的方法来实现此目的.

My problem is that I have little idea how to do this! I'm only an "intermediate" level SQL Developer, and I don't remember ever seeing anything like this before. Naturally, I could create the data I want in a program, or using cursors, but I'd like to know if there's a better, set-oriented way to do this.

我正在使用SQL Server 2008，所以如果有任何新功能会有所帮助，我想听听.

I'm using SQL Server 2008, so if any of the new features will help, I'd like to hear about it.

P.S.如果有人能想到一种更好的方式陈述这个问题，甚至更好的主题，我将不胜感激.我花了好一会儿才决定，"la脚"是我能想到的最好的方法. 涂片"听起来更糟.

P.S. If anyone can think of a better way to state this question, or even a better subject line, I'd greatly appreciate it. It took me quite a while to decide that "spread", while lame, was the best I could come up with. "Smear" sounded worse.

推荐答案

我将从构建一个Numbers表开始，该表包含从1到一百万左右的连续整数.一旦掌握了它们，它们就会派上用场.

I'd start by building a Numbers table holding sequential integers from 1 to a million or so. They come in really handy once you get the hang of it.

例如，下面是如何获得2008年每月的1号:

For example, here is how to get the 1st of every month in 2008:

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

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

Now, you can put that together using OUTER APPLY to find the most recent transaction for each date like so:

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;

这应该可以为您提供所需的信息，但是您可能需要向Google稍加寻找才能找到创建Numbers表的最佳方法.

This should give you what you're looking for, but you might have to Google around a little to find the best way to create the Numbers table.

这篇关于如何在T-SQL中基于上个月的数据确定丢失月份的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！