在 TSQL 中获取运行总计最大值的高性能方法
我们有一个交易表,其结构如下:
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 技巧。
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 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
(The TranxID grouping removes the issue caused by duplicate date values)
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.
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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
SQL Server
在计算运行总计方面很糟糕。这是针对您的查询(按日期分组)的解决方案:
您需要在
TranxDate
上有一个索引才能快速工作。SQL Server
sucks in calculating running totals.Here's a solution for your very query (which groups by dates):
You need to have an index on
TranxDate
for this to work fast.