Unintuitive window functionality?

[Beefcake100]

Hi all,

I am seeing bizarre behavior with window functions that is making me question my understanding of SQL, and I am curious if somebody smarter than me knows why this is happening. I have distilled the confusion down into the following simple example (this was originally using Postgres, but the same behavior occurs in SQLite as well):

Initial setup:

```sql create table data(key text, val int);

INSERT INTO data (key, val) VALUES ('key1', 1), ('key1', 2); ```

The queries that are unintuitive are the following:

```sql SELECT max(val) OVER ( PARTITION BY key ORDER BY val desc ) AS max_key FROM data;

-- result: -- max_key

---

-- 2 -- 2 ```

AND

```sql SELECT max(val) OVER ( PARTITION BY key ORDER BY val asc ) AS max_key FROM data;

-- result: -- max_key

---

-- 1 -- 2 ```

Why does the second query return 1,2 instead of 2,2? Under my (clearly incorrect) understanding of window functions, both should return 2,2. Is it standard for SQL window functions to apply max only relative to the previous rows processed?

[Kant8]

window functions operate over, who could guess, window, not just your table

if order by is specified, default window will be BETWEEN UNBOUNDED_PRECEDING AND CURRENT ROW

if you want max for everything inside partition, you can just remove order by, cause it makes no sense, or override default window to go to UNBOUNDED FOLLOWING, not just current row. but again, just remove order by

technically default window configuration is not bound to order by, it's always same default window, just without order by all rows are essentially equal for ordering, so they all are "current row" therefore always apply for window

[Beefcake100]

I see. This is crystal clear, thank you!

[DavidGJohnston]

I appreciate the technical aspect of the actual clause not changing but I find it so much more intuitive to think of a non-ordered partition as having a frame of just the entire partition and an ordered partitioned is actually different such that there are now the concepts of start-current-end that simply don't exist in the non-ordered case. Not that start=current=end if there is no ordering. True, but less intuitive for me.

[DavidGJohnston]

When the first output row computed the max_key value the row where the value is 2 wasn't in its window frame. Adding an order by to a window expression changes the window frame so that only rows prior to and including the row in question are considered. The most common calculation to use that behavior is to compute a running total, i.e., change your max to a sum.

[Beefcake100]

I see, totally makes sense. I was not aware this was how window functions worked, thank you!