【PostgreSQL 17】4 聚合与分组

聚合函数会忽略NULL数据行，但COUNT(*)除外

一、聚合函数

SELECT
	COUNT(manager_id),
	SUM(salary),
	AVG(salary),
	MAX(salary),
	MIN(salary)
FROM
	employees
;

SELECT
	COUNT(manager_id),
	COUNT(*)
FROM
	employees
;

SELECT
	COUNT(*) FILTER (WHERE department_id = 10) AS dept10_count
FROM
	employees
;

字符串聚合

将first_name按升序聚合为1行，用；分隔

SELECT
	STRING_AGG(
		first_name,
		';' ORDER BY first_name
	)
FROM
	employees
;

二、分组统计

统计每年的入职人数

SELECT
	EXTRACT(YEAR FROM hire_date),
	COUNT(*)
FROM
	employees
GROUP BY
	EXTRACT(YEAR FROM hire_date)
;

可以简写为1，这样会按照SELECT的第一个属性来分组

SELECT
	EXTRACT(YEAR FROM hire_date),
	COUNT(*)
FROM
	employees
GROUP BY
	1
;

HAVING 分组后过滤

SELECT
	department_id,
	COUNT(*)
FROM
	employees
GROUP BY
	department_id
HAVING
	COUNT(*) > 10
;

常见的错误

三、高级分组

示例数据

CREATE TABLE sales (
	item VARCHAR(10),
	year VARCHAR(4),
	quantity INT
);

INSERT INTO sales VALUES('apple', '2018', 800);
INSERT INTO sales VALUES('apple', '2018', 1000);
INSERT INTO sales VALUES('banana', '2018', 500);
INSERT INTO sales VALUES('banana', '2018', 600);
INSERT INTO sales VALUES('apple', '2019', 1200);
INSERT INTO sales VALUES('banana', '2019', 1800);

ROLLUP

SELECT
	item,
	year,
	SUM(quantity)
FROM
	sales
GROUP BY
	ROLLUP(item, year) -- 小计+合计
;

COALESCE

给null默认值

SELECT
	COALESCE(item, '所有产品') AS "产品",
	COALESCE(year, '所有年份') AS "年份",
	SUM(quantity)
FROM
	sales
GROUP BY
	ROLLUP(item, year) -- 小计+合计
;

CUBE

SELECT
	COALESCE(item, '所有产品') AS "产品",
	COALESCE(year, '所有年份') AS "年份",
	SUM(quantity)
FROM
	sales
GROUP BY
	CUBE(item, year)
;

GROUPING SETS

ROLLUP和CUBE 实际上就是GROUPING SET的特例
ROLLUP(item, year) = GROUPING SETS ((item, year), (item), ())
CUBE(item, year) = GROUPING SETS ((item, year), (item), (year), ())

GROUPING 判断是否汇总信息

假设存在item为NULL的数据

INSERT INTO sales VALUES (NULL, '2018', 10000);

SELECT
	COALESCE(item, '所有产品') AS "产品",
	COALESCE(year, '所有年份') AS "年份",
	SUM(quantity),
	GROUPING(item),
	GROUPING(year),
	GROUPING(item, year)
FROM
	sales
GROUP BY
	ROLLUP(item, year)
;

参考资料

[1] 不剪发的Tony老师 PostgreSQL开发指南 第12节~第14节