sql - 是否允许在 have 子句中使用 over()？

Question

我需要使用 over(partition by ...)在 having条款。

这是我的代码，它给出了错误:

group function is nested too deeply

select resort, discipline, gender, count(1)
from races
group by resort, discipline, gender
having sum(count(1)) over (partition by resort, discipline) > 10;

有没有办法使用 over()在 having条款还是有其他选择？

Answer 1

你总是可以嵌套它:

WITH cte AS (
  select resort, discipline, gender, count(1) AS cnt
  from races
  group by resort, discipline, gender
), cte2 AS (
  SELECT *, sum(cnt) over (partition by resort, discipline) AS s
  FROM cte
)
SELECT *
FROM cte2
WHERE s > 10;