Archive for January 22nd, 2010
From Stack Overflow:
We have a table of transactions which is structured like the following :
transactions TranxID ItemID TranxDate TranxAmt
TranxAmtcan 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
TranxDatewhen this occurred.
Note that TranxDate is not unique.
SQL Server is a very nice system, but using it for calculating running totals is a pain.
Oracle supports additional clauses for analytic functions,
ROWS, which define the boundaries of the function’s windows and hence can be used to implement running totals. By default, it is just enough to omit the
RANGE clause to make the analytic function apply to the window of the records selected so far, thus transforming it to a running total.
SQL Server‘s support for window functions only extends aggregate capabilities a little so that the aggregate can be returned along with each record that constitutes the group. For functions like
COUNT it is impossible to control the window boundaries and the records order. Such analytic functions can not be used to calculate running totals.
The common way to write such a running total query is using a subquery or a self join which would count the
SUM of all previous records. However, the complexity of this query is
O(n^2) and it’s not usable for any real volumes of data.
This is one of the few cases when the cursors are faster than a set-based solution described above. But we all are aware of the drawbacks of cursors and better search for something else.
This task, fortunately, is a little more simple than it may seem, because it deals with dates. The number of all possible dates is usually limited and a recursive query can deal with this task quite efficiently.
Let’s create a sample table:
Read the rest of this entry »