使用窗口功能的日期范围滚动总和

SELECT
    TH.ProductID,
    TH.TransactionDate,
    TH.ActualCost,
    RollingSum45 = SUM(TH.ActualCost) OVER (
        PARTITION BY TH.ProductID
        ORDER BY TH.TransactionDate
        RANGE BETWEEN 
            INTERVAL 45 DAY PRECEDING
            AND CURRENT ROW)
FROM Production.TransactionHistory AS TH
ORDER BY
    TH.ProductID,
    TH.TransactionDate,
    TH.ReferenceOrderID;

SELECT 
    TH.ProductID,
    TH.TransactionDate,
    TH.ActualCost,
    RollingSum45 =
    (
        SELECT SUM(TH2.ActualCost)
        FROM Production.TransactionHistory AS TH2
        WHERE
            TH2.ProductID = TH.ProductID
            AND TH2.TransactionDate <= TH.TransactionDate
            AND TH2.TransactionDate >= DATEADD(DAY, -45, TH.TransactionDate)
    )
FROM Production.TransactionHistory AS TH
ORDER BY
    TH.ProductID,
    TH.TransactionDate,
    TH.ReferenceOrderID;

CREATE UNIQUE INDEX i
ON Production.TransactionHistory
    (ProductID, TransactionDate, ReferenceOrderID)
INCLUDE
    (ActualCost);

#1 楼

很好的问题，保罗！我使用了两种不同的方法，一种在T-SQL中使用，一种在CLR中。
T-SQL快速摘要
T-SQL方法可以概括为以下步骤：

采用产品/日期的跨产品
合并观察到的销售数据
将该数据汇总到产品/日期级别
根据此汇总数据计算过去45天的滚动总和（其中包含任何“缺失”天）
将这些结果过滤为仅具有一个或多个销售额的产品/日期配对

使用SET STATISTICS IO ON，此方法报告Table 'TransactionHistory'. Scan count 1, logical reads 484，从而确认了在桌子上“单次通过”。作为参考，原始的循环搜索查询报告为Table 'TransactionHistory'. Scan count 113444, logical reads 438366。
如SET STATISTICS TIME ON所报告的，CPU时间为514ms。与原始查询的2231ms相比，它是有利的。
CLR快速摘要
CLR摘要可以概括为以下步骤：

将数据读取到内存中，按产品和date
在处理每笔交易时，会累加总成本。只要某笔交易是与前一笔交易不同的产品，就将运行总计重置为0。
维护一个指向与当前交易相同（产品，日期）的第一笔交易的指针。每当遇到与该（产品，日期）有关的最后一笔交易时，请计算该交易的滚动总和，并将其应用于具有相同（产品，日期）相同的所有交易
将所有结果返回给用户！
/>
使用SET STATISTICS IO ON，此方法报告没有发生逻辑I / O！哇，完美的解决方案！ （实际上，似乎SET STATISTICS IO不会报告CLR中发生的I / O。但是从代码中，很容易看到对表进行了一次扫描，并按Paul建议的索引顺序检索了数据。 />如SET STATISTICS TIME ON所报告，CPU时间现在为187ms。因此，与T-SQL方法相比，这是一个很大的改进。不幸的是，两种方法的总耗时非常相似，每次大约半秒钟。但是，基于CLR的方法的确必须向控制台输出113K的行（相比之下，按产品/日期分组的T-SQL方法只有52K的行），因此这就是我专注于CPU时间的原因。这种方法的另一个主要优点是，它产生的结果与原始的循环/查找方法完全相同，即使在同一天多次售出产品的情况下，每次交易都包含一行。 （在AdventureWorks上，我专门比较了逐行结果，并确认它们与Paul的原始查询是一致的。）
这种方法的缺点（至少以当前的形式）是它读取内存中的所有数据。但是，已设计的算法仅在任何给定时间严格需要内存中的当前窗口帧，并且可以对其进行更新以处理超出内存的数据集。 Paul通过生成此算法的实现（仅将滑动窗口存储在内存中）来说明这一点。这是以给CLR程序集更高的权限为代价的，但是在将此解决方案扩展到任意大的数据集时，绝对值得。
T-SQL-一种扫描，按日期分组
初始设置

USE AdventureWorks2012
GO
-- Create Paul's index
CREATE UNIQUE INDEX i
ON Production.TransactionHistory (ProductID, TransactionDate, ReferenceOrderID)
INCLUDE (ActualCost);
GO
-- Build calendar table for 2000 ~ 2020
CREATE TABLE dbo.calendar (d DATETIME NOT NULL CONSTRAINT PK_calendar PRIMARY KEY)
GO
DECLARE @d DATETIME = '1/1/2000'
WHILE (@d < '1/1/2021')
BEGIN
    INSERT INTO dbo.calendar (d) VALUES (@d)
    SELECT @d =  DATEADD(DAY, 1, @d)
END
GO

查询

DECLARE @minAnalysisDate DATE = '2007-09-01', -- Customizable start date depending on business needs
        @maxAnalysisDate DATE = '2008-09-03'  -- Customizable end date depending on business needs
SELECT ProductID, TransactionDate, ActualCost, RollingSum45, NumOrders
FROM (
    SELECT ProductID, TransactionDate, NumOrders, ActualCost,
        SUM(ActualCost) OVER (
                PARTITION BY ProductId ORDER BY TransactionDate 
                ROWS BETWEEN 45 PRECEDING AND CURRENT ROW
            ) AS RollingSum45
    FROM (
        -- The full cross-product of products and dates, combined with actual cost information for that product/date
        SELECT p.ProductID, c.d AS TransactionDate,
            COUNT(TH.ProductId) AS NumOrders, SUM(TH.ActualCost) AS ActualCost
        FROM Production.Product p
        JOIN dbo.calendar c
            ON c.d BETWEEN @minAnalysisDate AND @maxAnalysisDate
        LEFT OUTER JOIN Production.TransactionHistory TH
            ON TH.ProductId = p.productId
            AND TH.TransactionDate = c.d
        GROUP BY P.ProductID, c.d
    ) aggsByDay
) rollingSums
WHERE NumOrders > 0
ORDER BY ProductID, TransactionDate
-- MAXDOP 1 to avoid parallel scan inflating the scan count
OPTION (MAXDOP 1)

执行计划
从执行计划中，我们看到Paul提出的原始索引足以让我们执行一个有序命令扫描Production.TransactionHistory，使用合并联接将交易历史记录与每种可能的产品/日期组合结合起来。

假设
这种方法有一些重要的假设。我想应该由Paul决定是否可以接受它们：)

我正在使用Production.Product表。该表可在AdventureWorks2012上免费使用，并且该关系是通过Production.TransactionHistory上的外键强制执行的，因此我将其解释为公平的游戏。
这种方法依赖于以下事实：交易在AdventureWorks2012上没有时间成分；如果他们这样做了，那么在没有先跳过交易历史记录的情况下，将不可能再生成完整的产品/日期组合。
我正在生成一个行集，每个产品/日期对仅包含一行。我认为这“可以说是正确的”，并且在许多情况下可以返回更理想的结果。对于每种产品/日期，我添加了一个NumOrders列以指示发生了多少销售。请参阅以下屏幕快照，以比较在同一日期多次售出产品（例如，319 / 2007-09-05 00:00:00.000）的情况下原始查询与建议查询的比较结果。


CLR-一次扫描，完整的未分组结果集
主要功能主体
这里看不到很多东西；函数的主体声明输入（必须与相应的SQL函数匹配），建立SQL连接，然后打开SQLReader。

 // SQL CLR function for rolling SUMs on AdventureWorks2012.Production.TransactionHistory
[SqlFunction(DataAccess = DataAccessKind.Read,
    FillRowMethodName = "RollingSum_Fill",
    TableDefinition = "ProductId INT, TransactionDate DATETIME, ReferenceOrderID INT," +
                      "ActualCost FLOAT, PrevCumulativeSum FLOAT, RollingSum FLOAT")]
public static IEnumerable RollingSumTvf(SqlInt32 rollingPeriodDays) {
    using (var connection = new SqlConnection("context connection=true;")) {
        connection.Open();
        List<TrxnRollingSum> trxns;
        using (var cmd = connection.CreateCommand()) {
            //Read the transaction history (note: the order is important!)
            cmd.CommandText = @"SELECT ProductId, TransactionDate, ReferenceOrderID,
                                    CAST(ActualCost AS FLOAT) AS ActualCost 
                                FROM Production.TransactionHistory 
                                ORDER BY ProductId, TransactionDate";
            using (var reader = cmd.ExecuteReader()) {
                trxns = ComputeRollingSums(reader, rollingPeriodDays.Value);
            }
        }

        return trxns;
    }
}
 

核心逻辑
我已经分离出主要逻辑，因此更易于关注：

 // Given a SqlReader with transaction history data, computes / returns the rolling sums
private static List<TrxnRollingSum> ComputeRollingSums(SqlDataReader reader,
                                                        int rollingPeriodDays) {
    var startIndexOfRollingPeriod = 0;
    var rollingSumIndex = 0;
    var trxns = new List<TrxnRollingSum>();

    // Prior to the loop, initialize "next" to be the first transaction
    var nextTrxn = GetNextTrxn(reader, null);
    while (nextTrxn != null)
    {
        var currTrxn = nextTrxn;
        nextTrxn = GetNextTrxn(reader, currTrxn);
        trxns.Add(currTrxn);

        // If the next transaction is not the same product/date as the current
        // transaction, we can finalize the rolling sum for the current transaction
        // and all previous transactions for the same product/date
        var finalizeRollingSum = nextTrxn == null || (nextTrxn != null &&
                                (currTrxn.ProductId != nextTrxn.ProductId ||
                                currTrxn.TransactionDate != nextTrxn.TransactionDate));
        if (finalizeRollingSum)
        {
            // Advance the pointer to the first transaction (for the same product)
            // that occurs within the rolling period
            while (startIndexOfRollingPeriod < trxns.Count
                && trxns[startIndexOfRollingPeriod].TransactionDate <
                    currTrxn.TransactionDate.AddDays(-1 * rollingPeriodDays))
            {
                startIndexOfRollingPeriod++;
            }

            // Compute the rolling sum as the cumulative sum (for this product),
            // minus the cumulative sum for prior to the beginning of the rolling window
            var sumPriorToWindow = trxns[startIndexOfRollingPeriod].PrevSum;
            var rollingSum = currTrxn.ActualCost + currTrxn.PrevSum - sumPriorToWindow;
            // Fill in the rolling sum for all transactions sharing this product/date
            while (rollingSumIndex < trxns.Count)
            {
                trxns[rollingSumIndex++].RollingSum = rollingSum;
            }
        }

        // If this is the last transaction for this product, reset the rolling period
        if (nextTrxn != null && currTrxn.ProductId != nextTrxn.ProductId)
        {
            startIndexOfRollingPeriod = trxns.Count;
        }
    }

    return trxns;
}
 

Helpers
以下逻辑可以内联编写，但是将它们拆分成自己的方法时，它的读取要容易一些。

 private static TrxnRollingSum GetNextTrxn(SqlDataReader r, TrxnRollingSum currTrxn) {
    TrxnRollingSum nextTrxn = null;
    if (r.Read()) {
        nextTrxn = new TrxnRollingSum {
            ProductId = r.GetInt32(0),
            TransactionDate = r.GetDateTime(1),
            ReferenceOrderId = r.GetInt32(2),
            ActualCost = r.GetDouble(3),
            PrevSum = 0 };
        if (currTrxn != null) {
            nextTrxn.PrevSum = (nextTrxn.ProductId == currTrxn.ProductId)
                    ? currTrxn.PrevSum + currTrxn.ActualCost : 0;
        }
    }
    return nextTrxn;
}

// Represents the output to be returned
// Note that the ReferenceOrderId/PrevSum fields are for debugging only
private class TrxnRollingSum {
    public int ProductId { get; set; }
    public DateTime TransactionDate { get; set; }
    public int ReferenceOrderId { get; set; }
    public double ActualCost { get; set; }
    public double PrevSum { get; set; }
    public double RollingSum { get; set; }
}

// The function that generates the result data for each row
// (Such a function is mandatory for SQL CLR table-valued functions)
public static void RollingSum_Fill(object trxnWithRollingSumObj,
                                    out int productId,
                                    out DateTime transactionDate, 
                                    out int referenceOrderId, out double actualCost,
                                    out double prevCumulativeSum,
                                    out double rollingSum) {
    var trxn = (TrxnRollingSum)trxnWithRollingSumObj;
    productId = trxn.ProductId;
    transactionDate = trxn.TransactionDate;
    referenceOrderId = trxn.ReferenceOrderId;
    actualCost = trxn.ActualCost;
    prevCumulativeSum = trxn.PrevSum;
    rollingSum = trxn.RollingSum;
}
 

在SQL中将它们结合在一起
到目前为止，所有内容都在C＃中，所以让我们看一下所涉及的实际SQL。 （或者，您可以使用此部署脚本直接从我的程序集的各个部分创建程序集，而不是自己编译。）

USE AdventureWorks2012; /* GPATTERSON2\SQL2014DEVELOPER */
GO

-- Enable CLR
EXEC sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

-- Create the assembly based on the dll generated by compiling the CLR project
-- I've also included the "assembly bits" version that can be run without compiling
CREATE ASSEMBLY ClrPlayground
-- See http://pastebin.com/dfbv1w3z for a "from assembly bits" version
FROM 'C:\FullPathGoesHere\ClrPlayground\bin\Debug\ClrPlayground.dll'
WITH PERMISSION_SET = safe;
GO

--Create a function from the assembly
CREATE FUNCTION dbo.RollingSumTvf (@rollingPeriodDays INT)
RETURNS TABLE ( ProductId INT, TransactionDate DATETIME, ReferenceOrderID INT,
                ActualCost FLOAT, PrevCumulativeSum FLOAT, RollingSum FLOAT)
-- The function yields rows in order, so let SQL Server know to avoid an extra sort
ORDER (ProductID, TransactionDate, ReferenceOrderID)
AS EXTERNAL NAME ClrPlayground.UserDefinedFunctions.RollingSumTvf;
GO

-- Now we can actually use the TVF!
SELECT * 
FROM dbo.RollingSumTvf(45) 
ORDER BY ProductId, TransactionDate, ReferenceOrderId
GO

注意事项
CLR方法为优化算法提供了更大的灵活性，它可能由C＃专家进一步调整。但是，CLR策略也有缺点。请记住以下几点：

这种CLR方法会将数据集的副本保存在内存中。可以使用流方法，但是我遇到了最初的困难，发现存在一个突出的Connect问题，抱怨SQL 2008+中的更改使使用这种方法更加困难。仍然可能（如Paul所示），但是需要更高的权限，方法是将数据库设置为TRUSTWORTHY并将EXTERNAL_ACCESS授予CLR程序集。因此，存在一些麻烦和潜在的安全隐患，但是这种收益是一种流方法，比AdventureWorks上的方法可以更好地扩展到更大的数据集。
某些DBA可能无法访问CLR，因此该功能更多黑盒，它不那么透明，不那么容易修改，不那么容易部署，甚至不那么容易调试。与T-SQL方法相比，这是一个很大的缺点。

奖金：T-SQL＃2-在尝试思考问题后我将实际使用的实用方法
有创意的一段时间，我想我还会发布一种相当简单，实用的方法，如果我的日常工作中遇到这个问题，我可能会选择解决这个问题。它确实利用了SQL 2012+窗口功能，但没有以问题所希望的突破性方式使用：

-- Compute all running costs into a #temp table; Note that this query could simply read
-- from Production.TransactionHistory, but a CROSS APPLY by product allows the window 
-- function to be computed independently per product, supporting a parallel query plan
SELECT t.*
INTO #runningCosts
FROM Production.Product p
CROSS APPLY (
    SELECT t.ProductId, t.TransactionDate, t.ReferenceOrderId, t.ActualCost,
        -- Running sum of the cost for this product, including all ties on TransactionDate
        SUM(t.ActualCost) OVER (
            ORDER BY t.TransactionDate 
            RANGE UNBOUNDED PRECEDING) AS RunningCost
    FROM Production.TransactionHistory t
    WHERE t.ProductId = p.ProductId
) t
GO

-- Key the table in our output order
ALTER TABLE #runningCosts
ADD PRIMARY KEY (ProductId, TransactionDate, ReferenceOrderId)
GO

SELECT r.ProductId, r.TransactionDate, r.ReferenceOrderId, r.ActualCost,
    -- Cumulative running cost - running cost prior to the sliding window
    r.RunningCost - ISNULL(w.RunningCost,0) AS RollingSum45
FROM #runningCosts r
OUTER APPLY (
    -- For each transaction, find the running cost just before the sliding window begins
    SELECT TOP 1 b.RunningCost
    FROM #runningCosts b
    WHERE b.ProductId = r.ProductId
        AND b.TransactionDate < DATEADD(DAY, -45, r.TransactionDate)
    ORDER BY b.TransactionDate DESC
) w
ORDER BY r.ProductId, r.TransactionDate, r.ReferenceOrderId
GO

这实际上产生了一个相当简单的总体查询计划，即使同时考虑了两者以下是两个相关查询计划中的一个：


我喜欢这种方法的一些原因：

它产生问题语句中要求的完整结果集（与大多数其他T-SQL解决方案相反，后者返回结果的分组版本。
很容易解释，理解和调试。我一年后不会再来了，不知道如何在不破坏正确性或性能的情况下做出一点小改动
它在提供的数据集上运行在900ms左右，而不是原始循环的2700ms- seek
如果数据密集得多（每天有更多事务），则计算复杂度不会随着滑动窗口中事务的数量平方增加（就像原始查询那样）；我认为这解决了Paul担心要避免多次扫描的部分问题
由于新的tempdb惰性写入功能，在SQL 2012+的最新更新中基本上没有tempdb I / O导致
非常大的数据集，如果要考虑存储压力，则将每个产品的工作分为几批是很简单的

以下几个潜在的警告：

确实只扫描Production.TransactionHistory一次，但这并不是真正的“一次扫描”方法，因为#temp表的大小相似，并且还需要对该表执行附加的逻辑I / O。但是，我认为这与工作表没有太大的区别，因为我们已经定义了它的精确结构，因此可以对其进行更多的手动控制
根据您的环境，可以将tempdb的使用视为积极的（例如，它在一组单独的SSD驱动器上）或为负（服务器上的高并发性，已经有很多tempdb争用）

#2 楼

这是一个很长的答案，所以我决定在此处添加一个摘要。首先，我提出一种解决方案，该解决方案以与问题中相同的顺序产生完全相同的结果。它扫描主表3次：获取带有每个产品日期范围的ProductIDs列表，总结每天的成本（因为有多个具有相同日期的交易），将结果与原始行合并。 br />接下来，我比较了两种简化任务并避免最后扫描主表的方法。他们的结果是每日摘要，即，如果某产品的多个交易具有相同的日期，则将它们汇总为一行。我上一步中的方法对表进行了两次扫描。 Geoff Patterson的方法对表进行了一次扫描，因为他使用了有关日期范围和产品列表的外部知识。
最后，我提出了单遍解决方案，该解决方案再次返回每日摘要，但不需要外部有关日期范围或ProductIDs列表的知识。我将使用AdventureWorks2014数据库和SQL Server Express2014。

对原始数据库的更改：


将[Production].[TransactionHistory].[TransactionDate]的类型从datetime更改为date。时间分量仍然为零。
添加日历表[dbo].[Calendar]

向[Production].[TransactionHistory]添加索引


。

CREATE TABLE [dbo].[Calendar]
(
    [dt] [date] NOT NULL,
    CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED 
(
    [dt] ASC
))

CREATE UNIQUE NONCLUSTERED INDEX [i] ON [Production].[TransactionHistory]
(
    [ProductID] ASC,
    [TransactionDate] ASC,
    [ReferenceOrderID] ASC
)
INCLUDE ([ActualCost])

-- Init calendar table
INSERT INTO dbo.Calendar (dt)
SELECT TOP (50000)
    DATEADD(day, ROW_NUMBER() OVER (ORDER BY s1.[object_id])-1, '2000-01-01') AS dt
FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
OPTION (MAXDOP 1);

关于OVER子句的MSDN文章链接到Itzik Ben-Gan关于窗口函数的出色博客文章。他在那篇文章中解释了OVER的工作原理，ROWS和RANGE选项之间的区别，并提到了在日期范围内计算滚动总和的问题。他提到当前版本的SQL Server不能完全实现RANGE，也不能实现时间间隔数据类型。他对ROWS和RANGE之间差异的解释给了我一个主意。

没有空隙和重复的日期

如果TransactionHistory表包含没有空格且没有重复的日期，则以下查询将产生正确的结果：实际上，一个45行的窗口将恰好覆盖45天。

有空白且没有重复的日期

很遗憾，我们的数据中有空白。为了解决这个问题，我们可以使用Calendar表来生成一组没有间隔的日期，然后将LEFT JOIN的原始数据添加到该数据集中，并使用与ROWS BETWEEN 45 PRECEDING AND CURRENT ROW相同的查询。仅当日期不重复时（在同一ProductID内），这才会产生正确的结果。不幸的是，我们的数据在日期和日期上都有间隙可以在同一ProductID中重复。为了解决这个问题，我们可以用原始数据GROUP生成一组没有重复的日期。然后，使用ProductID, TransactionDate表生成一组没有间隔的日期。然后，我们可以使用带有Calendar的查询来计算滚动ROWS BETWEEN 45 PRECEDING AND CURRENT ROW。这将产生正确的结果。请参阅下面查询中的注释。

SELECT
    TH.ProductID,
    TH.TransactionDate,
    TH.ActualCost,
    RollingSum45 = SUM(TH.ActualCost) OVER (
        PARTITION BY TH.ProductID
        ORDER BY TH.TransactionDate
        ROWS BETWEEN 
            45 PRECEDING
            AND CURRENT ROW)
FROM Production.TransactionHistory AS TH
ORDER BY
    TH.ProductID,
    TH.TransactionDate,
    TH.ReferenceOrderID;

我确认此查询产生的结果与使用子查询的问题的处理方法相同。

执行计划



第一个查询使用子查询，第二个查询使用这种方法。您可以看到这种方法的持续时间和读取次数要少得多。这种方法中估计成本的大部分是最终的SUM，请参见下文。



子查询方法具有嵌套循环和ORDER BY复杂度的简单计划。 br />

这种方法的计划扫描O(n*n)几次，但是没有循环。如您所见，估计成本的70％以上是最终TransactionHistory的Sort。



顶部结果-ORDER BY，底部-subquery。


避免额外扫描

上面计划中的最后一个索引扫描，合并联接和排序是由带有原始表的最终OVER引起的，以使最终结果与使用子查询的慢速方法完全相同。返回的行数与INNER JOIN表中的行数相同。当同一产品在同一天发生数笔交易时，在TransactionHistory中存在行。如果可以只在结果中显示每日摘要，则可以删除最后的TransactionHistory，查询变得更简单，更快。上一个计划中的最后一个索引扫描，合并联接和排序已替换为过滤器，该过滤器删除了JOIN添加的行。 >仍然，对Calendar进行两次扫描。需要进行一次额外的扫描才能获取每种产品的日期范围。我很想知道它与另一种方法的比较，在另一种方法中，我们使用有关TransactionHistory中全球日期范围的外部知识，外加具有所有TransactionHistory的额外表Product来避免该额外扫描。我从此查询中删除了每天交易数的计算，以使比较有效。可以在两个查询中都添加它，但为了简化比较，我想使其保持简单。我还必须使用其他日期，因为我使用的是2014年版本的数据库。

WITH

-- calculate Start/End dates for each product
CTE_Products
AS
(
    SELECT TH.ProductID
        ,MIN(TH.TransactionDate) AS MinDate
        ,MAX(TH.TransactionDate) AS MaxDate
    FROM [Production].[TransactionHistory] AS TH
    GROUP BY TH.ProductID
)

-- generate set of dates without gaps for each product
,CTE_ProductsWithDates
AS
(
    SELECT CTE_Products.ProductID, C.dt
    FROM
        CTE_Products
        INNER JOIN dbo.Calendar AS C ON
            C.dt >= CTE_Products.MinDate AND
            C.dt <= CTE_Products.MaxDate
)

-- generate set of dates without duplicates for each product
-- calculate daily cost as well
,CTE_DailyCosts
AS
(
    SELECT TH.ProductID, TH.TransactionDate, SUM(ActualCost) AS DailyActualCost
    FROM [Production].[TransactionHistory] AS TH
    GROUP BY TH.ProductID, TH.TransactionDate
)

-- calculate rolling sum over 45 days
,CTE_Sum
AS
(
    SELECT
        CTE_ProductsWithDates.ProductID
        ,CTE_ProductsWithDates.dt
        ,CTE_DailyCosts.DailyActualCost
        ,SUM(CTE_DailyCosts.DailyActualCost) OVER (
            PARTITION BY CTE_ProductsWithDates.ProductID
            ORDER BY CTE_ProductsWithDates.dt
            ROWS BETWEEN 45 PRECEDING AND CURRENT ROW) AS RollingSum45
    FROM
        CTE_ProductsWithDates
        LEFT JOIN CTE_DailyCosts ON 
            CTE_DailyCosts.ProductID = CTE_ProductsWithDates.ProductID AND
            CTE_DailyCosts.TransactionDate = CTE_ProductsWithDates.dt
)

-- remove rows that were added by Calendar, which fill the gaps in dates
-- add back duplicate dates that were removed by GROUP BY
SELECT
    TH.ProductID
    ,TH.TransactionDate
    ,TH.ActualCost
    ,CTE_Sum.RollingSum45
FROM
    [Production].[TransactionHistory] AS TH
    INNER JOIN CTE_Sum ON
        CTE_Sum.ProductID = TH.ProductID AND
        CTE_Sum.dt = TH.TransactionDate
ORDER BY
    TH.ProductID
    ,TH.TransactionDate
    ,TH.ReferenceOrderID
;

两个查询均以相同的顺序返回相同的结果。

比较

这里是时间和IO统计信息。





两次扫描变体要快一些，并且读取次数更少，因为一次扫描变体必须大量使用Worktable。此外，单扫描变体生成的行超出了计划中所需要的数量。即使ProductIDs没有任何交易，它也会为ProductID表中的每个Product生成日期。 ProductID表中有504行，但是Product中只有441个产品有交易。而且，它会为每种产品生成相同的日期范围，这超出了所需范围。如果TransactionHistory的总体历史较长，而每个产品的历史较短，则多余的行数甚至会更高。

另一方面，可以优化两次扫描通过仅在TransactionHistory上创建另一个更窄的索引来进一步扩展。该索引将用于
计算每种产品的开始/结束日期（(ProductID, TransactionDate)），并且页面的数量少于覆盖索引的页面，因此导致读取次数减少。

因此，我们可以选择，要么进行额外的显式简单扫描，要么具有隐式工作表。

BTW，如果可以只包含每日摘要的结果是可以的，那么最好创建一个不包含以下内容的索引： t包括CTE_Products。它将使用更少的页面=>更少的IO。

WITH
-- two scans
-- calculate Start/End dates for each product
CTE_Products
AS
(
    SELECT TH.ProductID
        ,MIN(TH.TransactionDate) AS MinDate
        ,MAX(TH.TransactionDate) AS MaxDate
    FROM [Production].[TransactionHistory] AS TH
    GROUP BY TH.ProductID
)

-- generate set of dates without gaps for each product
,CTE_ProductsWithDates
AS
(
    SELECT CTE_Products.ProductID, C.dt
    FROM
        CTE_Products
        INNER JOIN dbo.Calendar AS C ON
            C.dt >= CTE_Products.MinDate AND
            C.dt <= CTE_Products.MaxDate
)

-- generate set of dates without duplicates for each product
-- calculate daily cost as well
,CTE_DailyCosts
AS
(
    SELECT TH.ProductID, TH.TransactionDate, SUM(ActualCost) AS DailyActualCost
    FROM [Production].[TransactionHistory] AS TH
    GROUP BY TH.ProductID, TH.TransactionDate
)

-- calculate rolling sum over 45 days
,CTE_Sum
AS
(
    SELECT
        CTE_ProductsWithDates.ProductID
        ,CTE_ProductsWithDates.dt
        ,CTE_DailyCosts.DailyActualCost
        ,SUM(CTE_DailyCosts.DailyActualCost) OVER (
            PARTITION BY CTE_ProductsWithDates.ProductID
            ORDER BY CTE_ProductsWithDates.dt
            ROWS BETWEEN 45 PRECEDING AND CURRENT ROW) AS RollingSum45
    FROM
        CTE_ProductsWithDates
        LEFT JOIN CTE_DailyCosts ON 
            CTE_DailyCosts.ProductID = CTE_ProductsWithDates.ProductID AND
            CTE_DailyCosts.TransactionDate = CTE_ProductsWithDates.dt
)

-- remove rows that were added by Calendar, which fill the gaps in dates
SELECT
    CTE_Sum.ProductID
    ,CTE_Sum.dt AS TransactionDate
    ,CTE_Sum.DailyActualCost
    ,CTE_Sum.RollingSum45
FROM CTE_Sum
WHERE CTE_Sum.DailyActualCost IS NOT NULL
ORDER BY
    CTE_Sum.ProductID
    ,CTE_Sum.dt
;

使用CROSS APPLY的单遍解决方案

答案，但这是又一个变体，它仅再次返回每日摘要，但是它仅扫描一次数据，并且不需要外部日期范围或ProductID列表的知识。它也不做中间排序。总体性能与以前的变体相似，但似乎有些差。

主要思想是使用数字表来生成可填补日期空白的行。对于每个现有日期，请使用ReferenceOrderID以天为单位计算间隔的大小，然后使用LEAD将所需的行数添加到结果集中。刚开始，我用一个永久的数字表尝试过。该计划在此表中显示了大量读取，尽管实际持续时间几乎与我使用CROSS APPLY快速生成数字时相同。

DECLARE @minAnalysisDate DATE = '2013-07-31', 
-- Customizable start date depending on business needs
        @maxAnalysisDate DATE = '2014-08-03'  
-- Customizable end date depending on business needs
SELECT 
    -- one scan
    ProductID, TransactionDate, ActualCost, RollingSum45
--, NumOrders
FROM (
    SELECT ProductID, TransactionDate, 
    --NumOrders, 
    ActualCost,
        SUM(ActualCost) OVER (
                PARTITION BY ProductId ORDER BY TransactionDate 
                ROWS BETWEEN 45 PRECEDING AND CURRENT ROW
            ) AS RollingSum45
    FROM (
        -- The full cross-product of products and dates, 
        -- combined with actual cost information for that product/date
        SELECT p.ProductID, c.dt AS TransactionDate,
            --COUNT(TH.ProductId) AS NumOrders, 
            SUM(TH.ActualCost) AS ActualCost
        FROM Production.Product p
        JOIN dbo.calendar c
            ON c.dt BETWEEN @minAnalysisDate AND @maxAnalysisDate
        LEFT OUTER JOIN Production.TransactionHistory TH
            ON TH.ProductId = p.productId
            AND TH.TransactionDate = c.dt
        GROUP BY P.ProductID, c.dt
    ) aggsByDay
) rollingSums
--WHERE NumOrders > 0
WHERE ActualCost IS NOT NULL
ORDER BY ProductID, TransactionDate
-- MAXDOP 1 to avoid parallel scan inflating the scan count
OPTION (MAXDOP 1);

该计划是“较长的”，因为查询使用两个窗口函数（CTE和LEAD）。 br />

#3 楼

一种替代的SQLCLR解决方案，执行速度更快，所需的内存更少：

部署脚本

这需要设置EXTERNAL_ACCESS权限，因为它使用到目标服务器和数据库的环回连接，而不是（慢速）上下文连接。这是如何调用该函数的方法：

SELECT 
    RS.ProductID,
    RS.TransactionDate,
    RS.ActualCost,
    RS.RollingSum45
FROM dbo.RollingSum
(
    N'.\SQL2014',           -- Instance name
    N'AdventureWorks2012'   -- Database name
) AS RS 
ORDER BY
    RS.ProductID,
    RS.TransactionDate,
    RS.ReferenceOrderID;

以与问题相同的顺序产生完全相同的结果。

执行计划：







分析器逻辑读取：481

此实现的主要优点是它比使用上下文连接更快，并且使用的内存更少。它一次只能将两件事保存在内存中：


任何重复的行（相同的产品和交易日期）。这是必需的，因为在产品或日期更改之前，我们不知道最终的总金额是多少。在样本数据中，产品和日期的组合包含64行。
当前产品的成本和交易日期的滑动范围仅为45天。调整离开45天滑动窗口的行的简单运行总和是必要的。

这种最少的缓存应确保此方法可扩展。肯定比尝试将整个输入集保留在CLR内存中要好。

源代码

#4 楼

如果您使用的是SQL Server 2014 64位企业版，开发人员版或评估版，则可以使用内存中OLTP。该解决方案将不会是一次扫描，并且几乎不会使用任何窗口函数，但是它可能会增加此问题的价值，并且所使用的算法可能会被其他解决方案用作启发。

首先您需要在AdventureWorks数据库上启用内存中OLTP。

alter database AdventureWorks2014 
  add filegroup InMem contains memory_optimized_data;

alter database AdventureWorks2014 
  add file (name='AW2014_InMem', 
            filename='D:\SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AW2014') 
    to filegroup InMem;

alter database AdventureWorks2014 
  set memory_optimized_elevate_to_snapshot = on;

该过程的参数是内存中表变量，必须将其定义为类型。

create type dbo.TransHistory as table
(
  ID int not null,
  ProductID int not null,
  TransactionDate datetime not null,
  ReferenceOrderID int not null,
  ActualCost money not null,
  RunningTotal money not null,
  RollingSum45 money not null,

  -- Index used in while loop
  index IX_T1 nonclustered hash (ID) with (bucket_count = 1000000),

  -- Used to lookup the running total as it was 45 days ago (or more)
  index IX_T2 nonclustered (ProductID, TransactionDate desc)
) with (memory_optimized = on);

ID在此表中不是唯一的，它对于ProductID和TransactionDate的每个组合都是唯一的。

该过程告诉您它做了什么，但总的来说，它是循环计算运行总计，对于每次迭代，它都会查找运行总计（如45天或更早）。

当前的总运行量减去45天前的总运行量就是我们正在寻找的45天的总和。

create procedure dbo.GetRolling45
  @TransHistory dbo.TransHistory readonly
with native_compilation, schemabinding, execute as owner as
begin atomic with(transaction isolation level = snapshot, language = N'us_english')

  -- Table to hold the result
  declare @TransRes dbo.TransHistory;

  -- Loop variable
  declare @ID int = 0;

  -- Current ProductID
  declare @ProductID int = -1;

  -- Previous ProductID used to restart the running total
  declare @PrevProductID int;

  -- Current transaction date used to get the running total 45 days ago (or more)
  declare @TransactionDate datetime;

  -- Sum of actual cost for the group ProductID and TransactionDate
  declare @ActualCost money;

  -- Running total so far
  declare @RunningTotal money = 0;

  -- Running total as it was 45 days ago (or more)
  declare @RunningTotal45 money = 0;

  -- While loop for each unique occurence of the combination of ProductID, TransactionDate
  while @ProductID <> 0
  begin
    set @ID += 1;
    set @PrevProductID = @ProductID;

    -- Get the current values
    select @ProductID = min(ProductID),
           @TransactionDate = min(TransactionDate),
           @ActualCost = sum(ActualCost)
    from @TransHistory 
    where ID = @ID;

    if @ProductID <> 0
    begin
      set @RunningTotal45 = 0;

      if @ProductID <> @PrevProductID
      begin
        -- New product, reset running total
        set @RunningTotal = @ActualCost;
      end
      else
      begin
        -- Same product as last row, aggregate running total
        set @RunningTotal += @ActualCost;

        -- Get the running total as it was 45 days ago (or more)
        select top(1) @RunningTotal45 = TR.RunningTotal
        from @TransRes as TR
        where TR.ProductID = @ProductID and
              TR.TransactionDate < dateadd(day, -45, @TransactionDate)
        order by TR.TransactionDate desc;

      end;

      -- Add all rows that match ID to the result table
      -- RollingSum45 is calculated by using the current running total and the running total as it was 45 days ago (or more)
      insert into @TransRes(ID, ProductID, TransactionDate, ReferenceOrderID, ActualCost, RunningTotal, RollingSum45)
      select @ID, 
             @ProductID, 
             @TransactionDate, 
             TH.ReferenceOrderID, 
             TH.ActualCost, 
             @RunningTotal, 
             @RunningTotal - @RunningTotal45
      from @TransHistory as TH
      where ID = @ID;

    end
  end;

  -- Return the result table to caller
  select TR.ProductID, TR.TransactionDate, TR.ReferenceOrderID, TR.ActualCost, TR.RollingSum45
  from @TransRes as TR
  order by TR.ProductID, TR.TransactionDate, TR.ReferenceOrderID;

end;

调用这样的过程。

-- Parameter to stored procedure GetRollingSum
declare @T dbo.TransHistory;

-- Load data to in-mem table
-- ID is unique for each combination of ProductID, TransactionDate
insert into @T(ID, ProductID, TransactionDate, ReferenceOrderID, ActualCost, RunningTotal, RollingSum45)
select dense_rank() over(order by TH.ProductID, TH.TransactionDate),
       TH.ProductID, 
       TH.TransactionDate, 
       TH.ReferenceOrderID,
       TH.ActualCost,
       0, 
       0
from Production.TransactionHistory as TH;

-- Get the rolling 45 days sum
exec dbo.GetRolling45 @T;

在我的计算机上进行测试，客户端统计报告的总执行时间约为750毫秒。为了进行比较，子查询版本需要3.5秒。

额外的麻烦：

常规T-SQL也可以使用此算法。使用range（而非行）计算运行总计，并将结果存储在临时表中。然后，您可以查询该表，并将其与45天前的运行总计进行自我连接，然后计算滚动总和。但是，与range相比，rows的实现速度相当慢，原因是需要以不同的方式对待order by子句的重复项，因此我无法通过这种方法获得所有的良好性能。解决此问题的方法可能是在使用last_value()来模拟rows运行总计的计算的运行总计上使用另一个窗口函数，例如range。另一种方法是使用max() over()。两者都有一些问题。使用max() over()版本找到用于避免排序和避免线轴的适当索引。我放弃了优化这些事情，但是如果您对到目前为止的代码感兴趣，请告诉我。

#5 楼

很好玩:)我的解决方案比@GeoffPatterson的解决方案要慢一些，但是部分原因是我要回溯到原始表以消除Geoff的一个假设（即每个产品/日期对一行） 。我假设这是最终查询的简化版本，并且可能需要原始表之外的其他信息。

注意：我借用了Geoff的日历表，实际上最后得到了一个非常好的结果。类似的解决方案：

-- Build calendar table for 2000 ~ 2020
CREATE TABLE dbo.calendar (d DATETIME NOT NULL CONSTRAINT PK_calendar PRIMARY KEY)
GO
DECLARE @d DATETIME = '1/1/2000'
WHILE (@d < '1/1/2021')
BEGIN
    INSERT INTO dbo.calendar (d) VALUES (@d)
    SELECT @d =  DATEADD(DAY, 1, @d)
END

这里是查询本身：

WITH myCTE AS (SELECT PP.ProductID, calendar.d AS TransactionDate, 
                    SUM(ActualCost) AS CostPerDate
                FROM Production.Product PP
                CROSS JOIN calendar
                LEFT OUTER JOIN Production.TransactionHistory PTH
                    ON PP.ProductID = PTH.ProductID
                    AND calendar.d = PTH.TransactionDate
                CROSS APPLY (SELECT MAX(TransactionDate) AS EndDate,
                                MIN(TransactionDate) AS StartDate
                            FROM Production.TransactionHistory) AS Boundaries
                WHERE calendar.d BETWEEN Boundaries.StartDate AND Boundaries.EndDate
                GROUP BY PP.ProductID, calendar.d),
    RunningTotal AS (
        SELECT ProductId, TransactionDate, CostPerDate AS TBE,
                SUM(myCTE.CostPerDate) OVER (
                    PARTITION BY myCTE.ProductID
                    ORDER BY myCTE.TransactionDate
                    ROWS BETWEEN 
                        45 PRECEDING
                        AND CURRENT ROW) AS RollingSum45
        FROM myCTE)
SELECT 
    TH.ProductID,
    TH.TransactionDate,
    TH.ActualCost,
    RollingSum45
FROM Production.TransactionHistory AS TH
JOIN RunningTotal
    ON TH.ProductID = RunningTotal.ProductID
    AND TH.TransactionDate = RunningTotal.TransactionDate
WHERE RunningTotal.TBE IS NOT NULL
ORDER BY
    TH.ProductID,
    TH.TransactionDate,
    TH.ReferenceOrderID;

基本上，我决定了最简单的方法要解决这个问题，是使用ROWS子句的选项。但这要求我每个ProductID，TransactionDate组合只能有一行，而不仅仅是那个，而且我必须每个ProductID和possible date只能有一行。我这样做是在CTE中结合了Product，calendar和TransactionHistory表。然后，我必须创建另一个CTE来生成滚动信息。我之所以必须这样做是因为，如果我将它直接加入到原始表中，则会得到行消除，从而导致结果失败。之后，只需将我的第二个CTE重新加入原始表即可。我确实添加了TBE列（将被消除）以摆脱在CTE中创建的空白行。另外，我在初始CTE中使用了CROSS APPLY来为我的日历表生成边界。

然后我添加了推荐的索引：

CREATE NONCLUSTERED INDEX [TransactionHistory_IX1]
ON [Production].[TransactionHistory] ([TransactionDate])
INCLUDE ([ProductID],[ReferenceOrderID],[ActualCost])

得到了最终的执行计划：





编辑：最后，我在日历表上添加了一个索引以加快性能以合理的幅度。

CREATE INDEX ix_calendar ON calendar(d)

#6 楼

我有一些不使用索引或引用表的替代解决方案。在您无权访问任何其他表且无法创建索引的情况下，它们可能很有用。当按TransactionDate进行分组时，仅通过一次数据传递和一个窗口函数，似乎确实可以获得正确的结果。但是，当您无法按TransactionDate进行分组时，我无法找出仅使用一个窗口函数的方法。不带覆盖索引的CPU时间为2808 ms，带覆盖索引的CPU时间为1950 ms。我正在使用AdventureWorks2014数据库和SQL Server Express 2014进行测试。让我们从何时可以按TransactionDate分组的解决方案开始。最近X天的运行总和也可以用以下方式表示：


行的运行总和=所有先前行的运行总和-先前所有行的运行总和日期在日期窗口之外。


在SQL中，表达这种情况的一种方法是制作数据的两个副本，第二个副本将成本乘以-1并相加X + 1天到日期列。计算所有数据的总和将实现上述公式。我将为一些示例数据显示此内容。以下是单个ProductID的一些示例日期。我将日期表示为数字，以便于计算。起始数据：

╔══════╦══════╗
║ Date ║ Cost ║
╠══════╬══════╣
║    1 ║    3 ║
║    2 ║    6 ║
║   20 ║    1 ║
║   45 ║   -4 ║
║   47 ║    2 ║
║   64 ║    2 ║
╚══════╩══════╝

添加第二个数据副本。第二份副本的日期增加了46天，成本乘以-1： >

╔══════╦══════╦═══════════╗
║ Date ║ Cost ║ CopiedRow ║
╠══════╬══════╬═══════════╣
║    1 ║    3 ║         0 ║
║    2 ║    6 ║         0 ║
║   20 ║    1 ║         0 ║
║   45 ║   -4 ║         0 ║
║   47 ║   -3 ║         1 ║
║   47 ║    2 ║         0 ║
║   48 ║   -6 ║         1 ║
║   64 ║    2 ║         0 ║
║   66 ║   -1 ║         1 ║
║   91 ║    4 ║         1 ║
║   93 ║   -2 ║         1 ║
║  110 ║   -2 ║         1 ║
╚══════╩══════╩═══════════╝

筛选出复制的行以获得所需的结果：

╔══════╦══════╦═══════════╦════════════╗
║ Date ║ Cost ║ CopiedRow ║ RunningSum ║
╠══════╬══════╬═══════════╬════════════╣
║    1 ║    3 ║         0 ║          3 ║
║    2 ║    6 ║         0 ║          9 ║
║   20 ║    1 ║         0 ║         10 ║
║   45 ║   -4 ║         0 ║          6 ║
║   47 ║   -3 ║         1 ║          3 ║
║   47 ║    2 ║         0 ║          5 ║
║   48 ║   -6 ║         1 ║         -1 ║
║   64 ║    2 ║         0 ║          1 ║
║   66 ║   -1 ║         1 ║          0 ║
║   91 ║    4 ║         1 ║          4 ║
║   93 ║   -2 ║         1 ║          0 ║
║  110 ║   -2 ║         1 ║          0 ║
╚══════╩══════╩═══════════╩════════════╝

以下SQL是一种实现方式上面的算法：

╔══════╦══════╦═══════════╦════════════╗
║ Date ║ Cost ║ CopiedRow ║ RunningSum ║
╠══════╬══════╬═══════════╬════════════╣
║    1 ║    3 ║         0 ║          3 ║
║    2 ║    6 ║         0 ║          9 ║
║   20 ║    1 ║         0 ║         10 ║
║   45 ║   -4 ║         0 ║          6 ║
║   47 ║    2 ║         0 ║          5 ║
║   64 ║    2 ║         0 ║          1 ║
╚══════╩══════╩═══════════╩════════════╝

在我的机器上，使用覆盖索引需要702毫秒的CPU时间，而没有索引需要734毫秒的CPU时间。查询计划可以在以下位置找到：https://www.brentozar.com/pastetheplan/?id=SJdCsGVSl

该解决方案的一个缺点是，在按以下方式订购时似乎存在不可避免的排序方式新的Date列。我不认为可以通过添加索引来解决这种问题，因为我们需要在执行排序之前合并两个数据副本。通过在ORDER BY中添加另一列，我可以消除查询末尾的排序。如果我按CopiedRow排序，我发现SQL Server将从排序中优化该列并执行显式排序。 TransactionDate要复杂得多。我将问题概括为同时需要按同一列进行划分和排序。 Paul提供的语法解决了该问题，因此使用SQL Server中可用的当前窗口函数来表达它是如此困难（如果不难表达就无需扩展该语法）也就不足为奇了。 >
如果我使用上面的查询而不进行分组，那么当有多个行具有相同的FilterFlag和TransactionDate时，我得到的滚动总和的值将不同。解决此问题的一种方法是执行与上述相同的运行总和计算，同时标记分区中的最后一行。可以使用ProductId（假设ProductId永远不会为NULL）完成此操作，而无需进行其他排序。对于最终的运行总和值，我使用TransactionDate作为窗口函数，将分区最后一行中的值应用于分区中的所有行。

WITH THGrouped AS 
(
    SELECT
    ProductID,
    TransactionDate,
    SUM(ActualCost) ActualCost
    FROM Production.TransactionHistory
    GROUP BY ProductID,
    TransactionDate
)
SELECT
ProductID,
TransactionDate,
ActualCost,
RollingSum45
FROM
(
    SELECT
    TH.ProductID,
    TH.ActualCost,
    t.TransactionDate,
    SUM(t.ActualCost) OVER (PARTITION BY TH.ProductID ORDER BY t.TransactionDate, t.OrderFlag) AS RollingSum45,
    t.OrderFlag,
    t.FilterFlag -- define this column to avoid another sort at the end
    FROM THGrouped AS TH
    CROSS APPLY (
        VALUES
        (TH.ActualCost, TH.TransactionDate, 1, 0),
        (-1 * TH.ActualCost, DATEADD(DAY, 46, TH.TransactionDate), 0, 1)
    ) t (ActualCost, TransactionDate, OrderFlag, FilterFlag)
) tt
WHERE tt.FilterFlag = 0
ORDER BY
tt.ProductID,
tt.TransactionDate,
tt.OrderFlag
OPTION (MAXDOP 1);

我的机器在没有覆盖索引的情况下花了2464ms的CPU时间。和以前一样，这似乎是不可避免的。查询计划可以在这里找到：https://www.brentozar.com/pastetheplan/?id=HyWxhGVBl

我认为上述查询还有改进的余地。当然，还有其他使用Windows函数获得所需结果的方法。