SQL PARTITION BY 语句基本运用

先演示一下结果，数据库表如下：

用GROUP的结果如下（By Customercity）：

但有一个缺陷，只能显示Customercity,要显示其他信息就不能显示。

这时，用Partition By可以显示相应的CustomerName等，结果如下：

操作过程及执行语句如下：

1.先创建一个数据库，并插入数据：

CREATE TABLE [dbo].[Orders]
(
    [orderid] INT,
    [Orderdate] DATE,
    [CustomerName] VARCHAR(100),
    [Customercity] VARCHAR(100), 
    [Orderamount] MONEY
)

INSERT [dbo].[Orders]  VALUES (216090, CAST(N'1826-12-19' AS Date), N'Edward', N'Phoenix', 4713.8900)
GO
INSERT [dbo].[Orders]  VALUES (508220, CAST(N'1826-12-09' AS Date), N'Aria', N'San Francisco', 9832.7200)
GO

INSERT [dbo].[Orders]  VALUES (271510, CAST(N'1902-09-03' AS Date), N'Lawrence', N'Chicago', 7199.61)
GO
INSERT [dbo].[Orders]  VALUES (517050, CAST(N'1873-07-05' AS Date), N'Roland', N'Austin', 936.12)
GO

INSERT [dbo].[Orders]  VALUES (834677, CAST(N'1932-09-29' AS Date), N'Stella', N'San Francisco', 2471.47)
GO
INSERT [dbo].[Orders]  VALUES (339137, CAST(N'2019-12-26' AS Date), N'Salador', N'Columbus', 4275.76)
GO

INSERT [dbo].[Orders]  VALUES (830492, CAST(N'1801-06-05' AS Date), N'Ernest', N'Houston', 3858.43)
GO
INSERT [dbo].[Orders]  VALUES (126979, CAST(N'1885-05-03' AS Date), N'Nicholas', N'San Jose', 8624.99)
GO

INSERT [dbo].[Orders]  VALUES (590451, CAST(N'2012-04-20' AS Date), N'Ray', N'New York', 6377.95)
GO
INSERT [dbo].[Orders]  VALUES (173941, CAST(N'1808-12-16' AS Date), N'Aaliyah', N'Columnbus', 5308.58)
GO

INSERT [dbo].[Orders]  VALUES (648122, CAST(N'1942-02-24' AS Date), N'Gilbert', N'Columbus', 6427.8)
GO
INSERT [dbo].[Orders]  VALUES (547728, CAST(N'1854-01-20' AS Date), N'Jorge', N'Austin', 2326.46)
GO

2.Group By的执行语句如下：（执行结果如上）

select Customercity,
count(*) as Count,
avg(Orderamount)as AvgOrderAmount,
min(Orderamount) as MinOrderAmount,
sum(Orderamount) as TotalOrderAmount
from Orders
group by Customercity

3.Partition By的执行语句如下：（执行结果如上）

select Customercity,
CustomerName,
OrderAmount,
count(OrderID) over (PARTITION by Customercity) as CountOfOrders,
avg(Orderamount) over(PARTITION by Customercity) as AvgOrderAmount,
min(Orderamount) over(PARTITION by Customercity) as MinOrderAmount,
sum(Orderamount) over(PARTITION by Customercity) as TotalOrderAmount
from Orders

4.用Partition By语句，显示分组后的Row Number，执行语句如下：

select Customercity,
CustomerName,
ROW_NUMBER() OVER(PARTITION BY Customercity ORDER BY Orderamount desc) AS "Row Number",
OrderAmount,
count(OrderID) over (PARTITION by Customercity) as CountOfOrders,
avg(Orderamount) over(PARTITION by Customercity) as AvgOrderAmount,
min(Orderamount) over(PARTITION by Customercity) as MinOrderAmount,
sum(Orderamount) over(PARTITION by Customercity) as TotalOrderAmount
from Orders