代码之家 › 专栏 › 技术社区 › MartW

在tsql中执行获取正在运行的合计的最大值的方法

sql-server-2000 sql-server-2008 tsql sql-server

2

MartW · 技术社区 · 16 年前

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

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

Tranxamt可以是正的也可以是负的,所以这个字段(对于任何itemID)的运行总数将随着时间的推移而上下浮动。获取当前总数显然很简单,但我所追求的是一种性能上的方法,当发生这种情况时,可以获得运行总数和事务日期的最高值。请注意,tranxdate不是唯一的,并且由于某些回溯,因此id字段不一定与给定项的tranxdate的顺序相同。
目前,我们正在这样做(@tbltranx是一个仅包含给定项的事务的表变量):

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分组删除了由重复日期值引起的问题)

对于一个项目,这给出了发生这种情况时的最高总额和交易日期。我们只在应用程序更新相关条目并将该值记录到另一个表中以供报告时计算该值,而不是在数万条条目中运行该值。

问题是,是否可以用更好的方法来实现这一点,这样我们就可以在不落入RBAR陷阱(有些itemID有数百个条目)的情况下即时计算出这些值(对于多个条目而言)。如果是这样,那么是否可以对其进行调整以获得事务子集的最高值(基于上面未包括的TransactionTypeID)。我目前正在使用SQL Server 2000执行此操作,但SQL Server 2008将很快在这里接管,因此可以使用任何SQL Server技巧。

1 回复 | 直到 16 年前

1

3

Quassnoi 16 年前

SQL Server 在计算运行总数方面很吃力。

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

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 让它快速工作。