
PostgreSQL 11 Reestablishes Window Functions Leadership - boshomi
https://modern-sql.com/blog/2019-02/postgresql-11
======
SOLAR_FIELDS
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
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?

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

~~~
mhuffman
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.

------
ibejoeb
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
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
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
In principle yes, it is even mentioned in footnotes of the matrix (2, 3).

~~~
taspeotis
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-
or...](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)

~~~
MarkusWinand
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.

------
Noumenon72
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.

