SUM 与 GROUP BY 显神通：ArkTS 聚合 SQL 把鸿蒙账单算得明明白白

dingchengyu_

7人浏览 · 2026-08-15 19:45:11

dingchengyu_ · 2026-08-15 19:45:11 发布

在这里插入图片描述

实例：个人记账本（Ledger）｜技术：SUM/COUNT 聚合、GROUP BY 统计、日期范围查询

一、从需求到 SQL：聚合查询的思维模型

上一篇文章我们看到了仪表盘页面上那些漂亮的大数字：本月支出、本月收入、总笔数、分类占比。这些数字不是前端算出来的，而是数据库算完、前端直接展示的。为什么要把计算下沉到 SQLite？三个理由：

性能：几万条流水在内存里遍历累加，遇到大数据量必然卡顿；而 SQLite 的聚合函数走索引扫描，毫秒级完成。
一致性：求和、分组逻辑集中在 SQL 里，前端多个页面复用同一个 DAO 方法，不会出现「这个页面算的跟那个页面不一样」的 bug。
简洁：前端只需要 await LedgerDao.summary(...) 拿一个对象，代码量最少。

本篇文章聚焦实例 3 最核心的四种聚合查询：区间汇总（SUM + CASE WHEN）、分类占比（GROUP BY）、月趋势（strftime）、笔数统计（COUNT）。学完这四种，你就掌握了报表类功能 80% 的 SQL 能力。

二、接口设计：三个显式返回类型

在写聚合方法之前，先定义返回类型。这是 ArkTS 的一个重要约束：不能用内联对象字面量作为函数返回类型（编译器报 arkts-no-obj-literals-as-types），必须显式声明 interface：

/** 区间汇总：总收入 / 总支出 / 笔数 */
export interface LedgerSummary {
  income: number;
  expense: number;
  count: number;
}

/** 分类统计条目 */
export interface CategoryStat {
  category: string;
  amount: number;
  count: number;
}

/** 月趋势条目 */
export interface MonthTrend {
  month: string;
  expense: number;
}

这三个接口分别对应页面的三块数据：仪表盘大数字、分类占比条、月趋势（本实例页面暂未展示趋势图，但接口已备好，读者可以自行扩展折线图）。接口用 export 导出，页面才能 import 使用——这是跨文件类型复用的标准姿势。

三、区间汇总：一条 SQL 出三个数

需求：本月总收入、总支出、总笔数。传统做法是三条查询或内存遍历，我们的做法是一条 SQL 全包：

static async summary(context: common.Context, start: number, end: number): Promise<LedgerSummary> {
  const store = await LedgerDao.getStore(context);
  const result = await store.querySql(
    `SELECT
       SUM(CASE WHEN type=1 THEN amount ELSE 0 END) AS income,
       SUM(CASE WHEN type=0 THEN amount ELSE 0 END) AS expense,
       COUNT(*) AS cnt
     FROM ${LedgerDao.TABLE}
     WHERE trade_time BETWEEN ${start} AND ${end}`
  );
  let income = 0, expense = 0, count = 0;
  if (result.goToNextRow()) {
    income = result.getDouble(result.getColumnIndex('income'));
    expense = result.getDouble(result.getColumnIndex('expense'));
    count = result.getLong(result.getColumnIndex('cnt'));
  }
  result.close();
  const s: LedgerSummary = { income: income, expense: expense, count: count };
  return s;
}

这条 SQL 有三个精妙之处：

精妙一：条件聚合 SUM(CASE WHEN type=1 THEN amount ELSE 0 END)。它把「收入求和」和「支出求和」合并进同一句 SQL——对每一行，先判断 type，是 1 就累加到收入，是 0 就累加到支出。数据库只需扫描一遍时间范围内的数据，同时产出两个数字。这正是字段设计时坚持 type 用 0/1 数字的根本原因：如果存的是 ‘支出’/‘收入’ 字符串，CASE WHEN 里就得写中文，既啰嗦又容易出错。

精妙二：COUNT(*) 与 SUM 平级。COUNT(*) 统计行数（即笔数），与两个 SUM 在同一个 SELECT 列表里，一次查询三个结果。COUNT(*) 和 COUNT(column) 的区别值得说一句：COUNT(*) 统计所有行（含 NULL），COUNT(column) 只统计该列非 NULL 的行。流水表的列都有 NOT NULL 约束，两者结果一致，但 COUNT(*) 语义更明确。

精妙三：区间过滤走索引。WHERE trade_time BETWEEN ${start} AND ${end} 命中了我们在建表时创建的 idx_ledger_time 索引。start/end 是毫秒时间戳，由页面层 monthRange() 传入，DAO 保持纯粹。

关于 ${start} 直接内联的安全性问题：这里把数字直接拼进 SQL 字符串，而不是用占位符。这是安全的——因为 start/end 来自 Date.getTime()，是我们自己计算出来的整数，不是用户输入，不存在 SQL 注入风险。但任何来自用户输入的值（如分类名、备注）都必须走 ValuesBucket 或 RdbPredicates 的预处理机制，这条红线一定要守住。

读取结果时注意：result.goToNextRow() 移动到第一行——聚合查询永远只返回一行，所以只需判断一次。读完必须 result.close() 释放游标，这是避免资源泄漏的硬性要求。

四、分类占比：GROUP BY 的艺术

需求：钱都花到哪里去了？按分类分组统计支出金额和笔数，按金额降序：

static async categoryStats(context: common.Context, start: number, end: number): Promise<CategoryStat[]> {
  const store = await LedgerDao.getStore(context);
  const result = await store.querySql(
    `SELECT category, SUM(amount) AS amount, COUNT(*) AS cnt
     FROM ${LedgerDao.TABLE}
     WHERE type=0 AND trade_time BETWEEN ${start} AND ${end}
     GROUP BY category
     ORDER BY amount DESC`
  );
  const list: CategoryStat[] = [];
  while (result.goToNextRow()) {
    const item: CategoryStat = {
      category: result.getString(result.getColumnIndex('category')),
      amount: result.getDouble(result.getColumnIndex('amount')),
      count: result.getLong(result.getColumnIndex('cnt')),
    };
    list.push(item);
  }
  result.close();
  return list;
}

GROUP BY 的工作机制：SQLite 先把满足 WHERE 条件的行按 category 值分组——「餐饮」的归一堆，「交通」的归一堆——然后对每组执行 SUM(amount) 和 COUNT(*)。结果是一行一个分类，天然去重。这正是我们 3-1 文章里「category 存文本、不建字典表」决策的回报：查询时零 JOIN，一个 GROUP BY 就得到了分类字典 + 统计。

WHERE type=0 的意义：占比统计只关心支出。如果不过滤，收入（工资、红包）也会被 GROUP BY 进结果，把「钱花到哪」的语义搞混。

ORDER BY amount DESC 的价值：让花钱最多的分类排第一。前端 ForEach 直接按返回顺序渲染，进度条天然「大头在上」，视觉上形成强烈的「钱都花在哪」的提示。把排序做进 SQL，而不是前端再 sort，是数据层的好习惯——SQL 返回即终态，前端零加工。

读取采用 while (result.goToNextRow()) 循环——GROUP BY 可能返回多行，逐行收集成数组。getColumnIndex 按别名取列，代码自文档化。

五、月趋势：strftime 时间格式化

需求：近 N 个月的支出走势，用于趋势折线图。这里遇到一个难题：流水表里存的是毫秒时间戳，怎么按「月」分组？

答案是 SQLite 内置的 strftime 函数：

static async monthlyTrend(context: common.Context, months: number): Promise<MonthTrend[]> {
  const store = await LedgerDao.getStore(context);
  const result = await store.querySql(
    `SELECT strftime('%Y-%m', trade_time/1000, 'unixepoch', 'localtime') AS month,
            SUM(CASE WHEN type=0 THEN amount ELSE 0 END) AS expense
     FROM ${LedgerDao.TABLE}
     GROUP BY month
     ORDER BY month DESC
     LIMIT ${months}`
  );
  const list: MonthTrend[] = [];
  while (result.goToNextRow()) {
    const item: MonthTrend = {
      month: result.getString(result.getColumnIndex('month')),
      expense: result.getDouble(result.getColumnIndex('expense')),
    };
    list.push(item);
  }
  result.close();
  return list;
}

strftime 的参数逐个拆解：

参数	含义	为什么需要
`'%Y-%m'`	输出格式：年-月	得到 ‘2025-06’ 这样的月份标签
`trade_time/1000`	毫秒 → 秒	SQLite 时间函数接受秒级时间戳
`'unixepoch'`	输入是 Unix 时间戳	告诉 strftime 如何解释数值
`'localtime'`	转本地时区	避免 UTC 与东八区的日期偏移

完整效果：1750000000000（毫秒）→ 1750000000（秒）→ '2025-06'。这是本实例最亮眼的技巧——零第三方库，纯 SQLite 自带函数就完成了时间维度分组，如果在前端做，你得把每行的毫秒时间戳转 Date、手动截取年月、再手动分组，代码量和出错概率都翻倍。

GROUP BY month 的技巧：这里 GROUP BY 的是 SELECT 里的别名 month（即 strftime 的结果），而不是原始列。SQLite 允许 GROUP BY 引用 SELECT 别名，这比写一遍完整的 strftime 表达式简洁得多。这是 SQL 里「先计算、再分组」的经典模式。

LIMIT ${months}：只取最近 N 个月（如 6）。配合 ORDER BY month DESC（最新的月在前），拿到的是倒序趋势；前端若要画正序折线，reverse() 一下即可。

六、基础查询回顾：与聚合的协作关系

聚合查询不是孤立的，它建立在基础 CRUD 之上。回顾本实例 DAO 的基础查询：

/** 全部流水：时间倒序 */
static async queryAll(context: common.Context): Promise<LedgerRecord[]> {
  const store = await LedgerDao.getStore(context);
  const predicates = new relationalStore.RdbPredicates(LedgerDao.TABLE);
  predicates.orderByDesc('trade_time');
  const result = await store.query(predicates);
  return LedgerDao.collect(result);
}

/** 按日期范围查询：start/end 毫秒时间戳 */
static async queryByRange(context: common.Context, start: number, end: number): Promise<LedgerRecord[]> {
  const store = await LedgerDao.getStore(context);
  const predicates = new relationalStore.RdbPredicates(LedgerDao.TABLE);
  predicates.between('trade_time', start, end).orderByDesc('trade_time');
  const result = await store.query(predicates);
  return LedgerDao.collect(result);
}

注意 queryByRange 的 between('trade_time', start, end)——这是 RdbPredicates 的链式 API，等价于 WHERE trade_time BETWEEN start AND end。基础查询用 RdbPredicates（防注入），聚合查询用 querySql（字符串 SQL），这是本实例 DAO 的混合策略：条件过滤用谓词对象，聚合计算用原生 SQL（因为 RdbPredicates 不提供 SUM/GROUP BY 能力）。两种方式各司其职。

七、collect 辅助方法：DRY 原则

每个查询方法都要「遍历 ResultSet → 逐行映射 → 关闭游标」，这段样板代码我们用私有方法收敛：

private static collect(result: relationalStore.ResultSet): LedgerRecord[] {
  const list: LedgerRecord[] = [];
  while (result.goToNextRow()) {
    list.push(LedgerDao.rowToRecord(result));
  }
  result.close();
  return list;
}

collect 接收 ResultSet，返回实体数组。rowToRecord 做单行映射。两个私有方法被所有查询复用，避免每个方法重复写 while 循环——这就是 DRY（Don’t Repeat Yourself）原则在 DAO 层的落地。聚合查询因为返回的是聚合结构（非实体），无法复用 collect，所以各自内联循环，这是合理的例外。

八、技术要点对照表

技术点	SQL 实现	页面用途	生产价值
双态汇总	`SUM(CASE WHEN type=1...)`	收入/支出大数字	一次扫描出两数
总笔数	`COUNT(*)`	「本月已记 N 笔」	与 SUM 平级共查
分类占比	`GROUP BY category`	占比进度条	天然去重 + 排序
月趋势	`strftime('%Y-%m', ...)`	折线图数据源	零依赖时间分组
区间过滤	`between('trade_time', a, b)`	本月范围	走时间索引
条件过滤	`WHERE type=0`	只看支出	语义精确

九、性能与安全注意事项

索引匹配：所有带 trade_time 条件的查询都命中 idx_ledger_time。生产环境可用 EXPLAIN QUERY PLAN 验证：
```
EXPLAIN QUERY PLAN SELECT * FROM ledger WHERE trade_time BETWEEN 1 AND 2;
```
输出应显示使用 idx_ledger_time，若显示全表扫描则索引没建对。
数字内联安全：${start}、${end}、${months} 内联安全，因为它们来自代码计算；用户输入永不内联。
游标释放：每个 ResultSet 用完必须 close()，聚合查询也不例外（虽然只返回一行）。
getDouble 处理 NULL：SUM() 在无匹配行时返回 NULL，getDouble 取到的是 0。我们在读取前先 let income = 0 初始化，即使 goToNextRow() 为 false 也有默认值，页面不会出现 NaN。