🙏 我们需要您的帮助!SQLtest.online 没有广告,完全依靠捐款运行。哪怕一点点也很有帮助。 立即捐助 →
SQL 代码已复制到剪贴板

SQL 基本聚合函数

第 4.1 课 · 阅读时间:约 8 分钟 · 练习题 ↓

SQL 聚合函数把一组行转换为汇总指标:数量、总和、平均值、最小值和最大值。在本课中,你将通过 Sakila 示例练习最常用的聚合函数,并学会为不同任务选择合适的计数方式。学完本课后,你将能够在分析型 SQL 中熟练使用 COUNT、SUM、AVG、MIN 和 MAX。

在前面的课程中,你主要在选择单独的行。现在进入下一个重要步骤:根据数据计算汇总值。

聚合函数在报表和分析中必不可少,因为它们能快速回答“有多少?”、“总共多少?”和“平均是多少?”这类问题。

payment 表上的 SQL 聚合函数:COUNT(*) 为 16049,COUNT(DISTINCT customer_id) 为 599,amount 的 SUM、AVG、MIN 和 MAX,以及 COUNT(*)、COUNT(列) 和 COUNT(DISTINCT) 各统计什么

核心聚合函数

COUNT():统计行数

基本语法:

COUNT(expression)

示例:

SELECT COUNT(*) AS total_payments
FROM payment;

结果:查询返回 payment 表的总行数。

COUNT(column) 与 COUNT(*)

这两种写法看起来相似,但行为不同:

  • COUNT(*) 统计结果集中的所有行。
  • COUNT(column) 只统计 column 不为 NULL 的行。

如果某列包含 NULL,COUNT(column) 可能小于 COUNT(*)。

SELECT
    COUNT(*) AS total_rentals,
    COUNT(return_date) AS returned_rentals
FROM rental;

说明:total_rentals 统计所有租赁记录,而 returned_rentals 只统计 return_date 有值的行。

COUNT(DISTINCT ...):统计唯一值

如果需要的是唯一值的个数,而不仅仅是行数,请使用 COUNT(DISTINCT column)。

SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM payment;

结果:查询返回有多少位不同的客户付过款,即使一位客户有很多条付款记录。

在实践中,这对“有多少位不同的客户购买过”这类问题很重要,因为简单的 COUNT(*) 会因为重复的行而多算。

SUM():计算总和

SELECT SUM(amount) AS total_amount
FROM payment;

结果:返回 amount 列的总和。

SUM(amount) 会忽略 NULL。如果所有值都是 NULL,结果为 NULL。

AVG():计算平均值

SELECT AVG(amount) AS average_amount
FROM payment;

结果:返回非 NULL 行上的平均金额。

如果需要让含 NULL 的行也计入分母,可以使用以下方法之一:

SELECT
    AVG(amount) AS avg_ignore_null,
    AVG(COALESCE(amount, 0)) AS avg_include_null_as_zero,
    SUM(amount) / COUNT(*) AS avg_sum_div_all_rows
FROM payment;

MAX():查找最大值

SELECT MAX(amount) AS max_amount
FROM payment;

结果:返回 amount 中的最大值。

MIN():查找最小值

SELECT MIN(amount) AS min_amount
FROM payment;

结果:返回 amount 中的最小值。

MIN() 和 MAX() 都会忽略 NULL。如果所有值都是 NULL,它们返回 NULL。

MIN(column) 与 ORDER BY ... LIMIT 1

两者并不总是等价的。

SELECT MIN(column_name)
FROM table_name;

SELECT column_name
FROM table_name
ORDER BY column_name
LIMIT 1;
  • MIN(column_name) 在非 NULL 值中查找最小值。
  • ORDER BY ... LIMIT 1 返回排序后的第一行。
  • 如果你的 DBMS 把 NULL 排在最前面,第二条查询可能返回 NULL,而 MIN() 仍然返回非 NULL 的最小值。

与 MIN() 等价的可靠写法:

SELECT column_name
FROM table_name
WHERE column_name IS NOT NULL
ORDER BY column_name
LIMIT 1;

实际应用

统计客户数

SELECT COUNT(*) AS total_customers
FROM customer;

按员工统计销售总额

SELECT
    staff_id,
    SUM(amount) AS staff_total
FROM payment
GROUP BY staff_id;

按客户计算平均付款额

SELECT
    customer_id,
    AVG(amount) AS avg_payment
FROM payment
GROUP BY customer_id;

统计付过款的不同客户数

SELECT COUNT(DISTINCT customer_id) AS paying_customers
FROM payment;

常见问题

COUNT(*) 和 COUNT(column) 有什么区别?

COUNT(*) 统计结果中的所有行。COUNT(column) 只统计指定列不为 NULL 的行。

什么时候使用 COUNT(DISTINCT ...)?

当你需要唯一值的个数而不是总行数时使用,例如统计不同的客户数而不是付款总笔数。

为什么 AVG 可能返回意外的值?

因为 AVG(column) 会忽略 NULL。如果希望这些行也计入分母,可以使用 COALESCE,或者用 SUM(column) 除以 COUNT(*)。


面试问题

SQL 中的聚合函数是什么?

聚合函数对多行数据计算一个汇总值,例如计数(COUNT)、求和(SUM)或平均值(AVG)。它们为每个组或整个结果集返回一个值。

COUNT(*)、COUNT(column) 和 COUNT(DISTINCT column) 有什么区别?

COUNT(*) 统计所有行,COUNT(column) 统计该列中非 NULL 的值,COUNT(DISTINCT column) 统计非 NULL 的唯一值。

MIN 和 ORDER BY ... LIMIT 1 为什么可能返回不同的结果?

如果某列包含 NULL,而 DBMS 排序时把 NULL 放在最前面,ORDER BY ... LIMIT 1 可能返回 NULL,而 MIN() 返回非 NULL 的最小值。


本课要点:

  • 聚合函数可以快速得到汇总指标。
  • COUNT(*)、COUNT(column) 和 COUNT(DISTINCT ...) 解决不同的计数问题。
  • SUM、AVG、MIN 和 MAX 通常会忽略 NULL,这会影响分析结果。
  • 需要统计唯一实体而不是总行数时,COUNT(DISTINCT ...) 必不可少。
  • 正确处理 NULL 直接影响报表的准确性。

尝试解决以下任务,以巩固您在本课中学到的内容。

理论题

  1. SQL中的“聚合”是什么?
  2. 没有聚合的 HAVING

MySQL

  1. 电影平均时长
  2. 最小和最大替换成本
  3. 计算包含演员的电影数量

DuckDB

  1. 整体旅行统计
  2. 中位数行程费用

PostgreSQL

  1. 总预订金额
  2. 航班延误分析

MariaDB

  1. 全体学生的 GPA 汇总

SQLite

  1. 企鹅与岛屿

SQL Server

  1. 订单汇总