# Understanding aggregate window functions

Consider the following table:

``````CREATE TABLE T1
(
keycol INT         NOT NULL CONSTRAINT PK_T1 PRIMARY KEY,
col1   VARCHAR(10) NOT NULL
);

INSERT INTO T1 VALUES
(2, 'A'),(3, 'A'),
(5, 'B'),(7, 'B'),(11, 'B'),
(13, 'C'),(17, 'C'),(19, 'C'),(23, 'C');
``````

Currently, I am looking into window functions and am trying out aggregate window functions. Although I feel I understand how the windows are defined with the `OVER` and `PARITION` clauses, I am unsure how the aggregate window functions are being calculated, such as `AVG() OVER ()`.

I am looking to understand the following three queries.

``````-- Query 1
SELECT keycol, col1, AVG(keycol) OVER (PARTITION BY col1) AS RowAvg
FROM T1
``````
```keycol | col1 | RowAvg
-----: | :--- | -----:
2 | A    |      2
3 | A    |      2
5 | B    |      7
7 | B    |      7
11 | B    |      7
13 | C    |     18
17 | C    |     18
19 | C    |     18
23 | C    |     18
```
``````-- Query 2
SELECT keycol, col1, AVG(keycol) OVER (ORDER BY keycol) AS RowAvg
FROM T1
``````
```keycol | col1 | RowAvg
-----: | :--- | -----:
2 | A    |      2
3 | A    |      2
5 | B    |      3
7 | B    |      4
11 | B    |      5
13 | C    |      6
17 | C    |      8
19 | C    |      9
23 | C    |     11
```
``````-- Query 3
SELECT keycol, col1, AVG(keycol) OVER (PARTITION BY col1 ORDER BY keycol) AS RowAvg
FROM T1
``````
```keycol | col1 | RowAvg
-----: | :--- | -----:
2 | A    |      2
3 | A    |      2
5 | B    |      5
7 | B    |      6
11 | B    |      7
13 | C    |     13
17 | C    |     15
19 | C    |     16
23 | C    |     18
```

Query 1: I believe RowAvg should be the average of the rows for each col1 level. Are the numbers 2 and 7 the FLOOR of the average or is my understanding incorrect?

Query 2: I am not too sure what is being done to produce RowAvg here. As no PARTITION or framing is used here, I believe the window should be the entire table, is this correct? Also, how is the RowAvg being found?

Query 3: Is this finding the (FLOOR) average for each partition however doing this incrementally? That is, for row 1 of the first partition ('A'), we find the average of that row. Then, for row 2 of the first partition, we find the average of the first 2 rows.

General question: Does introducing `ORDER BY` into the aggregate window function perform the aggregate function 'consecutively' such as in queries 1 and 2? It is interesting to see that in query 1, `AVG` is performed to each partition as a whole, whereas in queries 1 and 2 the RowAvg's are almost different for each row.

• Note that `keycol` is integer. So the `AVG(keycol)` will return you integer value and not decimal value. If you wanted the `AVG` result in decimal, use `CAST()` or `CONVERT()` on `keycol` to `decimal` data type before `AVG` Dec 27, 2019 at 1:04
• Q1 is explained above. For to understand the results in Q2 and Q3 you must remember that the default window boundaries in the presence of `ORDER BY` in the window definition is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`, not all records in the partition. For example, in Q3 for `keycol=19` the AVG() is calculated from records `13,17,19`, the value `23` is out of default bounds for this record, and in Q2 for the same record all records except `23` are processed. Dec 27, 2019 at 5:21
• So explicit ROWS/RANGE specification will remove disambiguate. The most common question (from one who forget this) is "Why FIRST_VALUE() gives correct result for a partition in all its records whereas LAST_VALUE() gives current record value for each record?". Dec 27, 2019 at 5:29

I suggest you add a sum to understand what is going on:

``````SELECT col1
, keycol
, SUM(keycol)  OVER (PARTITION BY col1) AS mysum
, AVG(keycol) OVER (PARTITION BY col1) AS RowAvg
FROM T1
order by col1;
``````

Since you don't have an order by in your window, the aggregate applies to the whole partition:

``````col1    keycol  mysum   RowAvg
A       2       5       2
A       3       5       2
B       5       23      7
B       7       23      7
B       11      23      7
C       13      72      18
C       17      72      18
C       19      72      18
C       23      72      18
``````

I.e. for partition A, mysum = 2+3 for every row in the partition

If you use an ORDER by clause the aggregate is applied from the beginning to the current row:

``````SELECT keycol
, col1
, SUM(keycol)  OVER (ORDER BY keycol) AS mysum
, AVG(keycol) OVER (ORDER BY keycol) AS RowAvg
FROM T1
order by col1;
``````

since you don't have a partition, the whole result set is treated as 1 partition:

``````keycol  col1    mysum   RowAvg
2       A       2       2
3       A       5       2
5       B       10      3
7       B       17      4
11      B       28      5
...
``````

For first row (according to order by) mysum = 2, rowavg = 2/1 , second row mysum = 2+3, rowavg = 5/2 , third row mysum = 2+3+5 rowavg = 10/3 ...

As you can see the sum(...) becomes a cumulative sum

With both a partition and order, the aggregate applies to each partition, but with the behaviour described above:

``````SELECT keycol
, col1
, SUM(keycol)  OVER (PARTITION BY col1 ORDER BY keycol) AS mysum
, AVG(keycol) OVER (PARTITION BY col1 ORDER BY keycol) AS RowAvg
FROM T1
order by col1;

keycol  col1    mysum   RowAvg
2       A       2       2
3       A       5       2
5       B       5       5
7       B       12      6
...
``````

For A you get mysum 2, 2+3. For B it restarts so it becomes 5, 5+7,

In addition you can override the default behavior which is:

``````SELECT keycol
, col1
, SUM(keycol)  OVER (PARTITION BY col1
ORDER BY keycol
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as ...
FROM T1
order by col1;
``````

by declaring your own, for example if you want a sliding average over 3 rows:

``````SELECT keycol
, col1
, AVG(keycol)  OVER (PARTITION BY col1
ORDER BY keycol
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as ...

...
13  C   15
17  C   16
19  C   19
23  C   21
``````

The first row gets an average of (13+17)/2 (since there is no preceding row), the second (13+17+19)/3, ... and the fourth row becomes (19+23)/2 (no rows following)

Fiddle with examples