SQL Server:PARTITION BY 和 GROUP BY 的区别
多年来，我一直将 GROUP BY 用于所有类型的聚合查询.最近，我一直在对一些使用 PARTITION BY 执行聚合的代码进行逆向工程.在阅读我能找到的关于 PARTITION BY 的所有文档时，它听起来很像 GROUP BY，也许添加了一些额外的功能?它们是具有相同通用功能的两个版本，还是完全不同的东西?
I've been using GROUP BY for all types of aggregate queries over the years. Recently, I've been reverse-engineering some code that uses PARTITION BY to perform aggregations. In reading through all the documentation I can find about PARTITION BY, it sounds a lot like GROUP BY, maybe with a little extra functionality added in? Are they two versions of the same general functionality, or are they something different entirely?
推荐答案
它们用在不同的地方.group by 修改整个查询，如:
They're used in different places. group by modifies the entire query, like:
select customerId, count(*) as orderCount
from Orders
group by customerId
但是 partition by 只适用于 a窗口函数，如row_number:
But partition by just works on a window function, like row_number:
select row_number() over (partition by customerId order by orderId)
as OrderNumberForThisCustomer
from Orders
group by 通常通过将它们汇总并计算每行的平均值或总和来减少返回的行数.partition by 不会影响返回的行数，但会改变窗口函数结果的计算方式.
A group by normally reduces the number of rows returned by rolling them up and calculating averages or sums for each row. partition by does not affect the number of rows returned, but it changes how a window function's result is calculated.
相关文章