There are often cases when for every row of ordered table you should count the sum of digit column values consist of rows, which are above or/and below with current in some order. So this is the task of reckon up running totals. For example, the table ordered by 'time' column:
time var 1 15 2 10 4 13 7 11
we shouls get the next table:
time var total_sum 1 15 15 2 10 25 4 13 38 7 11 49
Let's state the concrete task for the 'Painting' DB for the better understanding.
For every moment when the square q_id = 10 was painted we should get the sum of all spended paint at the moment.
Table 'utB' includes 'b_datetime' and 'b_vol' columns. We should count the sum of ' b_vol' in all rows for every value 'X' from the ' b_datetime' where the current time is b_datetime ≤ X.
Let's resolve the task with the two most popular ways.
This method supposed to do the running totals using the subquery. Being in a hurry you can do the next:
SELECT b_datetime,
(
SELECT SUM(T1.b_vol)
FROM utB T1
WHERE T1.b_datetime <= T2.b_datetime
AND T1.b_q_id = 10
) total_vol
FROM utB T2
WHERE b_q_id = 10;
However it's a wrong query! The cause is that the square #10 can be painted with the different spray cans so in such case we get duplicated rows:
b_datetime total_vol 20030101 01:12:31.000 255 20030101 01:12:31.000 255 20030101 01:12:33.000 265 20030101 01:12:34.000 275 20030101 01:12:35.000 520 20030101 01:12:36.000 765
This mistake can be fixed with adding DISTINCT to the first SELECT, but this time subquery will run for every row which have same 'b_datetime' values and only after that duplicates will be fixed. So we should fix duplicates before, for example:
SELECT b_datetime,
(
SELECT SUM(T1.b_vol)
FROM utB T1
WHERE T1.b_datetime <= T2.b_datetime
AND T1.b_q_id = 10
) total_vol
FROM
(
SELECT DISTINCT b_datetime fixing duplicates
FROM utB
WHERE b_q_id = 10
) T2;
The sence of method is that the table interflow itself with the condition ' X >= b_datetime'. Here 'X' should not be repeated. Otherwise duplicate rows will be counted multiple times in calculating the total sum. Then the sum of ' b_vol' is counted, ordered by ' b_datetime'. See the example below:
SELECT T2.b_datetime, SUM(T1.b_vol) total_vol
FROM utB T1
INNER JOIN
(
SELECT DISTINCT b_datetime fixing duplicates
FROM utB
WHERE b_q_id = 10 we contemplate only the square for which b_q_id = 10
) T2
ON T1.b_datetime <= T2.b_datetime
WHERE T1.b_q_id = 10
GROUP BY T2.b_datetime;
Below is incorrect result of the query where there is no DISTINCT in the table 'T1'.
b_datetime total_vol 20030101 01:12:31.000 510 20030101 01:12:33.000 265 20030101 01:12:34.000 275 20030101 01:12:35.000 520 20030101 01:12:36.000 765
This example was chosen specially to exceed the limits of theme because often you have to keep an eye on different nuances besides of counting running totals. Done and done.
Both methods demand multiple reading from table. It can be avoided by using numerical sequence generation. Let's reformulate the task to discourse forward:
We have to match the paint number in ascending order of 'b_datetime' to every moment the square 'q_id = 10' was painted. Also we should get the sum of all used paint for the square at the time it was painted for every numbers of paint.
Now, if we would use the first method we matched the tables with condition by using numder, but not time. It happens often. So the realisation of the first metod should be looked like this:
SELECT T2.rn, SUM(T1.b_vol) total_vol
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY b_datetime) rn,
SUM(b_vol)b_vol It's enough to count this only for one table
FROM utB WHERE b_q_id = 10
GROUP BY b_datetime fixing duplicates
)T1
INNER JOIN
(
SELECT ROW_NUMBER() OVER(ORDER BY b_datetime) rn
FROM utB WHERE b_q_id = 10
GROUP BY b_datetime
)T2
ON T1.rn <= T2.rn
GROUP BY T2.rn;
Here the ROW_NUMBER() function of TransactSQL is used for row numeration.
Notice that 'T2' table is just sequence of positive integers so it is not necessary to read 'utB' for creating it! It's enough to generate the sequence of positive integers. But we don't know how many numbers do we need to generate, otherwise we know that 'b_vol' is integer greater than zero and the quantity of paint for the one square can't be more than 765. So it will be enough to generate 765 numbers. You can get the quantity of numbers by the subquery, some times it's helpful and consist of the task. At the end we get such query:
SELECT T2.rn, SUM(T1.b_vol) total_vol
FROM
(
SELECT rn, b_vol, COUNT(*)OVER()cnt_rec counting amount of rows
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY b_datetime) rn,
SUM(b_vol)b_vol
FROM utB WHERE b_q_id = 10
GROUP BY b_datetime
)T
)T1
INNER JOIN
(
SELECT a + 10*b + 100*c rn
FROM
(SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8
UNION SELECT 9 UNION SELECT 10)AA,
(SELECT 0 b UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9)BB,
(SELECT 0 c UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
UNION SELECT 7)CC
)T2  sequence (1..800)
ON T1.rn <= T2.rn
AND T2.rn <= T1.cnt_rec  limiting the 'T2' rows amount using 'T1' rows amount
GROUP BY T2.rn;
Of course using such hook is not justified this time, but if we would have the table created by the complex resourceconsuming query instead of 'T1' table, the opportunity to avoid its selfmatching (double recalculation) will improve the performance. And this example was used just for simple explaining.
Home  SELECT exercises (rating stages)  DML exercises  Developers 
