PostgreSQL: PostgreSQL聚合函数与分组

Q: COUNT(*) 和 COUNT(1) 有区别吗？

PostgreSQL 中完全等价，COUNT(*) 是推荐写法，语义更清晰。

Q: 为什么 SUM 对空组返回 NULL 而不是 0？

SQL 标准规定：全 NULL 或空集的 SUM 返回 NULL。如需 0，用 COALESCE(SUM(col), 0)。

Q: HAVING 能否不用聚合函数？

可以，HAVING region = 'NA' 语法合法，但这种条件应放在 WHERE 中更高效。

Q: FILTER 子句和 CASE WHEN 性能一样吗？

基本一样，都只需扫描一次数据。FILTER 语法更清晰，是 PostgreSQL 推荐写法。

Q: GROUPING SETS 和 UNION 多条 SQL 有何区别？

GROUPING SETS 只需扫描一次表，UNION 多条 SQL 扫描多次。大数据量时性能差异显著。

Q: GROUP BY 后 SELECT 能出现非分组列吗？

PostgreSQL 严格模式不允许。SELECT 中的非聚合列必须出现在 GROUP BY 中，否则报错。

最后更新：2026-08-26

1. 你将学到

五大核心聚合函数：COUNT / SUM / AVG / MAX / MIN
GROUP BY 单列与多列分组
HAVING 子句过滤分组结果
PostgreSQL 特色：GROUPING SETS / ROLLUP / CUBE 多维分组
PostgreSQL 特色：FILTER 子句实现条件聚合
DISTINCT 在聚合中的应用
聚合函数对 NULL 的处理行为

2. 故事

Bob 是一家跨境电商平台的数据分析师。年底了，CEO 要求他做一份年度销售分析报告：

按 region 统计每个区域的订单数与销售额
按 quarter + region 多维度交叉分析
计算有订单月份与无订单月份的销售额差异
一次性产出所有维度的汇总数据

Bob 发现普通 GROUP BY 只能按一种维度分组，要写多条 SQL 再 UNION。直到他学到了 PostgreSQL 的 GROUPING SETS 和 FILTER 子句，一条 SQL 就搞定了所有需求。

3. Concept

(1) 聚合函数概览

聚合函数将多行输入折叠为一行输出，是数据分析的基石。

SQL

SELECT
  COUNT(*)        AS total_rows,
  COUNT(amount)   AS non_null_count,
  SUM(amount)     AS total_amount,
  AVG(amount)     AS avg_amount,
  MAX(amount)     AS max_amount,
  MIN(amount)     AS min_amount
FROM orders;

TEXT 📖 仅展示

 total_rows | non_null_count | total_amount | avg_amount | max_amount | min_amount
------------+----------------+--------------+------------+------------+------------
        100 |             95 |     12500000 |  131578.95 |     500000 |       1200

(2) 五大聚合函数详解

函数	作用	NULL 行为	返回类型
`COUNT(*)`	统计所有行（含 NULL）	包含 NULL	bigint
`COUNT(col)`	统计非 NULL 行	忽略 NULL	bigint
`SUM(col)`	求和	忽略 NULL，全 NULL 返回 NULL	与输入同类型
`AVG(col)`	求均值	忽略 NULL	numeric
`MAX(col)` / `MIN(col)`	最大/最小值	忽略 NULL	与输入同类型

▶ 示例：COUNT(*) vs COUNT(col)

SQL

SELECT
  COUNT(*)       AS all_rows,
  COUNT(discount) AS rows_with_discount
FROM orders;

TEXT 📖 仅展示

 all_rows | rows_with_discount
----------+-------------------
      100 |                 42

58 行 discount 为 NULL，COUNT(discount) 不计入。

▶ 示例：SUM 与 AVG 对 NULL 的处理

SQL

SELECT
  SUM(discount)  AS total_discount,
  AVG(discount)  AS avg_discount
FROM orders
WHERE region = 'NA';

TEXT 📖 仅展示

 total_discount |     avg_discount
----------------+--------------------
         125000 | 2976.1904761904762

AVG 只对非 NULL 行求平均：125000 / 42 ≈ 2976.19，而非 125000 / 100。

▶ 示例：MAX/MIN 获取极值

SQL

SELECT
  MAX(created_at) AS latest_order,
  MIN(created_at) AS earliest_order
FROM orders;

TEXT 📖 仅展示

     latest_order      |    earliest_order
------------------------+------------------------
 2025-12-28 15:30:00   | 2025-01-03 09:12:00

▶ 示例：聚合空结果集

SQL

SELECT
  COUNT(*)  AS cnt,
  SUM(amount) AS total
FROM orders
WHERE region = 'ANTARCTICA';

TEXT 📖 仅展示

 cnt | total
-----+-------
   0 |

COUNT 对空集返回 0，SUM 对空集返回 NULL——这是最容易踩的坑。

(3) GROUP BY 分组

GROUP BY 将行按指定列分组，每组产生一行聚合结果。

SQL

SELECT
  region,
  COUNT(*)    AS order_count,
  SUM(amount) AS total_amount
FROM orders
GROUP BY region;

TEXT 📖 仅展示

 region | order_count | total_amount
--------+-------------+--------------
 EU     |          35 |      4200000
 NA     |          45 |      5800000
 APAC   |          20 |      2500000

▶ 示例：GROUP BY 多列

SQL

SELECT
  region,
  EXTRACT(QUARTER FROM created_at)::int AS quarter,
  COUNT(*)    AS order_count,
  SUM(amount) AS total_amount
FROM orders
GROUP BY region, EXTRACT(QUARTER FROM created_at)
ORDER BY region, quarter;

TEXT 📖 仅展示

 region | quarter | order_count | total_amount
--------+---------+-------------+--------------
 APAC   |       1 |           5 |       620000
 APAC   |       2 |           6 |       780000
 APAC   |       3 |           4 |       500000
 APAC   |       4 |           5 |       600000
 EU     |       1 |           8 |       950000
 EU     |       2 |           9 |      1100000
 ...

(4) HAVING 过滤分组

WHERE 在分组前过滤行，HAVING 在分组后过滤组。

子句	作用时机	能否用聚合函数
WHERE	GROUP BY 之前	否
HAVING	GROUP BY 之后	是

▶ 示例：HAVING 过滤高销售额区域

SQL

SELECT
  region,
  SUM(amount) AS total_amount
FROM orders
GROUP BY region
HAVING SUM(amount) > 3000000
ORDER BY total_amount DESC;

TEXT 📖 仅展示

 region | total_amount
--------+--------------
 NA     |      5800000
 EU     |      4200000

APAC 的 2500000 被过滤掉。

▶ 示例：WHERE + HAVING 组合

SQL

SELECT
  region,
  COUNT(*)    AS order_count,
  SUM(amount) AS total_amount
FROM orders
WHERE amount >= 5000
GROUP BY region
HAVING COUNT(*) >= 10
ORDER BY total_amount DESC;

TEXT 📖 仅展示

 count 
-------
     5
(1 row)

先筛掉金额 < 5000 的订单，再筛掉组内不足 10 条的区域。

4. Key Points

(1) DISTINCT 在聚合中的应用

▶ 示例：统计去重客户数

SQL

SELECT
  COUNT(DISTINCT customer_id) AS unique_customers,
  COUNT(*)                    AS total_orders
FROM orders;

TEXT 📖 仅展示

 unique_customers | total_orders
------------------+--------------
              780 |          1000

▶ 示例：SUM DISTINCT 避免重复计算

SQL

SELECT
  SUM(DISTINCT bonus) AS unique_bonus_total
FROM employee_targets;

TEXT 📖 仅展示

  result  
----------
   42.50
(1 row)

(2) FILTER 子句（PostgreSQL 特色）

FILTER 允许对同一组数据按不同条件分别聚合，避免写多条 CASE WHEN。

SQL

SELECT
  region,
  COUNT(*) FILTER (WHERE amount >= 10000)  AS high_value_orders,
  COUNT(*) FILTER (WHERE amount < 10000)   AS low_value_orders,
  SUM(amount) FILTER (WHERE quarter = 1)   AS q1_revenue,
  SUM(amount) FILTER (WHERE quarter = 2)   AS q2_revenue
FROM orders
GROUP BY region;

方式	语法	可读性	性能
CASE WHEN	`SUM(CASE WHEN ... THEN x ELSE 0 END)`	一般	一次扫描
FILTER	`SUM(x) FILTER (WHERE ...)`	优秀	一次扫描

▶ 示例：FILTER 计算有/无订单月份

SQL

SELECT
  region,
  COUNT(DISTINCT EXTRACT(MONTH FROM created_at))
    FILTER (WHERE amount > 0)  AS months_with_orders,
  12 - COUNT(DISTINCT EXTRACT(MONTH FROM created_at))
    FILTER (WHERE amount > 0) AS months_without_orders
FROM orders
WHERE EXTRACT(YEAR FROM created_at) = 2025
GROUP BY region;

TEXT 📖 仅展示

 count 
-------
     5
(1 row)

▶ 示例：FILTER 做同比对比

SQL

SELECT
  region,
  SUM(amount) FILTER (WHERE EXTRACT(YEAR FROM created_at) = 2024) AS revenue_2024,
  SUM(amount) FILTER (WHERE EXTRACT(YEAR FROM created_at) = 2025) AS revenue_2025
FROM orders
GROUP BY region;

TEXT 📖 仅展示

  result  
----------
   42.50
(1 row)

(3) GROUPING SETS / ROLLUP / CUBE（PostgreSQL 特色）

一次查询产出多个维度的聚合结果，无需写多条 SQL 再 UNION。

▶ 示例：GROUPING SETS 自定义维度组合

SQL

SELECT
  region,
  EXTRACT(QUARTER FROM created_at)::int AS quarter,
  SUM(amount) AS total_amount
FROM orders
GROUP BY GROUPING SETS (
  (region, EXTRACT(QUARTER FROM created_at)),
  (region),
  (EXTRACT(QUARTER FROM created_at)),
  ()
)
ORDER BY region NULLS LAST, quarter NULLS LAST;

TEXT 📖 仅展示

 region | quarter | total_amount
--------+---------+--------------
 APAC   |       1 |       620000
 APAC   |       2 |       780000
 APAC   |       3 |       500000
 APAC   |       4 |       600000
 APAC   |         |      2500000
 EU     |       1 |       950000
 ...
        |       1 |      2200000
 ...
        |         |     12500000

NULL 代表该维度为汇总级别，用 GROUPING() 函数区分真实 NULL 和汇总 NULL。

▶ 示例：ROLLUP 层级汇总

SQL

SELECT
  region,
  EXTRACT(QUARTER FROM created_at)::int AS quarter,
  SUM(amount) AS total_amount,
  GROUPING(region) AS g_region,
  GROUPING(quarter) AS g_quarter
FROM orders
GROUP BY ROLLUP (region, EXTRACT(QUARTER FROM created_at))
ORDER BY region NULLS LAST, quarter NULLS LAST;

TEXT 📖 仅展示

  result  
----------
   42.50
(1 row)

语法	等价 GROUPING SETS	产出维度
`ROLLUP(a, b)`	`(a,b), (a), ()`	层级：明细→小计→总计
`CUBE(a, b)`	`(a,b), (a), (b), ()`	全排列：所有组合
`GROUPING SETS((a),(b))`	`(a), (b)`	自定义任意组合

▶ 示例：CUBE 全维度交叉

SQL

SELECT
  region,
  EXTRACT(QUARTER FROM created_at)::int AS quarter,
  SUM(amount) AS total_amount
FROM orders
GROUP BY CUBE (region, EXTRACT(QUARTER FROM created_at))
ORDER BY region NULLS LAST, quarter NULLS LAST;

TEXT 📖 仅展示

  result  
----------
   42.50
(1 row)

(4) 聚合函数 NULL 行为汇总

场景	COUNT(*)	COUNT(col)	SUM	AVG	MAX/MIN
有非 NULL 值	全部计数	只计非 NULL	忽略 NULL 求和	忽略 NULL 求均值	忽略 NULL 取极值
全部为 NULL	计行数	0	NULL	NULL	NULL
空结果集	0	0	NULL	NULL	NULL

5. Practice

▶ 示例：按产品类别统计销售额 Top 3

SQL

SELECT
  category,
  SUM(amount) AS total_amount
FROM orders
GROUP BY category
ORDER BY total_amount DESC
LIMIT 3;

TEXT 📖 仅展示

  result  
----------
   42.50
(1 row)

▶ 示例：每个区域的客户留存率

SQL

SELECT
  region,
  COUNT(DISTINCT customer_id) FILTER (
    WHERE EXTRACT(YEAR FROM created_at) = 2024
  ) AS customers_2024,
  COUNT(DISTINCT customer_id) FILTER (
    WHERE EXTRACT(YEAR FROM created_at) = 2025
  ) AS customers_2025,
  ROUND(
    COUNT(DISTINCT customer_id) FILTER (WHERE EXTRACT(YEAR FROM created_at) = 2025)::numeric
    / NULLIF(
      COUNT(DISTINCT customer_id) FILTER (WHERE EXTRACT(YEAR FROM created_at) = 2024),
      0
    ) * 100, 1
  ) AS retention_rate
FROM orders
GROUP BY region;

TEXT 📖 仅展示

 count 
-------
     5
(1 row)

▶ 示例：月度同比趋势

SQL

SELECT
  EXTRACT(MONTH FROM created_at)::int AS month_num,
  SUM(amount) FILTER (WHERE EXTRACT(YEAR FROM created_at) = 2024) AS revenue_2024,
  SUM(amount) FILTER (WHERE EXTRACT(YEAR FROM created_at) = 2025) AS revenue_2025
FROM orders
GROUP BY EXTRACT(MONTH FROM created_at)
ORDER BY month_num;

TEXT 📖 仅展示

  result  
----------
   42.50
(1 row)

▶ 示例：多维度销售报告（ROLLUP + FILTER）

SQL

SELECT
  region,
  category,
  SUM(amount) AS total_amount,
  COUNT(*) FILTER (WHERE amount >= 50000) AS big_deals,
  GROUPING(region)  AS g_region,
  GROUPING(category) AS g_category
FROM orders
GROUP BY ROLLUP (region, category)
ORDER BY region NULLS LAST, category NULLS LAST;

TEXT 📖 仅展示

 count 
-------
     5
(1 row)

▶ 示例：分组后筛选高频客户

SQL

SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 5 AND SUM(amount) >= 100000
ORDER BY total_spent DESC;

TEXT 📖 仅展示

 count 
-------
     5
(1 row)

6. Comprehensive Example

Bob 的年度销售分析——一条 SQL 产出全维度报告：

SQL

SELECT
  region,
  EXTRACT(QUARTER FROM created_at)::int AS quarter,
  SUM(amount)                              AS total_revenue,
  COUNT(*)                                 AS order_count,
  COUNT(DISTINCT customer_id)              AS unique_customers,
  SUM(amount) FILTER (WHERE amount >= 50000) AS big_deal_revenue,
  COUNT(*)  FILTER (WHERE amount >= 50000)   AS big_deal_count,
  AVG(amount)                              AS avg_order_value,
  GROUPING(region)  AS g_region,
  GROUPING(quarter) AS g_quarter
FROM orders
WHERE EXTRACT(YEAR FROM created_at) = 2025
GROUP BY ROLLUP (region, EXTRACT(QUARTER FROM created_at))
ORDER BY region NULLS LAST, quarter NULLS LAST;

TEXT 📖 仅展示

 region | quarter | total_revenue | order_count | unique_customers | big_deal_revenue | big_deal_count | avg_order_value | g_region | g_quarter
--------+---------+---------------+-------------+------------------+------------------+----------------+-----------------+----------+-----------
 APAC   |       1 |        620000 |           5 |                4 |          120000  |              1 |     124000.00   |        0 |         0
 APAC   |       2 |        780000 |           6 |                5 |          250000  |              2 |     130000.00   |        0 |         0
 APAC   |       3 |        500000 |           4 |                3 |           50000  |              1 |     125000.00   |        0 |         0
 APAC   |       4 |        600000 |           5 |                4 |          100000  |              1 |     120000.00   |        0 |         0
 APAC   |         |       2500000 |          20 |               12 |          520000  |              5 |     125000.00   |        0 |         1
 EU     |       1 |        950000 |           8 |                7 |          350000  |              3 |     118750.00   |        0 |         0
 ...
        |         |      12500000 |         100 |               780 |         5000000  |             45 |     125000.00   |        1 |         1

7. 执行流程

聚合查询的完整执行顺序：

flowchart TD
    A[FROM] --> B[WHERE]
    B --> C[GROUP BY]
    C --> D[HAVING]
    D --> E["Aggregate Functions<br/>COUNT/SUM/AVG/MAX/MIN"]
    E --> F[SELECT]
    F --> G[ORDER BY]
    G --> H[LIMIT]

    style A fill:#e1f5fe
    style C fill:#fff9c4
    style D fill:#fff9c4
    style E fill:#c8e6c9

步骤	子句	说明
1	FROM	确定数据源
2	WHERE	过滤行（分组前）
3	GROUP BY	分组
4	聚合函数	对每组计算
5	HAVING	过滤组（分组后）
6	SELECT	选择输出列
7	ORDER BY	排序
8	LIMIT	限制行数

❓ 常见问题

Q COUNT(*) 和 COUNT(1) 有区别吗？

A PostgreSQL 中完全等价，COUNT(*) 是推荐写法，语义更清晰。

Q 为什么 SUM 对空组返回 NULL 而不是 0？

A SQL 标准规定：全 NULL 或空集的 SUM 返回 NULL。如需 0，用 COALESCE(SUM(col), 0)。

Q HAVING 能否不用聚合函数？

A 可以，HAVING region = 'NA' 语法合法，但这种条件应放在 WHERE 中更高效。

Q FILTER 子句和 CASE WHEN 性能一样吗？

A 基本一样，都只需扫描一次数据。FILTER 语法更清晰，是 PostgreSQL 推荐写法。

Q GROUPING SETS 和 UNION 多条 SQL 有何区别？

A GROUPING SETS 只需扫描一次表，UNION 多条 SQL 扫描多次。大数据量时性能差异显著。

Q GROUP BY 后 SELECT 能出现非分组列吗？

A PostgreSQL 严格模式不允许。SELECT 中的非聚合列必须出现在 GROUP BY 中，否则报错。

📖 小节

五大聚合函数 COUNT/SUM/AVG/MAX/MIN 对 NULL 有不同处理策略
GROUP BY 按列分组，HAVING 过滤分组结果
WHERE 在分组前过滤行，HAVING 在分组后过滤组
PostgreSQL FILTER 子句替代 CASE WHEN，语法更简洁
GROUPING SETS / ROLLUP / CUBE 一次查询产出多维汇总
GROUPING() 函数区分真实 NULL 与汇总级别的 NULL
COUNT(*) 对空集返回 0，SUM/AVG 对空集返回 NULL

📝 作业

⭐ 统计 orders 表中每个 region 的订单数和总金额，按金额降序排列
⭐ 查询订单数超过 10 笔且平均金额超过 50000 USD 的 customer_id
⭐⭐ 使用 FILTER 子句，在一条 SQL 中输出每个 region 的 Q1~Q4 四个季度销售额
⭐⭐ 使用 CUBE 对 (region, category) 做全维度交叉汇总，并用 GROUPING() 标记汇总行
⭐⭐⭐ 写一条 SQL 同时输出：按 region 汇总、按 category 汇总、按 region+category 明细、以及全表总计，只扫描 orders 表一次

Web-Tutorial 技术团队

由多位开发者共同维护的编程教程平台。每篇教程由对应领域的开发者编写和审核，确保内容准确可靠。如发现任何问题，欢迎向我们反馈。

关于我们

PostgreSQL: PostgreSQL聚合函数与分组

1. 你将学到

2. 故事

3. Concept

(1) 聚合函数概览

(2) 五大聚合函数详解

▶ 示例：COUNT(*) vs COUNT(col)

▶ 示例：SUM 与 AVG 对 NULL 的处理

▶ 示例：MAX/MIN 获取极值

▶ 示例：聚合空结果集

(3) GROUP BY 分组

▶ 示例：GROUP BY 多列

(4) HAVING 过滤分组

▶ 示例：HAVING 过滤高销售额区域

▶ 示例：WHERE + HAVING 组合

4. Key Points

(1) DISTINCT 在聚合中的应用

▶ 示例：统计去重客户数

▶ 示例：SUM DISTINCT 避免重复计算

(2) FILTER 子句（PostgreSQL 特色）

▶ 示例：FILTER 计算有/无订单月份

▶ 示例：FILTER 做同比对比

(3) GROUPING SETS / ROLLUP / CUBE（PostgreSQL 特色）

▶ 示例：GROUPING SETS 自定义维度组合

▶ 示例：ROLLUP 层级汇总

▶ 示例：CUBE 全维度交叉

(4) 聚合函数 NULL 行为汇总

5. Practice

▶ 示例：按产品类别统计销售额 Top 3

▶ 示例：每个区域的客户留存率

▶ 示例：月度同比趋势

▶ 示例：多维度销售报告（ROLLUP + FILTER）

▶ 示例：分组后筛选高频客户

6. Comprehensive Example

7. 执行流程

❓ 常见问题

📖 小节

📝 作业

🙏 帮我们做得更好