1

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.

  • 4
    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 – Squirrel Dec 27 '19 at 1:04
  • 2
    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. – Akina Dec 27 '19 at 5:21
  • 2
    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?". – Akina Dec 27 '19 at 5:29
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.