VISUAL QUICKSTART GUIDE SQL Second Edition [Electronic resources] نسخه متنی

Calculating Running Statistics

A

running (or

cumulative )

statistic is a row-by-row calculation that uses progressively more data values, starting with a single value (the first value), continuing with more data values in the order in which they're supplied, and ending with all the values. A

running sum (total) and

running average (mean) are the most common running statistics.

Listing 9.10 calculates the running sum and running average of book sales, along with a cumulative count of data items. The query cross-joins two instances of the table titles, grouping the result by the first-table (t1) title IDs and limiting the second-table (t2) rows to ID values smaller than or equal to the t1 row to which they're joined. The intermediate cross-joined table, to which SUM(), AVG(), and COUNT() are applied, looks like this:

t1.id t1.sales t2.id t2.sales
----- -------- ----- --------
T01        566 T01   566
T02       9566 T01   566
T02       9566 T02   9566
T03      25667 T01   566
T03      25667 T02   9566
T03      25667 T03   25667
T04      13001 T01   566
T04      13001 T02   9566
T04      13001 T03   25667
T04      13001 T04   13001
T05     201440 T01   566
...

Note that the running statistics don't change for title T10 because its sales value is null. The ORDER BY clause is necessary because GROUP BY doesn't sort the result implicitly. See

Figure 9.10 for the result.

Figure 9.10. Result of Listing 9.10.

title_id RunSum  RunAvg RunCount
-------- ------- ------ --------
T01          566    566        1
T02        10132   5066        2
T03        35799  11933        3
T04        48800  12200        4
T05       250240  50048        5
T06       261560  43593        6
T07      1761760 251680        7
T08      1765855 220731        8
T09      1770855 196761        9
T10      1770855 196761        9
T11      1864978 186497       10
T12      1964979 178634       11
T13      1975446 164620       12

A

moving average is a way of smoothing a time series (such as a list of stock prices over time) by replacing each value by an average of that value and its nearest neighbors. Calculating a moving average is easy if you have a column that contains a sequence of integers or dates, such as in this table, named time_series:

seq price
--- -----
1  10.0
2  10.5
3  11.0
4  11.0
5  10.5
6  11.5
7  12.0
8  13.0
9  15.0
10  13.5
11  13.0
12  12.5
13  12.0
14  12.5
15  11.0

Listing 9.11 calculates the moving average of price. See

Figure 9.11 for the result. Each value in the result's moving-average column is the average of five values: the price in the current row and the prices in the four preceding rows (as ordered by seq). The first four rows are omitted because they don't have the required number of preceding values. You can adjust the values in the WHERE clause to cover any size averaging window. To make Listing 9.11 calculate a five-point moving average that averages each price with the two prices before it and the two prices after it, for example, change the WHERE clause to:

WHERE t1.seq >= 3
AND t1.seq <= 13
AND t1.seq BETWEEN t2.seq - 2 AND
t2.seq + 2

Figure 9.11. Result of Listing 9.11.

seq MovingAvg
--- ---------
5      10.6
6      10.9
7      11.2
8      11.6
9      12.4
10      13.0
11      13.3
12      13.4
13      13.2
14      12.7
15      12.2

If you have a table that already has running totals, you can calculate the differences between pairs of successive rows.

Listing 9.12 backs out the inter-city distances from the following table, named roadtrip, which contains the cumulative distances for each leg of a trip from Seattle, Washington to San Diego, California. See

Figure 9.12 for the result.

Figure 9.12. Result of Listing 9.12.

seq1 seq2 city1             city2             miles1 miles2 dist
---- ---- ----------------- ----------------- ------ ------ ----
1    2 Seattle, WA       Portland, OR           0    174  174
2    3 Portland, OR      San Francisco, CA    174    808  634
3    4 San Francisco, CA Monterey, CA         808    926  118
4    5 Monterey, CA      Los Angeles, CA      926   1251  325
5    6 Los Angeles, CA   San Diego, CA       1251   1372  121

seq city              miles
--- ----------------- -----
1 Seattle, WA           0
2 Portland, OR        174
3 San Francisco, CA   808
4 Monterey, CA        926
5 Los Angeles, CA    1251
6 San Diego, CA      1372

Listing 8.21 in Chapter 8 for another way to calculate a running statistic.