Imagine a situation where you're managing a score keeping system for games. Providing the cumulative sum of scores to the players would be necessary for maintaining the system. In this context, running totals refer to the progress of a variable over time or across a sequence of events. For example, consider a table as follows:
Now, let's say that you want to obtain a running total of experience_gained column for each record. That is, you aim to calculate the cumulative sum of the column up to the current row, providing a continuously updated total as you traverse the result set. You can achieve this as follows:
This query calculates the running total as well as total experience gained for each Pokémon, considering the order of timestamps. In the query, lines 4 and 5 both using the SUM window function, but with different OVER clauses, resulting in different calculations. Let's break down the difference between these two lines:
- Line 4: "Running Total of Exp. Gained"
- This calculates the running total of the experience_gained column within partitions defined by pokemon.name. The ORDER BY gained_timestamp ensures that the running total is calculated in the chronological order of the gained_timestamp.
- Line 5: "Total Exp. Gained"
- This simply calculates the total experience gained for each Pokémon name separately. Unlike line 4, it does not consider order of the gained_timestamp column. Consequently, output is the sum of experience gained for each Pokémon across all records without regard to the order in which the experiences were gained.
Of course, you can use some other functions in conjunction with the OVER clause. For example, if you use AVG function, you'll obtain moving average of a time series column.
0 Comments