5 ms·

PostgreSQL 11 Reestablishes Window Functions Leadership

150 points by boshomi 8y ago

ibejoeb 8y ago
Excellent work by the PostgreSQL folks. I did a naive implementation of `ignore nulls` a while back when exploring porting my product from Oracle, but it was slow. Great to see such progress implementing these extremely powerful features.
- ltbarcly3 8y ago
  I'm not sure if it would help, but postgresql also has 'is not distinct from' which is like a two valued version of equality (vs the three valued equality operator). https://modern-sql.com/feature/is-distinct-from https://modern-sql.com/feature/is-distinct-from
atwebb 8y ago
Just skimming through but on down, is the fetch first...with ties in Oracle not the same as select top (1) with ties from order by SortOrder in SQL Server?
- MarkusWinand 8y ago
  In principle yes, it is even mentioned in footnotes of the matrix (2, 3).
  - taspeotis 8y ago
    Thank you for compiling the matrices. I think "Parameters in Fetch First|Next" is wrong with regards to SQL Server, it supports "Parameters (?) in fetch first" [1]. [1] https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017#b-specifying-variables-for-offset-and-fetch-values https://docs.microsoft.com/en-us/sql/t-sql/queries/select-or...
    - MarkusWinand 8y ago
      Very well spotted! I've just fixed that. One of my test cases was binding that paramter as 'numeric' rather than 'interger'. The other DBs didn't care about that.
  - boshomi 8y ago
    @MarkusWindand: Thank you for the Websites [1],[2],[3] and the book »SQL Performance Explained«[4]. [1] https://modern-sql.com/ https://modern-sql.com/ [2] https://use-the-index-luke.com/ https://use-the-index-luke.com/ [3] https://db-engines.com/ https://db-engines.com/ [4] https://www.amazon.de/SQL-Performance-Explained-Everything-performance/dp/3950307826/ https://www.amazon.de/SQL-Performance-Explained-Everything-p...
    - MarkusWinand 8y ago
      Welcome. But db-engines.com is not my site (but also run by Austrians ;)
hmexx 8y ago
Interesting content, although the title is a little dramatic. 'Reestablishes Leadership' lol
- paulmd 8y ago
  Major Version Update to assert dominance
- deleted 8y ago
  [deleted]
- mhuffman 8y ago
  WHAT PostgreSQL IS DOING TO WINDOW FUNCTIONS, WILL SHAKE YOU TO YOUR CORE! On a serious note, PostgreSQL is the epitome, IMHO, of an open-source project that is tightly managed, but still able to change with the times and the user-bases changing needs. Really Postgres and Redis are the two projects that I think of when I think about "great" open-source projects.
- pilif 8y ago
  What they mean is that for years, Postgres had the most advanced support for window functions among the common databases. Then they lost that crown due to an update to MySQL, but now with 11, they are back to being the leader as they have brought in the missing feature plus some additional ones.
SOLAR_FIELDS 8y ago
Window functions will always have a special place in my heart as one of the magic moments on StackExchange where I discovered something truly powerful. Many years ago I had been asked as a lowly developer to perform some sort of reasonably complex dimensional reshape on a SQL Server table. I was at a loss so I asked on DBA StackExchange for advice and I got a response providing a glorious 4 line window function that gave me exactly what I needed. It really made me excited about diving deeper into SQL and motivated me enough to become quite good at it. It’s ended up being one of the most invaluable skills to have learned during my engineering career.
- Noumenon72 8y ago
  I've ended up learning more about SQL than Java at my first job because deploying Java solutions is so slow. What kind of engineering jobs appreciate good SQL skills?
Noumenon72 8y ago
I didn't know SQLite had gotten window functions. I have only used them in Postgres because I didn't know they were standard. Whenever I did use them, they were very handy. I used row_number to delete things in little batches like "where row_number between 40,000 and 60,000". And lag() to only print the times a column's value changed, with "case when lag(myColumn) over (myWindow) is null then myColumn else '' end". I'm glad to hear these are fully supported so I can feel safe learning more about them.

150 points by boshomi 8y ago

Excellent work by the PostgreSQL folks. I did a naive implementation of `ignore nulls` a while back when exploring porting my product from Oracle, but it was slow. Great to see such progress implementing these extremely powerful features.

ltbarcly3 8y ago
I'm not sure if it would help, but postgresql also has 'is not distinct from' which is like a two valued version of equality (vs the three valued equality operator). https://modern-sql.com/feature/is-distinct-from https://modern-sql.com/feature/is-distinct-from

atwebb 8y ago

Just skimming through but on down, is the fetch first...with ties in Oracle not the same as select top (1) with ties from order by SortOrder in SQL Server?

MarkusWinand 8y ago
In principle yes, it is even mentioned in footnotes of the matrix (2, 3).
- taspeotis 8y ago
  Thank you for compiling the matrices. I think "Parameters in Fetch First|Next" is wrong with regards to SQL Server, it supports "Parameters (?) in fetch first" [1]. [1] https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017#b-specifying-variables-for-offset-and-fetch-values https://docs.microsoft.com/en-us/sql/t-sql/queries/select-or...
  - MarkusWinand 8y ago
    Very well spotted! I've just fixed that. One of my test cases was binding that paramter as 'numeric' rather than 'interger'. The other DBs didn't care about that.
- boshomi 8y ago
  @MarkusWindand: Thank you for the Websites [1],[2],[3] and the book »SQL Performance Explained«[4]. [1] https://modern-sql.com/ https://modern-sql.com/ [2] https://use-the-index-luke.com/ https://use-the-index-luke.com/ [3] https://db-engines.com/ https://db-engines.com/ [4] https://www.amazon.de/SQL-Performance-Explained-Everything-performance/dp/3950307826/ https://www.amazon.de/SQL-Performance-Explained-Everything-p...
  - MarkusWinand 8y ago
    Welcome. But db-engines.com is not my site (but also run by Austrians ;)

hmexx 8y ago

Interesting content, although the title is a little dramatic. 'Reestablishes Leadership' lol

paulmd 8y ago
Major Version Update to assert dominance
deleted 8y ago
[deleted]
mhuffman 8y ago
WHAT PostgreSQL IS DOING TO WINDOW FUNCTIONS, WILL SHAKE YOU TO YOUR CORE! On a serious note, PostgreSQL is the epitome, IMHO, of an open-source project that is tightly managed, but still able to change with the times and the user-bases changing needs. Really Postgres and Redis are the two projects that I think of when I think about "great" open-source projects.
pilif 8y ago
What they mean is that for years, Postgres had the most advanced support for window functions among the common databases. Then they lost that crown due to an update to MySQL, but now with 11, they are back to being the leader as they have brought in the missing feature plus some additional ones.

SOLAR_FIELDS 8y ago

Window functions will always have a special place in my heart as one of the magic moments on StackExchange where I discovered something truly powerful. Many years ago I had been asked as a lowly developer to perform some sort of reasonably complex dimensional reshape on a SQL Server table. I was at a loss so I asked on DBA StackExchange for advice and I got a response providing a glorious 4 line window function that gave me exactly what I needed. It really made me excited about diving deeper into SQL and motivated me enough to become quite good at it. It’s ended up being one of the most invaluable skills to have learned during my engineering career.

Noumenon72 8y ago
I've ended up learning more about SQL than Java at my first job because deploying Java solutions is so slow. What kind of engineering jobs appreciate good SQL skills?

Noumenon72 8y ago

I didn't know SQLite had gotten window functions. I have only used them in Postgres because I didn't know they were standard. Whenever I did use them, they were very handy. I used row_number to delete things in little batches like "where row_number between 40,000 and 60,000". And lag() to only print the times a column's value changed, with "case when lag(myColumn) over (myWindow) is null then myColumn else '' end". I'm glad to hear these are fully supported so I can feel safe learning more about them.