线上轨迹日志数据分析服务技术方案

项	内容
版本	v1.1
日期	2026-08-14
状态	P0 已开工：代码在仓库 `jetwave-usage-analytics/`
建议项目名	`jetwave-usage-analytics`（JUA）
关联	TLA（`trajectory-log-analyzer`）、`jetwave-traj-store`、离线 `current-distribution-analyzer`
源表	`surf_motion_trajectory_history`
已有分析表	`surf_motion_trajectory_analysis`（故障 / KPI）

0. 结论（先看这段）

把现在靠脚本 + Parquet + HTML 看板做的「电流工况 / 翼型 / 负载」分析，固化成一条跟线上库按 id 对齐的增量服务：下载 OSS 日志 → 解析 → 写入分析库 → 产出翼型号、负载档、航次评价。你提供机器、MySQL、OSS；本方案不改业务写入路径。

你的要求	方案怎么落地
基础环境你来建	复用现网 MySQL + OSS；服务用 Docker + cron，和 TLA 同机或旁路即可
日志下载、解析、入库	下载/解析复用 TLA；本服务只消费「已分析成功」的会话，避免两套 OSS 客户端
用 id 对上线上库	全链路主键 `trajectory_id = surf_motion_trajectory_history.id`，1:1
业务扩展字段	可查询列走正式字段；新指标先进 `ext_json` + 字段登记表，稳定后再 ALTER
自动核对线上库	水位增量 + 每日对账（条数、deleted、object_key、关键数值）
翼型 / 负载 / 运行评价	离线标定模型 + 在线逐条打标（不能每来一条就全量 K-Means）

明确不做什么（本方案边界）：

不替代 TLA 的故障判定（has_fault / 故障码只读同步）。
不替代 jetwave-traj-store 的地图回放 / 时空检索。
不把全部轨迹点灌进业务 MySQL（点级走对象存储或已有 PG，会话结论进 MySQL）。
不在业务表上直接加分析列（分析库旁路，JOIN 用 id）。

1. 为什么单独做这个服务

现状是三套能力叠在一起，但工况分析还没产品化：

业务 MySQL + OSS
        │
        ├─ TLA          会话故障 / 版本 / 温度     → surf_motion_trajectory_analysis
        ├─ traj-store   点入库 / 地图 / 回放       → PostgreSQL
        └─ 离线脚本     电流分布 / 翼型 / 负载     → Parquet + HTML（人工跑）

离线脚本已经证明：用 trajectory_id 能把点级电流和线上 history 行对上，并能给出翼型簇、负载档、电池内阻。缺口是：

不能随新日志自动跑。
结果不在库里，后台 / App 无法按 id 查。
加字段要改脚本、改表、改报表，没有演进通道。
线上 deleted、改 object_key、补 SN 后，分析侧不会自动纠偏。

本服务补的就是这一层：会话级使用画像（Usage Profile）。

2. 系统位置

  业务源（只读，不改写）
  ┌─────────────────────────────────────────┐
  │ MySQL: surf_motion_trajectory_history   │
  │        surf_device_basic_remote 等      │
  │ OSS:   object_key → JSON 轨迹文件        │
  └──────────────────┬──────────────────────┘
                     │
         ┌───────────┴────────────┐
         ▼                        ▼
  ┌─────────────┐          ┌─────────────┐
  │ TLA（已有）  │          │ traj-store  │
  │ 故障 / KPI  │          │ 点 + 地图   │
  └──────┬──────┘          └──────┬──────┘
         │ 只读                   │ 可选读点
         ▼                        │
  ┌─────────────────────────────────────────┐
  │ jetwave-usage-analytics（本方案）        │
  │                                         │
  │ 1. 拉 pending（TLA=done 且本服务未跑）   │
  │ 2. 下载 JSON（或读 TLA/OSS 缓存）        │
  │ 3. 点级过滤 → 特征 → 翼型/负载/评价      │
  │ 4. UPSERT 会话画像表（PK=trajectory_id） │
  │ 5. 对账任务：对齐线上 history            │
  └──────────────────┬──────────────────────┘
                     │
                     ▼
              后台 / API / 看板
              GET /usage/{trajectory_id}

黄金键： 任何一张分析表、任何一条 API，会话主键都是 trajectory_id，等于线上 history.id。禁止用 remote_sn + 时间戳 当主键。

3. 存储选型

你提到 MySQL / MongoDB 都可以。结合「必须和线上库按 id 对上、还要跟着业务加字段」，推荐 MySQL 会话库 + JSON 扩展 + 点级不进 MySQL。

数据	放哪	原因
会话结论（翼型、负载、评分、分位数）	MySQL 新表，可与业务库同实例或只读从库旁的分析库	`JOIN history.id = usage.trajectory_id` 零映射
尚未定型的新指标	同一行的 `ext_json`	加字段当天就能写，不用等发版 ALTER
点级采样（电流/速度序列）	OSS Parquet（按日分区）或复用 traj-store	单航次几千～几万点，全进 MySQL 会把业务库打满
翼型质心、负载阈值等模型	MySQL `jua_model_version`	在线打标必须钉死版本，可回放
字段登记 / 对账差异	MySQL 小表	可审计

不建议把会话主表放 MongoDB： 线上是 MySQL，跨库对 id 做一致性核对、和后台现有报表 JOIN 成本更高。Mongo 只适合以后做「原始 JSON 全文检索」的附属库，本阶段不做。

若分析库必须与业务库隔离：分析 MySQL 单独实例，表结构仍用 trajectory_id，对账任务跨库比对 history.id。

4. 数据模型

4.1 主键与关联

surf_motion_trajectory_history.id
        │ 1:1
        ├─ surf_motion_trajectory_analysis.trajectory_id     （TLA，已有）
        └─ surf_motion_usage_profile.trajectory_id           （本服务，新建）

辅助键：object_key UNIQUE（防同一 OSS 文件双写）。
内容指纹：content_fingerprint（JSON SHA256）+ usage_analyzer_version + model_version，三者未变则跳过。

4.2 会话画像主表（核心，示意 DDL）

CREATE TABLE surf_motion_usage_profile (
  trajectory_id        INT PRIMARY KEY COMMENT '=history.id',
  object_key           VARCHAR(255) NOT NULL,
  play_user_id         INT NULL,
  board_id             INT NULL,
  remote_id            INT NULL,
  electrical_sn        VARCHAR(64) NULL,
  board_sn             VARCHAR(32) NULL,
  session_date         DATE NULL,

  -- 质量门（不过门也可落库，但 wing/load 标 unknown）
  quality_pass         TINYINT(1) NOT NULL DEFAULT 0,
  quality_reason       VARCHAR(64) NULL COMMENT 'too_short|no_takeoff|low_points|…',
  valid_points         INT NOT NULL DEFAULT 0,
  duration_sec         INT NULL,

  -- 负载
  load_index           DECIMAL(8,4) NULL COMMENT 'median(I/v)，v clip≥15',
  load_tier            VARCHAR(16) NULL COMMENT 'light|medium|heavy|unknown',
  load_model_version   VARCHAR(16) NULL,

  -- 翼型（在线=最近质心，不是当场 K-Means）
  wing_cluster_id      TINYINT NULL,
  wing_label           VARCHAR(32) NULL COMMENT 'low_drag|cruise|high_lift|large|unknown',
  wing_confidence      DECIMAL(5,4) NULL,
  wing_model_version   VARCHAR(16) NULL,
  eff_vector           JSON NULL COMMENT '[I_p50 × 5 速度段]',

  -- 电流 / 功率摘要（可直接给电池规格、后台列表）
  current_p50          DECIMAL(6,1) NULL,
  current_p95          DECIMAL(6,1) NULL,
  current_p99          DECIMAL(6,1) NULL,
  current_mean         DECIMAL(6,1) NULL,
  power_p95_kw         DECIMAL(6,2) NULL,

  -- 电池健康（会话级；设备级另表滚动）
  r_int_mohm           DECIMAL(8,2) NULL,
  soc_band             VARCHAR(16) NULL COMMENT 'e.g. 40-80',

  -- 运行评价
  run_score            SMALLINT NULL COMMENT '0-100',
  run_grade            VARCHAR(8) NULL COMMENT 'A|B|C|D|F',
  run_tags             JSON NULL COMMENT '["cruise_stable","harsh_accel",…]',
  run_breakdown        JSON NULL COMMENT '各分项得分',

  -- 扩展与幂等
  ext_json             JSON NULL,
  content_fingerprint  CHAR(64) NULL,
  usage_analyzer_version VARCHAR(16) NOT NULL,
  model_version        VARCHAR(16) NOT NULL,
  analyze_status       VARCHAR(16) NOT NULL DEFAULT 'done',
  error_message        VARCHAR(512) NULL,
  source_add_time      DATETIME NULL COMMENT 'history.add_time 快照，对账用',
  source_deleted       TINYINT(1) NOT NULL DEFAULT 0,
  analyzed_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY uk_object_key (object_key),
  KEY idx_date (session_date),
  KEY idx_wing (wing_label, load_tier),
  KEY idx_sn (electrical_sn),
  KEY idx_user (play_user_id),
  KEY idx_status (analyze_status, analyzed_at)
);

4.3 配套表

表	作用
`jua_field_registry`	字段目录：正式列 / `ext_json` 路径、类型、首次出现版本、是否已晋升
`jua_model_version`	翼型质心、负载分位阈值、评分权重；`status=active` 仅一条
`jua_device_health`	按 `electrical_sn` 滚动的 R_int、告警（跨航次）
`jua_sync_job`	增量水位、对账批次、差异计数
`jua_sync_diff`	对账明细：missing / extra / field_mismatch / deleted_drift
`jua_json_key_stat`	OSS JSON 新出现的根字段 / 点字段计数（驱动「自动扩展」）

点级不建 MySQL 宽表。需要回放算法时：读 OSS 缓存，或向 traj-store 按 trajectory_id 拉点。

5. 流水线

5.1 增量主路径（每 15 分钟，可与 TLA 错开 2～5 分钟）

1. 读 jua_sync_job 水位 watermark_add_time
2. SELECT history 行：
     deleted=0 AND object_key 非空
     AND TLA.analyze_status='done'          -- 故障层已完成，可复用 SN/时长
     AND (
          usage 无行
       OR usage.analyze_status='failed'
       OR usage.usage_analyzer_version < 当前
       OR usage.model_version < 当前 active 模型
       OR usage.content_fingerprint <> 文件哈希
     )
   ORDER BY add_time ASC LIMIT BATCH
3. 下载 OSS JSON（命中 TLA 本地缓存则跳过下载）
4. 点级过滤 → 特征 → 打标 → UPSERT usage_profile
5. 推进水位；失败行 status=failed，不堵队列

TLA 尚未跑完的新日志：等下一轮，不要在 JUA 里再实现一套故障引擎。

5.2 点级过滤（与已验证离线规则对齐，阈值进配置）

Log 级建议入门（可标 quality_pass=0 仍落库）：

max_hour_speed ≥ 25（未起飞）
航时 20～120 min（过短练习 / 异常超长）
TLA has_fault=0 可作为「工况样本」开关；评价分仍可对故障航次打低分

点级默认：

hourSpeed ≥ 15
electricCurrent ≥ 5
batteryVoltage ≥ 38
可选 batteryElectricity ≥ 10

这些阈值放配置中心 / .env，变更记入 usage_analyzer_version。

5.3 历史回填

先让 TLA 把目标日期范围内的 analyze_status=done 铺满。
JUA --backfill --date-from --date-to --limit 分批 UPSERT。
与离线脚本同一套特征函数，回填后抽 100 条对比 load_index / current_p95，允许误差在 1% 内。

6. 翼型、负载、运行评价（在线算法）

离线 analyze_advanced.py 是全量拟合；线上必须改成 标定 + 推断，否则每条新日志都会改写历史簇标签。

6.1 翼型

离线（周更或样本 +N% 触发）：

取 quality_pass=1 且 5 个速度段都有中位电流的航次。
5 维向量：15-20 / 20-25 / 25-30 / 30-35 / >35 的电流 P50。
StandardScaler + K-Means(K=4)，质心按「高速斜率 / 巡航电流」排序后映射到固定业务名：
low_drag 低阻翼（轻快型）
cruise 标准翼（巡航型）
high_lift 高升力翼（抓浪型）
large 大型翼（初学型）
写入 jua_model_version.payload（scaler + centroids + 标签顺序），status=active。

在线（每条日志）：

算本航次 5 维向量；缺段则 wing_label=unknown，wing_confidence=0。
标准化后求到 4 个质心的欧氏距离，取最近簇。
confidence = 1 - d_min / (d_min + d_second)。低于 0.25 标 unknown。
只写本行，不回刷历史行（除非主动升 model_version 并开重算任务）。

这是效率曲线代理翼型，不是 BOM 里的零件号。真值翼型号若以后进设备档案，用 board_id 外挂对照表，和本字段并存。

6.2 负载

点：load_index = I / max(v, 15)
航次：load_index = median(点)
分档用冻结分位（当前离线结果约 light < 1.76 ≤ medium ≤ 2.05 < heavy），放进模型版本。
人群结构变了再重标定，新旧阈值用 load_model_version 区分，避免今天的「中载」被明天的全量重跑改掉。

6.3 运行评价（建议首版规则分，不做黑盒）

百分制，分项可调权重（写入模型版本）：

分项	权重（初值）	规则要点
有效性	15	时长、有效点数、是否起飞
巡航稳定	25	20–30 km/h 占比、巡航段电流 CV
动力余量	15	P95 电流相对该负载档阈值是否过高
热管理	15	电池 / 电机 / 电调峰值（复用 TLA 温度列）
操控平滑	15	油门/电流一阶差分的 P95（急加速惩罚）
健康与安全	15	TLA `has_fault` / stall_ratio / 失控类标志

输出：run_score、run_grade（A≥85, B≥70, C≥55, D≥40, 其余 F）、run_tags（如 cruise_dominant、heavy_load、harsh_accel、thermal_hot）。

首版用规则，便于和业务对口径；模型版评分以后只能作为 ext_json.ml_score，正式列仍以规则分为准，直到标定稳定。

6.4 电池健康（会话 → 设备）

会话：在 SOC 40–80%（可配）下算 R_int ≈ ΔV/ΔI（低电流段 <20 A vs 高电流段 >60 A）。
设备：jua_device_health 按 electrical_sn 维护中位 R_int、最近 10 航次趋势。
告警：超过该 SN 基线 150%（现网经验阈值约 35 mΩ）写入标签，不直接改业务库。

electrical_sn 空值：对账任务从 JSON 头 / 点字段回填，并与 TLA 列交叉验证。

7. 字段自动扩展

目标：新固件在 JSON 里多一个字段时，不用等改 Java 业务表也能先被分析侧看见、存住、统计；稳定后再变成可筛选列。

OSS JSON 出现新 key
    → ingest 时计入 jua_json_key_stat（key、出现次数、样例、首次 trajectory_id）
    → 未登记 key 写入 ext_json.<path>
    → 登记表 status=observed
    → 人工/规则：出现率 > 阈值 且 类型稳定 → status=promoted
    → 发 migration：ALTER 加列 + 回填 + 报表可用

jua_field_registry 示意：

列	含义
`field_key`	`json.bmsCellDelta` / `point.motorTorque` / `metric.cruise_cv`
`storage`	`column` / `ext_json`
`column_name`	晋升后的 MySQL 列名
`value_type`	int / float / string / json
`first_seen_at` / `first_trajectory_id`	溯源
`status`	observed / ignored / promoted
`analyzer_version`	开始采集的版本

约束：

禁止运行时对业务库 history 做 ALTER。
分析库 ALTER 只走 migration 文件，和 usage_analyzer_version 绑定。
ext_json 有大小上限（建议 16 KB），超限截断并打 ext_truncated。

8. 与线上库同步、核对

分析库是旁路，线上仍是唯一业务真相。同步分三层。

8.1 增量同步（近实时）

以 history.add_time 为水位，只处理新行和 TLA 已完成行（见 §5.1）。
快照 source_add_time、source_deleted、object_key，便于对账。

8.2 变更同步（每天）

拉取 history 中 add_time 或业务 update 窗口内变化的 id（若线上无 updated_at，则用 deleted 翻转 + object_key 变更检测）：

线上变化	分析侧动作
`deleted=1`	`source_deleted=1`，列表默认过滤；不物理删（审计）
`object_key` 变更	重新下载、重算、换指纹
`play_user_id` / `board_id` / `remote_id`	只更新画像上的维度列，不重跑点级
TLA 重算（analyzer_version↑）	若依赖故障/温度，则 JUA 按配置决定是否重打评价

8.3 对账任务（每天一次，出 diff 表）

-- 例：线上有、分析无（漏分析）
SELECT h.id
FROM surf_motion_trajectory_history h
LEFT JOIN surf_motion_usage_profile u ON u.trajectory_id = h.id
WHERE h.deleted = 0 AND h.object_key <> ''
  AND h.add_time < :watermark_lag          -- 避开正在跑的批次
  AND u.trajectory_id IS NULL;

核对项：

覆盖率：应分析集合 vs usage 行数。
键一致：object_key、play_user_id、board_id。
数值交叉：history.max_hour_speed vs 点级 max(speed)（允许标定/截断差异，超差进 diff）。
删除漂移：线上 deleted=1 但画像仍当有效样本。
指纹：OSS 文件变更但未重算。

差异写入 jua_sync_diff，严重类（漏分析、key 不一致）告警；对账任务可 --repair 自动补跑漏网 id。

8.4 只读原则

JUA 从不 UPDATE surf_motion_trajectory_history。发现线上字段空（如 SN），只写分析表，并在 diff 里建议业务补数。

9. 服务形态与接口

独立仓库 jetwave-usage-analytics，与 TLA 同样：Python + Docker + cron。不把算法塞进 Java 业务服务。

jetwave-usage-analytics/
├── jua/
│   ├── worker.py              # 增量入口
│   ├── backfill.py
│   ├── reconcile.py           # 对账 / --repair
│   ├── oss_client.py          # 可薄封装复用 TLA 缓存目录
│   ├── features/              # 点过滤、效率向量、load_index、R_int
│   ├── models/                # 载入 active 质心 / 阈值 / 评分权重
│   ├── eval/                  # 运行评价
│   ├── schema/                # ext_json + json_key_stat
│   └── db/
├── sql/                       # 建表 + migrations
├── docker-compose.yml
└── README.md

建议 API（给后台，鉴权沿用内网或 API Key）：

方法	路径	用途
GET	`/api/v1/usage/{trajectory_id}`	按线上 id 取翼型/负载/评分
GET	`/api/v1/usage`	筛选：日期、wing_label、load_tier、grade、electrical_sn
GET	`/api/v1/devices/{electrical_sn}/health`	设备滚动内阻
GET	`/api/v1/models/active`	当前翼型/负载模型版本
GET	`/api/v1/sync/diffs`	对账差异
POST	`/api/v1/jobs/reanalyze`	按 id 列表或版本强制重算

后台列表：history LEFT JOIN usage_profile ON id = trajectory_id 即可，无需同步写业务表。

已有 HTML 看板（current-distribution-analyzer/output/surf-current-dashboard.html）后续改为读 API 聚合，而不是读死数据。

10. 你需要提供的基础环境

项	最低要求	说明
计算	2C4G，与 TLA 同机可	增量每批 50～200 条；周更 K-Means 单独跑
MySQL 8	业务库只读账号 + 分析库读写账号	分析表建议独立库 `surf_analytics`，跨库 JOIN 或 ETL 同步 id 维表
OSS	与 TLA 同一套读权限、同一 `OSS_BASE_URL`	缓存目录可共享，避免重复下载
磁盘	≥ 100 GB（OSS JSON 缓存）	点级 Parquet 另算，可放对象存储
调度	cron 或现有任务平台	增量 15 min；对账每天；模型周更
网络	访问 MySQL + OSS	不需要公网；API 仅内网

环境变量与 TLA 对齐：MYSQL_*、OSS_*、BATCH_SIZE、DATA_DIR。另加 JUA_MODEL_VERSION、RECONCILE_LAG_MINUTES。

11. 分阶段实施

P0 — 可按 id 查询的会话画像（2 周量级）

建 surf_motion_usage_profile + jua_sync_job
增量 worker：过滤 → load_index / 电流分位数 / quality_pass
负载分档用冻结阈值 1.76 / 2.05
GET /usage/{id}
历史回填 quality 通过的航次

验收： 任意线上 history.id，接口返回同一 id 的负载档和 P95 电流；与离线脚本抽检误差 < 1%。

P1 — 翼型在线打标 + 运行评价（2 周）

用现有离线质心固化 jua_model_version
在线最近邻 + confidence
规则评分 run_score / run_grade / run_tags
周更重训脚本（默认不回刷全量）

验收： 同一模型版本下重复跑同一 id，翼型标签不变；confidence 低的标 unknown。

P2 — 对账与字段演进（1～2 周）

每日覆盖率 / deleted / object_key 对账 + --repair
ext_json + jua_json_key_stat + 字段登记
electrical_sn 空值回填与 TLA 交叉

验收： 人为把一条 deleted=1，对账出现 deleted_drift；JSON 新 key 出现在登记表且进 ext_json。

P3 — 设备健康与看板产品化

jua_device_health 与 R_int 告警
看板改 API；可选把摘要字段只读镜像给 traj-store

12. 与现有项目的职责表

系统	继续做什么	不要做什么
业务 MySQL `history`	航次真相、OSS 指针、用户/板/遥控 id	不写分析列
TLA	故障、版本、温度、时长、SN 解析	不算翼型/负载（避免双份逻辑）
traj-store	点、地图、空间查询	不实现工况评分
离线 current-distribution-analyzer	P0 对照基线、模型标定语料	不再作为线上数据源
JUA（本服务）	翼型、负载、评价、对账、字段扩展	不改 history、不重写 fault_engine

TLA 的 max_current、温度、electrical_sn 本服务只读引用，评价分项直接用，避免点扫描两遍时可先读 TLA 再补特征。

13. 风险

风险	应对
翼型只是电流曲线簇，不是零件翼型号	字段命名 `wing_label`，文档写明「效率代理」；有 BOM 后再加 `wing_part_no`
全量 K-Means 会改历史标签	在线只推断；回刷必须升 `model_version` 且可关
OSS 下载成为瓶颈	共享 TLA `DATA_DIR`；失败进 failed 不堵水位
分析库与业务库时钟/延迟	对账加 `RECONCILE_LAG`；不以「刚插入 1 分钟」当漏分析
`electrical_sn` 大量为空	P2 回填；设备健康在 SN 覆盖率达标前只出会话级 R_int
点级进 MySQL	禁止；需要序列时读缓存或 traj-store

14. 验收总清单

[ ] usage.trajectory_id 与 history.id 对任意抽检航次一致
[ ] 新写入的 history 行在 TLA done 后 30 分钟内出现 usage 行（或明确 failed）
[ ] 同一 JSON + 同一 model_version 重跑，翼型/负载/评分不变
[ ] deleted=1 的航次不对运营样本统计造成污染
[ ] 新 JSON 字段进入 ext_json 与登记表，不丢、不炸主流程
[ ] 后台可用 history.id 打开翼型、负载档、运行评价
[ ] 文档中的负载阈值、点级过滤与离线已验证规则一致（变更走版本号）

15. 推荐的第一句实施命令（环境就绪后）

1. 分析库执行 sql/001_usage_profile.sql
2. 配置 .env：只读业务 MySQL + 读写分析 MySQL + 与 TLA 相同 OSS / DATA_DIR
3. 导入离线质心为 jua_model_version = 2026.08.1（active）
4. docker compose run --rm jua-worker --limit 50
5. 用已知 id（例如 TLA 已分析成功的航次）调 GET /api/v1/usage/{id}
6. 对账跑通后再开 cron：worker */15；reconcile 每天

本方案把「能分析」变成「按线上 id 持续分析」。P0 先保证每条日志有负载和电流画像；翼型与评分在模型版本钉死后上线，避免重蹈全量脚本改历史标签的问题。