Optimization of running total calculation in SQL for multiple values per join condition
Posted
by
Kiril
on Stack Overflow
See other posts from Stack Overflow
or by Kiril
Published on 2013-10-28T09:52:14Z
Indexed on
2013/10/28
9:53 UTC
Read the original article
Hit count: 327
I have the following table (test_table):
date value
---------------
d1 10.0
d1 20.0
d2 60.0
d2 10.0
d2 -20.0
d3 40.0
I calculate the running total as follows. I use the same query twice, because first I need to calculate the values for a specifi date, and afterwards I can calculate the running total. Otherwise, joining the two tables where date is not unique, I would get too many results from the join:
SELECT t1.date, SUM(t2.value) AS total
FROM
(SELECT date, SUM(value) AS value FROM test_table GROUP BY date) AS t1
JOIN
(SELECT date, SUM(value) AS value FROM test_table GROUP BY date) AS t2
ON t1.date >= t2.date
GROUP BY t1.date
ORDER BY t1.date
This gives me (which is fine):
date total
-------------
d1 30.0
d2 80.0
d3 120.0
BUT, this query isn't very efficient, because I need to change conditions in two places, if necessary. In production, the test_table is a lot bigger (> 4 Mio. rows), and the query takes too much time to complete. Question: How can I avoid using the same query twice?
© Stack Overflow or respective owner
