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

在此链接找到:OVER子句

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

在此链接找到:OVER子句

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

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

在小组由

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

它将给出单行计数，即14

但是在除以

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

它将增加14行计数

-- 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是聚合的。为了使用PARTITION BY，你必须用OVER子句来包含它。