BI 数据服务

本章目标：能设计一张面向看板的 ADS 宽表，用预聚合和缓存把查询压到秒级，并说清指标字典、权限和导出边界的做法。 前置知识：数仓分层、OLAP 查询调优 预计用时：100 分钟

看板卡顿通常不是前端的问题：一个页面背后可能是十几条聚合查询，每条都扫几千万行。BI 数据服务要做的，是把“查询时算”改成“提前算好、按需取用”，同时保证每个看板的数字口径一致。

1. 为什么需要 BI 数据服务

把明细表直接连给 BI 工具，会遇到四个问题：

BI 数据服务的核心是三层隔离：明细计算在数仓，结果存到服务层，看板只读服务层。

DWD 明细 ──► DWS 汇总 ──► ADS 宽表/指标表 ──► 查询网关 ──► BI 看板
                                  │
                                  └── 缓存（分钟级）

2. 核心概念

2.1 服务层表怎么设计

看板上的“按城市看 GMV”读汇总表，“点开某个城市看订单列表”读明细宽表。两类表都要有，不能只建一种。

2.2 指标字典

每个指标至少记录七项：

指标编码：gmv_paid
指标名称：支付 GMV
业务定义：统计周期内支付成功订单的金额之和
计算公式：SUM(amount) WHERE status = 'paid'
统计粒度：订单
数据来源：dwd_order_detail
负责人：交易数据组
版本：v1，2024-03-01 生效

没有字典，就会出现“同名不同义”。指标变更要升版本，并在看板上标注生效时间，不能悄悄改公式。

2.3 预聚合的三种形态

• 汇总表：按常用维度提前聚合，例如城市 + 日期。查询最快，但维度组合固定。
• 物化视图：由引擎自动维护，查询命中时直接读结果。适合稳定的高频查询。
• 缓存：把查询结果按条件存起来，设置 TTL。实现简单，但要注意失效和一致性。

预聚合的代价是灵活性下降。业务要看一个没预计算过的维度组合，仍然要回明细表算。所以通常保留明细宽表作为兜底。

2.4 查询网关

BI 工具不直接连数据库，而是经过查询网关：

• 统一鉴权，识别用户和角色。
• 按查询类型路由：汇总查 OLAP，明细查数仓，导出走异步任务。
• 限制扫描量、并发数和返回行数。
• 记录查询日志，便于排查慢查询和审计。

2.5 权限模型

• 表级：能不能访问这张表。
• 行级：只能看自己负责的区域，例如 city = '北京'。
• 列级：能看订单金额，但不能看手机号。
• 脱敏：必须展示时做掩码，例如 138****0000。

权限要在服务端强制执行，不能只靠前端隐藏按钮。否则用户直接调接口就能绕过。

3. 动手示例

用 data/sample/orders_demo.csv 建服务层表，观察宽表、汇总表和 ROLLUP 的差别。

3.1 建明细宽表

CREATE OR REPLACE VIEW orders AS
SELECT * FROM read_csv_auto('data/sample/orders_demo.csv', header = true);

CREATE OR REPLACE TABLE ads_order_wide AS
SELECT
    order_id,
    CAST(order_date AS DATE) AS dt,
    city,
    status,
    CAST(amount AS DECIMAL(12, 2)) AS amount
FROM orders;

SELECT COUNT(*) AS rows FROM ads_order_wide;

宽表一行一个订单，保留了城市、状态、日期三个常用筛选维度。真实宽表还会冗余商品品类、支付方式等字段，但不要把所有描述字段都塞进来。

3.2 建日汇总表

CREATE OR REPLACE TABLE ads_city_day_gmv AS
SELECT
    dt,
    city,
    COUNT(*) FILTER (WHERE status = 'paid') AS paid_cnt,
    SUM(amount) FILTER (WHERE status = 'paid') AS gmv
FROM ads_order_wide
GROUP BY dt, city;

SELECT * FROM ads_city_day_gmv ORDER BY dt, city;

看板上的趋势图直接查这张表，扫描行数从订单级降到“日期 × 城市”级。新增维度时，要么扩这张表的粒度，要么另建汇总表。

3.3 用 ROLLUP 一次算出小计和总计

SELECT
    CASE WHEN GROUPING(city) = 1 THEN '全部城市' ELSE city END AS city,
    CASE WHEN GROUPING(status) = 1 THEN '全部状态' ELSE status END AS status,
    SUM(amount) AS gmv,
    COUNT(*) AS order_cnt
FROM ads_order_wide
WHERE status <> 'canceled'
GROUP BY ROLLUP (city, status)
ORDER BY city, status;

结果里会出现城市小计和全站总计。GROUPING() 用来区分“这个 NULL 是数据本身为空”还是“这是汇总行”。不要把汇总行再和明细行相加，否则金额翻倍。

3.4 对比查询扫描量

EXPLAIN ANALYZE
SELECT city, SUM(amount) AS gmv
FROM ads_order_wide
WHERE dt BETWEEN DATE '2024-01-01' AND DATE '2024-01-31'
  AND status = 'paid'
GROUP BY city;

再看汇总表版本：

EXPLAIN ANALYZE
SELECT city, SUM(gmv) AS gmv
FROM ads_city_day_gmv
WHERE dt BETWEEN DATE '2024-01-01' AND DATE '2024-01-31'
GROUP BY city;

对比执行计划里的扫描行数和耗时。数据量越大，差距越明显。注意汇总表的 gmv 已经过滤过 paid，查询时不能再加 status 条件，除非表里保留了状态维度。

3.5 缓存键与失效

key = bi:city_gmv:{start}:{end}:{city|all}:{metric_version}
ttl = 300s

某个日期分区更新后，删除包含该日期的缓存键，而不是清空整个缓存。缓存值里带上 metric_version，公式变更时自然读到新结果。

3.6 查询网关配置示例

routes:
  - match: "ads_city_day_gmv"
    engine: olap
    cache_ttl: 300s
    max_rows: 10000
  - match: "ads_order_wide"
    engine: olap
    cache_ttl: 60s
    max_rows: 50000
  - match: "export_*"
    engine: async
    max_rows: 1000000

row_policies:
  - role: analyst_beijing
    table: ads_order_wide
    filter: "city = '北京'"
    deny_columns: ["user_phone"]

导出行数上限不是拍脑袋定的，要结合数据库承受能力和实际业务需求。超限的导出走异步任务，生成文件后再通知下载。

4. 常见坑

4.1 指标口径不一致

同一张看板上，GMV 一个含运费、一个不含，数字对不上。所有指标从字典取，不允许各看板自己写公式。口径变更要通知下游并升版本。

4.2 平均值再平均

客单价应该用 总金额 / 总订单数，不能把每天的客单价再求平均。比率型指标要先算分子分母，再在目标粒度相除。

4.3 去重指标跨维度相加

北京的 UV 加上上海的 UV，不等于全国 UV，因为同一个人可能两地都有访问。去重指标要回到明细或位图重新计算。

4.4 缓存没有版本

公式改了但缓存还是旧结果，用户以为数据错了。缓存键带指标版本，发布时主动失效相关键。

4.5 行级权限只做在前端

前端隐藏了城市选项，但接口仍返回全部数据。权限必须在查询网关或数据库层执行，前端只负责展示。

4.6 大导出打爆数据库

用户点一次“导出全部”，扫几亿行，其他看板全部超时。导出走异步、限行数、限并发，并优先读副本或对象存储。

4.7 时区不一致

数据库按 UTC 存，看板按北京时间展示，凌晨的数据会落到前一天。统一在服务层做时区转换，并在指标字典里写明时区。

5. 在线练习

自测：

• 能写出一个按日期和城市汇总的 ADS 表。
• 能解释为什么去重指标不能跨维度直接相加。

6. 面试延伸

面试题：看板查询慢，怎么优化？

是什么：先看查询扫描量和执行计划，再建汇总表或物化视图，加缓存和查询网关限流，最后才考虑扩资源。

解决什么问题：看板通常重复查相同维度组合，预聚合和缓存能避免每次扫明细。

我的实操边界：本章用 DuckDB 对比了宽表和汇总表的执行计划，未在生产 BI 平台做压测。

面试题：怎么保证多个看板口径一致？

是什么：建指标字典，统一指标编码、公式、粒度和版本；看板只引用指标，不各自写 SQL。

解决什么问题：同名指标不同算法会让业务失去信任，排查成本极高。

我的实操边界：本章设计了字典字段，未参与企业级指标平台建设。

面试题：预聚合表和明细宽表怎么配合？

是什么：高频固定维度走预聚合，未预计算的组合和下钻走明细宽表；两者由同一份 DWD 加工。

解决什么问题：只建汇总表不灵活，只建明细表太慢，分层组合兼顾性能和灵活度。

我的实操边界：本章两种表都建过，未做过自动化的汇总表推荐。

面试题：行级权限怎么实现？

是什么：在查询网关或数据库层注入行过滤条件，结合角色和用户属性；列级权限做字段屏蔽或脱敏。

解决什么问题：防止越权查看和导出，前端隐藏不能作为安全边界。

我的实操边界：本章给出配置形态，未接入真实权限系统。

7. 本章验收

• 能画出从 DWD 到 ADS 再到 BI 看板的链路，并标出缓存位置。
• 能写出一个订单明细宽表和一个按日期城市的汇总表。
• 能用 ROLLUP 和 GROUPING() 区分明细值与汇总值。
• 能写出一条带版本的缓存键，并说明分区更新后怎么失效。
• 能解释客单价为什么不能对每日客单价再求平均。
• 能列出至少三种 BI 服务层权限控制方式。