SQL: PARTITION BY和GROUP BY的区别

partition by实际上并不卷起数据。它允许您在每个组的基础上重置一些东西。例如，你可以通过对分组字段进行分区并在组内的行上使用rownum()来获得组内的序数列。这使您的行为有点像在每个组的开头重置的标识列。

它们被用在不同的地方。GROUP BY修改整个查询，比如:

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

但是PARTITION BY只适用于< em > < / em >窗口函数，比如ROW_NUMBER():

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

• GROUP BY通常减少滚动返回的行数
• PARTITION BY不影响返回的行数，但是它 改变窗口函数结果的计算方式

PARTITION BY是解析的，而GROUP BY是聚合的。为了使用PARTITION BY，你必须用在条款来包含它。

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

可以在此链接找到:在条款

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

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

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

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

据我所知，Partition By与Group By几乎相同，但有以下区别:

这个group by实际上对结果集进行分组，每个组返回一行，因此SQL Server只允许在SELECT列表中聚合函数或列，这些函数或列是group by子句的一部分(在这种情况下，SQL Server可以保证每个组都有唯一的结果)。

以MySQL为例，它允许在SELECT列表中有Group By子句中没有定义的列，在这种情况下，每个组仍然返回一行，但是如果列没有唯一的结果，那么就不能保证将输出什么!

但是使用Partition By，尽管该函数的结果与使用Group By的聚合函数的结果相同，但仍然得到正常的结果集，这意味着每个底层行得到一行，而不是每个组得到一行，因此在SELECT列表中每个组的列可能不是唯一的。

因此，作为一个总结，当需要每个组输出一行时，Group By是最好的，当需要所有行但仍然需要基于组的聚合函数时，Partition By是最好的。

当然也可能存在性能问题，参见http://social.msdn.microsoft.com/Forums/ms-MY/transactsql/thread/0b20c2b5-1607-40bc-b7a7-0c60a2a55fba。

我们可以举一个简单的例子。

考虑一个名为TableA的表，其值如下:

id  firstname                   lastname                    Mark
-------------------------------------------------------------------
1   arun                        prasanth                    40
2   ann                         antony                      45
3   sruthy                      abc                         41
6   new                         abc                         47
1   arun                        prasanth                    45
1   arun                        prasanth                    49
2   ann                         antony                      49

GROUP BY

SQL GROUP BY子句可以用在SELECT语句中进行收集 跨多个记录的数据，并将结果按一个或多个分组 列。< / p >

简单来说，GROUP BY语句与 聚合函数将结果集按一个或多个分组 列。< / p >

语法:

SELECT expression1, expression2, ... expression_n,
aggregate_function (aggregate_expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n;

我们可以在表中应用GROUP BY:

select SUM(Mark)marksum,firstname from TableA
group by id,firstName

结果:

marksum  firstname
----------------
94      ann
134     arun
47      new
41      sruthy

在我们的实际表中，我们有7行，当我们应用GROUP BY id时，服务器会根据id对结果进行分组:

简单地说:

这里GROUP BY通常减少滚动返回的行数

. Sum()

PARTITION BY

在进入PARTITION BY之前，让我们看一下OVER子句:

根据MSDN的定义:

对象中的窗口或用户指定的行集 查询结果集。然后，窗口函数为每一行计算一个值 在窗户里。可以对函数使用OVER子句进行计算 诸如移动平均线、累计总数、

PARTITION BY不会减少返回的行数。

我们可以在示例表中应用PARTITION BY:

SELECT SUM(Mark) OVER (PARTITION BY id) AS marksum, firstname FROM TableA

结果:

marksum firstname
-------------------
134     arun
134     arun
134     arun
94      ann
94      ann
41      sruthy
47      new

看看结果——它将对行进行分区，并返回所有行，不像GROUP BY。

-- BELOW IS A SAMPLE WHICH OUTLINES THE SIMPLE DIFFERENCES
-- READ IT AND THEN EXECUTE IT
-- THERE ARE THREE ROWS OF EACH COLOR INSERTED INTO THE TABLE
-- CREATE A database called testDB




-- use testDB
USE [TestDB]
GO




-- create Paints table
CREATE TABLE [dbo].[Paints](
[Color] [varchar](50) NULL,
[glossLevel] [varchar](50) NULL
) ON [PRIMARY]


GO




-- Populate Table
insert into paints (color, glossLevel)
select 'red', 'eggshell'
union
select 'red', 'glossy'
union
select 'red', 'flat'
union
select 'blue', 'eggshell'
union
select 'blue', 'glossy'
union
select 'blue', 'flat'
union
select 'orange', 'glossy'
union
select 'orange', 'flat'
union
select 'orange', 'eggshell'
union
select 'green', 'eggshell'
union
select 'green', 'glossy'
union
select 'green', 'flat'
union
select 'black', 'eggshell'
union
select 'black', 'glossy'
union
select 'black', 'flat'
union
select 'purple', 'eggshell'
union
select 'purple', 'glossy'
union
select 'purple', 'flat'
union
select 'salmon', 'eggshell'
union
select 'salmon', 'glossy'
union
select 'salmon', 'flat'




/*   COMPARE 'GROUP BY' color to 'OVER (PARTITION BY Color)'  */


-- GROUP BY Color
-- row quantity defined by group by
-- aggregate (count(*)) defined by group by
select count(*) from paints
group by color


-- OVER (PARTITION BY... Color
-- row quantity defined by main query
-- aggregate defined by OVER-PARTITION BY
select color
, glossLevel
, count(*) OVER (Partition by color)
from paints


/* COMPARE 'GROUP BY' color, glossLevel to 'OVER (PARTITION BY Color, GlossLevel)'  */


-- GROUP BY Color, GlossLevel
-- row quantity defined by GROUP BY
-- aggregate (count(*)) defined by GROUP BY
select count(*) from paints
group by color, glossLevel






-- Partition by Color, GlossLevel
-- row quantity defined by main query
-- aggregate (count(*)) defined by OVER-PARTITION BY
select color
, glossLevel
, count(*) OVER (Partition by color, glossLevel)
from paints

假设在表中有14条name列的记录

在group by

select name,count(*) as totalcount from person where name='Please fill out' group BY name;

它将给出单行计数，即14

而是在partition by中

select row_number() over (partition by name) as total from person where name = 'Please fill out';

它将增加14行计数

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

对不起，我的英语不好。

然而，当你使用PARTITION BY时，你的结果集是相同的，但你只是对窗口函数进行了聚合，而你没有合并记录，你仍然会有相同的记录计数。

当你使用GROUP BY时，结果行通常会比输入行少。

但是，当使用PARTITION BY时，得到的行数应该与传入的行数相同。

PARTITION BY语义

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

• 窗口分区(窗口函数是SQL标准)
• 表分区(供应商特定的扩展来组织存储，例如在甲骨文或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每次查询只能按一组表达式进行分组。