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.
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.
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.
Here is the best way that I've found to think about them. Sometimes you run across a SQL problem that makes you think, "This would be easy if I just sorted my table, and ran a program over it breaking it into partitions then processing each partition in order." Whenever you do that, window functions will solve that problem.
Note that in practice I've found that queries using window functions often are best done by something of a form: SELECT ... FROM (SELECT ...) foo WHERE ... GROUP BY ... where the window functions are all in the inner query, and the outer query's group by collapses each partition into a single row.