使用 over 和 partition函数分组数据

使用 over 和 partition函数分组数据

除了group by， sql也可以使用 over 和partition by对结果集根据一定条件进行分组。本文解释如何结合这两个函数对数据进行分组展示。

准备数据

为了说明方便，我们定义表"student"，有五列数据：id,name,age,gender,total_score.创建表语句为:

CREATE TABLE student
(
    id INT PRIMARY KEY IDENTITY,
    name VARCHAR(50) NOT NULL,
    gender VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    total_score INT NOT NULL, 
 )

接着我们插入一些模拟数据：

INSERT INTO student 
VALUES ('Jolly', 'Female', 20, 500), 
('Jon', 'Male', 22, 545), 
('Sara', 'Female', 25, 600), 
('Laura', 'Female', 18, 400), 
('Alan', 'Male', 20, 500), 
('Kate', 'Female', 22, 500), 
('Joseph', 'Male', 18, 643), 
('Mice', 'Male', 23, 543), 
('Wise', 'Male', 21, 499), 
('Elis', 'Female', 27, 400);

下面开始了解问题。

需求

现在表中有10条记录，我们需要结果为id，name，gender，另外我们还需要属于每个性别的学生数，每个性别的平均年龄以及每个性别的总分。结果如下图:

ID	NAME	GENDER	Total_students	Average_Age	Total_Score
1	Jolly	Female	5	22	2400
3	Sara	Female	5	22	2400
4	Laura	Female	5	22	2400
6	Kate	Female	5	22	2400
10	Elis	Female	5	22	2400
7	Joseph	Male	5	20	2730
8	Mice	Male	5	20	2730
9	Wise	Male	5	20	2730
5	Alan	Male	5	20	2730
2	Jon	Male	5	20	2730

我们看到，前三列包括每条记录的值，而后三列包括根据gender分组的聚集值。前五列中，Average_Age显示性别为Female的平均年龄，Total_Score显示性别为Female的总分和。总之结果集中既包括聚集值也包括非聚集值。

下面我们先获取聚集值，根据特定的列进行group by，使用group by 子句：

SELECT gender, count(gender) AS Total_Students, AVG(age) as Average_Age, SUM(total_score) as Total_Score
FROM student
GROUP BY gender

结果为：

GENDER	Total_students	Average_Age	Total_Score
Female	5	22	2400
Male	5	20	2730

下面我们直接在分组语句中加入id 和 name （在select语句中直接加入非聚集列），看看是否可以得到期望的结果：

SELECT id, name, gender, count(gender) AS total_students, AVG(age) as Average_Age, SUM(total_score) as Total_Score
FROM student
GROUP BY gender

执行结果出错：

消息 8120，级别 16，状态 1，第 1 行
选择列表中的列 'student.id' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

错误指出id必须包括在group by子句中。显然这种方案行不通。

使用 JOIN 语句

既然我们已经得到了聚集数据，那么可以使用join实现聚集结果和非聚集结果合并。我们定义子查询返回根据gender分组结果，包括gender, Total_Students, Average_Age and the Total_Score 。然后和student表jion ，条件是gender。代码如下：

SELECT id, name, Aggregation.gender, Aggregation.Total_students, Aggregation.Average_Age, Aggregation.Total_Score
FROM student
INNER JOIN
(SELECT gender, count(gender) AS Total_students, AVG(age) AS Average_Age, SUM(total_score) AS Total_Score
FROM student
GROUP BY gender) AS Aggregation
on Aggregation.gender = student.gender

上面语句能够得到期望结果，但不是最佳方案，使用join和子查询增加代码复杂性，而且效率也不高（子查询产生临时表）。最佳方案将over 和partition by 子句集合使用。

使用over 和partition by

使用over 和partition by 子句，只需指定要对聚合结果进行分区的列，代码如下：

SELECT id, name, gender,
COUNT(gender) OVER (PARTITION BY gender) AS Total_students,
AVG(age) OVER (PARTITION BY gender) AS Average_Age,
SUM(total_score) OVER (PARTITION BY gender) AS Total_Score
FROM student

这种方法非常有效，非聚集列和聚集列一次性都返回了。

分组排名与排序

我们还可以利用over 和partition by进行分组排名和排序：

SELECT id, name, gender,
COUNT(gender) OVER (PARTITION BY gender) AS Total_students,
AVG(age) OVER (PARTITION BY gender) AS Average_Age,
SUM(total_score) OVER (PARTITION BY gender) AS Total_Score,
RANK() OVER (PARTITION BY gender ORDER BY total_score DESC) AS RANK_GENDER,
ROW_NUMBER() OVER (PARTITION BY gender ORDER BY total_score DESC) AS ROW_NUM
FROM student

执行结果：

ID	NAME	GENDER	Total_students	Average_Age	Total_Score	RANK_GENDER	ROW_NUM
1	Jolly	Female	5	22	2400	1	1
3	Sara	Female	5	22	2400	2	2
4	Laura	Female	5	22	2400	2	3
6	Kate	Female	5	22	2400	4	4
10	Elis	Female	5	22	2400	4	5
7	Joseph	Male	5	20	2730	1	1
8	Mice	Male	5	20	2730	2	2
9	Wise	Male	5	20	2730	3	3
5	Alan	Male	5	20	2730	4	4
2	Jon	Male	5	20	2730	5	5

我们看到排名自动跳过了重复名次。

总结

本文介绍了如何使用over 和partition by，与group by不同的是，其并没有合并记录，在一些场景中非常有用。