我们有一个交易表,其结构如下:
TranxID int (PK and Identity field)
ItemID int
TranxDate datetime
TranxAmt money
TranxAmt 可以是正数或负数,因此该字段(对于任何 ItemID)的运行总计将随着时间的推移而上下波动。获取当前总计显然很简单,但我追求的是一种在发生这种情况时获取运行总计和 TranxDate 的最高值的高性能方法。请注意,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分组消除了重复日期值引起的问题)
对于一个项目,这为我们提供了发生这种情况时的 HighestTotal 和 TranxDate。我们不是对数以万计的条目动态运行此值,而是仅在应用程序更新相关条目时计算该值,并将该值记录在另一个表中以供报告使用。
问题是,能否以更好的方式完成此操作,以便我们可以即时计算出这些值(一次针对多个项目),而不会陷入 RBAR 陷阱(某些 ItemID 有数百个条目)。如果是这样,那么是否可以对其进行调整以获得交易子集的最高值(基于上面未包括的 TransactionTypeID)。我目前正在使用 SQL Server 2000 执行此操作,但 SQL Server 2008 很快就会接管这里,因此可以使用任何 SQL Server 技巧。
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)
You need建立索引TranxDate
以便快速发挥作用。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)