实例：送礼人情簿（Gift Ledger）｜技术：单表人情记录、收送双向统计（SUM CASE WHEN）、GROUP BY 关系分组、对象往来聚合

一、业务需求分析

1.1 人情往来的记账需求

在中国社会，人情往来（随礼）是一笔重要的"隐形账本"：谁家结婚随了多少、谁给我回礼多少、某位亲戚我送过几次……人情簿要回答：

1. 我送出/收到多少钱？——收送双向统计；
2. 各关系（亲戚/朋友/同事）的往来分布？——按关系分组；
3. 跟某个人往来是否平衡？——按对象聚合，算差额；
4. 具体记录可追溯——谁、何时、何因、多少。

建模上这是一个单表应用（不像 26-32 有主从结构），但查询是重头戏：双向统计（收/送）、多维度分组（关系/对象）、差额计算——这是 SUM + CASE WHEN + GROUP BY 的综合演练。

1.2 功能清单

编号	功能	技术要点
1	记录人情（对象/关系/方向/金额/事由）	INSERT 单表
2	方向筛选（全部/送出/收到）	equalTo + Tab
3	收送汇总（送出总额/收到总额/净差额）	SUM CASE WHEN
4	按关系分组统计 ★	GROUP BY + 双方向聚合
5	按对象往来聚合 ★	GROUP BY person + 差额
6	按对象搜索	LIKE
7	删除记录	DELETE

1.3 与前 32 实例的关系

实例	结构	本实例特色
03 记账本	单表流水	收/支 双方向
33 人情簿	单表	双向统计 + 多维度分组 + 差额

33 是 SQLite 系列的"聚合查询收官课"：把 03 记账本的收支双方向、06 进销存的按类分组、17 预算的差额计算集中到一个单表应用里。它的价值不是表结构（很简单），而是查询的维度组织——同一个 direction 字段，能从"总账"、“按关系”、"按对象"三个维度切。

二、字段设计表

2.1 单表 gift（人情记录）

字段名	类型	约束	说明
id	INTEGER	PRIMARY KEY AUTOINCREMENT	自增主键
person	TEXT	NOT NULL	往来对象（名字/称呼）
relation	TEXT	DEFAULT ‘朋友’	关系（亲戚/朋友/同事/同学/邻居…）
direction	INTEGER	NOT NULL DEFAULT 0	0 送出 / 1 收到
amount	REAL	NOT NULL DEFAULT 0	金额（元）
occasion	TEXT	DEFAULT ‘’	事由（结婚/满月/乔迁/生日…）
date	INTEGER	NOT NULL	日期（0 点时间戳）
note	TEXT	DEFAULT ‘’	备注
created_at	INTEGER	NOT NULL	录入时间戳

2.2 设计要点详解

要点一：direction 双向编码。

direction: 0/1（送出/收到）是核心字段，类似记账本的收支。它驱动的统计模式贯穿全篇：

SUM(CASE WHEN direction=0 THEN amount ELSE 0 END) AS out_sum   -- 送出总额
SUM(CASE WHEN direction=1 THEN amount ELSE 0 END) AS in_sum    -- 收到总额

同一个字段，两种视角——送出（direction=0）与收到（direction=1）在一条 SQL 里同时算出。这是条件聚合的双向版本（26 的 statusSummary 是多值版本）。

要点二：relation 用自由文本。

关系（亲戚/朋友/同事）是有限但开放的集合：新关系随时可能出现（“前同事”、“邻居”）。用 TEXT 自由输入 + GROUP BY 聚合，天然支持新关系——分类字段用自由文本 + 分组聚合，比硬编码枚举更灵活（代价是同一关系可能拼写不一致，如"同事"/"前同事"分成两组）。

要点三：person 不独立建表。

每个人（王强、张阿姨）是 TEXT 字段而非人员表外键。理由：人情记录是"轻量往来"，不维护人员档案（电话/地址）；GROUP BY person 即可聚合某对象的往来。何时升级为人员表：需要维护人员资料、按人员分组统计更多维度时。

要点四：amount 用 REAL。

金额可能有小数（随礼 888.8 元），REAL 存储（同 03 记账本）。SUM 聚合金额，toFixed(0) 展示取整。

要点五：date 存 0 点时间戳。

与 31 行程同款约定——"哪天随的礼"是天粒度，对齐 0 点便于按日期排序与区间查询。

三、建表 SQL

CREATE TABLE IF NOT EXISTS gift (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  person TEXT NOT NULL,
  relation TEXT DEFAULT '朋友',
  direction INTEGER NOT NULL DEFAULT 0,
  amount REAL NOT NULL DEFAULT 0,
  occasion TEXT DEFAULT '',
  date INTEGER NOT NULL,
  note TEXT DEFAULT '',
  created_at INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_gift_person ON gift (person);
CREATE INDEX IF NOT EXISTS idx_gift_relation ON gift (relation);
CREATE INDEX IF NOT EXISTS idx_gift_date ON gift (date);

索引分析：

• idx_gift_person：按对象搜索（LIKE）与按人聚合（GROUP BY person）的加速；
• idx_gift_relation：按关系分组（GROUP BY relation）；
• idx_gift_date：按日期排序（列表倒序）。

四、GiftDao 数据访问层

4.1 收送汇总：双向条件聚合 ★核心查询

static async summary(context: common.Context): Promise<{ outAmount: number; inAmount: number; count: number; balance: number }> {
  const store = await GiftDao.getStore(context);
  const result = await store.querySql(
    `SELECT COUNT(*) AS cnt,
            COALESCE(SUM(CASE WHEN direction=0 THEN amount ELSE 0 END), 0) AS out_sum,
            COALESCE(SUM(CASE WHEN direction=1 THEN amount ELSE 0 END), 0) AS in_sum
     FROM ${GiftDao.TABLE}`
  );
  // 读出 cnt / out_sum / in_sum
  return { outAmount, inAmount, count, balance: outAmount - inAmount };
}

一条 SQL 出三个数：总笔数 + 送出总额 + 收到总额。SUM(CASE WHEN direction=0 ...) 是"送出"的条件聚合，direction=1 是"收到"。净差额在应用层计算：balance = outAmount - inAmount（正=送出多，负=收到多）。

COALESCE 兜底：空表时 SUM 返回 NULL，COALESCE(..., 0) 归一为 0（同 29/31 的约定）。

4.2 按关系分组统计 ★核心查询

static async relationStats(context: common.Context): Promise<RelationStat[]> {
  const result = await store.querySql(
    `SELECT relation,
            SUM(CASE WHEN direction=0 THEN 1 ELSE 0 END) AS out_cnt,
            COALESCE(SUM(CASE WHEN direction=0 THEN amount ELSE 0 END), 0) AS out_sum,
            SUM(CASE WHEN direction=1 THEN 1 ELSE 0 END) AS in_cnt,
            COALESCE(SUM(CASE WHEN direction=1 THEN amount ELSE 0 END), 0) AS in_sum
     FROM ${GiftDao.TABLE}
     GROUP BY relation
     ORDER BY out_sum + in_sum DESC`
  );
}

GROUP BY relation + 双方向四聚合：每个关系组算出送出次数、送出金额、收到次数、收到金额——一组四数。这是 03 记账本分类占比的"双向扩展版"。

排序：ORDER BY out_sum + in_sum DESC 按"该关系总流水"降序——往来最多的关系排最前。

4.3 按对象往来聚合 ★核心查询

static async personStats(context: common.Context): Promise<PersonStat[]> {
  const result = await store.querySql(
    `SELECT person,
            MAX(relation) AS relation,
            COALESCE(SUM(CASE WHEN direction=0 THEN amount ELSE 0 END), 0) AS out_sum,
            COALESCE(SUM(CASE WHEN direction=1 THEN amount ELSE 0 END), 0) AS in_sum
     FROM ${GiftDao.TABLE}
     GROUP BY person
     ORDER BY (out_sum - in_sum) DESC`
  );
  // 应用层算 balance = outAmount - inAmount
}

GROUP BY person + MAX(relation)：同一人可能有不同关系记录（王强先标"朋友"后标"同学"），MAX(relation) 取一个代表值（字母序最大，非严格语义——教学简化，注释说明）。聚合列（SUM）与代表列（MAX）混合是"按人汇总 + 附属性"的常用写法。

排序：ORDER BY (out_sum - in_sum) DESC 按差额降序——你送出最多（对方欠你最多）的人排最前，这是人情簿最有价值的排序。

4.4 增删查

static async insert(context: common.Context, g: Gift): Promise<number> { /* 标准 INSERT */ }
static async delete(context: common.Context, id: number): Promise<number> { /* 标准 DELETE */ }
static async queryAll / queryByDirection / searchByPerson

单表 CRUD 与前 20 个实例同款。queryByDirection 用 equalTo('direction', n)，searchByPerson 用 like('person', '%kw%')。

4.5 种子数据

// 10 条记录：送出 7 条、收到 3 条
// 关系覆盖：亲戚 3 / 朋友 3 / 同学 2 / 同事 1 / 邻居 1
// 王强出现 2 次（送出 500 + 收到 600）→ 按对象聚合演示差额
// 陈姐收到 800（乔迁回礼）→ 收到方向演示

种子设计：王强一送一收（差额 -100，对方多还 100）→ "按对象往来"的差额演示；关系分布均匀 → “按关系分组"有层次；送出多于收到 → 净差额为正（人情常见"先送后收”）。

五、技术要点对照表

技术点	实现方式	生产价值
双向编码	direction 0/1	一字段两方向
双向聚合	SUM CASE WHEN × 2	一条 SQL 出收送
分组统计	GROUP BY relation + 四聚合	关系维度分布
对象聚合	GROUP BY person + MAX	按人往来汇总
差额计算	out - in 应用层	人情平衡判断
差额排序	ORDER BY (out-in) DESC	最关心的人在前
代表列	MAX(relation)	聚合附属性
单表设计	person 不建表	轻量往来够用

六、文章小结

本篇完成了人情簿的数据层：一张 gift 表承载全部记录（对象/关系/方向/金额/事由），查询是主角——双向条件聚合（收送总额）、GROUP BY 分组（按关系四聚合、按对象带差额）、差额排序（欠我最多的人在前）。这套模型的精髓是：单表 + 多维聚合——结构简单不等于查询简单，direction 一个字段从"总账/关系/对象"三个维度切分，每次切分都是一组 CASE WHEN。

下一篇《页面 UI 与操作实现》将搭建：收送汇总标题、方向 Tab、人情记录列表、新增弹窗（方向切换 + 表单）、统计抽屉（关系分组 + 对象往来）。

---

七、数据层深度扩展

1. 年度/月度维度

人情有季节性（国庆结婚潮、春节随礼）。按月份组：

SELECT strftime('%Y-%m', date/1000, 'unixepoch', 'localtime') AS month,
       SUM(CASE WHEN direction=0 THEN amount ELSE 0 END) AS out_sum,
       SUM(CASE WHEN direction=1 THEN amount ELSE 0 END) AS in_sum
FROM gift
GROUP BY month
ORDER BY month

复用 03 记账本的 monthlyTrend 思路。时间维度 + 方向维度的交叉统计是人情簿进阶查询。

2. 关系的规范化

自由文本 relation 的"同事/前同事"分组问题。规范化方案：

• 预置关系枚举 + 下拉选择（页面约束输入）；
• 或 relation_id 外键到关系表（可扩展关系属性）。

何时规范化：关系种类固定且需要跨关系统计精确时。家庭场景自由文本 + GROUP BY 足够。

3. 差额的语义

balance = out - in：正=你送出的多（对方欠你人情）；负=你收到的多（你欠对方）。差额是"人情债"的数字表达——但人情不能真当债算，页面文案用"对方欠/你欠"是戏谑语气，语义边界要清晰。

4. 删除与修改

• 删除：单条 DELETE（误记录删除）；
• 修改：updateGift（金额记错修正）——本实例未实现，trivial 扩展；
• 审计：加 created_at 已有，可按录入时间追溯。

5. FAQ

Q1：为什么 person 不建表外键？
A：人情记录不维护人员档案，GROUP BY person 即可聚合。若需人员电话/地址/亲属关系，再建 person 表（参考 12 CRM 的客户表）。按需建表，避免过度设计。

Q2：MAX(relation) 取的关系准吗？
A：不准（字母序最大）。教学简化——若要精确，按"该人最近一条记录的关系"取（子查询）或把关系提到聚合语义之外。注释标注局限，读者按需升级。

Q3：方向 Tab 与统计抽屉的数据关系？
A：方向 Tab 只过滤列表；统计抽屉是全量聚合（不分方向 Tab）。若要做"送出明细的按关系统计"，需把 filter 传入查询——本实例统计抽屉固定全量，简化演示。

Q4：金额单位能换吗（千元/万元）？
A：存储元（REAL），展示可 amount/10000 转万元。存储规范、展示灵活——不要在存储层做展示单位转换。

Q5：人情簿需要备份吗？
A：随礼数据是重要资产（多年积累），建议定期导出——参考 10 数据备份导出的 CSV/JSON 导出实现。人情簿 + 备份是完整方案。

---

八、下篇预告

下一篇《页面 UI 与操作实现》将完成：收送汇总标题（送出/收到/净差）、方向 Tab（红送绿收）、人情记录列表（±金额着色）、新增弹窗（方向切换胶囊 + 表单）、统计抽屉（关系分组表 + 对象往来表）。敬请期待。