在没有子查询的情况下，通过RANK()在HAVING子句中进行过滤

Question

我正在获取按组最大值（data CTE 模拟了从实际表中进行的一组连接）：

with data as (
    select 'Austria' as country, 1 as id, 'red' as colour, 120 as quantity
    union all select 'Austria', 2, 'green', 96
    union all select 'Austria', 3, 'blue', 103

    union all select 'Belgium', 1, 'red', 33
    union all select 'Belgium', 2, 'green', 12
    union all select 'Belgium', 3, 'blue', 40
)
select country, colour, quantity
from (
    select country, colour, quantity,
    rank() over (partition by country order by quantity desc, id) as position
    from data
    group by country, id, colour, quantity
) subquery
where position=1;

这样做是有效的，但我被迫将查询用RANK()包装在子查询中，因为这种替代方法会触发语法错误：

-- [...]
select country, colour, quantity,
rank() over (partition by country order by quantity desc, id) as position
from data
group by country, id, colour, quantity
having rank() over (partition by country order by quantity desc, id)=1;

窗口函数只能出现在SELECT或ORDER BY子句中。

是否有替代语法来避免这个限制，或者子查询是唯一合理的方法？

最终目标是将此代码集成到更大的动态生成的SQL表达式集中。如果我可以保持单个查询，我可以用数组定义不同的部分（选择、连接的表、where、group by、having和order by）。否则我需要考虑进行重大改写。

- Álvaro González

Answer 1

您可以像以下这样使用top 1 with ties：

select top (1) with ties country, colour, quantity,
    rank() over (partition by country order by quantity desc, id) as position
from data
--group by country, id, colour, quantity
order by rank() over (partition by country order by quantity desc, id)

- Kannan Kandasamy

---

加1，但我认为“按国家、ID、颜色、数量分组”是多余的。 - TriV

是的，我们不需要使用冗余的 group by。 - Kannan Kandasamy

尽管使用Top(1) With Ties更加优雅，但它会使用额外的排序运算符，如果你有大量的数据，这会增加一些开销。 - Kannan Kandasamy

谢谢，这应该很好地与底层框架集成。GROUP BY子句是代码编辑的剩余部分。 - Álvaro González

Answer 2

如果你看性能差异，我仍然觉得子查询方法比上面的带有ties的top(1)方法更好，因为下面有排序运算符:

尽管带有ties的top(1)更优雅...以下是基于执行计划快照的性能差异。

- Kannan Kandasamy

---

我没有经验或知识来判断100%排序是否比两个50%排序更好或更差，但我理解你的总体观点。此外，“TOP 1 WITH TIES”方法可能更加脆弱，因为如果我在ORDER BY子句中添加更多列，它很容易被破坏。 - Álvaro González

如果您看到第一个查询的“查询成本（相对于批处理）：”仅为33％，而第二个查询使用“Top（1）with ties”则为67％，并且如果我们单独分析它，则是一个额外的排序运算符... - Kannan Kandasamy