📚 课程目录第4章面板数据模型设计

4. 面板数据模型设计

逐笔数据质量监控面板能否做到“秒级定位异常、分钟级回溯趋势”，很大程度上取决于底层数据模型是否设计得当。逐笔成交与逐笔委托是典型的高频、海量、事件型数据，单日全市场原始报文可达数十亿条。如果面板每次刷新都直接扫描原始流水，既无法满足实时性，也会拖垮存储。因此本节的核心任务是：围绕“质量指标”这一分析目标，设计一张逐笔数据质量事实表、若干维度表，明确指标计算粒度，并把预聚合与实时计算拆分到不同存储层。

一、事实表：以“质量检查结果”为一行

事实表不应存放原始逐笔记录，而应存放一次质量检查的度量结果。最细粒度的一行可以定义为：某个时间窗口内、某个市场、某只标的、某一类检查项所产生的一组统计量。字段设计建议如下（示例）：

fact_tick_quality
  stat_time      DATETIME     -- 统计窗口起点，如 2024-06-03 09:30:00
  window_sec     INT          -- 窗口长度，如 1 / 60 / 300
  market_id      SMALLINT     -- 市场：1=沪市 2=深市
  security_id    VARCHAR(16)  -- 标的代码
  check_id       SMALLINT     -- 检查项：1=时间戳乱序 2=价格越界 3=成交量异常 ...
  total_cnt      BIGINT       -- 窗口内逐笔记录总数
  fail_cnt       BIGINT       -- 命中异常规则的记录数
  max_gap_ms     INT          -- 相邻记录最大时间间隔（毫秒）
  p99_latency_ms INT          -- 时延99分位
  quality_score  DECIMAL(5,2) -- 综合质量分
  etl_batch      VARCHAR(32)  -- 批次标识，便于回溯重算

这里的关键取舍是：把“可加性度量”和“不可加性度量”分开。total_cnt、fail_cnt 是可加的，任意窗口可向上汇总；max_gap_ms、p99_latency_ms 不可加，只能在同一粒度内计算，跨窗口汇总时必须重新取分位数或最大值，不能简单相加。这一点直接决定了后续预聚合层的设计方式。

二、维度表：让指标可切片

维度表负责给事实表提供分析视角，至少需要三张：

dim_market：市场编码、交易所名称、交易时段（连续竞价/集合竞价/盘后）、时区偏移。
dim_security：标的代码、名称、板块（主板/创业板/科创板）、上市状态、所属行业。用代理键 security_key 关联事实表，避免代码变更导致历史数据错位。
dim_check_rule：检查项编码、规则名称、阈值参数、严重等级（提示/警告/严重）、责任团队。把阈值放进维度表，规则调整时无需改事实表。

此外建议增加一张dim_time（交易日期、时段、分钟序号），用于按时间段聚合。维度表采用星型结构围绕事实表，避免雪花化带来的多跳关联，保证面板查询的响应速度。

三、计算粒度：三个正交切面

指标粒度由三个切面共同决定：按市场（沪/深/北）、按标的（个股/指数成分/全市场汇总）、按时间段（1秒、1分钟、5分钟、日）。三者组合形成事实表的主键。实践中不建议一开始就做“标的×秒”的最细粒度落库，因为单日数据量会膨胀到亿级；更合理的做法是：实时层保留“市场×检查项×10秒”的轻粒度用于告警，预聚合层保留“标的×检查项×1分钟”用于下钻，日汇总层保留“标的×检查项×日”用于趋势。

粒度设计口诀：实时层求“快而粗”，聚合层求“准而中”，汇总层求“全而稳”。三层粒度不必一致，但必须能通过时间窗口对齐相互印证。

四、分层存储：实时计算与预聚合各司其职

推荐采用三层存储架构。下图展示了从逐笔原始流到面板查询的完整数据路径与各层职责：

三层之间通过时间窗口对齐保证一致性：10 秒窗口的 fail_cnt 累加得到 1 分钟值，1 分钟值累加得到日值；而 p99_latency_ms 在每层都基于该层窗口内的原始样本重新计算，绝不跨层相加。这样面板上从“全市场日趋势”下钻到“某只股票某分钟”时，数字能对得上。

五、一个可落地的建表与写入示例

以预聚合层为例，用分区表承载每日数据，按市场与日期分区，写入时以 (stat_time, market_id, security_id, check_id) 为主键做幂等覆盖，避免重跑批次产生重复行：

CREATE TABLE dws_tick_quality_1min (
  stat_time   DATETIME      NOT NULL,
  market_id   SMALLINT      NOT NULL,
  security_id VARCHAR(16)   NOT NULL,
  check_id    SMALLINT      NOT NULL,
  total_cnt   BIGINT,
  fail_cnt    BIGINT,
  fail_ratio  DECIMAL(8,6),
  p99_latency_ms INT,
  quality_score  DECIMAL(5,2),
  PRIMARY KEY (stat_time, market_id, security_id, check_id)
) PARTITION BY RANGE (stat_time);

-- 幂等写入：同主键覆盖
INSERT INTO dws_tick_quality_1min VALUES (...)
ON DUPLICATE KEY UPDATE
  total_cnt=VALUES(total_cnt), fail_cnt=VALUES(fail_cnt),
  fail_ratio=VALUES(fail_ratio), p99_latency_ms=VALUES(p99_latency_ms),
  quality_score=VALUES(quality_score);

质量分 quality_score 建议用加权公式，例如：100 - 40×fail_ratio - 30×min(max_gap_ms/1000,1) - 30×min(p99_latency_ms/500,1)，各权重可按业务容忍度调整。示例中若某标的某分钟 fail_ratio=0.02、max_gap_ms=800、p99_latency_ms=120，则得分约为 100−0.8−24−7.2≈68，落入“警告”区间，面板即触发黄色标记。这些数值仅为演示计算方式，实际阈值需结合交易所规则与历史基线标定。

常见误区：把原始逐笔明细直接当作事实表，再在查询时现算指标。这样既无法支撑秒级面板，也让“按标的×分钟”的下钻变成全表扫描。事实表存的是检查结果，不是原始事件。

最后，维度表与事实表的关联键要稳定：security_id 用交易所原始代码，security_key 用代理键；check_id 与 dim_check_rule 一一对应，规则阈值变更时新增版本行而非覆盖旧行，保证历史指标可复现。至此，事实表定义“度量什么”，维度表定义“从哪些角度看”，粒度定义“多细”，分层存储定义“在哪算、算多快”，四者共同构成面板可靠运行的数据底座。