When you are selecting aggregate functions such as SUM(), you should
define the window over which the function will be applied. To do this, you
specify the OVER() clause. If you pass no further info to OVER(), the window is
the whole set of input records. That is the default. But it is not too useful.
Instead you can pass a PARTITION BY inside the OVER clause to define the
column or columns that form the windows. If you pass a single column, then each
unique value for that column forms a window in which the aggregates function
will be applied.
If you only specify the PARTITION BY, the window is all records with the
same value in the column(s) you specify. However you can also add an ORDER BY
clause, which causes the window to operate differently. ORDER BY and column(s)
instruct the window to be all records with the same value in the specified
column up to the current row. For example, if your aggregate function is SUM(),
then PARTITION BY plus ORDER BY lets you for a running total within each value
for the column.
You can further control the window for aggregate
functions by adding a ROWS BETWEEN clause to the PARTITION BY and ORDER BY.
This allows you to define the starting row and ending row for your window.
These rows can be relative to the current row (e.g. 5 PRECEEDING) or absolute
(e.g. UNBOUNDED PRECEEDING).
