SQLite 聚合函数：COUNT、SUM、AVG、MIN、MAX

聚合函数到底做了什么

你目前接触到的大多数 SQL 函数都是逐行处理的：UPPER(name) 每行运行一次，ROUND(price, 2) 也是每行运行一次。聚合函数不一样。它们查看的是一整组行，并把这组行汇总成一个值。

先建一张小表来练习：

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL,
    status TEXT
);

INSERT INTO orders (customer, amount, status) VALUES
    ('Ada',   42.00, 'paid'),
    ('Boris', 15.50, 'paid'),
    ('Ada',   88.00, 'paid'),
    ('Carl',  10.00, 'refunded'),
    ('Ada',   NULL,  'pending');

SELECT COUNT(*), SUM(amount), AVG(amount) FROM orders;

五行进去，一行出来。这就是全部的思维模型：聚合函数把多行压缩成一个汇总。没有 GROUP BY 时，这个汇总覆盖结果中的所有行。

COUNT：统计行还是统计值

COUNT 有三种写法，它们的区别很重要：

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL
);

INSERT INTO orders (customer, amount) VALUES
    ('Ada',   42.00),
    ('Boris', 15.50),
    ('Ada',   88.00),
    ('Carl',  NULL),
    ('Ada',   NULL);

SELECT
    COUNT(*)               AS rows_total,
    COUNT(amount)          AS rows_with_amount,
    COUNT(DISTINCT customer) AS unique_customers
FROM orders;

• COUNT(*) 统计行数。包括 NULL，总是返回一个数字。
• COUNT(column) 统计该列中非 NULL 值的个数。
• COUNT(DISTINCT column) 统计不重复的非 NULL 值的个数。

一共五行，其中三行有 amount，有三个不同的客户。如果你看到 COUNT(amount) 比 COUNT(*) 小而感到奇怪，原因就在这里：NULL 不计入统计。

SUM、AVG、MIN、MAX

算术类聚合函数的表现和你预期的一样，只有一条不太显眼的规则：它们都会跳过 NULL：

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (10), (20), (30), (NULL);

SELECT
    SUM(amount) AS total,
    AVG(amount) AS mean,
    MIN(amount) AS smallest,
    MAX(amount) AS largest,
    COUNT(amount) AS counted
FROM orders;

AVG 的结果是 (10 + 20 + 30) / 3 = 20.0，而不是 60 / 4 = 15.0。分母是非 NULL 值的个数。如果这不是你想要的，比如你更希望把缺失的数据当作 0，就要明确写出来：

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (10), (20), (30), (NULL);

SELECT AVG(COALESCE(amount, 0)) FROM orders;

MIN 和 MAX 同样适用于文本和日期：文本按字典序比较，标准日期格式则按 ISO 字符串比较。

SUM 和 TOTAL

SQLite 还有第二个类似求和的聚合函数 TOTAL，它解决了 SUM 的两个小麻烦：

CREATE TABLE empty_table (amount REAL);

SELECT SUM(amount), TOTAL(amount) FROM empty_table;

• 对零行求 SUM 返回 NULL，TOTAL 返回 0.0。
• 对全是 NULL 的值求 SUM 返回 NULL，TOTAL 返回 0.0。
• TOTAL 总是返回浮点数，所以永远不会发生整数运算溢出。

代价是：TOTAL 不是标准 SQL，而且结果总是 REAL，如果你期望得到整数，可能会感到意外。当“没有行就等于零”正是你的应用需要的答案时用它；需要符合 SQL 标准的行为时，就继续用 SUM。

在聚合函数中使用 DISTINCT

DISTINCT 可以放进任何聚合函数，而不只是 COUNT。它会在聚合运行之前去掉重复的值：

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada',   42.00),
    ('Ada',   42.00),
    ('Boris', 15.50),
    ('Carl',  10.00);

SELECT
    SUM(amount)          AS sum_all,
    SUM(DISTINCT amount) AS sum_unique,
    COUNT(DISTINCT customer) AS unique_customers
FROM orders;

SUM(amount) 把每一行的金额相加。SUM(DISTINCT amount) 把每个不重复的金额只加一次，适用于“不重复发票金额的总和”这类场景，但你很少会真正需要它。COUNT(DISTINCT customer) 才是常用的写法。

FILTER：只聚合一部分行

当你只想聚合其中一部分行时，最直接的做法是用 WHERE。但 WHERE 会过滤所有内容，你没法用这种方式在同一个查询里既统计已付款订单又统计退款。FILTER 解决了这个问题：

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL, status TEXT);
INSERT INTO orders (amount, status) VALUES
    (42.00, 'paid'),
    (15.50, 'paid'),
    (88.00, 'paid'),
    (10.00, 'refunded'),
    (25.00, 'pending');

SELECT
    COUNT(*) FILTER (WHERE status = 'paid')      AS paid_count,
    COUNT(*) FILTER (WHERE status = 'refunded')  AS refund_count,
    SUM(amount) FILTER (WHERE status = 'paid')   AS paid_revenue
FROM orders;

每个 FILTER (WHERE ...) 子句只作用于它所在的那个聚合函数。只扫描一遍表，就能汇总出多个切片。在有 FILTER 之前，人们会写 SUM(CASE WHEN status = 'paid' THEN amount END)，思路一样，只是要多打些字。

GROUP_CONCAT：拼接字符串

GROUP_CONCAT 是个特例。它返回的不是数字，而是把所有值拼接成一个字符串：

CREATE TABLE tags (post_id INTEGER, tag TEXT);
INSERT INTO tags VALUES
    (1, 'sqlite'),
    (1, 'database'),
    (1, 'tutorial'),
    (2, 'python'),
    (2, 'sqlite');

SELECT
    post_id,
    GROUP_CONCAT(tag)        AS default_separator,
    GROUP_CONCAT(tag, ' | ') AS pipe_separated
FROM tags
GROUP BY post_id;

默认的分隔符是逗号。传入第二个参数就能换成别的分隔符。顺序是不保证的，除非你把调用写成 GROUP_CONCAT(tag ORDER BY tag)。当输出要显示在界面上、你希望它保持稳定时，这一点很有用。

不带 GROUP BY 的聚合

到目前为止，所有不带 GROUP BY 使用聚合函数的示例都只产生了一行。规则就是这样：包含聚合函数但没有 GROUP BY 的 SELECT，是对整张表（经过 WHERE 过滤之后）的单行汇总。

你可以随意组合多个聚合函数：

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL, status TEXT);
INSERT INTO orders (amount, status) VALUES
    (42.00, 'paid'), (15.50, 'paid'), (88.00, 'paid'),
    (10.00, 'refunded'), (25.00, 'pending');

SELECT
    COUNT(*)    AS orders,
    SUM(amount) AS revenue,
    AVG(amount) AS average,
    MIN(amount) AS smallest,
    MAX(amount) AS largest
FROM orders
WHERE status = 'paid';

你不能做的，是把未聚合的列和聚合函数混在一起，还指望得到合理的结果：

-- Allowed by SQLite, but the value of `customer` is arbitrary.
SELECT customer, SUM(amount) FROM orders;

SQLite 在这里不会报错（其他数据库会），但它会随便挑一个客户的名字和总数一起显示。如果你想按客户求和，就需要 GROUP BY，这正是下一页的内容。

下一步：GROUP BY 和 HAVING

对整张表聚合，回答的是“总共有多少”。按组聚合，比如按客户、按月份、按状态，能回答更有意思的问题。GROUP BY 用来在聚合之前把行分到不同的组里，HAVING 用来根据聚合结果进行过滤。这就是下一页要讲的内容。