PostgreSQL - MAX 函数

PostgreSQL 聚合函数：MAX, MIN, AVG, SUM, COUNT

聚合函数对一组行执行计算并返回单个汇总值。它们对于数据分析和报告至关重要。虽然 MAX() 是一个很好的起点，但最好与它的伙伴：MIN、AVG、SUM 和 COUNT 一起学习。

设置：示例 ‘products’ 表

让我们使用 products 表来探索这些函数：

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50),
    price NUMERIC(10, 2),
    stock_quantity INT
);

INSERT INTO products (name, category, price, stock_quantity) VALUES
('Laptop', 'Electronics', 1200.00, 10),
('Mouse', 'Electronics', 25.00, 150),
('Desk Chair', 'Furniture', 150.00, 30),
('Keyboard', 'Electronics', 75.00, 75),
('Bookshelf', 'Furniture', 250.00, 15);

核心聚合函数

这些函数可以用于整个表以获得单个结果。

-- 查找最贵和最便宜的产品价格
SELECT MAX(price) AS max_price, MIN(price) AS min_price FROM products;
-- 结果：max_price = 1200.00, min_price = 25.00

-- 查找所有库存的平均价格和总价值
SELECT AVG(price) AS average_price, SUM(price * stock_quantity) AS total_stock_value FROM products;
-- 结果：average_price = 340.00, total_stock_value = 26025.00

理解 COUNT

COUNT 很特别，因为它的行为取决于您给它的参数：

• COUNT(*)：计算集合中的总行数。SELECT COUNT(*) FROM products; -- 返回 5
• COUNT(column_name)：计算 column_name 不为 NULL 的行数。
• COUNT(DISTINCT column_name)：计算列中唯一、非 NULL 值的数量。SELECT COUNT(DISTINCT category) FROM products; -- 返回 2

使用 GROUP BY 分组数据

聚合函数的真正力量在于与 GROUP BY 子句结合使用。GROUP BY 根据列值将多行折叠为单个汇总行。它允许您为每个组运行聚合计算。

示例：获取每个类别的摘要

让我们查找每个类别中的产品数量、平均价格和最贵产品。

SELECT
    category,
    COUNT(*) AS number_of_products,
    ROUND(AVG(price), 2) AS average_price,
    MAX(price) AS max_price
FROM products
GROUP BY category;

此查询生成一份强大的汇总报告：

  category   | number_of_products | average_price | max_price
-------------+--------------------+---------------+-----------
 Electronics |                  3 |        433.33 |   1200.00
 Furniture   |                  2 |        200.00 |    250.00
(2 rows)

使用 HAVING 过滤组

如果您想根据聚合值过滤结果怎么办？您不能使用 WHERE，因为 WHERE 在聚合发生之前过滤行。为此，您需要 HAVING 子句。

示例：查找产品数量超过 2 个的类别

SELECT
    category,
    COUNT(*) AS number_of_products
FROM products
GROUP BY category
HAVING COUNT(*) > 2;

此查询首先按类别分组并计数产品，然后过滤这些组，只保留满足 HAVING 条件的组。

  category   | number_of_products
-------------+--------------------
 Electronics |                  3
(1 row)

最佳实践和关键概念

• WHERE vs. HAVING：记住操作顺序：WHERE 在 GROUP BY 之前过滤行，而 HAVING 在 GROUP BY 之后过滤组。
• NULL 处理：大多数聚合函数（COUNT(*) 除外）在计算中会忽略 NULL 值。
• 性能：对 GROUP BY 子句中使用的列创建索引可以显著加快大型表上的聚合查询。
• 现代聚合：PostgreSQL 还有许多其他强大的聚合函数，如 STRING_AGG（将字符串连接成一个）和 ARRAY_AGG（将值收集到数组中），这些对于高级数据整形非常有用。