实例：预算管理（Budget）｜技术：WHERE + SUM 聚合、进度派生、双表联动

一、预算查询的三层聚合

预算管理的查询体系是「从单表到双表联动」的递进：queryBudgets（预算列表）→ spentByCategory（分类支出）→ queryWithSpend（预算+支出聚合）。本篇文章逐一深入，重点讲 WHERE + SUM 的条件聚合 与 queryWithSpend 的派生计算。

二、queryBudgets：按月查预算

static async queryBudgets(context: common.Context, month: string): Promise<Budget[]> {
  const store = await BudgetDao.getStore(context);
  const predicates = new relationalStore.RdbPredicates(BudgetDao.TABLE);
  predicates.equalTo('month', month).orderByAsc('id');
  const result = await store.query(predicates);
  return BudgetDao.collectBudgets(result);
}

SELECT * FROM budget WHERE month = '2024-06' ORDER BY id ASC;

按月等值 + 插入序：equalTo('month', month) 过滤当月预算 + orderByAsc('id') 按创建顺序（餐饮/交通/购物…）。命中 idx_budget_month 索引——月份等值查询走索引。

三、spentByCategory：WHERE + SUM 条件聚合

某分类的支出总额是本实例最核心的聚合：

static async spentByCategory(context: common.Context, category: string): Promise<number> {
  const store = await BudgetDao.getStore(context);
  const result = await store.querySql(
    `SELECT SUM(amount) AS total FROM ${BudgetDao.EXPENSE_TABLE} WHERE category = '${category}'`
  );
  let total = 0;
  if (result.goToNextRow()) {
    total = result.getDouble(result.getColumnIndex('total'));
  }
  result.close();
  return total;
}

SELECT SUM(amount) AS total FROM expense WHERE category = '餐饮';
-- 622.5（餐饮 9 笔支出之和）

WHERE + SUM 的执行逻辑：先过滤（category = '餐饮' 的 9 行）→ 再聚合（9 行的 amount 求和）——**「先过滤后聚合」**是条件聚合的标准流程。

空结果的 NULL 陷阱：某分类无支出时 SUM 返回 NULL（不是 0）——goToNextRow() 命中一行（NULL 值），getDouble 取 NULL 列返回 0（ResultSet 的容错）。**「SUM 空表/空组返回 NULL」**是 SQL 常识——页面依赖 spent=0 的兜底。

与影音 AVG 的对比：实例 13 的 AVG(rating) 是全表聚合（无 WHERE），spentByCategory 是带 WHERE 的条件聚合——**「无条件聚合 vs 条件聚合」**的递进。条件聚合 = WHERE 限定范围后聚合，是「按维度求总额」的通用模式。

字符串拼接 SQL 的注意：category = '${category}' 是模板拼接——分类名来自页面（下拉/常量），非用户自由输入（无注入风险）。「拼接 SQL 的前提：参数来自受控来源」——用户自由输入必须用参数绑定（RdbPredicates.equalTo）。

四、queryWithSpend：预算 + 支出的聚合计算

预算进度 = 预算表 + 支出表的双表联动计算：

static async queryWithSpend(context: common.Context, month: string): Promise<BudgetWithSpend[]> {
  const store = await BudgetDao.getStore(context);
  const budgets = await BudgetDao.queryBudgets(context, month);
  const list: BudgetWithSpend[] = [];
  for (const b of budgets) {
    const spent = await BudgetDao.spentByCategory(context, b.category);
    const percent = b.amount > 0 ? Math.min(100, Math.round(spent / b.amount * 100)) : 100;
    const item: BudgetWithSpend = {
      budget: b,
      spent: spent,
      percent: percent,
      overrun: spent > b.amount,
    };
    list.push(item);
  }
  return list;
}

逐预算循环：对每条预算，查对应分类的支出总额（spentByCategory）→ 计算 percent → 判断 overrun——「程序循环 + SQL 聚合」的组合（每条预算一次 SUM 查询）。

percent 的计算链：

1. spent / b.amount：已花 ÷ 预算 = 比率（0.42）；
2. * 100：转百分比（42.3）；
3. Math.round：四舍五入（42）；
4. Math.min(100, ...)：封顶 100（花超了也显示 100%——进度条宽度不溢出）。

b.amount > 0 守卫：预算金额为 0 时除零——?: 100 直接给 100%（0 预算默认「用满」）。「除零守卫」：比率计算的防御性写法。

overrun 的派生：spent > b.amount——纯比较逻辑（布尔），与 percent 的封顶无关（percent 封顶 100 但 overrun 独立判断）。「展示值封顶 + 状态值独立」——percent 管视觉（条宽），overrun 管状态（标红）。

BudgetWithSpend 接口（数据层的复合返回）：

export interface BudgetWithSpend {
  budget: Budget;
  spent: number;
  percent: number;
  overrun: boolean;
}

「实体 + 派生字段」的返回结构——页面拿到的就是「可直接渲染的完整数据」（不需要再算）。DAO 层做计算、页面只渲染是数据层的分工原则。

五、totalForMonth：总预算 vs 总支出

static async totalForMonth(context: common.Context, month: string): Promise<MonthTotal> {
  const store = await BudgetDao.getStore(context);
  const budgets = await BudgetDao.queryBudgets(context, month);
  let budgetTotal = 0;
  for (const b of budgets) {
    budgetTotal += b.amount;
  }
  const result = await store.querySql(
    `SELECT SUM(amount) AS total FROM ${BudgetDao.EXPENSE_TABLE}`
  );
  let spendTotal = 0;
  if (result.goToNextRow()) {
    spendTotal = result.getDouble(result.getColumnIndex('total'));
  }
  result.close();
  const s: MonthTotal = { budgetTotal: budgetTotal, spendTotal: spendTotal };
  return s;
}

两种求和的对比：

维度	实现	原因
总预算	前端循环累加	queryBudgets 已取回数据
总支出	SQL SUM 全表	支出表未取回，SQL 聚合更高效

「已取回就前端算，没取回就 SQL 算」——性能与代码简洁的平衡。总支出也可以写 SELECT SUM(amount) FROM expense（无 WHERE 的全表聚合）。

MonthTotal 接口：{ budgetTotal, spendTotal }——总对照数字（页面算 percent 与剩余）。

六、insert/delete：流水操作

insertExpense（记一笔支出）：

static async insertExpense(context: common.Context, e: Expense): Promise<number> {
  const store = await BudgetDao.getStore(context);
  const values: relationalStore.ValuesBucket = {
    category: e.category, amount: e.amount, note: e.note, spend_time: e.spendTime,
  };
  return await store.insert(BudgetDao.EXPENSE_TABLE, values);
}

insertBudget（设预算）同构。deleteExpense（删流水）——equalTo('id', id) 标准删除。

删除后进度自动变化：删一笔支出 → refresh 重算 spent/percent/overrun——「流水操作 → 聚合重算」的联动由页面 refresh 驱动（不是数据库触发器）。

七、技术要点对照表

技术点	实现方式	生产价值
条件聚合	WHERE + SUM	分类支出总额
进度计算	spent/amount + round + min	百分比
除零守卫	b.amount > 0 三元	0 预算不崩
超支判断	spent > amount	预警布尔
复合返回	BudgetWithSpend 实体+派生	页面直渲染
双表联动	逐预算查支出 SUM	进度对照

八、常见问题 FAQ

Q1：为什么不用 SQL JOIN 一次查「预算 + 支出」？
A：预算按 category 关联支出——理论上 SELECT budget.*, SUM(expense.amount) FROM budget LEFT JOIN expense ON budget.category = expense.category GROUP BY budget.id 可一次完成。但 RdbStore 的 querySql 写长 JOIN + GROUP BY 可读性差，且循环 + 多次简单查询在数据量小时更清晰。「程序循环 vs SQL JOIN」的选择：教学选循环（可读性优先），大数据量选 JOIN（性能优先）。

Q2：SUM 返回 NULL 时 getDouble 会怎样？
A：ResultSet 的 getDouble 对 NULL 列返回 0（容错设计）——total = 0 兜底。但其他环境（如直接读 SQLite CLI）SUM 空组返回 NULL——**「SQL 层 NULL 是常态，应用层容错为 0」**是跨环境的一致化。

Q3：percent 封顶 100，超支怎么显示？
A：percent 管进度条宽度（封顶 100 不溢出）；overrun 管状态视觉（超支标签 + 红条）——「视觉与状态分离」：进度条不会超过 100%，但超支有独立红色高亮。

Q4：预算金额 0 会怎样？
A：b.amount > 0 ? ... : 100——0 预算直接 percent=100（默认用满）、overrun 判断 spent > 0（有任何支出即超支）。除零守卫避免 spent/0 产生 Infinity/NaN。

Q5：为什么总预算用前端累加而总支出用 SQL？
A：queryBudgets 已经把当月预算取回内存——前端循环累加零成本；支出表没有全量取回，SQL SUM(amount) 一条语句高效求全表。**「数据已在手就本地算，数据在库就 SQL 算」**是实践原则。

Q6：页面 refresh 时先查 budgets 再查 totalForMonth——重复查了预算？
A：是的——refresh 调 queryWithSpend（内部 queryBudgets）和 totalForMonth（内部又 queryBudgets），预算查了两次。数据量小无所谓；优化可让 queryWithSpend 返回总预算（内部累加）。「教学代码允许轻微重复查询」——可读性优先于极致性能。

Q7：支出表的 category 和预算表不一致怎么办？
A：页面从预算卡进入记支出（selectedCategory 来自预算）——分类名受控（不会打出预算外的分类）。若用户自由输入分类，会出现「支出有分类但预算无此分类」——SUM 照常算、但无预算可比。「受控输入保证双表分类一致」。

九、文章小结

本篇文章深入讲解了预算管理的数据层核心：WHERE + SUM 条件聚合（先过滤后聚合的分类支出总额 + NULL 兜底）、queryWithSpend 的双表联动派生计算（逐预算查支出 → percent 计算链 + 除零守卫 → overrun 独立判断）、「实体 + 派生字段」的复合返回结构、「前端累加 vs SQL 聚合」的求和选择。核心心法是「进度 = 两表对照 + 展示封顶与状态分离」——预算 vs 实际的完整计算链。

下一篇（17-4）展示 6 类预算 + 25 笔支出种子数据，让进度面板一开屏就有超支预警故事。

十、边界与权衡：条件聚合与查询策略再探

本章把三个最容易出错的环节再深挖一层：spentByCategory 的 NULL 语义、percent 计算链的除零与封顶、queryWithSpend 的循环 vs JOIN。

1. WHERE + SUM 的 NULL 处理链

SELECT SUM(amount) AS total FROM expense WHERE category = '餐饮';  -- 622.5
SELECT SUM(amount) FROM expense WHERE category = '影音';           -- NULL（空组）

同一句 SUM，有支出返回总和、无支出返回 NULL（不是 0）——SQL 标准规定聚合函数对空输入返回 NULL。从 SQL 到页面要过三层：

层级	行为	说明
SQL 层	SUM 空组 → NULL	无输入行即 NULL 结果
ResultSet 层	getDouble(NULL) → 0	RdbStore 容错取值
应用层	total = 0 初始化	页面「无支出 = 0」语义成立

关键细节：goToNextRow() 对空聚合仍返回 true——结果集里有 1 行，只是该列是 NULL，所以不能靠「无行」判断，必须靠 getDouble 的容错。「NULL 是 SQL 层的事实，0 是应用层的约定」——每层各守其责。

2. percent 计算链的边界：除零守卫 + 封顶

const percent = b.amount > 0 ? Math.min(100, Math.round(spent / b.amount * 100)) : 100;

用四类输入走一遍计算链：

场景	spent	amount	计算链结果
正常	630	1500	42
花超	1800	1500	100（min 封顶）
恰好花完	1500	1500	100
零预算	5	0	100（?: 守卫短路）

两个边界的顺序：b.amount > 0 守卫在最外层——除零直接短路，除法根本不执行；Math.min(100, ...) 在最内层——比值正常但超 100% 时封顶。「除零用短路、超限用封顶」：两种边界、两种手法，缺一不可——去掉守卫得 NaN，去掉封顶得 120% 溢出进度条。

3. queryWithSpend：循环 + SQL vs 单条 JOIN

SELECT b.id, b.category, b.amount, COALESCE(SUM(e.amount), 0) AS spent
FROM budget b LEFT JOIN expense e ON b.category = e.category
WHERE b.month = '2024-06' GROUP BY b.id, b.category, b.amount;

这条 JOIN + GROUP BY 与「循环 + SUM」逻辑等价，但取舍不同：

维度	循环 + SUM（现方案）	单条 JOIN
语句数	1 + N（N=预算数）	1
可读性	每句短小、逻辑直白	长 SQL 难调试
性能	大数据量慢（N 次查询）	一次扫描更优
教学价值	每步可打印验证	黑盒一条

判断标准：预算仅 6 类、每次 SUM 命中索引毫秒返回——小 N 循环方案既清晰又不慢；分类上百、每类千笔时才值得 JOIN 化。**「小 N 用循环、大 N 用 JOIN」**是数据量驱动的取舍，不是对错问题。

4. FAQ 追加

Q1：SUM 空组返回 NULL 时，怎么判断「这个分类有没有支出」？
A：不能靠 goToNextRow()（空聚合也有 1 行），也不能直接拿 NULL——正确做法是接受 ResultSet 的容错：getDouble 把 NULL 转 0，再用 total > 0 判断有无支出。**「SQL 层 NULL → 应用层 0」**后再做业务判断。

Q2：percent 封顶 100 会影响 overrun 超支判断吗？
A：不影响——percent 是展示值（进度条宽度），overrun 是状态值（标红），两者独立计算。花 1800/1500：percent=100（条满）、overrun=true（红标）——**「封顶不吞状态」**是刻意设计。

Q3：JOIN 里 COALESCE 是什么？循环方案为什么不用？
A：LEFT JOIN 下无支出的分类 SUM 为 NULL，COALESCE(x, 0) 转 0——和循环方案的「getDouble 容错」是同一意图的两条路。「SQL 层转 0 vs 应用层转 0」：JOIN 必须在 SQL 层转（否则拿回 NULL），循环方案在应用层转即可。