指标管理平台实战：原子/派生/复合指标与SQL自动生成

简介这份 30 页的 PPT 聚焦电信企业指标管理平台的整体建设思路适合数据治理、指标口径统一、BI 报表与业务系统集成方向的产品经理、数据分析师及 IT 实施人员参考。内容先交代平台产生的背景与原因——系统林立、接口错综、数据源不一致与指标口径理解差异导致各部门结果对不上再给出以统一规范减少人为错误的解决思路。平台部分讲清两大核心能力指标规范定义、指标定义、发布与维护以及指标查询、基础指标多维报表生成和对外对接接口并针对熟悉业务不懂技术、业务与技术兼通、合作厂家三类对象设计不同操作界面。建设内容覆盖功能框架图形化呈现、指标定义规范与流程、指标分类并配有功能实例便于对照。整包为 1 个 PPT 文件约 646KB按总体概述、平台介绍、建设内容分节可直接用于方案汇报或立项材料参考。已有 133 人学习。1. 指标管理平台的价值不在报表里一份 30 页 PPT 该落到哪几层同一张经营看板上「支付转化率」在增长团队手里是 7 日内下单转支付在财务口径里是自然月内已支付订单占比两个数字差了 3 个百分点会议就开成了对数会。指标管理平台要解决的就是这件事把散落在 SQL、BI 看板、Excel 和口头约定里的指标定义收敛成一套有唯一编码、有口径描述、有责任人、有版本记录的元数据再基于这份元数据自动推导计算逻辑和查询服务。很多团队做完的那 30 页 PPT分层图、命名规范、审批流程都画得很完整真正上线后却没人用卡点通常只有三个元模型表达不了复杂口径、SQL 不能从定义自动生成、口径变更追溯不回去。下面按元模型建表、口径解析与 SQL 生成、血缘版本与审批、服务化与口径校验这条主线往下走适合数据平台工程师、数仓开发和负责指标体系落地的数据产品。2. 指标元模型设计原子指标、派生指标、复合指标怎么建表2.1 三层指标模型的边界与选型理由原子指标 业务过程 度量字段 聚合方式不携带时间窗口也不允许在定义阶段绑定维度。派生指标 原子指标 时间窗口 过滤条件例如「近 7 日有效支付金额」就是 atom_pay_amt 加上 7d 窗口和 is_valid1 的过滤。复合指标 多个派生或原子指标的四则运算结果例如支付转化率 支付用户数 / 下单用户数。为什么时间窗口不放在原子层因为原子指标是唯一事实来源一旦把「7 日」写进原子指标就会出现 atom_pay_amt_7d、atom_pay_amt_30d 这类编码爆炸口径变更时要在几十个近似编码里找引用关系。放在派生层的代价是行数多但换来的是血缘清晰原子指标变更向下游派生指标的影响面可以用一条递归查询算出来。另一条经验是复合指标只存公式不存结果。公式里引用的是指标编码而不是字段名这样底层换表、换引擎时复合指标不用改只改原子指标的 source_table 即可。2.2 五张核心表的建表语句与字段约束元数据落到 MySQL 或 PostgreSQL 都行关键是字段约束要在库层面卡住而不是只靠文档约定。-- 原子指标业务过程 度量 聚合方式 CREATE TABLE metric_atom ( atom_id BIGINT NOT NULL AUTO_INCREMENT, atom_code VARCHAR(64) NOT NULL COMMENT 唯一编码如 atom_pay_amt, atom_name VARCHAR(128) NOT NULL COMMENT 中文名如支付金额, biz_process VARCHAR(64) NOT NULL COMMENT 业务过程如交易支付, measure_col VARCHAR(64) NOT NULL COMMENT 度量字段如 pay_amount, agg_func VARCHAR(16) NOT NULL COMMENT SUM/COUNT/COUNT_DISTINCT/MAX, source_table VARCHAR(128) NOT NULL COMMENT 来源表如 dwd_trade_pay_di, owner VARCHAR(64) NOT NULL COMMENT 责任人, status TINYINT NOT NULL DEFAULT 0 COMMENT 0草稿 1审批中 2已上线 3已下线, PRIMARY KEY (atom_id), UNIQUE KEY uk_atom_code (atom_code) ) COMMENT原子指标定义; -- 派生指标原子指标 时间窗口 过滤条件 CREATE TABLE metric_derived ( derived_id BIGINT NOT NULL AUTO_INCREMENT, derived_code VARCHAR(64) NOT NULL COMMENT 如 der_pay_amt_7d, derived_name VARCHAR(128) NOT NULL, atom_code VARCHAR(64) NOT NULL COMMENT 引用的原子指标编码, time_window VARCHAR(16) NOT NULL COMMENT 1d/7d/30d/natural_month, filter_expr VARCHAR(512) DEFAULT NULL COMMENT 过滤条件如 is_valid 1, dim_scope VARCHAR(512) DEFAULT NULL COMMENT 允许下钻的维度编码逗号分隔, status TINYINT NOT NULL DEFAULT 0, PRIMARY KEY (derived_id), UNIQUE KEY uk_derived_code (derived_code), KEY idx_atom_code (atom_code) ) COMMENT派生指标定义; -- 复合指标只存公式不存结果 CREATE TABLE metric_composite ( composite_code VARCHAR(64) NOT NULL, composite_name VARCHAR(128) NOT NULL, formula VARCHAR(512) NOT NULL COMMENT 如 ${der_pay_uv} / ${der_order_uv}, divide_guard TINYINT NOT NULL DEFAULT 1 COMMENT 分母为0返回NULL, status TINYINT NOT NULL DEFAULT 0, PRIMARY KEY (composite_code) ) COMMENT复合指标定义;另外两张表容易被漏掉。一张是 metric_dim_ref把指标和维度做多对多关联dim_scope 里写字符串只能做人读校验真正下钻白名单要靠这张关联表另一张是 metric_version每次上线写一条不可变快照字段结构就是指标表的 JSON 序列化加版本号和生效时间。字段设计上有三个必调项agg_func 建议用枚举而非自由文本渲染 SQL 时直接查字典filter_expr 必须限定为白名单表达式禁止出现子查询和函数调用status 与 owner 是审批流和责任到人的基础缺了这两个字段平台最后会退化成一张查不到责任人的维表。表名核心字段是否必填说明metric_atomatom_code是全局唯一命名规范强校验metric_atomagg_func是枚举值渲染层做字典映射metric_derivedatom_code是外键语义删除前要查下游metric_derivedtime_window是决定 SQL 的时间条件模板metric_compositeformula是用 ${code} 占位禁止裸字段metric_versionversion是递增整数配合生效时间2.3 把命名规范写成可执行校验而不是文档条款命名规范写在 PPT 里没人看写成校验函数才能在录入接口上拦住。import re CODE_RULES { atom: re.compile(r^atom_[a-z][a-z0-9_]{2,40}$), derived: re.compile(r^der_[a-z][a-z0-9_]{2,40}$), composite: re.compile(r^cmp_[a-z][a-z0-9_]{2,40}$), } VALID_WINDOW {1d, 7d, 30d, natural_month} def validate_code(metric_type: str, code: str) - None: rule CODE_RULES.get(metric_type) # 类型不存在直接报错避免默认放行 if rule is None: raise ValueError(f未知指标类型: {metric_type}) if not rule.match(code): raise ValueError(f编码不合规: {code}需匹配 {rule.pattern}) def validate_derived(row: dict, atom_codes: set) - list: errors [] if row[atom_code] not in atom_codes: # 引用完整性防止孤儿派生指标 errors.append(f引用的原子指标不存在: {row[atom_code]}) if row[time_window] not in VALID_WINDOW: errors.append(f时间窗口不合法: {row[time_window]}) return errorsvalidate_code 做的是格式层拦截前缀区分类型是为了让人在 SQL 里一眼看出依赖层级validate_derived 做的是引用完整性校验atom_codes 从库里查全量已上线原子指标编码传入。实际接入时把这两个函数挂在录入接口和批量导入脚本两处批量导入最容易绕过前端校验。2.4 常见误用把派生指标当原子指标存最常见的坑是把「近 7 日支付金额」直接建成原子指标理由是这样查询时少一层 join。代价是三个月后出现了 der_pay_amt_7d_ios、der_pay_amt_7d_new_user 这类带渠道和人群的定义同一个时间窗口散在十几个编码里口径对不齐时根本不知道改哪个。正确做法是人群、渠道这类修饰走派生指标的 filter_expr 和 dim_scope不要新建原子指标。3. 指标口径解析与 SQL 自动生成从定义到可执行查询的最小链路3.1 指标定义字段到 SQL 片段的映射关系自动生成的前提是定义字段和 SQL 片段一一对应映射关系先定死再写模板。定义字段生成的 SQL 片段示例measure_col agg_func聚合表达式SUM(pay_amount)filter_exprWHERE 追加条件is_valid 1time_window分区时间条件dt date_sub(${bizdate}, 6)dim_scope metric_dim_refGROUP BY 白名单GROUP BY city_id, channelformula表达式替换${der_pay_uv} / ${der_order_uv}divide_guard空值保护NULLIF(分母, 0)映射表定完之后聚合函数不再允许自由填写只允许映射表里出现的四种。这一条能挡掉大量「生成出来的 SQL 能跑但结果错误」的情况比如把 AVG 用在已经聚合过的宽表字段上。3.2 用 Python Jinja2 渲染指标 SQL 的最小实现from jinja2 import Template AGG_TPL { SUM: SUM({col}), COUNT: COUNT({col}), COUNT_DISTINCT: COUNT(DISTINCT {col}), MAX: MAX({col}), } SQL_TEMPLATE Template(SELECT {%- for d in dims %} {{ d }}, {%- endfor %} {{ agg_expr }} AS metric_value FROM {{ source_table }} WHERE dt BETWEEN {{ start_dt }} AND {{ end_dt }} {%- if filter_expr %} AND {{ filter_expr }} {%- endif %} {%- if dims %} GROUP BY {{ dims | join(, ) }} {%- endif %}) def build_agg_expr(agg_func: str, col: str) - str: tpl AGG_TPL.get(agg_func.upper()) # 只允许映射表内的聚合函数 if tpl is None: raise ValueError(f不支持的聚合函数: {agg_func}) return tpl.format(colcol) def render_metric_sql(meta: dict, dims: list, start_dt: str, end_dt: str) - str: allowed set((meta.get(dim_scope) or ).split(,)) for d in dims: if d not in allowed: # 维度白名单防止越权下钻 raise ValueError(f维度未授权: {d}) return SQL_TEMPLATE.render( dimsdims, agg_exprbuild_agg_expr(meta[agg_func], meta[measure_col]), source_tablemeta[source_table], start_dtstart_dt, end_dtend_dt, filter_exprmeta.get(filter_expr), )三个参数需要说明dims 必须经白名单过滤后再传给模板这是防注入的第一道闸start_dt、end_dt 由时间窗口推导不要让调用方直接传日期字符串否则 7d 口径会被传成任意区间filter_expr 虽然来自元数据但入库前要过一遍表达式白名单只放行字段 运算符 常量的形式。复合指标在渲染层多一步先用正则把 formula 里的 ${code} 全部替换成子查询或中间 CTE再套外层 SELECT。替换时用\$\{([a-z0-9_])\}抓取编码遇到未注册的编码直接抛错不要静默替换成 NULL。3.3 时间窗口参数表与维度组合的下钻策略time_window时间条件表达式说明1ddt ${bizdate}当日快照7ddt BETWEEN date_sub(${bizdate}, 6) AND ${bizdate}含当天共 7 天30ddt BETWEEN date_sub(${bizdate}, 29) AND ${bizdate}含当天共 30 天natural_monthdt BETWEEN trunc(${bizdate},MM) AND last_day(${bizdate})自然月跨月要重跑维度组合不要开放自由勾选。常见做法是按「常用组合」预置三到五套例如日期 渠道、日期 城市、日期 新老客其余组合走异步查询。自由组合下钻会把 GROUP BY 基数拉到几十万单次查询几十秒看板体验直接崩掉。3.4 生成的 SQL 跑不出结果时先查这四处一是维度字段没有出现在来源表里GROUP BY 报列不存在二是时间字段名不统一有的表用 dt 有的用 stat_date模板里要参数化而不是硬编码三是 COUNT_DISTINCT 和 SUM 混用宽表里指标已经聚合过再 SUM 会重复累加四是分母为 0 时复合指标返回 NULL 而不是 0看板上显示为空业务方会以为任务失败。前三条属于渲染层配置错误第四条是 divide_guard 的语义问题要在指标详情页写清楚。4. 指标血缘、版本与审批口径变更怎么不炸线上4.1 血缘用邻接表存查询用递归 CTE血缘只需要存边上游是谁、下游是谁、类型是表还是指标。CREATE TABLE metric_lineage ( id BIGINT AUTO_INCREMENT, up_type VARCHAR(16) NOT NULL COMMENT table/metric, up_code VARCHAR(128) NOT NULL, down_type VARCHAR(16) NOT NULL, down_code VARCHAR(128) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_edge (up_type, up_code, down_type, down_code) ) COMMENT指标与表血缘边; -- MySQL 8.0 / PostgreSQL查 atom_pay_amt 的全部下游 WITH RECURSIVE downstream AS ( SELECT up_code, down_code, 1 AS lvl FROM metric_lineage WHERE up_type metric AND up_code atom_pay_amt UNION ALL SELECT l.up_code, l.down_code, d.lvl 1 FROM metric_lineage l JOIN downstream d ON l.up_code d.down_code WHERE d.lvl 5 -- 深度上限防止脏数据成环 ) SELECT DISTINCT down_code, MIN(lvl) AS min_level FROM downstream GROUP BY down_code ORDER BY min_level;递归 CTE 写法比闭包表省空间代价是每次查询都要递归深度上限一定要设否则一条环状脏数据能把库拖死。字段级血缘如果暂时做不了至少把 source_table 和 measure_col 记进原子指标表级血缘能覆盖八成影响面评估场景。存储方式写入成本查询成本适用场景邻接表低递归查询中等边数十万以内主流选择闭包表高单表查询低血缘查询频繁、实时展示字段级血缘很高高需要精确到列的下游影响分析4.2 版本快照与差异比对版本表存 JSON 快照上线时写一条禁止更新历史行。口径变更时把新旧快照做字段级 diff自动生成变更说明推给下游订阅者。WATCH_KEYS (agg_func, measure_col, filter_expr, time_window, source_table, formula) def diff_version(old: dict, new: dict) - list: changes [] for k in WATCH_KEYS: if old.get(k) ! new.get(k): changes.append({field: k, before: old.get(k), after: new.get(k)}) return changes # 为空表示仅改名或改责任人不触发下游通知WATCH_KEYS 是重点只盯影响计算结果字段。改中文名、换责任人、补描述不需要通知下游否则通知疲劳真正影响结果的变更会被淹没。diff 结果为空时直接走快速上线通道非空时强制进入审批。4.3 审批状态机与回滚脚本状态取值固定为 0 草稿、1 审批中、2 已上线、3 已下线禁止业务侧自定义状态值。状态允许的下一步触发条件0 草稿1 审批中校验函数全部通过1 审批中2 已上线 / 0 草稿审批通过 / 驳回2 已上线3 已下线影响面确认完成3 已下线2 已上线回滚需指定目标版本回滚不是简单把 status 改回去要连版本快照一起还原。START TRANSACTION; -- 1) 还原定义到目标版本 UPDATE metric_derived d JOIN metric_version v ON v.metric_code d.derived_code AND v.version 7 -- 目标版本号 SET d.atom_code v.snapshot-$.atom_code, d.time_window v.snapshot-$.time_window, d.filter_expr v.snapshot-$.filter_expr, d.status 2 WHERE d.derived_code der_pay_amt_7d; -- 2) 写入一条新的版本记录保留回滚动作本身 INSERT INTO metric_version(metric_code, version, snapshot, op_type) VALUES(der_pay_amt_7d, 8, JSON_OBJECT(rollback_to, 7), ROLLBACK); COMMIT;回滚也写新版本而不是删记录这样「谁在什么时候回滚到哪一版」有据可查。snapshot 字段用 JSON 类型MySQL 5.7 以上和 PostgreSQL 都支持注意-在 PostgreSQL 里要换成-的等价写法跨库迁移时这段 SQL 要单独适配。4.4 变更上线前的四步影响面确认第一步用递归 CTE 查出全部下游指标和看板第二步对下游逐条跑 diff标记出结果会变的指标第三步算预估影响行数和时间跨度历史上线过的最大区间要重跑第四步选定双跑对账窗口通常取上线前后各 7 天。跳过第三步是常见事故源改一个聚合函数回溯三个月的任务跑了一整夜。5. 指标服务化与口径一致性校验上线后怎么证明它是对的5.1 查询 API 的缓存键与预聚合取舍指标查询接口的缓存键必须由四段拼成metric_code 维度组合 时间窗口 bizdate。少任何一段都会串数据尤其是只有 metric_code 和 bizdate 两段时带城市维度和不带城市维度的请求会互相覆盖。TTL 按窗口区分1d 类指标当天可以缓存 10 分钟自然月类可以放到小时级。高频维度组合走预聚合调度任务每天把三到五套常用组合写进一张 ads 表接口优先查预聚合未命中再回源。判断要不要预聚合看一条规则同一组合的日均查询次数超过 200 次且扫描分区超过 30 个就值得落表。5.2 双跑对账用相对误差而不是绝对误差告警新口径上线前新旧两套逻辑并行跑一段用 FULL JOIN 比对结果。SELECT COALESCE(a.dt, b.dt) AS dt, COALESCE(a.city_id, b.city_id) AS city_id, a.metric_value AS new_value, b.metric_value AS old_value, COALESCE(a.metric_value, 0) - COALESCE(b.metric_value, 0) AS diff FROM ads_metric_new a FULL JOIN ads_metric_old b ON a.dt b.dt AND a.city_id b.city_id WHERE ABS(COALESCE(a.metric_value, 0) - COALESCE(b.metric_value, 0)) 0.01 * NULLIF(b.metric_value, 0) -- 相对误差超过 1% 才算异常 ORDER BY ABS(COALESCE(a.metric_value, 0) - COALESCE(b.metric_value, 0)) DESC LIMIT 200;用相对误差而不是绝对误差是因为金额类指标的单日误差几百块很正常用户数类指标差 3 个人就可能意味着口径错了。NULLIF 防止分母为 0 导致整列变成 NULL 而静默通过。实际排查时先看 diff 集中在哪几个维度值上集中在单一渠道说明是过滤条件写错遍布所有维度说明是聚合粒度对不上。一个容易被忽略的技巧对账任务本身也要写入指标元数据把校验通过率和最大偏差作为平台自身的监控指标口径质量问题就会从「业务方反馈」变成「平台自己先发现」。上线后第一周每天跑一次稳定后改成每周跑一次历史偏差曲线留着下次有人质疑数字时直接调出来看。本文还有配套的精品资源点击获取