postgresql - postgres查询中的多个命名窗口

Question

postgres 文档因此指定了一个窗口定义子句：

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

[ ,...]指定多个窗口是可能的。我在文档中找不到其他任何东西来确认或否认这是可能的。我该如何进行这项工作？

在此查询中，我可以单独使用任一窗口子句，但即使语法遵循规范，我也不能同时使用两者：

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)

我可以通过不使用窗口定义来获得技术效果，但是对于重用窗口的复杂查询来说，这会让人厌烦：

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

标签： postgresqlwindow-functions

Answer 1

不要重复window关键字：

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)