SQLite - GROUP BY 子句

SQLite：GROUP BY 子句

SQLite 的 GROUP BY 子句是与 SELECT 语句配合使用的一个强大功能，用于将一列或多列中具有相同值的多行数据收集并整理成单个汇总行。它是数据聚合和报表生成的基石。

GROUP BY 通常与 COUNT()、SUM()、AVG()、MAX() 和 MIN() 等聚合函数一起使用，以便对每组行执行计算。

语法

GROUP BY 子句位于 WHERE 子句（如果存在）之后，并位于 HAVING 和 ORDER BY 子句之前。子句的标准顺序对于查询的有效性至关重要：

SELECT
  column_1,
  aggregate_function(column_2)
FROM
  table_name
WHERE
  [ conditions ]
GROUP BY
  column_1
HAVING
  [ group_conditions ]
ORDER BY
  column_1;

SELECT 列表中未封装在聚合函数中的任何列都必须包含在 GROUP BY 子句中。这是一条标准的 SQL 规则，用于确保结果的确定性。

实际示例：分析员工数据

让我们考虑一个现代科技公司的 employees（员工）表。此表存储员工的详细信息，包括他们的部门和薪资。

-- 首先，我们创建表并插入一些数据。

CREATE TABLE employees (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   name TEXT NOT NULL,
   department TEXT NOT NULL,
   salary INTEGER NOT NULL
);

INSERT INTO employees (name, department, salary)
VALUES
('Alice', 'Engineering', 90000),
('Bob', 'Engineering', 85000),
('Charlie', 'Marketing', 70000),
('David', 'Engineering', 120000),
('Eve', 'Sales', 75000),
('Frank', 'Marketing', 68000),
('Grace', 'Sales', 82000);

现在，我们使用 GROUP BY 来找出每个部门的员工数量以及每个部门的总薪资支出。

SELECT
  department,
  COUNT(id) AS number_of_employees,
  SUM(salary) AS total_salary_expense
FROM
  employees
GROUP BY
  department;

此查询生成清晰的聚合汇总：

department   number_of_employees  total_salary_expense
-----------  -------------------  --------------------
Engineering  3                    295000
Marketing    2                    138000
Sales        2                    157000

使用 HAVING 子句筛选分组

如果你只想查看总薪资支出超过 150,000 美元的部门怎么办？你不能为此使用 WHERE 子句，因为 WHERE 在行分组之前进行筛选。相反，你应该使用 HAVING 子句，它在聚合之后筛选分组。

SELECT
  department,
  SUM(salary) AS total_salary_expense
FROM
  employees
GROUP BY
  department
HAVING
  SUM(salary) > 150000;

现在，结果只包含满足 HAVING 条件的分组：

department   total_salary_expense
-----------  --------------------
Engineering  295000
Sales        157000

常见错误和最佳实践

1. 混淆 WHERE 和 HAVING

这是一个常见的混淆点。请记住这条规则：WHERE 筛选单个行，而 HAVING 筛选聚合后的分组。对原始表列设置条件时使用 WHERE，对聚合函数的结果设置条件时使用 HAVING。

2. 选择未聚合、未分组的列

标准 SQL 严格禁止选择不在聚合函数中且不在 GROUP BY 列表中的列。然而，SQLite 有一个独特的特性允许这样做。例如：

-- 此查询在 SQLite 中有效，但在大多数其他 SQL 数据库中无效。
SELECT department, name FROM employees GROUP BY department;

在这种情况下，SQLite 将从每个部门分组中的任意一行返回一个 name 值。这可能是不可预测的，并且通常被认为是糟糕的实践，除非你故意获取一个样本值。为了获得可预测的结果，请始终聚合或按你选择的列进行分组。

最佳实践：使用别名以提高清晰度

使用 AS 关键字为你的聚合列（如 number_of_employees）创建别名，可以使你的查询及其结果更易于阅读和理解。