使用具有“order by”组件的窗口函数时，结果集排序是什么？
我正在 SEDE 上进行查询：
select top 20
row_number() over(order by "percentage approved" desc, approved desc),
row_number() over(order by "total edits" asc),
*
from editors
where "total edits" > 30
考虑到两个窗口函数，结果集的顺序是什么？
我怀疑它未定义，但找不到明确的答案。 OTOH，使用此类窗口函数的查询结果是根据 over(order by ...) 子句排序的。
I'm working on a query on the SEDE:
select top 20
row_number() over(order by "percentage approved" desc, approved desc),
row_number() over(order by "total edits" asc),
*
from editors
where "total edits" > 30
What is the ordering of the result set, taking into account the two window functions?
I suspect it's undefined but couldn't find a definitive answer. OTOH, results from queries with one such window function were ordered according to the over(order by ...) clause.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
结果可以按任何顺序返回。
现在，它们通常会按照 OVER 子句中指定的顺序返回，但这只是因为 SQL Server 可能会选择一个对行进行排序的查询计划来计算聚合。这绝不是保证的，因为它可以随时选择不同的查询计划，特别是当您使查询变得更加复杂时，这会扩展可能的查询计划的空间。
The results can be returned in any order.
Now, they will often be returned in the same order as specified in the
OVERclause, but this is just because SQL Server is likely to pick a query plan that sorts the rows to calculate the aggregate. This is by no means guaranteed, as it could pick a different query plan at any time, especially as you make your query more complex which extends the space of possible query plans.没有显式
ORDER BY的 ANY SQL Server 查询的结果集是未定义的。这包括当您在查询中使用窗口函数或在子查询中使用
ORDER BY时。结果顺序将取决于很多因素，除非您指定ORDER BY，否则无法保证这些因素。The result set of ANY SQL Server query that doesn't have an explicit
ORDER BYis undefined.This includes when you have window functions within the query, or an
ORDER BYin a subquery. The result order will depend on a lot of factors, none of which are guaranteed unless you specify anORDER BY.