7.2 数据建模与表设计

7.2 数据建模与表设计

本节摘要：7.1 的布局手术是事后补救，本节把它前移到建表时刻：类型、物理顺序、粒度、物化策略四个决定，在写入的那几分钟里预付未来每次查询的成本。嵌入式分析场景的建模原则与教科书上的第三范式各有取舍，本节按"查询怎么查"反推"表怎么建"。

建模的视角差：事务范式与分析反范式

学关系数据库时先学范式分解：消除冗余、一张表一件事。这套原则为高频小事务而生——每一行写入都要便宜、一致。分析场景的读写比恰好倒过来：写入一天一次、批量进行，查询每小时成百次、每次都扫描。于是建模的经济学变了：写入侧的冗余是查询侧的红利。把维表属性冗余进事实表（宽表化），查询少一次连接；把常用聚合预先算好存起来，报表查询从扫千万行变成读几十行。这不是推翻范式——业务库照旧用范式建模，分析库按查询模式反范式冗余，两套体系用第5章的接口同步。

决定一：类型，最小且够用

类型决定存储体积、比较速度与嗅探风险三件事。原则是最小且够用：金额用 DECIMAL 而不是 DOUBLE（浮点误差在聚合里会累积成对不上账）；状态、渠道这类低基数文本用枚举或保持短 VARCHAR；时间戳带时区语义时用 TIMESTAMPTZ，纯本地时间用 TIMESTAMP。宽表有三百列时，每列省八个字节就是每行省两 KB——行组读进内存的体积、剪枝后传输的体积、聚合的哈希表体积，全部按比例缩。

最容易踩的是"全列 VARCHAR 一把梭"：从 CSV 摸底直接建表，嗅探读成什么就是什么。回头看主线案例的 trades_clean 建表语句，类型是逐列显式指定的——金额 DECIMAL、状态收成小写短文本、时间戳定死。那次显式化花了五分钟，换来了后续所有章节的数字口径统一。

决定二：物理顺序，把最常用的过滤列排前面

这是第3.1节行组结构与第4.4节 Zone Maps 的直接应用：每个行组记录各列的最小最大值，过滤条件落在行组区间外就整组跳过。跳过的效率取决于区间收得多窄，而区间宽度取决于物理顺序。建表时用 ORDER BY 按最常用的过滤列排序，是嵌入式分析里性价比最高的一步：

-- 主线案例同款建表姿势：按时间排序落表
CREATE TABLE trades_clean AS
SELECT ... FROM read_csv_auto('trades_2024q3.csv')
ORDER BY trade_time;

-- 多过滤场景：区分度高的列排前面
CREATE TABLE events AS
SELECT ... FROM raw
ORDER BY device_id, event_time;
​

代价也要说清：ORDER BY 让建表多一次排序，千万行级是分钟级的一次性成本；后续追加数据若不打乱顺序，按时间区间追加即可——这就是时序数据"按月分文件、文件内有序"模式的由来。反向的坑在第4.5节见过：乱序导入的表，时间过滤的剪枝完全失效，事后补救要整表重建。

决定三：粒度，明细与聚合之间

一份数据存多细，是建模里最主观的决定。存最细的明细，任何问题都能重算，但每次查询都扫大表；只存聚合，查询飞快，但口径一改就要回原始数据重跑。嵌入式的常见解法是双层并存：明细按月分区落 Parquet（重算与审计用），常用粒度的聚合物化成小表（日常查询与报表用），后者由前者派生、可随时重建。第4.5节收官那步物化的周度退款表，就是这个模式的实例。

判断该不该新增一层聚合，问两个问题：这组数字被反复查询吗？口径稳定吗？两个都答是，值得物化；口径还在摇摆，先留着视图——视图存定义不存数据，每次实时算，是聚合层的"草稿态"。草稿态的价值常被低估：口径讨论期间，业务方今天要"下单口径"明天要"支付口径"，视图切换一个定义就完成改口；等口径落定再物化，返工成本几乎为零。粒度决定从来不是一次性的，它随业务问题的稳定程度而流动——建模做的是给每一段稳定度配上合适的形态。

决定四：物化与刷新策略

物化引出最后一个决定：派生表怎么保持新鲜。三个档位。随用随刷：分析会话开头一句 CREATE OR REPLACE TABLE AS，简单直接，适合个人工作流；定时刷新：调度器按小时或按天跑刷新脚本，报表读物化表，适合团队共享；触发式追加：新数据到达只算增量区间，合并进物化表，适合数据量大、时效要求高的流水线。档位选择取决于时效要求与数据增速，没有越级推荐的必要——个人分析从第一档起步，够用很久。

四个决定合起来的检查单

落地形态：文件怎么组织

四个决定之外还有个工程问题：数据落成什么、放哪里。嵌入式场景的主流答案是按时间分区的 Parquet 文件集，配合引擎的直接查询能力（第3.4节）当文件集用。每月一个目录、目录内按批切文件，单文件控制在数百 MB 量级——太小则文件数爆炸、元数据开销抬头，太大则单文件读写的并行度上不去。查询时路径里带通配符，引擎把整个文件集当一张表查。

更进一步是分区裁剪：目录名编码分区键（比如按年、月分层），查询条件带上年月时，引擎直接跳过整个目录的文件，连打开都省。这与行组剪枝是两级配合——目录级先粗筛，行组级再细筛，第4.5节那类时间过滤查询在两级齐备时几乎只碰目标区间的数据。主线案例季度切换时"建新库、旧库归档"的做法，就是这个模式的个人版：库文件按季切，库内表按月物化，层层都有各自的裁剪粒度。

宽表也有它的反方向提醒：列不是免费的。三百列的宽表里，高频查询往往只碰八列；列存天然支持列裁剪，所以代价主要是写入与维护——列越多，建表显式类型的清单越长，上游加列时的同步面越宽。实践中"高频二十列进宽表、长尾列留在明细 Parquet"的分层比一口气宽到底更耐用。判断列该进哪一层的标尺是"查询命中率"：连续两周被查到的列升入宽表，一个月无人问津的列退回明细——分层不是一次建模定终身，是随负载演化的 living 结构。

从建模决定回到主线案例

把四个决定对着主线案例过一遍，看它们各自值了什么。类型显式化（决定一）换来的是口径统一——金额用 DECIMAL 后，对账再没漂移过。物理顺序（决定二）换来的是第4.5节的第三刀——按时间重排后扫描端只剩零头。粒度（决定三）换来的是报表与审计各得其所——周度物化表供报表，月度 Parquet 供重算。物化策略（决定四）换来的是八十毫秒的报表查询与一句刷新脚本的分工。四个决定没有一个用到复杂语法，全是建表语句里的几行关键字——建模的杠杆从来不在写法的炫技里，在写入时刻的决定里。

本节要点回顾

• 读写比决定建模经济学：写入冗余换查询红利，业务库范式、分析库宽表。
• 类型最小且够用：金额 DECIMAL、低基数收窄、时区语义选对类型。
• 物理顺序是最大的隐藏开关：ORDER BY 常用过滤列，剪枝效率由区间宽度决定。
• 双层粒度：明细落 Parquet 供重算，聚合物化成小表供日常。
• 刷新三档位：随用随刷起步，按需升级定时或增量。