分组

概述

在处理大数据时，通常需要在不同层次上对结果进行聚合。例如，统计每个班级的学生人数、找出每个类别的最高价格等。在本课中，我们将学习如何使用 SQL 对结果进行分组并计算聚合值。

简单分组

假设我们有一个员工表，记录每位员工的姓名、角色、部门和薪资。该表结构如下：

1CREATE TABLE emp AS 
2    SELECT * 
3    FROM (VALUES
4        ('Haki',    'R&D',      'Manager',   10000),
5        ('Dan',     'R&D',      'Developer', 7000),
6        ('Jax',     'R&D',      'Developer', 7500),
7        ('George',  'Sales',    'Manager',   8500),
8        ('Bill',    'Sales',    'Developer', 8000),
9        ('David',   'Sales',    'Developer', 8000)
10    ) AS t(
11        name,       department,  role,       salary
12    );
13
14SELECT * FROM emp;

要统计每个部门的员工数量，可以使用 GROUP BY 子句：

1SELECT 
2    department, 
3    COUNT(*) AS number_of_employees
4FROM
5    emp
6GROUP BY 
7    department;

还可以同时计算每个部门的最大薪资和平均薪资：

1SELECT 
2    department, 
3    COUNT(*) AS number_of_employees,
4    MAX(salary) AS max_salary,
5    AVG(salary) AS average_salary
6FROM
7    emp
8GROUP BY 
9    department;

如果希望按部门和角色两个维度进行分组：

1SELECT 
2    department,
3    role,
4    COUNT(*) AS number_of_employees,
5    MAX(salary) AS max_salary,
6    AVG(salary) AS average_salary
7FROM
8    emp
9GROUP BY 
10    department,
11    role;

也可以使用列的位置编号代替列名进行分组（1 表示第一个 SELECT 列，即 department；2 表示 role）：

1SELECT 
2    department,
3    role,
4    COUNT(*) AS number_of_employees,
5    MAX(salary) AS max_salary,
6    AVG(salary) AS average_salary
7FROM
8    emp
9GROUP BY 
10    1, 2

提取员工姓名的首字母：

1SELECT name, substring(name, 1, 1) AS initial FROM emp;

按姓名首字母分组统计人数：

1SELECT 
2    substring(name, 1, 1) AS initial,
3    COUNT(*) 
4FROM 
5    emp
6GROUP BY
7    substring(name, 1, 1);

此时可以使用别名进行分组：

1SELECT 
2    substring(name, 1, 1) AS initial,
3    COUNT(*) 
4FROM 
5    emp
6GROUP BY
7    initial;

按角色分组，计算每种角色的平均薪资：

1SELECT 
2  role, 
3  AVG(salary) as average_salary_for_role
4FROM
5  emp
6GROUP BY 
7  role;

若只想保留平均薪资高于 8000 的角色，可使用 HAVING 子句过滤分组结果：

1SELECT 
2  role, 
3  AVG(salary) as average_salary_for_role
4FROM
5  emp
6GROUP BY
7  role
8HAVING
9  AVG(salary) > 8000;