多年来,我一直在对所有类型的聚合查询使用GROUP BY。最近,我一直在逆向工程一些使用PARTITION BY来执行聚合的代码。
在阅读我能找到的所有关于PARTITION BY的文档时,它听起来很像GROUP BY,可能还添加了一些额外的功能。
它们是相同功能的两个版本还是完全不同的东西?
多年来,我一直在对所有类型的聚合查询使用GROUP BY。最近,我一直在逆向工程一些使用PARTITION BY来执行聚合的代码。
在阅读我能找到的所有关于PARTITION BY的文档时,它听起来很像GROUP BY,可能还添加了一些额外的功能。
它们是相同功能的两个版本还是完全不同的东西?
当前回答
分区的 将结果集划分为多个分区。窗口函数分别应用于每个分区,并为每个分区重新启动计算。
在此链接找到:OVER子句
其他回答
-- 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
它提供汇总数据而不进行汇总
例如,假设我想返回销售区域的相对位置
使用PARTITION BY,我可以返回给定区域的销售额和同一行中所有销售区域的最大销售额。
这确实意味着您将有重复的数据,但它可能适合最终消费者,因为数据已经聚合,但没有数据丢失——就像GROUP BY的情况一样。
使用GROUP BY时,生成的行通常比输入的行少。
但是,当使用PARTITION BY时,得到的行数应该与传入的行数相同。
分区的 将结果集划分为多个分区。窗口函数分别应用于每个分区,并为每个分区重新启动计算。
在此链接找到:OVER子句
我们可以举一个简单的例子。
考虑一个名为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
集团
可以在SELECT语句中使用SQL GROUP BY子句进行收集 跨多个记录的数据,并将结果按一个或多个分组 列。 简单来说,GROUP BY语句与 聚合函数将结果集按一个或多个分组 列。
语法:
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之前,让我们看一下OVER子句:
根据MSDN的定义:
控件中定义窗口或用户指定的行集 查询结果集。然后,窗口函数为每一行计算一个值 在窗户里。可以对函数使用OVER子句进行计算 诸如移动平均线、累计总数、 运行总数,或每组结果的前N名。
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不同。