账房先生的数据库算盘：ArkTS 为鸿蒙记账本设计流水表与分类字典

实例个人记账本Ledger技术SUM/COUNT 聚合、GROUP BY 统计、日期范围查询阅读收益学完本篇文章你将掌握鸿蒙应用中「流水型业务数据」从建表到 DAO 封装的完整方法论并理解为什么报表类功能要把时间戳字段设计成核心索引。一、业务需求分析记账本到底要记录什么在开始写任何一行代码之前我们先冷静下来思考一个问题一个「个人记账本」应用它最核心的业务数据是什么答案只有一个词——流水Ledger。每一笔收入、每一笔支出都是流水的具体表现。我们不妨把自己代入产品经理的角色对着白板画出记账本的需求清单记一笔账用户能快速录入一笔收入或支出包含金额、分类、备注、时间四个核心要素。这是整个应用的数据入口必须快、必须稳。本月总支出 / 总收入 / 结余统计打开应用第一眼看到的就是这个数字面板。用户想知道「我这个月花了多少、挣了多少、还剩多少」。按分类统计占比除了总览用户还想知道「钱都花到哪里去了」——餐饮占了多少、交通占了多少、购物占了多少。这个需求天然对应数据库里的GROUP BY分组聚合。按月查看历史流水上个月花了多少三个月前的大额支出是什么用户需要按月份切分时间维度来回顾历史。按日期范围筛选更精细的场景——「6 月 1 日到 6 月 15 日我花了多少钱」这对应 SQL 里的BETWEEN区间查询。把这五个需求翻译成数据库术语我们会发现它们全部围绕两个字段展开trade_time交易时间和amount金额。时间字段支撑区间查询、按月分组金额字段支撑求和、均值等所有聚合运算。这就是流水型业务的核心时间是纵轴金额是横轴分类是标签。很多初学者一上来就设计一张「大而全」的表把用户信息、预算信息、账户信息全部塞进去结果查询效率低下、逻辑混乱。正确的做法是先明确这张表只为「一笔账」服务其他概念用户、预算、账户在后续的实例中各自独立成表通过外键关联。这就是我们第一个实例待办清单和第二个实例通讯录反复强调的「单表职责单一」原则。二、字段设计每一列都有它的宿命明确了业务需求我们就可以开始设计ledger表的字段了。设计表结构时我习惯先列出候选字段然后逐个质问它这个字段被谁查询被谁聚合值域是什么下面是我们最终敲定的字段清单字段名类型约束说明idINTEGERPRIMARY KEY AUTOINCREMENT自增主键每笔流水唯一标识typeINTEGERNOT NULL收支类型0 支出 / 1 收入amountREALNOT NULL金额元如 25.50categoryTEXTNOT NULL分类餐饮/交通/购物/工资…noteTEXTDEFAULT ‘’备注可为空trade_timeINTEGERNOT NULL交易时间戳毫秒created_timeINTEGERNOT NULL创建时间戳毫秒逐个字段拆解说明设计决策背后的考量id 自增主键。这是所有表的默认配置无需多言。AUTOINCREMENT 保证删除最后一条记录后新插入的记录不会复用旧的 id避免缓存引用错乱。type 用 0/1 数字而非字符串。为什么不直接存 ‘支出’ / ‘收入’两个理由第一数字占用的存储空间远小于 UTF-8 编码的中文字符串第二数字可以直接参与WHERE type0这种条件判断还能在聚合 SQL 里用CASE WHEN type1 THEN amount ELSE 0 END做条件求和。生产级应用里凡是值域有限的枚举字段一律用整数编码展示层再映射成文案。这是第一实例 TodoDao 里completed字段用 0/1 的同一个道理。amount 用 REAL 存「元」。这是一个需要权衡的决策。账务系统在严谨场景银行、支付下必须用 INTEGER 存「分」防止浮点误差但个人记账场景下用户手动输入的金额最多两位小数REAL 在查询聚合时的误差在展示层四舍五入后完全不可见而且「元」单位对用户心智更友好代码里不需要做分/元的换算。我们在注释里特意标注了这一点提醒读者如果将来接入支付系统务必把字段改为amount_cents INTEGER。这是「够用就好」与「生产严谨」之间的务实取舍。category 存 TEXT 而非外键。有的架构师会主张建一张category字典表用外键关联。但在个人记账场景下分类是用户自由填写的标签今天写「餐饮」明天可能写「吃饭」强行做成字典表反而增加维护成本。我们让 category 直接存文本查询时GROUP BY category即可天然得到分类字典。第二个实例通讯录里我们建了索引列pinyin来优化排序这里分类字段同样适合加索引吗不一定——因为分类基数不同值的数量很小SQLite 优化器大概率选择全表扫描索引反而多余。这个细节我们在第四节再细说。note 默认空字符串。备注是可选项但字段必须有默认值避免插入时显式传空导致的 SQL 拼接问题。trade_time 与 created_time 为什么是两个字段这是很多初学者最容易混淆的地方。trade_time是「这笔钱实际发生的时间」比如用户补录昨天的一笔消费trade_time 是昨天而created_time是「这条记录写入数据库的时间」永远是现在。报表统计必须基于 trade_time——如果用户补录了昨天的账只有按 trade_time 统计才能反映真实的花钱节奏。这两个字段分离是流水表设计的黄金法则。三、建表 SQL把设计变成现实字段设计完毕接下来用 SQL 把它落地。注意我们的建表语句全部以IF NOT EXISTS开头并配套建立索引CREATETABLEIFNOTEXISTSledger(idINTEGERPRIMARYKEYAUTOINCREMENT,typeINTEGERNOTNULL,amountREALNOTNULL,categoryTEXTNOTNULL,noteTEXTDEFAULT,trade_timeINTEGERNOTNULL,created_timeINTEGERNOTNULL);CREATEINDEXIFNOTEXISTSidx_ledger_timeONledger(trade_time);CREATEINDEXIFNOTEXISTSidx_ledger_typeONledger(type);这里有两个值得展开讲的点为什么给 trade_time 建索引因为我们的所有统计 SQL——区间查询、按月分组、SUM 聚合——都带WHERE trade_time BETWEEN ? AND ?或GROUP BY month这样的时间条件。没有索引的话SQLite 每次都要全表扫描有了idx_ledger_time优化器可以直接走 B 树索引快速定位到时间范围内的记录。个人记账的数据量几百上千条也许感知不到差别但同样的设计放在百万级流水的生产系统里就是毫秒与秒级的差距。索引是给未来设计的。为什么给 type 建索引type 只有 0/1 两个值基数极低严格来说索引收益不大。但我们在统计 SQL 里频繁使用WHERE type0过滤而且 type 与 trade_time 经常组合出现。建这个索引更多的是一种「声明式意图」告诉后来的维护者这个字段是查询热点。实际生产中你可以用EXPLAIN QUERY PLAN验证索引是否被使用这也是我们第 8 批文章分页实例会详细演示的调优方法。关于执行时机getStore方法里先建表再建索引的顺序很重要索引必须依附于已存在的表。我们的 DAO 把建表和索引放在同一个方法里串行执行保证幂等——多次调用不会报错因为都有IF NOT EXISTS保护。四、LedgerDao 封装把 SQL 关进类里数据层的核心是LedgerDao类。它的职责边界非常清晰只负责与数据库打交道不包含任何 UI 逻辑。页面拿到 DAO 返回的数据模型直接渲染即可。这种分层让 UI 与数据彻底解耦——将来换数据库、改表结构页面代码一行都不用动。先看数据模型的定义exportinterfaceLedgerRecord{id:number;type:number;// 0 支出 / 1 收入amount:number;category:string;note:string;tradeTime:number;createdTime:number;}注意字段命名风格数据库里是蛇形snake_casetrade_timeArkTS 模型里是驼峰camelCasetradeTime。这是行业惯例——SQL 风格用下划线代码风格用驼峰DAO 在两者之间做映射转换。映射逻辑集中在rowToRecord方法里privatestaticrowToRecord(result:relationalStore.ResultSet):LedgerRecord{return{id:result.getLong(result.getColumnIndex(id)),type:result.getLong(result.getColumnIndex(type)),amount:result.getDouble(result.getColumnIndex(amount)),category:result.getString(result.getColumnIndex(category)),note:result.getString(result.getColumnIndex(note))||,tradeTime:result.getLong(result.getColumnIndex(trade_time)),createdTime:result.getLong(result.getColumnIndex(created_time)),};}这个映射方法有讲究我们用的是ResultSet.getColumnIndex按列名取值而不是getRow()拿整行再转。为什么两个原因第一getColumnIndex让代码自文档化——一眼就能看出哪一列对应哪个字段第二对于可能为 NULL 的列如 note我们可以用|| 提供默认值避免 null 泄漏到 UI 层。这是鸿蒙 RDB 开发中非常实用的小技巧。然后是单例复用的getStorestaticasyncgetStore(context:common.Context):PromiserelationalStore.RdbStore{if(LedgerDao.store){returnLedgerDao.store;}constconfig:relationalStore.StoreConfig{name:ledger.db,securityLevel:relationalStore.SecurityLevel.S1,};LedgerDao.storeawaitrelationalStore.getRdbStore(context,config);// 建表 建索引代码见上节hilog.info(DOMAIN,TAG,流水表初始化成功);returnLedgerDao.store;}store是静态私有字段首次调用时创建并缓存后续所有方法直接复用。securityLevel: S1表示数据安全级别最低——个人记账数据不涉密S1 足够如果记录的是健康数据或支付信息应该考虑 S2/S3。这是鸿蒙 RDB 特有的安全设计从实例 1 开始我们就一直沿用这个模式。注意这里有个 ArkTS 的语法细节在静态方法里引用静态字段必须用类名LedgerDao.store不能写this.store。这是因为 ArkTS 对this的语义做了严格限制this只在实例方法里指向当前实例静态上下文里this是未定义的。这个坑我们前两批文章都踩过编译器的报错信息arkts-no-this-in-static就是提醒你这一点。所有 DAO 方法签名里的 context 统一用common.Context而不是更具体的UIAbilityContext——用基类更利于复用页面传getContext(this)得到的实例天然是UIAbilityContext向下兼容。五、核心 CRUD写、查、删流水业务的写操作非常简单——只有「记一笔」和「删一笔」没有更新账记错了就删掉重记这是记账 App 的常见交互也避免了 UPDATE 带来的审计风险。看插入方法staticasyncinsert(context:common.Context,r:LedgerRecord):Promisenumber{conststoreawaitLedgerDao.getStore(context);constvalues:relationalStore.ValuesBucket{type:r.type,amount:r.amount,category:r.category,note:r.note,trade_time:r.tradeTime,created_time:r.createdTime,};returnawaitstore.insert(LedgerDao.TABLE,values);}ValuesBucket是鸿蒙 RDB 的「键值容器」键是数据库列名值是列数据。它等价于 SQL 的INSERT INTO ledger (type, amount, ...) VALUES (?, ?, ...)的预处理参数形式——这也是为什么我们的 DAO 从不拼接字符串 SQL 插值除了后面讲聚合时数字参数直接内联的场景那是安全的因为值来自我们自己计算。store.insert返回新插入行的 id方便后续引用。查询方法有三个层级对应第一节的三种需求全部流水时间倒序——列表页需要staticasyncqueryAll(context:common.Context):PromiseLedgerRecord[]{conststoreawaitLedgerDao.getStore(context);constpredicatesnewrelationalStore.RdbPredicates(LedgerDao.TABLE);predicates.orderByDesc(trade_time);constresultawaitstore.query(predicates);returnLedgerDao.collect(result);}RdbPredicates是鸿蒙的「查询条件构造器」链式调用orderByDesc等价于 SQL 的ORDER BY trade_time DESC。collect是遍历 ResultSet 并逐行映射为模型数组的公共方法避免每个查询重复写 while 循环。按日期范围查询——统计页的底座staticasyncqueryByRange(context:common.Context,start:number,end:number):PromiseLedgerRecord[]{conststoreawaitLedgerDao.getStore(context);constpredicatesnewrelationalStore.RdbPredicates(LedgerDao.TABLE);predicates.between(trade_time,start,end).orderByDesc(trade_time);constresultawaitstore.query(predicates);returnLedgerDao.collect(result);}between(trade_time, start, end)等价于WHERE trade_time BETWEEN start AND end。注意 start/end 都是毫秒时间戳——本月区间就是「本月 1 号 00:00 的时间戳」到「现在的时间戳」这个计算放在页面层monthRange()DAO 只负责接收参数保持纯粹。删除流水staticasyncdelete(context:common.Context,id:number):Promisenumber{conststoreawaitLedgerDao.getStore(context);constpredicatesnewrelationalStore.RdbPredicates(LedgerDao.TABLE);predicates.equalTo(id,id);returnawaitstore.delete(predicates);}equalTo(id, id)等价于WHERE id ?。删除返回受影响行数页面层可以据此判断是否删成功。六、聚合查询一条 SQL 的魔法现在进入本实例的核心技术点——聚合 SQL。我们的仪表盘页面需要四个数字总收入、总支出、总笔数、分类占比。如果逐条遍历所有记录在内存里累加也能得到结果但数据量一大就卡正确做法是把计算下沉到 SQLite让它用索引快速完成求和。区间汇总——一条 SQL 出三个数staticasyncsummary(context:common.Context,start:number,end:number):PromiseLedgerSummary{conststoreawaitLedgerDao.getStore(context);constresultawaitstore.querySql(SELECT SUM(CASE WHEN type1 THEN amount ELSE 0 END) AS income, SUM(CASE WHEN type0 THEN amount ELSE 0 END) AS expense, COUNT(*) AS cnt FROM${LedgerDao.TABLE}WHERE trade_time BETWEEN${start}AND${end});// ...读取 result 中的 income/expense/cnt}这条 SQL 的精髓在SUM(CASE WHEN type1 THEN amount ELSE 0 END)它把「收入求和」和「支出求和」合并进了同一条语句用一个条件聚合同时算出两个数。这正是我们在字段设计时坚持 type 用 0/1 数字的原因——如果 type 是字符串这个 CASE WHEN 就得写 ‘收入’ 字面量既啰嗦又容易拼写错误。COUNT(*)统计笔数与两个 SUM 平级一次查询三个结果数据库只扫描一遍时间范围内的数据性能最优。分类占比——GROUP BY 的艺术staticasynccategoryStats(context:common.Context,start:number,end:number):PromiseCategoryStat[]{constresultawaitstore.querySql(SELECT category, SUM(amount) AS amount, COUNT(*) AS cnt FROM${LedgerDao.TABLE}WHERE type0 AND trade_time BETWEEN${start}AND${end}GROUP BY category ORDER BY amount DESC);// ...遍历 result每行一个分类}GROUP BY category把支出按分类分组SUM(amount)求每组金额、COUNT(*)求每组笔数ORDER BY amount DESC让花钱最多的分类排在最前——页面上的「分类占比进度条」直接按这个顺序渲染越靠上的条越长视觉上天然形成「大头支出」的提示。这里用WHERE type0只统计支出因为「分类占比」这个概念对收入没有意义。月趋势——strftime 时间格式化staticasyncmonthlyTrend(context:common.Context,months:number):PromiseMonthTrend[]{constresultawaitstore.querySql(SELECT strftime(%Y-%m, trade_time/1000, unixepoch, localtime) AS month, SUM(CASE WHEN type0 THEN amount ELSE 0 END) AS expense FROM${LedgerDao.TABLE}GROUP BY month ORDER BY month DESC LIMIT${months});// ...}strftime(%Y-%m, trade_time/1000, unixepoch, localtime)是 SQLite 内置的日期格式化函数我们的时间戳是毫秒先除以 1000 转成秒unixepoch告诉 SQLite 这是 Unix 时间戳localtime转成本地时区最后%Y-%m提取「年-月」得到 ‘2025-06’ 这样的月份标签。GROUP BY month按月份分组求和配合LIMIT 6就能得到近 6 个月的支出趋势——这是趋势折线图的数据源。这个函数是 SQLite 自带的能力不需要任何第三方库是本实例最亮眼的技巧之一。七、技术要点对照表技术点实现方式生产价值双态汇总SUM(CASE WHEN type1...)一条 SQL 同时出收入支出扫描一次分类占比GROUP BY category ORDER BY amount DESC饼图/进度条数据源天然排序月趋势strftime(%Y-%m, ...)SQLite 自带时间格式化零依赖日期范围between(trade_time, a, b)按时间段筛选走索引金额精度REAL 存元严谨场景用分平衡易用与精度注释留痕枚举编码type 用 0/1 数字存储小、可参与 CASE WHEN时间双字段trade_time created_time补录场景报表不失真八、与「文章版」代码的差异说明这篇文章对应的文章目录里早期有一份「文章版」LedgerDao 代码与本文最终落地版本有几处关键差异这里如实说明避免读者照着旧文章敲代码踩坑静态字段引用旧版写this.store、this.TABLEArkTS 编译直接报arkts-no-this-in-static落地版全部改为LedgerDao.store、LedgerDao.TABLE。ResultSet 读取旧版用result.getRow()拿到ValuesBucket再row.id as number强转落地版改用result.getColumnIndex(id)getLong/getDouble/getString类型更安全NULL 值可兜底。返回类型旧版summary返回内联对象字面量类型{ income, expense, count }ArkTS 禁止arkts-no-obj-literals-as-types落地版定义LedgerSummary、CategoryStat、MonthTrend三个显式接口。context 类型旧版用UIAbilityContext落地版统一为基类common.Context可复用性更强。种子数据旧版没有initSeedData落地版内置 30 条跨月流水种子数据开屏即有完整仪表盘效果详见 3-4 文章。九、文章小结记账本数据层围绕trade_time amount两个核心字段展开区间查询用BETWEEN汇总用SUM CASE WHEN占比用GROUP BY趋势用strftime格式化。这四类聚合 SQL 是报表类功能的地基。从第一个实例的基础 CRUD到第二个实例的 LIKE 搜索与排序再到本实例的聚合统计我们正在逐步建立一套完整的 SQLite 生产级能力矩阵——下一个实例电子日记本将展示长文本存储与时间分组查询的又一变体。本篇文章动手练习建议在 DevEco Studio 里打开LedgerDao.ets尝试把summary里的SUM(CASE WHEN...)拆成两条独立查询对比一下多一次数据库访问的代价再试着给category加一个普通索引用EXPLAIN QUERY PLAN SELECT * FROM ledger WHERE category餐饮观察优化器的选择。理解「什么时候该建索引、什么时候不该建」是数据层工程师的分水岭。