SQL分组排序

发表于 分类于 阅读次数： 本文字数： 2.3k 阅读时长 ≈ 6 分钟

简介：本文将介绍在数据库中，使用SQL对数据进行分组排序。

1 技巧背景

在进行数据分析中，我们时常会用到分组排序功能。如现有多个品类的商品销售情况，我们需要分析商品在自己的品类内的销售排名，则需要用到分组排序。

2 测试数据导入

在数据库中，我们有一个记录商品ID、商品类别、销售额的表，如下图所示：

测试数据导入代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
--创建测试数据表
CREATE TABLE #销售数据
(商品ID NVARCHAR(6),
商品类别 NVARCHAR(6),
销售额 DECIMAL(12,2))

--在测试数据表中导入数据
INSERT INTO #销售数据
(商品ID,商品类别,销售额)
VALUES
('100001','饼干',23657.21),
('100002','膨化食品',36544.21),
('100003','方便面',23642.77),
('100004','膨化食品',48328.15),
('100005','方便面',16258.45),
('100006','饼干',16321.14),
('100007','饼干',4504.65),
('100008','膨化食品',1486.15),
('100009','饼干',4504.65),
('100010','膨化食品',56424.81)

3 计算方法

3.1 使用ROW_NUMBER() OVER()函数

3.1.1 无分组计算方式：

在无需根据商品类别的统计销售额排名的计算中，我们可以参考以下代码进行处理：

1
2
3
4
5
6
SELECT
商品ID,
商品类别,
销售额,
ROW_NUMBER() OVER(ORDER BY 销售额 DESC) AS '排名'
FROM #销售数据

代码运行结果如下图所示：

以上计算方法是针对单个商品销售额进行降序排名。

3.1.2 分组计算方式：

在需要根据商品类别的统计销售额排名计算中，我们可以参考以下代码进行处理：

1
2
3
4
5
6
SELECT
商品ID,
商品类别,
销售额,
ROW_NUMBER() OVER(PARTITION BY 商品类别 ORDER BY 销售额 DESC) AS '排名'
FROM #销售数据

在上述代码中，我们加入了PARTITION BY 商品类别，这样我们就可以根据商品类别进行分组，再统计各商品类别内商品的销售额排名。
以上代码运行结果如下图所示：

3.2 使用DENSE_RANK() OVER()函数

在使用ROW_NUMBER() OVER函数的时候，大家会发现，上述数据中，商品ID为100007和100009这两个商品销售额同样为4504.65，但是商品100007是第3名，商品100009是第4名，这样的结果是不太符合我们进行数据分析使用的，所以我们会考虑使用DENSE_RANK() OVER()函数。

代码如下：

1
2
3
4
5
6
SELECT
商品ID,
商品类别,
销售额,
DENSE_RANK() OVER(PARTITION BY 商品类别 ORDER BY 销售额 DESC) AS '排名'
FROM #销售数据

以上代码运行结果如下图所示：

这里我们可以看到，现在商品100007和100009的排名均为第3名了。

3.3 使用RANK() OVER()函数

在实际的数据分析中，我们会使用到跳跃排序，即假如有两个第2名，那么排名顺序则为1,2,2,4，如此类推。
在尝试RANK() OVER()函数前，我们需要在测试数据里面追加两条数据，以便更好的呈现结果。
追加数据代码：

1
2
3
4
5
INSERT INTO #销售数据
(商品ID,商品类别,销售额)
VALUES
('100011','饼干',3542.21),
('100012','饼干',3821.42)

在追加数据后，我们可以运行以下代码：

1
2
3
4
5
6
SELECT
商品ID,
商品类别,
销售额,
RANK() OVER(PARTITION BY 商品类别 ORDER BY 销售额 DESC) AS '排名'
FROM #销售数据

代码运行结果如下图所示：

我们可以看到商品100007和100009的排名均为第3名，商品100012的排名为第5名了。

3.4 总结

为了让大家更好的了解三个分组排序函数的区别，这里我们可以运行以下代码，然后对比不同函数的排名结果：

1
2
3
4
5
6
7
8
SELECT
商品ID,
商品类别,
销售额,
ROW_NUMBER() OVER (PARTITION BY 商品类别 ORDER BY 销售额 DESC) AS 'ROW_NUMBER() OVER()函数排名',
DENSE_RANK() OVER (PARTITION BY 商品类别 ORDER BY 销售额 DESC) AS 'DENSE_RANK() OVER()排名',
RANK() OVER(PARTITION BY 商品类别 ORDER BY 销售额 DESC) AS 'RANK() OVER()排名'
FROM #销售数据

以上代码运行结果如下：

这样我们就可以明显看到三个函数在分组排序中有什么区别了。