0

The syntax for aggregates and window functions is confusing, this does not work,

SELECT (max(x)+5) OVER ()
FROM generate_series(1,10) AS t(x);

While this works

SELECT 5+max(x) OVER ()
FROM generate_series(1,10) AS t(x);

That's fine if the operator is commutative, but I'm trying to divide an interval by seconds (in my specific case though I worked around it). Is there anyway to simplify this.

SELECT (max(x)/5) OVER ()
FROM generate_series(1,10) AS t(x);

So another encapsulating query is not needed? Is this a PostgreSQL thing, or a SQL thing? Is there a way to disambiguate the query?

5

If I'm understanding your question correctly, you're trying to divide the result of the window function by 5. The entire window function consists of MAX(x) OVER () therefore your query would look something like this:

SELECT MAX(x) OVER ()/5 FROM generate_series(1,10) AS t(x);

This will result in 10 rows all containing the value 2, since the generate_series function results in 10 rows and each would contain the result of our window function.

  • 2
    purely for clarity, it can be useful to write this as (MAX(x) OVER ())/5 – Jack Douglas Dec 16 '16 at 10:04
  • or, div(max() OVER (), 5). I just can't believe that's how SQL parses it. That's especially ugly. – Evan Carroll Dec 16 '16 at 20:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.