Skip to content

Tag: window-functions

Window functions filter through current row

This is a follow-up to this question, where my query was improved to use window functions instead of aggregates inside a LATERAL join. While the query is now much faster, I’ve found that the results are not correct. I need to perform computations on x year trailing time frames. For example, price_to_maximum_earnings is computed per row by getting max(earnings) over

What’s the default window frame for window functions

Running the following code: The result is: There is no window frame defined in the above code, it looks the default window frame is rowsBetween(Window.unboundedPreceding, Window.currentRow) Not sure my understanding about default window frame is correct Answer From Spark Gotchas Default frame specification depends on other aspects of a given window defintion: if the ORDER BY clause is specified and

SQL: difference between PARTITION BY and GROUP BY

I’ve been using GROUP BY for all types of aggregate queries over the years. Recently, I’ve been reverse-engineering some code that uses PARTITION BY to perform aggregations. In reading through all the documentation I can find about PARTITION BY, it sounds a lot like GROUP BY, maybe with a little extra functionality added in. Are they two versions of the