如何用 SQL 高效计算多表 usage 客户账单

结论

关联、计费和汇总应尽量交给数据库处理，PHP 只读取聚合结果并生成 invoice。不要按 customer、terminal 或 usage record 逐个循环查询，否则会产生 N+1 查询。面对 500 万条 usage 数据，大量网络传输和对象创建会严重拖慢处理速度。

处理流程如下：

1. 按 billing period 过滤 usage。
2. 用 term_ident 关联 terminals.term_number。不存在的 terminal 会被 INNER JOIN 自动忽略。
3. 通过 terminals.tariff_id 找到 tariff。
4. 使用 CASE 将 usage_type 映射到 cost_1、cost_2、cost_3。
5. 在 SQL 中计算 usage_amount * unit_cost，再按 customer、日期和 terminal 汇总。
6. PHP 通过 Doctrine DBAL 流式读取结果，不进行 ORM 对象 hydration。

用一条 SQL 完成计费

假设：

• usage.date 是真正的 DATE 或 DATETIME 字段；
• terminals.term_number 唯一标识一个 terminal；
• usage_type 目前只有 1、2、3；
• 账期包含 2010-12-15 和 2011-01-15 两个端点。

可以使用条件聚合：

SELECT
    c.cust_id,
    c.cust_name,
    DATE(u.usage_date) AS usage_date,
    t.term_number,

    SUM(
        CASE
            WHEN u.usage_type = 1
            THEN u.usage_amount * tr.cost_1
            ELSE 0
        END
    ) AS usage_cost_1,

    SUM(
        CASE
            WHEN u.usage_type = 2
            THEN u.usage_amount * tr.cost_2
            ELSE 0
        END
    ) AS usage_cost_2,

    SUM(
        CASE
            WHEN u.usage_type = 3
            THEN u.usage_amount * tr.cost_3
            ELSE 0
        END
    ) AS usage_cost_3,

    SUM(
        u.usage_amount *
        CASE u.usage_type
            WHEN 1 THEN tr.cost_1
            WHEN 2 THEN tr.cost_2
            WHEN 3 THEN tr.cost_3
            ELSE 0
        END
    ) AS total_cost

FROM `usage` AS u
INNER JOIN terminals AS t
    ON t.term_number = u.term_ident
INNER JOIN tariffs AS tr
    ON tr.tariff_id = t.tariff_id
INNER JOIN customers AS c
    ON c.cust_id = t.cust_id

WHERE u.usage_date >= '2010-12-15'
  AND u.usage_date <  '2011-01-16'

GROUP BY
    c.cust_id,
    c.cust_name,
    DATE(u.usage_date),
    t.term_number

ORDER BY
    c.cust_id,
    usage_date,
    t.term_number;

这里使用半开区间：

usage_date >= '2010-12-15'
AND usage_date < '2011-01-16'

即使 usage_date 是包含时间的 DATETIME，这种写法也不会漏掉 2011-01-15 当天的数据。不要写成：

usage_date <= '2011-01-15'

对于 DATETIME 字段，这通常只会包含当天 00:00:00 的数据。

如果 “15/01/2011” 原本就是下一账期的起点，则应改为：

usage_date >= '2010-12-15'
AND usage_date <  '2011-01-15'

示例数据并不一致

按照题目给出的实际表结构，customer 2 在 2011-01-01 有两条记录：

usage_type 1: 1 × cost_1(2) = 2
usage_type 2: 5 × cost_2(0) = 0

因此，这组数据对应的结果应是：

Customer 2

date         terminal  usage_cost_1  usage_cost_2  usage_cost_3  total_cost
01/01/2011   14324     2             0             0             2

给出的期望值 18、0、6、24 无法从示例中的 usage 和 tariff 推导出来。第一条 usage 的 usage_amount 是 20，按 tariff 1 应计算为 20 × 2 = 40，而不是 90 × 2 = 180。如果实际业务还包含倍率、最低消费、单位换算或未展示的 usage 数据，需要把相应规则补充到 SQL 中。

更易维护的 tariff 表结构

当 usage 类型固定且数量很少时，可以使用 cost_1、cost_2、cost_3 这种横向结构，但它不便扩展。每增加一种 usage 类型，都要修改表结构和 SQL。

更适合长期维护的做法是把 tariff cost 拆成明细表：

CREATE TABLE tariff_rates (
    tariff_id   BIGINT NOT NULL,
    usage_type  SMALLINT NOT NULL,
    unit_cost   DECIMAL(18, 6) NOT NULL,
    PRIMARY KEY (tariff_id, usage_type),
    FOREIGN KEY (tariff_id) REFERENCES tariffs(tariff_id)
);

数据示例：

tariff_id  usage_type  unit_cost
1          1           2
1          2           0
1          3           3
2          1           1
2          2           1
2          3           1

改成这种结构后，不再需要通过 CASE 选择单价：

SELECT
    c.cust_id,
    c.cust_name,
    DATE(u.usage_date) AS usage_date,
    t.term_number,
    u.usage_type,
    SUM(u.usage_amount) AS usage_amount,
    SUM(u.usage_amount * r.unit_cost) AS usage_cost

FROM `usage` AS u
INNER JOIN terminals AS t
    ON t.term_number = u.term_ident
INNER JOIN tariff_rates AS r
    ON r.tariff_id = t.tariff_id
   AND r.usage_type = u.usage_type
INNER JOIN customers AS c
    ON c.cust_id = t.cust_id

WHERE u.usage_date >= '2010-12-15'
  AND u.usage_date <  '2011-01-16'

GROUP BY
    c.cust_id,
    c.cust_name,
    DATE(u.usage_date),
    t.term_number,
    u.usage_type

ORDER BY
    c.cust_id,
    usage_date,
    t.term_number,
    u.usage_type;

如果 invoice 必须输出固定的 usage_cost_1、usage_cost_2、usage_cost_3 三列，可以在最外层查询中进行条件聚合，数据库内部的计费模型仍保持规范化。

必要的索引

500 万条记录并没有超出数据库正常处理的范围，但连接字段和日期字段必须有合适的索引。

至少需要检查以下索引：

CREATE UNIQUE INDEX ux_terminals_term_number
    ON terminals (term_number);

CREATE INDEX ix_terminals_customer
    ON terminals (cust_id);

CREATE INDEX ix_terminals_tariff
    ON terminals (tariff_id);

CREATE INDEX ix_usage_date_term
    ON `usage` (usage_date, term_ident);

如果查询通常先按账期扫描，再通过 term_ident 连接，那么：

(usage_date, term_ident)

通常可以作为索引设计的起点。如果数据库的执行计划更倾向于从 terminal 反查 usage，则可能更适合：

(term_ident, usage_date)

最终仍要使用数据库提供的 EXPLAIN 或 EXPLAIN ANALYZE 检查执行计划，不能只根据字段数量判断。

过滤时不要给字段套函数：

-- 不推荐
WHERE DATE(u.usage_date) >= '2010-12-15'

这种写法可能导致普通索引无法用于范围查找。函数可以出现在 SELECT 和 GROUP BY 中，但过滤条件应直接比较原字段。

正确建模日期和金额字段

CSV 中的 11/12/2010 无法直接判断日月顺序。导入数据库时，应按明确的格式解析成标准日期，并存储为 DATE 或 DATETIME，不要继续保留为字符串。

金额和单价建议使用定点数：

DECIMAL(18, 6)

需要精确结算的金额不要使用 FLOAT 或 DOUBLE，否则可能产生舍入误差。具体精度和小数位数应根据业务中的最大 usage、单价和货币最小单位确定。

还要明确在哪一层舍入。例如，下面两种算法可能得到不同结果：

逐条 usage 计算并四舍五入，然后求和
先汇总 usage，再计算费用并四舍五入

账单系统必须确定其中一种规则，并确保 SQL 与财务规则一致。

Doctrine 中不要进行完整 ORM hydration

这个场景不需要把几百万条 usage 转换成 Entity。Doctrine ORM 的 identity map、对象创建和关联管理会增加内存占用和 CPU 开销。

可以通过 Doctrine DBAL 执行原生 SQL，并逐行读取结果：

$sql = <<<'SQL'
SELECT
    c.cust_id,
    c.cust_name,
    DATE(u.usage_date) AS usage_date,
    t.term_number,
    SUM(CASE
        WHEN u.usage_type = 1
        THEN u.usage_amount * tr.cost_1
        ELSE 0
    END) AS usage_cost_1,
    SUM(CASE
        WHEN u.usage_type = 2
        THEN u.usage_amount * tr.cost_2
        ELSE 0
    END) AS usage_cost_2,
    SUM(CASE
        WHEN u.usage_type = 3
        THEN u.usage_amount * tr.cost_3
        ELSE 0
    END) AS usage_cost_3,
    SUM(
        u.usage_amount *
        CASE u.usage_type
            WHEN 1 THEN tr.cost_1
            WHEN 2 THEN tr.cost_2
            WHEN 3 THEN tr.cost_3
            ELSE 0
        END
    ) AS total_cost
FROM `usage` AS u
INNER JOIN terminals AS t
    ON t.term_number = u.term_ident
INNER JOIN tariffs AS tr
    ON tr.tariff_id = t.tariff_id
INNER JOIN customers AS c
    ON c.cust_id = t.cust_id
WHERE u.usage_date >= :period_start
  AND u.usage_date < :period_end
GROUP BY
    c.cust_id,
    c.cust_name,
    DATE(u.usage_date),
    t.term_number
ORDER BY
    c.cust_id,
    usage_date,
    t.term_number
SQL;

$result = $connection->executeQuery($sql, [
    'period_start' => '2010-12-15',
    'period_end'   => '2011-01-16',
]);

$currentCustomerId = null;

while ($row = $result->fetchAssociative()) {
    if ($currentCustomerId !== $row['cust_id']) {
        $currentCustomerId = $row['cust_id'];

        // 开始生成该 customer 的 invoice。
    }

    // 将当前聚合行写入 invoice。
}

不同版本的 Doctrine DBAL 可能提供不同的流式读取方法，但处理原则相同：使用 DBAL/native SQL 逐行消费结果，不要调用 fetchAllAssociative() 一次把所有结果装入内存。

生产环境的处理流程

生产环境可以采用以下流程：

1. 将 CSV 导入 staging table。
2. 导入时验证并转换日期、数字和 usage type。
3. 对账期数据执行一次集合化 SQL 计算。
4. 将计算结果写入账单明细表，不要在每次展示 invoice 时重新扫描原始 usage。
5. PHP 从账单明细表生成 PDF、邮件或其他输出。
6. 保存计费时使用的 tariff、单价和账期，保证历史账单以后仍可重现。

账单明细表可以采用类似的结构：

CREATE TABLE invoice_lines (
    invoice_id      BIGINT NOT NULL,
    cust_id         BIGINT NOT NULL,
    usage_date      DATE NOT NULL,
    term_number     BIGINT NOT NULL,
    usage_cost_1    DECIMAL(18, 6) NOT NULL,
    usage_cost_2    DECIMAL(18, 6) NOT NULL,
    usage_cost_3    DECIMAL(18, 6) NOT NULL,
    total_cost      DECIMAL(18, 6) NOT NULL
);

计费任务使用：

INSERT INTO invoice_lines (...)
SELECT ...

这样可以直接生成账单快照，既避免重复计算，也能防止后续修改 tariff 时影响历史 invoice。

现有流程需要 24 小时，很可能并不是因为数据量太大。更常见的原因包括逐行循环、重复查询、缺少索引、转换字符串日期，或者把大量原始数据传到应用程序后再计算。更合适的优化方向是改用一次集合化查询，并检查数据库执行计划。具体能提升多少，还要根据数据库类型、硬件、数据分布和执行计划实测。

备注：内容仅供参考。