What is the most efficient SQL statement to create running totals in Advantage SQL
I would like to create a running total query statement in Advantage SQL and wonder what is the most efficient way. I am able to get the data with a query like this:
SELECT a.fiscal,sum(a.amount) periodTotal,(SELECT sum(b.amount)
FROM tableName b WHERE b.fiscal<=a.fiscal) runningTotal
FROM tableName a GROUP BY a.fiscal,runningTotal
but the dataset is large (over 1 million records) and so I'm wondering if this is the most efficient way to get this info.
The raw data in the table is like so:
Fiscal Account
Period ID Amount
====== ======= ======
1 Cash 1
1 Cash 开发者_如何转开发2
2 Cash 1
2 Cash 2
1 A/R 1
1 A/R 2
2 A/R 1
2 A/R 2
I would like the results to be like so:
Total
Fiscal Account For Running
Period ID Period Total
====== ======= ====== =======
1 Cash 3 3
2 Cash 3 6
1 A/R 3 3
2 A/R 3 6
The fastest way that I found is to use an intermediate table:
SELECT "Fiscal Period", "Account ID", Sum( amount ) PeriodTotal
INTO #temp
FROM TableName
GROUP BY 2, 1;
SELECT "Fiscal Period", "Account ID", PeriodTotal,
( SELECT Sum(PeriodTotal) FROM #temp b
WHERE b."Account ID" = a."Account ID"
And b."Fiscal Period" <= a."Fiscal Period" ) RunningTotal
FROM #temp a
Perhaps not the most efficient, but an SQL Cursor and iterate over the table?
精彩评论