聚合与分组

聚合函数

聚合函数用于对一组值执行计算，并返回单一的值。常用的聚合函数包括：COUNT、SUM、AVG、MAX、MIN。

COUNT

COUNT 用于计算满足特定条件的记录数。

SELECT COUNT(*) FROM employees;
SELECT COUNT(DISTINCT department_id) FROM employees;

SUM

SUM 用于计算某列数值的总和。

SELECT department, SUM(salary) FROM employees GROUP BY department;

AVG

AVG 用于计算某列数值的平均值。

SELECT department, AVG(salary) FROM employees GROUP BY department;

MAX / MIN

MAX 和 MIN 用于计算某列的最大值和最小值。

SELECT department, MAX(salary) FROM employees GROUP BY department;
SELECT department, MIN(salary) FROM employees GROUP BY department;

GROUP BY 子句

GROUP BY 用于根据一个或多个列对结果集进行分组。通常与聚合函数一起使用。

按单列分组

SELECT department, AVG(salary) FROM employees GROUP BY department;

按多列分组

SELECT department, hired_at, COUNT(*) FROM employees GROUP BY department, hired_at;

HAVING 子句

HAVING 用于过滤分组后的结果，通常与 GROUP BY 一起使用。与 WHERE 不同，HAVING 是在分组之后对结果进行过滤。

SELECT department, AVG(salary) FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

排序分组结果

可以使用 ORDER BY 对分组后的结果进行排序。

SELECT department, AVG(salary) FROM employees
GROUP BY department
ORDER BY AVG(salary) DESC;

小结

聚合与分组操作是 PostgreSQL 中强大的数据分析功能，帮助用户从大量数据中提取统计信息。通过 GROUP BY 和聚合函数的结合使用，可以实现各种类型的汇总、分组和统计分析。