25 GROUP、HAVING 与聚合：防止 JOIN 把金额算大

对应项目：getCustomerDetail()。它把支付与租赁分别统计，是为了避免两个一对多集合相乘。

1. 门店客户数：从最简单的分组开始

需求：每个门店有多少启用客户，只返回数量不少于 100 的门店。

import { Op, fn, col, where as sqlWhere } from "sequelize";

const rows = await Customer.findAll({
  attributes: [
    "store_id",
    [fn("COUNT", col("customer_id")), "customer_count"],
  ],
  where: { active: 1 },
  group: ["store_id"],
  having: sqlWhere(fn("COUNT", col("customer_id")), Op.gte, 100),
  order: [["store_id", "ASC"]],
  raw: true,
});

SQL 核心：

SELECT store_id, COUNT(customer_id) AS customer_count
FROM customer
WHERE active = 1
GROUP BY store_id
HAVING COUNT(customer_id) >= 100
ORDER BY store_id ASC;

结果是一行一个门店，不再是一行一个客户。原来 Customer Model 的“完整客户类型”无法准确描述分组结果，需要独立报表行类型及运行时检查。

2. WHERE 在分组前，HAVING 在分组后

WHERE active = 1 先去掉停用客户，再计数。

HAVING COUNT(...) >= 100 筛选已经计算出来的组。

错误 SQL：

SELECT store_id, COUNT(*) FROM customer
WHERE COUNT(*) >= 100
GROUP BY store_id;

WHERE 阶段还没有分组 COUNT，不能这样写。HAVING 中可否使用别名与数据库有关；示例重复函数表达式，比依赖某个方言的别名规则更明确。

3. COUNT 的三个版本

SELECT COUNT(*) FROM rental;                    -- 记录行数
SELECT COUNT(return_date) FROM rental;          -- 非 NULL return_date 数量
SELECT COUNT(DISTINCT customer_id) FROM rental; -- 不重复客户数

对 LEFT JOIN 尤其要小心：没有租赁的客户也保留一条 NULL 扩展行。

SELECT c.customer_id,
       COUNT(*) AS join_row_count,
       COUNT(r.rental_id) AS rental_count
FROM customer AS c
LEFT JOIN rental AS r ON r.customer_id = c.customer_id
GROUP BY c.customer_id;

没有租赁时，join_row_count 是 1，rental_count 才是 0。要数孩子，数子表非空主键，不是盲目 COUNT(*)。

对应 ORM：

const rows = await Customer.findAll({
  attributes: [
    "customer_id",
    [fn("COUNT", col("rentals.rental_id")), "rental_count"],
  ],
  include: [{ model: Rental, as: "rentals", attributes: [], required: false }],
  group: [col("Customer.customer_id")],
  raw: true,
});

这里刻意不加载完整 rentals 数组；聚合与加载每条明细是两种不同目标。

4. SUM、AVG、NULL 与 COALESCE

SELECT SUM(amount), AVG(amount) FROM payment WHERE customer_id = 99999;

若没有记录，SUM/AVG 通常是 NULL，而 COUNT 是 0。

SELECT COALESCE(SUM(amount), 0) AS payment_total
FROM payment WHERE customer_id = :customer_id;

COALESCE 用 0 替换 NULL，让“没有支付记录”返回总额 0。是否应该把平均值 NULL 替换成 0 则是业务判断：没有样本与平均值为零并非同义。

SUM/AVG 通常忽略 NULL 输入。业务如果禁止金额 NULL，要用字段约束保证，不是统计时悄悄补值。

5. ONLY_FULL_GROUP_BY 不该随手关闭

错误思路：按 store_id 分组，同时 SELECT 任意 first_name：

SELECT store_id, first_name, COUNT(*)
FROM customer
GROUP BY store_id;

一个门店有很多名字，数据库不知道选谁。MySQL 的 ONLY_FULL_GROUP_BY 会拒绝不合法的非聚合列；它也会识别某些由键约束决定的函数依赖，但不能理解你未写明的业务意图。

选项是：删除不需要的列、把确实构成分组维度的列加入 GROUP BY，或明确使用 MIN/MAX 等聚合。不是为了通过 SQL 就关闭模式或随意使用 ANY_VALUE。

6. 本次练习的核心陷阱：Rental × Payment

客户有两条租赁，支付记录分别为 10 元、20 元、30 元，共三条。

错误查询：

SELECT c.customer_id, COUNT(r.rental_id), SUM(p.amount)
FROM customer AS c
LEFT JOIN rental AS r ON r.customer_id = c.customer_id
LEFT JOIN payment AS p ON p.customer_id = c.customer_id
WHERE c.customer_id = :customer_id
GROUP BY c.customer_id;

两条租赁各自搭配三条支付，得到六行。租赁被重复计数，60 元被求和成 120 元。

这不是浮点误差，也不是 Sequelize 类型问题，而是 JOIN 行集已经错误。

COUNT(DISTINCT r.rental_id) 可以修正租赁数量。
SUM(DISTINCT p.amount) 不能正确替代总额：两笔同为 10 元的支付会被误当成一笔。
顶层 distinct: true 也不会自动修好 SUM。

即使 payment 有 rental_id，也必须按真正统计口径设计 JOIN，不能仅因为有外键就认定所有组合都安全。

7. 三种正确方案

方案 A：独立查询，组合 DTO

分别查支付总額和未归还租赁数。优点是直观、容易测试；代价是多次查询，必要时需一致性快照。

方案 B：当前工程采用的独立标量子查询

interface CustomerAggregateRow {
  payment_total: string;
  open_rental_count: string;
}

const [aggregate] = await db.query<CustomerAggregateRow>(`
  SELECT
    (SELECT COALESCE(SUM(amount), 0)
     FROM payment WHERE customer_id = :customer_id) AS payment_total,
    (SELECT COUNT(*)
     FROM rental WHERE customer_id = :customer_id
       AND return_date IS NULL) AS open_rental_count
`, {
  type: QueryTypes.SELECT,
  replacements: { customer_id },
});

不需要 Payment Model，也不会出现两个集合相乘。适合单客户详情。没有外层 FROM 的 SELECT 仍返回一行；这不意味着该客户存在，要先查客户。

方案 C：预聚合后再 JOIN

适合批量客户报表的 SQL 思路：

SELECT c.customer_id,
       COALESCE(p.payment_total, 0) AS payment_total,
       COALESCE(r.open_rental_count, 0) AS open_rental_count
FROM customer AS c
LEFT JOIN (
  SELECT customer_id, SUM(amount) AS payment_total
  FROM payment GROUP BY customer_id
) AS p ON p.customer_id = c.customer_id
LEFT JOIN (
  SELECT customer_id, COUNT(*) AS open_rental_count
  FROM rental WHERE return_date IS NULL GROUP BY customer_id
) AS r ON r.customer_id = c.customer_id;

每个预聚合结果对一个客户最多一行，所以不会相乘。大报表仍需 EXPLAIN、索引和数据量评估；派生表可能扫描很多数据，不是必定比独立查询快。

8. 为什么聚合结果是字符串？

项目开启 supportBigNumbers: true, bigNumberStrings: true。

金额 SUM 的 DECIMAL 结果通常以字符串保留十进制精度。
Raw COUNT 返回整数统计类型，可能按 BIGINT 字符串读取，避免超出 JS 安全整数范围。
Sequelize 的 count() 有自己的数字转换路径，不能据此推断 Raw COUNT 也会是 number。

计数可以在检查安全整数范围后转 number：

const count = Number(aggregate.open_rental_count);
if (!Number.isSafeInteger(count) || count < 0) {
  throw new Error("计数超出有效范围");
}

金额保留 string；需要财务运算时用明确的十进制方案或最小货币单位整数方案，不直接 Number 后反复累加。也不能在已有不精确的 number 上调用 String，期待恢复精度。

9. 练习

用纸上数据计算客户无租赁时 COUNT(*) 与 COUNT(rental_id)。
构造两笔相同金额支付，证明 SUM DISTINCT 错误。
分别输出 Raw COUNT 与 Rental.count() 的值和 typeof。
把客户统计改成按门店汇总，明确“一行”的业务含义。

下一章：什么时候需要事务。