window-functions - 如何在窗口函数中使用具有子句而不是 avg 进行过滤？

Question

我创建了一个小提琴http://sqlfiddle.com/#!17/f882b/23。

它有带有名字、年龄、国家、体重的表“苏格兰人”。

select name, weight, country,
avg(weight) over (order by name) as avg_weight_over_computed
from scots 
-- group by country
having avg_weight_over_computed > 76
order by name

以上不适用于“有”子句。我想过滤那些 avg_weights > 76。如何做到这一点？

标签： window-functionspostgresql-9.3sqlfiddle

Answer 1

在这种情况下，可以将 HAVING 转换为子查询中的 where 子句吗？

SELECT * FROM
(
  select 
    name, 
    weight, 
    country,
    avg(weight) over (order by name) as avg_weight_over_computed
  from 
      scots 
)
AS X
where 
  avg_weight_over_computed > 76