2

The SQL Server documentation on the OVER clause HERE, mentions that the OVER clause:

Determines the partitioning and ordering of a rowset before the associated window function is applied

By this I assume that aggregate functions are 'window functions'? While I understand what a window is (or at least how it works) when using the OVER clause, what is the default window for an aggregation function when no OVER clause is used?

i.e. for the SQL statement:

SELECT
  someCol,
  someCol2,
  AVG(someCol3) [avg]
FROM
  tbl
GROUP BY
  someCol,
  someCol2

What is the Window that the SUM function is being applied to?

Is it the grouped table prior to the SUM function being applied? In this case is it correct to assume that all window functions are the last operations to be applied to a result set? Since FROM / WHERE / GROUP BY / HAVING / SELECT / ORDER BY must all have been worked out to produce the final window on which to apply aggregation functions?

====================

In response to the answer below by Max Vernon:

If I write this query:

SELECT
    CP.iYear,
    AVG(CP.mUpgradeCost) OVER(PARTITION BY iYear) AS [avg]
FROM ProForma.dbo.CapitalProject CP

I get the results you refer to, with a row for each year in CapitalProject, and since there are many repeated years I get the same average for each year. I.e.

+-------+------------+
| iYear | avg        |
+-------+------------+
| 0     | 18636.3636 |
+-------+------------+
| 0     | 18636.3636 |
+-------+------------+
| 1     | 46741.5151 |
+-------+------------+
| 1     | 46741.5151 |
+-------+------------+

I then changed the SQL to group on years so that I got a single line per year. the SQL is:

SELECT
    CP.iYear,
    AVG(AVG(CP.mUpgradeCost)) OVER(PARTITION BY iYear) AS [avg]
FROM ProForma.dbo.CapitalProject CP
GROUP BY CP.iYear

HOWEVER, when I altered the function to this:

SELECT
    CP.iYear,
    SUM(AVG(CP.mUpgradeCost)) OVER(PARTITION BY iYear) AS [avg]
FROM ProForma.dbo.CapitalProject CP
GROUP BY CP.iYear

I was expecting the averages for each row over the partitioned table to be added together. But they didn't. Why is that?

Clearly the window that the SUM is being applied to is not a nested query as i thought it might be

2

I don't think you understand what's going on from your interactions on another question which you linked here.

Search for this line in the question: AVG(AVG(CP.mUpgradeCost)) OVER(PARTITION BY iYear) AS [avg]. If I change that to SUM(AVG(CP.mUpgradeCost)) OVER(PARTITION BY iYear) AS [sum], I get the same value

So let's clarify a few things.

Namely

  1. Empty OVER ()
  2. SQL evaluation

Empty OVER()

what is the default window for an aggregation function when no OVER clause is used?

You require an OVER clause to have a window. And, as far as I know all sql variants require at least, OVER (). So the question, what does an OVER () do..

Well, a window is composed, per the 2011 spec as

  • The window name.
  • Optionally, the ordering window name—that is, the name of another window, called the ordering window, that is used to define the partitioning and ordering of the present window.
  • The window partitioning clause—that is, a if any is specified in either the present or in the window descriptor of the ordering window.
  • The window ordering clause—that is, a if any is specified in either the present or in the window descriptor of the ordering window.
  • The window framing clause—that is, a , if any.

All of these have defaults.

  • The order defaults to undefined
  • The partition defaults to the entire query
  • The name defaults to something undefined (platform-dependent) implicitly.

SQL Evaluation of GROUP BY and WINDOW

This shows the sum, avg, and count of three windows. Here you'll notice we have one window (by definition) over which each function operates, that window adheres to the defaults listed above: sum, avg, and count each show only one result because they're operating over unbounded unpartitioned result-sets.

SELECT x, sum(x) OVER (), avg(x) OVER (), count(x) OVER ()
FROM ( VALUES (1),(2),(3) ) AS t(x);

 x | sum |        avg         | count 
---+-----+--------------------+-------
 1 |   6 | 2.0000000000000000 |     3
 2 |   6 | 2.0000000000000000 |     3
 3 |   6 | 2.0000000000000000 |     3
(3 rows)

Here we create three groups, with one row in each group. We show the sum, avg, and count of those groups. Because each group previously had only one row, our result set is still three rows. GROUP BY didn't do much, but you'll notice that the aggregates only run over the group.

SELECT x, sum(x), avg(x), count(x)
FROM ( VALUES (1),(2),(3) ) AS t(x)
GROUP BY x;

 x | sum |          avg           | count 
---+-----+------------------------+-------
 1 |   1 | 1.00000000000000000000 |     1
 2 |   2 |     2.0000000000000000 |     1
 3 |   3 |     3.0000000000000000 |     1
(3 rows)

Here is where I think you're confused...

SELECT x, sum(sum(x)) OVER (), avg(avg(x)) OVER (), count(count(x)) OVER ()
FROM ( VALUES (1),(2),(3) ) AS t(x)
GROUP BY x;

 x | sum |          avg           | count 
---+-----+------------------------+-------
 1 |   6 | 2.00000000000000000000 |     3
 2 |   6 | 2.00000000000000000000 |     3
 3 |   6 | 2.00000000000000000000 |     3
(3 rows)

When we GROUP BY (x) with a window function, we add more complexity. In the above we still get the same result set, but we calculate first the sum, avg, and count over a GROUP with one row. Look at the query right above this one. Then after that runs, we calculate the window functions sum (1+2+3), avg (1+2+3)/2, and count 1+1+1 over a window which includes all the aggregates above. sum(sum(x)) OVER () finds the sum(x) for the group which is one row, and then after that's done processing the sum() OVER () for the window.

  • Thank you for you help. Looks like the group by is processed first - then the window functions are applied over that result. So with sum(sum(x)) OVER(), the inner sum(x) is applied over the grouped 'base' result, which will be three throws, with x = to the sum (one number summed per row - which is x). Then the outer sum() OVER is calculated, which sums those 3 values. – Zach Smith Mar 28 '17 at 14:21
4

Your example code:

SELECT
  someCol,
  someCol2,
  AVG(someCol3) [avg]
FROM
  tbl
GROUP BY
  someCol,
  someCol2

The "window" for the AVG(someCol3) column is the combination of unique values for each someCol, someCol2 tuple, by virtue of the GROUP BY clause.

You could rewrite the above query as:

SELECT
  someCol,
  someCol2,
  AVG(someCol3) OVER (PARTITION BY someCol, someCol2)
FROM
  tbl

The results would be similar, although not identical. The 2nd variant returns a result row for every row in the tbl table, whereas the 1st variant returns a result row for every unique combination of someCol, someCol2. In the 2nd result set, each row would contain the average value of someCol3 for each unique combination of someCol, someCol2 by virtue of the PARTITION BY clause.

You can see this by creating a test-bed:

IF OBJECT_ID('dbo.TestAgg') IS NOT NULL
DROP TABLE dbo.TestAgg;
CREATE TABLE dbo.TestAgg
(
    someCol int NOT NULL
    , someCol2 int NOT NULL
    , someCol3 decimal(10,5) NOT NULL
);

INSERT INTO dbo.TestAgg (someCol, someCol2, someCol3)
VALUES (1, 1, 1)
    , (2, 2, 2)
    , (2, 2, 3)
    , (4, 1, 4);

SELECT ta.someCol
    , ta.someCol2
    , AVG(ta.someCol3)
FROM dbo.TestAgg ta
GROUP BY ta.someCol
    , ta.someCol2
ORDER BY ta.someCol
    , ta.someCol2;

SELECT ta.someCol
    , ta.someCol2
    , AVG(ta.someCol3) OVER (PARTITION BY ta.someCol, ta.someCol2)
FROM dbo.TestAgg ta
ORDER BY ta.someCol
    , ta.someCol2;

The results:

+---------+----------+------------------+
| someCol | someCol2 | (No column name) |
+---------+----------+------------------+
|       1 |        1 |         1.000000 |
|       2 |        2 |         2.500000 |
|       4 |        1 |         4.000000 |
+---------+----------+------------------+


+---------+----------+------------------+
| someCol | someCol2 | (No column name) |
+---------+----------+------------------+
|       1 |        1 |         1.000000 |
|       2 |        2 |         2.500000 |
|       2 |        2 |         2.500000 |
|       4 |        1 |         4.000000 |
+---------+----------+------------------+

If you specify a blank OVER () clause, the "window" is the entire resultset:

SELECT ta.someCol
    , ta.someCol2
    , COUNT(ta.someCol3) OVER ()
    , COUNT(ta.someCol3) OVER (PARTITION BY ta.someCol, ta.someCol2)
    , AVG(ta.someCol3) OVER ()
    , AVG(ta.someCol3) OVER (PARTITION BY ta.someCol, ta.someCol2)
FROM dbo.TestAgg ta
+---------+----------+------------------+------------------+------------------+------------------+
| someCol | someCol2 | (No column name) | (No column name) | (No column name) | (No column name) |
+---------+----------+------------------+------------------+------------------+------------------+
|       1 |        1 |                4 |                1 |         2.500000 |         1.000000 |
|       2 |        2 |                4 |                2 |         2.500000 |         2.500000 |
|       2 |        2 |                4 |                2 |         2.500000 |         2.500000 |
|       4 |        1 |                4 |                1 |         2.500000 |         4.000000 |
+---------+----------+------------------+------------------+------------------+------------------+

You can perform multiple aggregates on a single column however, you cannot do it in a single SELECT ... statement; you must use a subquery or CTE to do so:

SELECT s.someCol
    , s.someCol2
    , SUM(avg_someCol3)
FROM (
    SELECT ta.someCol
        , ta.someCol2
        , avg_someCol3 = AVG(ta.someCol3) OVER (PARTITION BY ta.someCol, ta.someCol2)
    FROM dbo.TestAgg ta
    ) s
GROUP BY s.someCol
    , s.someCol2

Returns:

+---------+----------+------------------+
| someCol | someCol2 | (No column name) |
+---------+----------+------------------+
|       1 |        1 |         1.000000 |
|       2 |        2 |         5.000000 |
|       4 |        1 |         4.000000 |
+---------+----------+------------------+

However, in this contrived example, the value of 5 makes no sense that I can understand as the sum of an average. Perhaps your real problem makes more sense. Perhaps you really want this:

SELECT s.someCol
    , s.someCol2
    , avg_someCol3
FROM (
    SELECT ta.someCol
        , ta.someCol2
        , avg_someCol3 = AVG(ta.someCol3) OVER (PARTITION BY ta.someCol, ta.someCol2)
    FROM dbo.TestAgg ta
    ) s
GROUP BY s.someCol
    , s.someCol2
    , s.avg_someCol3

Results:

+---------+----------+--------------+
| someCol | someCol2 | avg_someCol3 |
+---------+----------+--------------+
|       1 |        1 |     1.000000 |
|       2 |        2 |     2.500000 |
|       4 |        1 |     4.000000 |
+---------+----------+--------------+

This is more easily obtained by using the DISTINCT keyword, although it make me shudder to think I just suggested using it:

SELECT DISTINCT ta.someCol
    , ta.someCol2
    , AVG(ta.someCol3) OVER (PARTITION BY ta.someCol, ta.someCol2)
FROM dbo.TestAgg ta
  • Thank you for your answer. I've updated my question to incorporate your answer – Zach Smith Mar 23 '17 at 20:33
  • I don't have a real use case for this. I'm trying to figure out the OVER statement. What's wrong with DISTINCT? – Zach Smith Mar 23 '17 at 20:54
  • 2
    There is nothing wrong with DISTINCT if you understand why you would want to use it, and you're not just using it as a crutch to eliminate "those extra rows that are showing up for some unknown reason". – Max Vernon Mar 23 '17 at 21:06

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.