SQL: PARTITION BY和GROUP BY的区别

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

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

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

当前回答

它们被用在不同的地方。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不影响返回的行数，但是它更改窗口函数结果的计算方式。

其他回答

分区的将结果集划分为多个分区。窗口函数分别应用于每个分区，并为每个分区重新启动计算。

在此链接找到:OVER子句

按语义分区

你的问题是关于SQL Server的，它目前只在窗口函数中支持PARTITION BY子句，但正如我在这篇博客文章中解释的那样，在SQL中PARTITION BY还有其他的含义，包括:

窗口分区(窗口函数是SQL标准) 表分区(供应商特定的扩展来组织存储，例如在Oracle或PostgreSQL中) MATCH_REGOGNIZE分区(这也是一个SQL标准) MODEL或SPREADSHEET分区(SQL的Oracle扩展) OUTER JOIN分区(SQL标准)

除了最后一个，它重用了PARTITION BY语法来实现某种交叉连接逻辑，所有这些PARTITION BY子句都有相同的含义:

分区将一个数据集划分为不重叠的子集。

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

按语义分组

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只能根据每个查询的一组表达式进行分组。

它提供汇总数据而不进行汇总

例如，假设我想返回销售区域的相对位置

使用PARTITION BY，我可以返回给定区域的销售额和同一行中所有销售区域的最大销售额。

这确实意味着您将有重复的数据，但它可能适合最终消费者，因为数据已经聚合，但没有数据丢失——就像GROUP BY的情况一样。

它们被用在不同的地方。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不影响返回的行数，但是它更改窗口函数结果的计算方式。

小的观察。使用“partition by”动态生成SQL的自动化机制相对于“group by”要简单得多。对于“group by”，我们必须注意“select”列的内容。

对不起，我的英语不好。

推荐文章