multiple named windows in a postgres query

Question

The postgres docs specify a window definition clause thus:

[ WINDOW window_name AS ( window_definition ) [, ...] ]

The [,...] specifies that multiple windows are possible. I find nothing else in the docs to confirm or deny it's possible. How do I make this work?

In this query, I can use either window clause on its own but I can't use both even though the syntax follows the spec:

select q.*
, min(value) over w_id as min_id_val
--, min(value) over w_kind as min_kind_val
from (
    select 1 as id, 1 as kind, 3.0 as value
    union select 1, 2, 1.0
    union select 2, 1, 2.0
    union select 2, 2, 0.5
) as q
window w_id as (partition by id)
-- , 
-- window w_kind as (partition by kind)

I can get the technical effect by not using window definitions, but that gets tiresome for a complex query where windows are re-used:

select q.*
, min(value) over (partition by id) as min_id_val
, min(value) over (partition by kind) as min_kind_val
from (
    select 1 as id, 1 as kind, 3.0 as value
    union select 1, 2, 1.0
    union select 2, 1, 2.0
    union select 2, 2, 0.5
) as q

Some remarks on why the documentation is the way it is are here — Adam Mackler
– Adam Mackler, Commented Feb 16, 2023 at 17:22

user330315 · Accepted Answer · 2020-01-08 19:00:00Z

12

Don't repeat the window keyword:

select q.*, 
       min(value) over w_id as min_id_val, 
       min(value) over w_kind as min_kind_val
from (
  values 
   (1,1,3.0),
   (1, 2, 1.0),
   (2, 1, 2.0),
   (2, 2, 0.5)
) as q(id,kind,value)
window w_id as (partition by id), 
       w_kind as (partition by kind)

answered Jan 8, 2020 at 19:00

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Michael Grazebrook Over a year ago

Thanks. Perfect answer. I'd tick this as correct but created a new account for a new project and Stack Overflow won't let me!

user330315 Over a year ago

@MichaelGrazebrook stackoverflow.com/help/merging-accounts

Collectives™ on Stack Overflow

multiple named windows in a postgres query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related