Search code examples
sqlanalytic-functions

Evaluating multiple window function

If a window is provided multiple times in the same query, how is it evaluated? Does the query parser check if one window is the same as another or easily 'derived' from another. For example in the following:

SELECT
    MAX(val) OVER (PARTITION BY product_id ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) one,
    MAX(val) OVER (PARTITION BY product_id ORDER BY date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) two,
    MAX(val) OVER (PARTITION BY product_id ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) three
FROM 
    table

How do database engines 'optimize' this query, if they do at all? Does it involve calculated a single window and altering that for other calculations, or does this create three distinct windows? Where might I be able to find more information on how/when the window functions are evaluated (any backend is fine -- oracle, mysql, sqlserver, postgres)?

Solution

  • This depends on the database. That said, the partition by and order by incur overhead for processing data. There is a good chance that the database will not need to re-do that work because the window frame specification ("rows between") differs slightly.

    Of course, different partition by and order by conditions would mean that the data could not be re-used and would need to be reprocessed.

    So, given the specification you have with slight differences, there is an opportunity for a good optimizers to re-use intermediate results. However, it is easy to modify the clauses so they cannot be re-used.