SQL：PARTITION BY 和 GROUP BY 的区别

Question

多年来，我一直在使用 GROUP BY 来处理所有类型的 聚合 查询。最近，我一直在对一些使用 PARTITION BY 执行聚合的代码进行逆向工程。

在阅读所有我能找到的关于 PARTITION BY 的文档时，它听起来很像 GROUP BY ，可能添加了一些额外的功能。

它们是相同通用功能的两个版本还是完全不同的东西？

原文由 Mike Mooney 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

它们用于不同的地方。 GROUP BY 修改整个查询，如：

 select customerId, count(*) as orderCount
from Orders
group by customerId

但是 PARTITION BY 仅适用于 窗口函数，例如 ROW_NUMBER() ：

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

• GROUP BY 通常通过将它们滚动并计算每行的平均值或总和来减少返回的行数。
• PARTITION BY 不会影响返回的行数，但会改变窗口函数结果的计算方式。

原文由 Andomar 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

PARTITION BY 语义

您的问题专门针对 SQL Server，它目前仅在窗口函数中支持 PARTITION BY 子句，但正如我在这篇博文中解释的那样，SQL Server 中 PARTITION BY 的各种含义，还有其他的，包括：

• 窗口分区（窗口函数是 SQL 标准）
• 表分区（用于组织存储的供应商特定扩展，例如在 Oracle 或 PostgreSQL 中）
• MATCH_REGOGNIZE 分区（也是SQL标准）
• MODEL 或 SPREADSHEET 分区（Oracle 对 SQL 的扩展）
• OUTER JOIN 分区（SQL 标准）

除了最后一个，它重用 PARTITION BY 语法来实现某种 CROSS JOIN 逻辑，所有这些 PARTITION BY 子句具有相同的含义：

分区将数据集分成不重叠的子集。

基于此分区，可以实现 每个分区 的进一步计算或存储操作。例如，对于窗口函数，例如 COUNT(*) OVER (PARTITION BY criteria) ， COUNT(*) 值是 按分区 计算的。

GROUP BY 语义

GROUP BY 允许类似的分区行为，尽管它也会 以各种奇怪的方式转换整个查询的语义。大多数使用 GROUP BY 查询可以使用窗口函数重写，尽管通常， GROUP BY 语法更简洁，也可能得到更好的优化。

例如，这些在逻辑上是相同的，但我希望 GROUP BY 子句表现更好：

 -- Classic
SELECT a, COUNT(*)
FROM t
GROUP BY a

-- Using window functions
SELECT DISTINCT a, COUNT(*) OVER (PARTITION BY a)
FROM t

主要区别在于：

• 窗口函数也可以是非聚合函数，例如 ROW_NUMBER()
• 每个窗口函数都可以有自己的 PARTITION BY 子句，而 GROUP BY 每个查询只能按一组表达式分组。

原文由 Lukas Eder 发布，翻译遵循 CC BY-SA 4.0 许可协议