Fun with SQL: Window Functions in Postgres

Re:Fun with SQL: Window Functions in Postgres by blattimwind on Monday June 04, 2018 @06:34PM (#17233252)

Window functions are super handy to turn a correlated subquery into a join, because they make group-wise limits fairly easy (just put a ROW_NUMBER() concerning the relevant partition, i.e. what would be your GROUP BY in the correlated subquery, in your query and select from that query filtering on rank; then join on whatever you previously correlated on).

Basically: SELECT ... FROM (SELECT ..., ROW_NUMBER() OVER (PARTITION BY my_group [ORDER BY...]) AS rank) AS foo WHERE foo.rank <= 10

Without window functions you can usually only do this using a correlated subquery, which usually is much slower for fairly obvious reasons.

[ Parent | on HN ]

Re:Fun with SQL: Window Functions in Postgres by da_chicken on Monday June 04, 2018 @07:28PM (#17233581)

Yup, if all you ever learn is one windowing function, the "greatest N per group" pattern with ROW_NUMBER() is easily the most common.

[ Parent | on HN ]

Re:Fun with SQL: Window Functions in Postgres by btilly on Monday June 04, 2018 @06:54PM (#17233354)

That's another common variation, using a where in the outer query to select only a few of the rows from the window function.

And I've also used them as a better performing version of a full outer join - do a select of the combined fields from a window function query that is selected off of a UNION ALL. You have to jump a lot of hoops to do it, but the performance win can be worthwhile.

[ Parent | on HN ]