How does unbounded preceding and current row work exactly?

Question

I have the following data:

CREATE SCHEMA test_schema;
CREATE TABLE test_schema.joinedDf(id LONG, firstName TEXT, lastName TEXT, age INT, month INT, salary DECIMAL);

INSERT INTO test_schema.joinedDf(id, firstName, lastName, age, month, salary) VALUES
(1111, 'Peter', 'Ivanov', 29, 10, 300000),
(1111, 'Peter', 'Ivanov', 29, 12, 350000),
(1111, 'Peter', 'Ivanov', 29, 11, 350000);

When I do this:

SELECT id, firstName, lastName, age, month, salary,
AVG(salary) OVER (PARTITION BY id ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) average_salary
FROM test_schema.joinedDf;

I get this data:

1111, Peter, Ivanov, 29, 10, 300000, 333333.3333
1111, Peter, Ivanov, 29, 12, 350000, 333333.3333
1111, Peter, Ivanov, 29, 11, 350000, 333333.3333

But when I do:

SELECT id, firstName, lastName, age, month, salary,
AVG(salary) OVER (PARTITION BY id ORDER BY salary) average_salary
FROM test_schema.joinedDf;

I get:

1111, Peter, Ivanov, 29, 10, 300000, 300000.0000
1111, Peter, Ivanov, 29, 12, 350000, 333333.3333
1111, Peter, Ivanov, 29, 11, 350000, 333333.3333

I read that by default, if you specify order by inside the partition by clause. You get the window frame of unbounded preceding and current row. But why doesn't the data look like this?:

1111, Peter, Ivanov, 29, 10, 300000, 300000.0000
1111, Peter, Ivanov, 29, 12, 350000, 325000.0000
1111, Peter, Ivanov, 29, 11, 350000, 333333.3333

So, we first have Partition(300000), the average is 300000, then we have Partition(300000, 350000), the average is 325000, then we have Partition(300000, 350000, 350000) the average is 333333.3333. No?

Your order by is insufficient for ordering the data as the last two records are a tie. I'm assuming that's where the duplicate `avg()` is coming from. Change that to `ORDER BY month` and you should get what you expect. — JNevill, May 16 '23 at 17:13
@JNevill I don’t think it’s the right thing to do in this case. Read my answer for more info. Let me know what you think. — pavel_orekhov, May 18 '23 at 18:07

Salman A · Answer 1 · 2023-05-16T21:11:11.473

The behavior of window specification is actually well documented. In this query:

SELECT t.*, AVG(salary) OVER (
    PARTITION BY id ORDER BY salary
) AS ra
FROM t

the window specification contains order but no frame clause, it will be treated as:

SELECT t.*, AVG(salary) OVER (
    PARTITION BY id ORDER BY salary
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS ra
FROM t

Where CURRENT ROW is defined as:

For RANGE, the bound is the peers of the current row.

So when processing row #2 and 3 both will be considered as peers; both will have:

Running SUM = 300000 + 350000 + 350000 = 1000000
Running AVG = (300000 + 350000 + 350000) / 3 = 333333.333333

Note that some window functions such as LAG, LEAD, ROW_NUMBER and RANK operate on the entire partition (by design) and behave differently.

DB<>Fiddle

Thank you very much, great points. +1 but I think I wrote a more comprehensive answer, so, I accepted mine. — pavel_orekhov, May 18 '23 at 18:10

Jonhtra · Answer 2 · 2023-05-16T18:02:42.647

1

When you do AVG(salary) OVER (PARTITION BY id order by salary ) average_salary you will have 1 partition created by id and 2 partitions inside id partition created by order by clause and RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW will be used.

1st partition will be salary for month 10

2nd partition are going to be salaries for months 12 and 11

CURRENT ROW of your first partition will be row with month 10 and as that is only one average salary will be 30000.

CURRENT ROW of your last partition will be row with month 11 (last row) and RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means range between CURRENT ROW and all previous rows created by partitioning by id (not order by) and that is why salary for month 10 would be taken into account and you get 333333.333 average.

To get result you expected to get you would have to use this query

SELECT id, firstName, lastName, age, month, salary,
   AVG(salary) OVER (partition by id order by salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) average_salary
FROM test_schema.joinedDf;

Here is demo DEMO

edited May 16 '23 at 18:02

answered May 16 '23 at 17:59

Jonhtra

897
2
12
18

This was very helpful to me when I was trying to figure out how WINDOW functions works https://momjian.us/main/writings/pgsql/window.pdf – Jonhtra May 16 '23 at 18:01
I don't think your code is right. Did you mean to write "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"? I think you meant "ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"? – pavel_orekhov May 16 '23 at 18:02
No, I meant what I wrote :) I added dbfiddle – Jonhtra May 16 '23 at 18:08
Oh, sorry, you're right, I apologize. It's just that I read it somewhere that if you don't specify the window frame, the default is `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`? But it's not the case, I guess, so, what is the default, if you don't specify it? – pavel_orekhov May 16 '23 at 18:12
1

Oh, nvm, the default is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`. – pavel_orekhov May 16 '23 at 18:13
Yup, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is default – Jonhtra May 16 '23 at 18:14
So, the problem is `RANGE` vs `ROWS`. `RANGE` looks at values (which have ties), while `ROWS` looks at specific rows. – pavel_orekhov May 16 '23 at 18:17
You can look at it like this. RANGE will look at partition by id and ROWS will look at partitions created by order by – Jonhtra May 16 '23 at 18:19
I don't think I understood this part, but I read it from somewhere, that RANGE will look at a range of values, while ROWS will look at specific ROWS. I posted my own answer, thank you very much. – pavel_orekhov May 16 '23 at 18:23

pavel_orekhov · Accepted Answer · 2023-05-19T10:44:30.740

The problem is that the default window frame (if you specify order by) is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (more about it here, it's for spark, but mysql works similarly: What's the default window frame for window functions), while, if we write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW it works fine.

So, it's a ROWS vs. RANGE issue.

From https://www.mysqltutorial.org/mysql-window-functions/ :

The frame unit specifies the type of relationship between the current row and frame rows. It can be ROWS or RANGE. The offsets of the current row and frame rows are the row numbers if the frame unit is ROWS and row values the frame unit is RANGE.

So, since we in our RANGE we have duplicates, it processes them as "one unit", instead of separately.

For example, let's look at this data:

if we run the same query:

select id, firstName, lastName, age, month, salary,
AVG(salary) OVER (PARTITION BY id order by salary) average_salary
from test_schema.joinedDf;

we get:

Because the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and it will first process this data:

Frame(300,000) avg 300000
Frame(300,000, 350,000, 350,000) avg 333333
Frame(300,000, 350,000, 350,000) avg 333333
Frame(300,000, 350,000, 350,000, 400,000, 400,000) avg 360,000
Frame(300,000, 350,000, 350,000, 400,000, 400,000) avg 360,000

While ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW would process this:

Frame(300,000), avg 300,000
Frame(300,000, 350,000), avg 325,000
Frame(300,000, 350,000, 350,000), avg 333,333
Frame(300,000, 350,000, 350,000, 400,000) avg 350,000
Frame(300,000, 350,000, 350,000, 400,000, 400,000) avg 360,000.

Mostly correct but some notes: with rows, if two rows tie for order by (in your example 2,3 and 4,5) then they can appear in any order within that tie (e.g. 3,2 or 2,3; and 5,4 or 4,5). Also the word partition is incorrect in your bullet points. — Salman A, May 18 '23 at 19:07
@SalmanA thank you very much, this is a great comment. May I ask you to suggest another word in place of “partition”? — pavel_orekhov, May 18 '23 at 19:13
Partitions are subset of rows created by PARTITION BY clause. I guess the correct word would be Frame. It is defined for each row, moving frame (bounded on one or both sides) will be different for each row. Your bullet points need to show 5 frames instead of 3, frame for row 2,3 and 4,5 would be same. — Salman A, May 18 '23 at 19:22

SelVazi · Answer 4 · 2023-05-16T18:25:14.020

select id, firstName, lastName, age, month, salary,
AVG(salary) OVER (PARTITION BY id order by salary ) average_salary
from test_schema.joinedDf;

Because there are duplicate salaries, this query did not work as expected, instead you need to order by a unique column (like rn generated by row_number()) or a date/timestamp if exists :

WITH CTE AS (
  select id, firstName, lastName, age, month, salary, row_number() over () as rn
  from test_schema.joinedDf
)
select id, firstName, lastName, age, month, salary, 
       AVG(salary) OVER (PARTITION BY id order by rn ) average_salary
from CTE

Result :

id  firstName   lastName    age month   salary  average_salary
1111    Peter   Ivanov      29  10      300000  300000.0000
1111    Peter   Ivanov      29  12      350000  325000.0000
1111    Peter   Ivanov      29  11      350000  333333.3333

Demo here

lemon · Answer 5 · 2023-05-16T18:59:08.390

When the window function AVG finds two (or more) tied values, the running AVG considers them together, that's the reason why you get your last two averages identical.

If you want to make the running AVG to take one row at a time, you need to untie the tied records. The only way to do it, is by ordering according to a field that is not tied between the two tied records, in your case "month" only.

As already mentioned in the comments section, it's enough to use "month" inside the ORDER BY clause. In order to retain the original nature of your query, you can also use it alongside "salary".

SELECT id, firstName, lastName, age, month, salary,
       AVG(salary) OVER (PARTITION BY id ORDER BY salary, month) average_salary
FROM test_schema.joinedDf;

Output:

id	firstName	lastName	age	month	salary	average_salary
1111	Peter	Ivanov	29	10	300000	300000.0000
1111	Peter	Ivanov	29	11	350000	325000.0000
1111	Peter	Ivanov	29	12	350000	333333.3333

Check the demo here.

Thank you very much. I think that the problem is not in the AVG function as you describe it. The problem is that the default window frame is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` while if we write `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` it works fine. So, it's a `ROWS` vs `RANGE` issue. — pavel_orekhov, May 16 '23 at 18:19

How does unbounded preceding and current row work exactly?

5 Answers5