实例：健康数据（Health）｜技术：AVG/SUM 聚合、日期范围、趋势对比

一、聚合查询的战场

健康数据是前面所有实例中聚合查询最密集的：周日均（AVG）、周合计（SUM）、趋势对比（两周均值差）、近 7 天序列（范围查询）。本篇文章逐一深入，重点讲 日期范围的聚合 与 趋势对比查询——这是健康应用的数据层灵魂。

二、周汇总：AVG + 日期范围

本周概览需要五项日均值——本周 AVG(weight/steps/sleep/water/exercise)。落地版用一个方法一次取回：

static async weekSummary(context: common.Context): Promise<WeekSummary> {
  const store = await HealthDao.getStore(context);
  const start = HealthDao.daysAgo(6);   // 7 天前（含今天）
  const today = HealthDao.today();
  const result = await store.querySql(
    `SELECT AVG(weight) AS w, AVG(steps) AS s, AVG(sleep_hours) AS sh,
            AVG(water) AS wa, AVG(exercise_minutes) AS e
     FROM ${HealthDao.TABLE}
     WHERE record_date >= '${start}' AND record_date <= '${today}'`
  );
  const summary: WeekSummary = { avgWeight: 0, avgSteps: 0, avgSleep: 0, avgWater: 0, avgExercise: 0 };
  if (result.goToNextRow()) {
    summary.avgWeight = result.getDouble(result.getColumnIndex('w'));
    summary.avgSteps = result.getDouble(result.getColumnIndex('s'));
    summary.avgSleep = result.getDouble(result.getColumnIndex('sh'));
    summary.avgWater = result.getDouble(result.getColumnIndex('wa'));
    summary.avgExercise = result.getDouble(result.getColumnIndex('e'));
  }
  result.close();
  return summary;
}

生成的 SQL：

SELECT AVG(weight) AS w, AVG(steps) AS s, AVG(sleep_hours) AS sh,
       AVG(water) AS wa, AVG(exercise_minutes) AS e
FROM health
WHERE record_date >= '2024-06-19' AND record_date <= '2024-06-25';

五个 AVG 一次查询：一条 SQL 同时求五项日均——比五次查询高效得多。一查多值是聚合查询的优化模式（减少往返）。

日期范围怎么算「本周」？ 落地版简化为「近 7 天」（daysAgo(6) 到 today）——不按周一~周日算自然周，避免复杂的星期计算。**「滚动 7 天」替代「自然周」**是 Demo 的务实简化；真实健康 App 按自然周（date('now', 'weekday 0', '-7 days') 等 SQLite 日期函数）计算。

AVG 与缺日：近 7 天如果有几天没记录（只记了 5 天），AVG 只对有记录的日期求平均（缺日不计入分母）——「有记录日子的平均」而非「7 天强行平均」。AVG 的自然行为：NULL 行自动忽略。

三、趋势对比：两周均值差

体重趋势 = 本周均值 - 上周均值：

static async weightTrend(context: common.Context): Promise<{ current: number; last: number }> {
  const store = await HealthDao.getStore(context);
  const weekStart = HealthDao.daysAgo(6);
  const lastWeekStart = HealthDao.daysAgo(13);
  const today = HealthDao.today();
  const result = await store.querySql(
    `SELECT
       (SELECT AVG(weight) FROM ${HealthDao.TABLE}
        WHERE record_date >= '${weekStart}' AND record_date <= '${today}') AS current_avg,
       (SELECT AVG(weight) FROM ${HealthDao.TABLE}
        WHERE record_date >= '${lastWeekStart}' AND record_date <= '${weekStart}' ) AS last_avg`
  );
  let current = 0;
  let last = 0;
  if (result.goToNextRow()) {
    current = result.getDouble(result.getColumnIndex('current_avg'));
    last = result.getDouble(result.getColumnIndex('last_avg'));
  }
  result.close();
  return { current: current, last: last };
}

两个日期区间的划分：

• 本周：[daysAgo(6), today]（近 7 天）；
• 上周：[daysAgo(13), daysAgo(7)]（再往前 7 天）——注意边界 daysAgo(7) 不与本周重叠。

两个子查询各算一个 AVG——本周均值与上周均值，页面相减得趋势（↑/↓）。趋势 = 两组聚合的差：这是所有「变化趋势」查询的通用模式（涨跌、增减、快慢）。

边界重叠的坑：上周的结束边界应是 daysAgo(7)（本周开始前一天），写成 weekStart（daysAgo(6)）会让「6 天前那天」被两周同时计入——日期区间划分必须互斥。落地版用 <= weekStart 表示上周结束，与本周起点 daysAgo(6) 重叠一天？检查：本周是 >= daysAgo(6)，上周是 <= daysAgo(6) 且 >= daysAgo(13)——daysAgo(6) 那天两周都算！这是边界 bug。正确写法：上周结束用 daysAgo(7)（今天之前第 7 天），保证 [daysAgo(13), daysAgo(7)] 与 [daysAgo(6), today] 不重叠。读者写趋势对比时务必检查区间边界。

四、近 7 天序列：趋势图数据源

柱状图的数据源是近 7 天睡眠序列：

static async queryRange(context: common.Context, startDate: string, endDate: string): Promise<HealthRecord[]> {
  // greaterThanOrEqualTo('record_date', startDate)
  //   .lessThanOrEqualTo('record_date', endDate).orderByAsc('record_date')
}

queryRange 的通用性：不仅是睡眠柱状图——近 7 天列表、任意日期范围的明细都复用。「日期范围 + 排序」是时序数据的基础查询，所有可视化都从它取数。

页面组装 sleepBars：

this.sleepBars = this.records.map((r: HealthRecord) => {
  const bar: SleepBar = { date: r.recordDate, value: r.sleepHours };
  return bar;
});

记录列表（queryRange 结果）直接映射成柱状图数据——列表与图表同源，一次查询两个用途。

五、插入与 UNIQUE 冲突处理

每日新增：record_date UNIQUE 约束防止同日重复。落地版的处理：

static async insert(context: common.Context, r: HealthRecord): Promise<number> {
  const store = await HealthDao.getStore(context);
  const values: relationalStore.ValuesBucket = {
    record_date: r.recordDate, weight: r.weight, steps: r.steps,
    sleep_hours: r.sleepHours, heart_rate: r.heartRate,
    water: r.water, exercise_minutes: r.exerciseMinutes, remark: r.remark,
  };
  return await store.insert(HealthDao.TABLE, values);
}

页面层的冲突提示（onSave 前先查）：

const existing = await HealthDao.queryByDate(this.context, date);
if (existing && !this.editing) {
  promptAction.showToast({ message: '今天已有记录，请编辑' });
  return;
}

双重防线：页面先查（友好提示）+ 数据库 UNIQUE 约束（硬性兜底）。**「应用层友好 + 数据库硬性」**是数据完整性的最佳实践——应用层提示比裸报错友好，数据库约束防止并发/绕过。

六、更新与删除

更新（编辑当日记录）：equalTo('id', id) + 全量更新六指标（与便签全量更新同款）。

删除：equalTo('id', id) + delete——支持删除某天记录（页面记录行长按或删除按钮）。

七、技术要点对照表

技术点	实现方式	生产价值
周日均	五 AVG 一次查询	一查多值
日期范围	字符串比较	无需日期函数
趋势对比	两子查询 AVG 之差	变化量化
近 7 天	queryRange + ASC	图表数据源
UNIQUE 冲突	页面先查 + DB 约束	双重防线
日期格式化	padStart 补零	字典序正确

八、常见问题 FAQ

Q1：AVG 遇到没有记录的日期会怎样？
A：AVG 只对有记录的行求平均——近 7 天只记了 5 天，AVG 是这 5 天的平均（缺日不计入分母）。如果想「7 天平均（缺日按 0）」，需 SUM(weight)/7 手工算。AVG 的语义是「有值平均」，用前想清楚。

Q2：为什么用「近 7 天」而不是「本周一~今天」？
A：自然周要算「本周一是哪天」（周几判断 + 跨月边界），Demo 简化成滚动 7 天（daysAgo(6)~today）避免复杂日期计算。真实应用用 SQLite 日期函数：date('now', 'start of week', '+1 day') 等。简化牺牲周语义，换取零日期复杂度。

Q3：趋势对比的区间边界为什么容易错？
A：本周 [daysAgo(6), today] 与上周 [daysAgo(13), daysAgo(6)] 在 daysAgo(6) 重叠——那天的体重被两周同时平均。正确划分：上周结束用 daysAgo(7)（[daysAgo(13), daysAgo(7)]），与本周起点 daysAgo(6) 互斥。画时间轴检查边界是趋势查询的必修课。

Q4：柱状图高度 value * 8 的缩放系数怎么定的？
A：睡眠时长范围 0~12h，12 * 8 = 96（约 100 视觉高度）——按最大值的展示高度反推系数。换成体重（40~100kg）要调系数或归一化（value / max * 100）。缩放系数 = 目标最大高度 / 数据最大值。

Q5：五项日均一次查询，还是五项分开查？
A：一次查询（五 AVG 一条 SQL）——往返一次拿到全部日均。分开查要 5 次 querySql。能合并的聚合尽量合并，减少 RDB 往返是性能常识。

Q6：queryRange 返回的记录没有的日期怎么办？
A：近 7 天某天没记录，queryRange 不含该行——列表少一天、柱状图少一根柱。想「缺日补 0」（柱状图连续），需前端按日期遍历补空值（7 天循环，无记录补 0）。补零是前端职责，数据层返回真实数据。

九、文章小结

本篇文章深入讲解了健康数据的数据层核心：周汇总（五 AVG 一次查询的优化模式）、趋势对比（两子查询均值之差，注意区间互斥的边界坑）、近 7 天序列（范围查询喂图表）、UNIQUE 冲突的双重防线。核心心法是「聚合查询的一次查询多值」与「趋势 = 两组聚合之差」——健康应用的仪表盘数据全部来自这两类查询。

下一篇（14-4）展示 14 天健康记录种子数据，让仪表盘一开屏就数字饱满、柱状图成列。

十、执行计划、边界补零与 FAQ 实战

1. 五 AVG 一次查询的执行计划分析

周汇总 SQL 为什么快？用 SQLite 的 EXPLAIN QUERY PLAN 看执行计划：

EXPLAIN QUERY PLAN
SELECT AVG(weight) AS w, AVG(steps) AS s, AVG(sleep_hours) AS sh,
       AVG(water) AS wa, AVG(exercise_minutes) AS e
FROM health
WHERE record_date >= '2024-06-19' AND record_date <= '2024-06-25';

层级	细节	含义
SEARCH	health USING INDEX sqlite_autoindex_health_1	record_date UNIQUE 自动建索引，范围查询走索引
WHERE	record_date > ? AND record_date < ?	只读 7 行，不是全表扫描

两个关键点：UNIQUE 约束自动生成索引（建表即有，无需手动 CREATE INDEX），范围过滤直接命中索引只取 7 行；五个 AVG 共享这一次扫描——每读一行，五个累加器（sum + count）各更新一次，一条 SQL 内部只遍历一次表。「一次扫描 + 多累加器」就是一查多值的底层原理。

2. 趋势对比：两周区间边界（重叠陷阱详解）

把 14 天画到时间轴上：

daysAgo(13)        daysAgo(7)  daysAgo(6)          today
   |——— 上周 [13,7] ———|——— 本周 [6,0] ———|
                        ↑            ↑
                    上周结束边界   本周起点

写法	上周区间	结果
错误	[daysAgo(13), daysAgo(6)]	daysAgo(6) 那天被两周同时平均，重叠 1 天
正确	[daysAgo(13), daysAgo(7)]	与本周互斥，14 天各归其位

口诀：上周结束 = 本周起点 - 1 天。重叠 1 天的后果：那天的体重在两个均值里各出现一次，趋势差被「稀释」——体重明明在降，箭头可能变平。画时间轴检查是趋势查询的必修课。

3. queryRange 缺日补零：前端处理

queryRange 只返回有记录的日期——缺日时柱状图会少柱。前端按 7 天循环补零：

private buildSleepBars(): SleepBar[] {
  const bars: SleepBar[] = [];
  for (let i = 6; i >= 0; i--) {
    const date = HealthDao.daysAgo(i);
    const rec = this.records.find((r: HealthRecord) => r.recordDate === date);
    bars.push({ date: date, value: rec ? rec.sleepHours : 0 });
  }
  return bars;
}

补零在页面层做：数据层返回真实行（保证数据可信），展示层负责「连续化」（保证图表好看）。注意补零与真 0 值在视觉上无法区分——但健康数据里「没睡」和「没记录」展示为 0 均可接受。

4. UNIQUE 冲突的双重防线再挖一层

页面先查（友好提示）只是第一道防线——并发双击或绕过页面的写入仍会触发 UNIQUE 冲突，insert 抛 SQLITE_CONSTRAINT_UNIQUE 异常。落地版在保存处再加 try/catch 兜底：

try {
  await HealthDao.insert(this.context, record);
  promptAction.showToast({ message: '保存成功' });
} catch (e) {
  promptAction.showToast({ message: '同日记录已存在，请编辑' });
}

防线	机制	定位
第一道	页面先查 + 提示	友好交互
第二道	UNIQUE 约束	数据硬性保障
第三道	try/catch 捕获异常	异常兜底

三层防线各司其职：页面防误操作、数据库防重复、catch 防崩溃。

5. 本节 FAQ

Q1：UNIQUE 的自动索引在哪建的？
A：建表语句 record_date TEXT NOT NULL UNIQUE 的 UNIQUE 约束让 SQLite 自动创建索引（sqlite_autoindex_health_1），无需手动 CREATE INDEX——范围查询天然走索引。若去掉 UNIQUE，需自己补 CREATE INDEX idx_health_date ON health(record_date)。

Q2：缺日补零放数据层做可以吗？
A：不建议。数据层应返回「实际存在的数据」，补零是展示语义（图表连续化），放页面层让数据层保持纯粹——查询结果永远真实可审计。

Q3：重叠 1 天的趋势误差有多大？
A：重叠那天同时进两周均值，趋势被向 0 拉近。以体重为例：本周真均值 72.8、上周 73.6，趋势 -0.8；若 daysAgo(6) 那天（73.5）被两周共用，趋势会被稀释到约 -0.7——量级不大但方向可能失真。边界互斥是趋势正确的底线。