在 TSQL 中获取运行总数最大值的高性能方法 [英] Performant way to get the maximum value of a running total in TSQL

问题描述

我们有一个交易表，其结构如下:

We have a table of transactions which is structured like the following :

TranxID    int (PK and Identity field)
ItemID     int
TranxDate  datetime
TranxAmt   money

TranxAmt 可以为正也可以为负，因此该字段的运行总数(对于任何 ItemID)将随着时间的推移而上下波动.获取当前总数显然很简单，但我所追求的是一种在发生这种情况时获取运行总数和 TranxDate 最大值的高性能方法.请注意，TranxDate 不是唯一的，并且由于某些回溯日期，ID 字段不一定与给定项目的 TranxDate 具有相同的顺序.
目前我们正在做这样的事情(@tblTranx 是一个表变量，只包含给定项目的交易):

TranxAmt can be positive or negative, so the running total of this field (for any ItemID) will go up and down as time goes by. Getting the current total is obviously simple, but what I'm after is a performant way of getting the highest value of the running total and the TranxDate when this occurred. Note that TranxDate is not unique, and due to some backdating the ID field is not necessarily in the same sequence as TranxDate for a given Item.
Currently we're doing something like this (@tblTranx is a table variable containing just the transactions for a given Item) :

SELECT Top 1 @HighestTotal = z.TotalToDate, @DateHighest = z.TranxDate
FROM
    (SELECT a.TranxDate, a.TranxID, Sum(b.TranxAmt) AS TotalToDate
    FROM @tblTranx AS a
    INNER JOIN @tblTranx AS b ON a.TranxDate >= b.TranxDate
    GROUP BY a.TranxDate, a.TranxID) AS z
ORDER BY z.TotalToDate DESC

(TranxID 分组消除了重复日期值引起的问题)

(The TranxID grouping removes the issue caused by duplicate date values)

对于一个项目，这为我们提供了发生这种情况时的 HighestTotal 和 TranxDate.我们不会针对数万个条目即时运行此程序，而是仅在应用更新相关条目时计算此值，并将该值记录在另一个表中以用于报告.

This, for one Item, gives us the HighestTotal and the TranxDate when this occurred. Rather than run this on the fly for tens of thousands of entries, we only calculate this value when the app updates the relevant entry and record the value in another table for use in reporting.

问题是，这是否可以以更好的方式完成，以便我们可以即时计算出这些值(一次针对多个项目)，而不会落入 RBAR 陷阱(某些 ItemID 有数百个条目).如果是这样，那么是否可以对其进行调整以获得事务子集的最高值(基于上面未包含的 TransactionTypeID).我目前正在使用 SQL Server 2000 执行此操作，但 SQL Server 2008 将很快接管这里，因此可以使用任何 SQL Server 技巧.

The question is, can this be done in a better way so that we can work out these values on the fly (for multiple items at once) without falling into the RBAR trap (some ItemIDs have hundreds of entries). If so, could this then be adapted to get the highest values of subsets of transactions (based on a TransactionTypeID not included above). I'm currently doing this with SQL Server 2000, but SQL Server 2008 will be taking over soon here so any SQL Server tricks can be used.

推荐答案

SQL Server 在计算运行总数方面很糟糕.

SQL Server sucks in calculating running totals.

这是您查询的解决方案(按日期分组):

Here's a solution for your very query (which groups by dates):

WITH    q AS
        (
        SELECT  TranxDate, SUM(TranxAmt) AS TranxSum
        FROM    t_transaction
        GROUP BY
                TranxDate
        ),
        m (TranxDate, TranxSum) AS
        (
        SELECT  MIN(TranxDate), SUM(TranxAmt)
        FROM    (
                SELECT  TOP 1 WITH TIES *
                FROM    t_transaction
                ORDER BY
                        TranxDate
                ) q
        UNION ALL
        SELECT  DATEADD(day, 1, m.TranxDate),
                m.TranxSum + q.TranxSum
        FROM    m
        CROSS APPLY
                (
                SELECT  TranxSum
                FROM    q
                WHERE   q.TranxDate = DATEADD(day, 1, m.TranxDate) 
                ) q
        WHERE   m.TranxDate <= GETDATE()
        )
SELECT  TOP 1 *
FROM    m
ORDER BY
        TranxSum DESC
OPTION (MAXRECURSION 0)

您需要在 TranxDate 上有一个索引以使其快速工作.

You need to have an index on TranxDate for this to work fast.

这篇关于在 TSQL 中获取运行总数最大值的高性能方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！