实例：旅行行程规划（Trip Planner）｜技术：行程/每日安排双表、日期区间计算、预算进度聚合、状态流转

一、业务需求分析

1.1 旅行规划的核心诉求

旅行是"计划性最强"的消费活动之一。出发前要定目的地、算天数、排预算；行程中要逐日安排、记录花费；回来后要回顾总结。旅行行程规划应用要回答：

1. 这次旅行是什么？——目的地、起止日期、天数、预算；
2. 每天做什么、花多少？——按天拆解计划与花费；
3. 钱够不够？——已花费 vs 总预算的进度；
4. 现在处于什么阶段？——规划中 / 进行中 / 已完成。

建模上，行程是"主表"，每天的安排是"从表"——一个行程包含多天安排，这与 30 菜谱的"主明细表"同构，但日期维度的加入带来了新技巧：天数计算、日期区间查询、预算按天聚合。

1.2 功能清单

编号	功能	技术要点
1	创建行程（标题/目的地/天数/预算）	INSERT，start/end 日期计算
2	状态筛选（全部/规划中/进行中/已完成）	equalTo + Tab
3	每日安排编辑（计划 + 花费）	从表 upsert（存在更新/不存在插入）
4	预算进度条（已花费/预算）	SUM(cost) 聚合 + 百分比
5	行程详情（每日安排 + 状态流转）	主从组装
6	即将出发提醒（未来 N 天）	日期区间查询
7	统计（各状态数量/预算总额）	条件聚合

1.3 与 30 菜谱的对照

维度	30 美食菜谱	31 旅行行程
主表	recipe（菜谱）	trip（行程）
从表	recipe_ingredient（食材明细）	trip_day（每日安排）
从表序号	输入顺序（主料在前）	dayNo（第几天）+ date（日期）
从表新增	一次性批量插入	逐日 upsert（编辑）
特色	反查	预算进度 + 日期区间

31 的独特价值：从表带"日期"维度。dayNo 是逻辑序号，date 是物理日期——两者配合支持"第几天"的展示与"日期区间"的查询。同时从表新增方式从"批量"变为"逐日编辑"（upsert），这是行程规划的使用习惯。

二、字段设计表

2.1 主表 trip（行程）

字段名	类型	约束	说明
id	INTEGER	PRIMARY KEY AUTOINCREMENT	自增主键
title	TEXT	NOT NULL	行程标题（云南七日游）
destination	TEXT	DEFAULT ‘’	目的地
start_date	INTEGER	NOT NULL	出发日期（当日 0 点时间戳）
end_date	INTEGER	NOT NULL	返回日期
budget	REAL	NOT NULL DEFAULT 0	总预算（元）
status	INTEGER	NOT NULL DEFAULT 0	0 规划中 / 1 进行中 / 2 已完成
note	TEXT	DEFAULT ‘’	备注
created_at	INTEGER	NOT NULL	创建时间戳

2.2 从表 trip_day（每日安排）

字段名	类型	约束	说明
id	INTEGER	PRIMARY KEY AUTOINCREMENT	自增主键
trip_id	INTEGER	NOT NULL	逻辑外键 → trip.id
day_no	INTEGER	NOT NULL	第几天（1 起）
date	INTEGER	NOT NULL	当日日期（0 点时间戳）
plan	TEXT	DEFAULT ‘’	当天安排文本
cost	REAL	NOT NULL DEFAULT 0	当天花费（元）

2.3 设计要点详解

要点一：day_no 与 date 双字段。

• day_no：逻辑序号（第 1 天、第 2 天…），展示用（“D1 洱海环湖”）；
• date：物理日期，计算用（按日期查询、与"今天"比较）。

两者配合：列表按 day_no 排序展示；"进行中"的判断可按 date 区间（今天是否在 [start, end] 内）。逻辑序与物理日分离——day_no 不随 date 变化（用户可能调整出发日期，day_no 不变）。

要点二：日期存"当日 0 点时间戳"。

行程的日期是"天"粒度，存储时对齐到当日 0 点（today0.setHours(0,0,0,0)），这样：

• 天数计算：(end - start) / 86400000 + 1 得到整数天数；
• 日期区间查询：start_date >= now 判断"未出发"；
• 展示：new Date(ts) 取月日。

对齐 0 点避免"出发日是今天下午 3 点"这类混乱——日期类字段一律对齐到天粒度。

要点三：budget 用 REAL。

预算与花费是金额，REAL 存储（同 03 记账本的 amount）。COALESCE(SUM(cost), 0) 聚合已花费，进度条用 spent / budget 计算百分比。

要点四：从表编辑用 upsert 而非只增。

行程的每日安排是"可反复编辑"的内容（今天计划改了、花费补记了），不是只追加的日志。所以 saveDay 做 upsert：id > 0 更新、否则插入。这与 26-29 的"只追加"从表形成对照——从表的写入模式由业务语义决定：历史记录只追加，规划内容可修改。

三、建表 SQL

CREATE TABLE IF NOT EXISTS trip (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  destination TEXT DEFAULT '',
  start_date INTEGER NOT NULL,
  end_date INTEGER NOT NULL,
  budget REAL NOT NULL DEFAULT 0,
  status INTEGER NOT NULL DEFAULT 0,
  note TEXT DEFAULT '',
  created_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS trip_day (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  trip_id INTEGER NOT NULL,
  day_no INTEGER NOT NULL,
  date INTEGER NOT NULL,
  plan TEXT DEFAULT '',
  cost REAL NOT NULL DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_trip_status ON trip (status);
CREATE INDEX IF NOT EXISTS idx_trip_date ON trip (start_date);
CREATE INDEX IF NOT EXISTS idx_day_trip ON trip_day (trip_id);

索引分析：

• idx_trip_status：状态筛选 Tab；
• idx_trip_date：**即将出发查询（start_date BETWEEN）**与按日期排序；
• idx_day_trip：按行程查每日安排。

四、TripDao 数据访问层

4.1 每日安排 upsert ★核心方法

static async saveDay(context: common.Context, d: TripDay): Promise<void> {
  const store = await TripDao.getStore(context);
  if (d.id > 0) {
    // 更新：只改 plan 与 cost
    const values: relationalStore.ValuesBucket = { plan: d.plan, cost: d.cost };
    const predicates = new relationalStore.RdbPredicates(TripDao.DAY_TABLE);
    predicates.equalTo('id', d.id);
    await store.update(values, predicates);
  } else {
    // 插入新行
    await store.insert(TripDao.DAY_TABLE, {
      trip_id: d.tripId, day_no: d.dayNo, date: d.date,
      plan: d.plan, cost: d.cost,
    });
  }
}

upsert 的判断依据是 d.id > 0：编辑已有安排（有 id）→ 更新；新建安排（id=0）→ 插入。页面在点击"编辑"时把整行 TripDay（含 id）传入，保存时 id 决定走哪条分支。

为什么单行操作不需要事务：一次只写一行，无跨表一致性需求。事务只在"多行/跨表必须原子"时使用——不滥用事务。

4.2 天数计算

const dayCount = Math.max(1, Math.round((t.endDate - t.startDate) / 86400000) + 1);

日期区间的天数公式：(end - start) / 一天的毫秒数 + 1。如 7 月 1 日到 7 月 7 日：差值 6 天 + 1 = 7 天（含首尾）。Math.max(1, ...) 防御 end <= start 的异常输入（至少算 1 天）。

4.3 即将出发查询：日期区间 ★核心查询

static async queryUpcoming(context: common.Context, days: number): Promise<Trip[]> {
  const store = await TripDao.getStore(context);
  const now = Date.now();
  const end = now + days * 86400000;
  const result = await store.querySql(
    `SELECT * FROM ${TripDao.TABLE}
     WHERE start_date >= ${now} AND start_date <= ${end}
     ORDER BY start_date ASC`
  );
  return TripDao.collectTrips(result);
}

"即将出发"的区间：出发日在 [今天, 今天+N天] 内。start_date >= now（未出发）+ start_date <= now + N天（N 天内）。走 idx_trip_date 索引，ORDER BY start_date ASC 最早出发的在前。日期区间查询的标准形态（与 29 临期筛选同款）。

4.4 预算聚合

static async spentOf(context: common.Context, tripId: number): Promise<number> {
  const store = await TripDao.getStore(context);
  const result = await store.querySql(
    `SELECT COALESCE(SUM(cost), 0) AS total
     FROM ${TripDao.DAY_TABLE}
     WHERE trip_id = ${tripId}`
  );
  // COALESCE 兜底无记录的 NULL
}

已花费 = 该行程所有天花费之和。COALESCE(SUM(cost), 0) 处理"还没有任何天记录"的空集（SUM 返回 NULL）。页面拿 spent 与 budget 算进度百分比。

4.5 状态统计：条件聚合

static async summary(context: common.Context): Promise<{...}> {
  const result = await store.querySql(
    `SELECT COUNT(*) AS total,
            SUM(CASE WHEN status=0 THEN 1 ELSE 0 END) AS planned,
            SUM(CASE WHEN status=1 THEN 1 ELSE 0 END) AS ongoing,
            SUM(CASE WHEN status=2 THEN 1 ELSE 0 END) AS done,
            COALESCE(SUM(budget), 0) AS budget
     FROM ${TripDao.TABLE}`
  );
}

一条 SQL 出五个数：总数 + 三状态计数 + 预算总额。与 26 的 statusSummary 同款条件聚合。

4.6 视图组装

static async queryTripViews(context: common.Context, status: number): Promise<TripView[]> {
  const trips = status === -1 ? await TripDao.queryAll(context) : await TripDao.queryByStatus(context, status);
  const views: TripView[] = [];
  for (const t of trips) {
    const dayList = await TripDao.queryDays(context, t.id);
    const spent = await TripDao.spentOf(context, t.id);
    const dayCount = Math.max(1, Math.round((t.endDate - t.startDate) / 86400000) + 1);
    const v: TripView = { trip: t, dayCount: dayCount, spent: spent, dayList: dayList };
    views.push(v);
  }
  return views;
}

每行程组装：每日安排（详情抽屉用）+ 已花费（进度条用）+ 天数（卡片展示）。三个派生数据一次组装完，页面零计算。

4.7 种子数据

// 行程 1：规划中（10 天后出发），7 天 7 条安排，预算 8000
// 行程 2：进行中（今天出发），2 天 2 条安排，预算 2000
// 行程 3：已完成（30 天前），3 天 3 条安排，预算 1500

种子覆盖三种状态，状态 Tab 与统计都有演示数据；每日安排含花费，预算进度条有实际进度。

五、技术要点对照表

技术点	实现方式	生产价值
主从双表	trip + trip_day	行程与逐日计划分离
day_no + date	逻辑序 + 物理日	展示与计算分离
0 点时间戳	setHours(0,0,0,0)	天数计算精确
天数公式	(end-start)/day+1	含首尾的天数
upsert	id>0 更新否则插入	规划内容可编辑
日期区间	start_date BETWEEN	即将出发提醒
预算聚合	COALESCE(SUM(cost),0)	进度条数据源
条件聚合	CASE WHEN × 3	状态统计一条 SQL

六、文章小结

本篇完成了旅行规划的数据层：trip 存行程档案（含日期区间与预算），trip_day 存每日安排（含 dayNo 与 date），upsert 支持逐日编辑，日期区间查询服务"即将出发"，预算用 SUM 聚合驱动进度条，状态用条件聚合一屏统计。这套模型的精髓是："日期区间"的时间维度——天数由区间推导、提醒由区间查询、进度由区间内记录聚合，一切围绕"一段旅程的时间边界"展开。

下一篇《页面 UI 与操作实现》将搭建：状态 Tab、行程卡片（含预算进度条）、行程详情抽屉（每日安排 + 状态流转）、每日安排编辑弹窗、新增行程表单。

---

七、数据层深度扩展

1. 日期区间的边界陷阱

天数公式 (end - start) / 86400000 + 1 的前提是两端都对齐 0 点。若 start 是"7月1日 15:00"、end 是"7月7日 10:00"，差值 5.79 天，Math.round 得 6，+1 = 7 天——碰巧对；但若 end 是"7月7日 20:00"，差值 6.2 天 round 得 6，仍对。只要两端同一天粒度（0 点），公式恒准。跨时区旅行（出国）建议用 UTC 对齐或存"日期字符串"，避免时区偏移导致的天数差 1。

2. 行程状态自动流转

本实例状态手动切换（点 Tab）。可自动推导：

const autoStatus = (start: number, end: number, now: number): number => {
  if (now < start) return 0;          // 未出发：规划中
  if (now <= end) return 1;           // 区间内：进行中
  return 2;                           // 已过 end：已完成
};

列表查询时用自动状态覆盖存储值（或定时任务更新存储值）。自动状态保证不遗漏——用户忘了切换状态也不会显示错误。教学用手动切换演示状态机，自动流转留作扩展。

3. 预算的超支与结余

进度条 Math.min(100, spent/budget*100) 截断 100，超支显示红色。更精细的统计：

• 结余：budget - spent（可负）；
• 日均预算：budget / dayCount；
• 分类花费（交通/住宿/餐饮）：trip_day 加 cost_type 字段，GROUP BY 统计（复用 03 记账本的分类占比）。

4. 从表的排序键

ORDER BY day_no ASC 是逻辑排序（第 1 天到第 N 天）；若按实际日期排序（调整出发日期后），用 ORDER BY date ASC。排序键的选择要明确语义：展示行程按 dayNo（编号不跳变），按日期的日历视图用 date。

5. FAQ

Q1：为什么天数不直接存字段？
A：天数 = 日期区间的推导值，存字段会与 start/end 不同步（改日期忘改天数）。派生值实时计算，不落库——这是数据建模的 DRY 原则。

Q2：upsert 为什么不用 SQLite 的 ON CONFLICT？
A：relationalStore 的 insert 不直接暴露 ON CONFLICT 语法（可用 querySql），且"先查 id 再决定"在业务层更清晰。数据量小，两次操作开销可忽略。可读性优先于 SQL 技巧。

Q3：每日安排能排序/换位吗？
A：本实例按 day_no 固定。若要"某天插入半天安排"，可修改 day_no（后续行 +1），需要 UPDATE 多行——可用事务包裹。教学保持简单。

Q4：跨年行程（12月31日~1月2日）天数算得对吗？
A：对。时间戳是绝对毫秒，跨年不影响差值计算。毫秒时间戳天然无跨年/跨月边界问题（对比字符串日期）。

Q5：预算进度能按天看曲线吗？
A：可以——按 date 分组 SUM(cost) 得每日花费，累计得进度曲线。数据源就是 trip_day，参考 03 记账本的 monthlyTrend 实现。

---

八、下篇预告

下一篇《页面 UI 与操作实现》将完成：状态 Tab（四段筛选）、行程卡片（含预算进度条 + 超支红色）、详情抽屉（每日安排列表 + 状态流转胶囊）、每日安排编辑弹窗（计划 + 花费）、新增行程表单。敬请期待。