SQL Server：PARTITION BY和GROUP BY之间的区别 [英] SQL Server: Difference between PARTITION BY and 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 ：

select row_number() over (partition by customerId order by orderId)
    as OrderNumberForThisCustomer
from Orders

group 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.

这篇关于SQL Server：PARTITION BY和GROUP BY之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！