简介面向数据分析与数据治理从业者的一份指标治理专题资料聚焦修饰词Filter与维度Dimension在指标体系中的核心差异与落地应用适合已具备SQL与数据仓库基础、希望规范指标定义与复用的中高级读者。压缩包内为1个PDF文档约453KB篇幅紧凑便于集中阅读。内容围绕维度作为观察视角与分类依据、在SQL中对应GROUP BY并改变分析粒度修饰词作为范围限定、对应WHERE或HAVING且不改变指标定义展开并用销售额按产品类目拆卸、仅含在线支付等案例说明二者边界同时给出保持维度中立、避免过度修饰、标准化定义等治理建议。已有61人学习可帮助读者在建设指标体系时减少重复造指标提升复用与分析效率。1. 从一次指标对不齐说起维度决定怎么看修饰词决定算什么上线一套指标体系后最先暴露的问题往往不是 SQL 写错而是同一个「订单量」在两张报表里对不上一张按地区拆一张只统计支付成功的订单还有人把「新用户」塞进 GROUP BY。指标治理里修饰词与维度的边界一旦含糊指标数量会指数膨胀口径也无法复用。这套方法把维度Dimension和修饰词Filter拆成两个治理对象维度是观察视角负责拆解分析粒度修饰词是计算范围限定负责筛选数据子集。适合数据分析师、数据仓库开发和指标平台产品一起对齐概念也适合正在做指标体系、维度建模、SQL 查询规范的人拿来当落地检查表。2. 维度建模把观察视角固定成可复用的 GROUP BY 字段在指标治理里维度不是报表上的一个下拉筛选而是决定聚合粒度的字段。维度建模的核心任务是把业务上常用的观察视角固定成可复用的维度表或维度字段让同一个指标在不同维度下拆解时计算逻辑保持一致。订单量按地区、渠道、产品类别拆就是维度用法如果按支付成功、新用户过滤后再统计那是修饰词。两者在 SQL 里的位置完全不同维度进 GROUP BY修饰词进 WHERE/HAVING。下面从维度约束、维度表落地和聚合验证三块展开。2.1 维度的三个硬约束离散、可枚举、可分组维度本质是数据的观察视角或分类依据通常是从业务过程里抽象出来的离散字段比如地区、渠道、产品类别、日期。它要满足三个硬约束第一可枚举值域可管理不能是自由文本第二可分组放进 GROUP BY 后能得到一致的分组结果第三可下钻层级关系清晰比如国家-省-市。不符合这些约束的字段比如“支付成功”只是一个状态条件硬塞进维度会让指标语义变得不稳定。管理维度时我一般会维护维度名称、编码、类型、层级和是否可下钻避免不同人用不同字段名表达同一个视角。维度名字段示例类型可下钻层级地区region_code枚举国家-省-市渠道channel_code枚举App/小程序/H5产品类目category_id枚举一级-二级-三级时间dt日期日-周-月这四个维度可以组合出“地区×渠道”“类目×时间”等分析路径但组合数会增长。治理上要限制常用组合而不是让每个组合都生成一个指标。维度中立性很重要时间、地区、渠道、产品类目是普适分类而“促销商品”“新用户”更偏向特定业务条件放错位置会污染指标体系。2.2 维度表落地从业务过程到维度字段维度建模案例通常从订单事实表开始。事实表存订单 ID、用户 ID、地区 ID、渠道编码、支付状态、支付金额、日期分区等字段地区、渠道、产品类目拆到维度表。这样新增一个地区属性时不需要改事实表。常见做法是CREATE TABLE dim_region ( region_id STRING COMMENT 地区ID, region_name STRING COMMENT 地区名称, parent_id STRING COMMENT 父级地区ID, level INT COMMENT 层级1省 2市 3区 ); CREATE TABLE dim_channel ( channel_code STRING COMMENT 渠道编码, channel_name STRING COMMENT 渠道名称App/小程序/H5, is_online INT COMMENT 是否线上渠道 );逻辑说明维度表用唯一主键和事实表关联避免把地区名、渠道名直接冗余在事实表里level支持层级下钻is_online这类属性如果用于过滤更适合做成修饰词或维度属性而不是改指标定义。参数说明region_id是事实表外键channel_code要可枚举is_online如果用于“仅线上渠道销售额”它进入 WHERE不进入 GROUP BY。2.3 维度组合与 SQL 聚合用 GROUP BY 验证粒度用 SQL 验证维度是否真正生效最直接的是把维度字段放进 SELECT 和 GROUP BY再看输出行数是否符合预期。SELECT d.region_name, o.channel_code, COUNT(DISTINCT o.order_id) AS order_count FROM dwd_order o JOIN dim_region d ON o.region_id d.region_id WHERE o.dt 2024-06-01 GROUP BY d.region_name, o.channel_code;逻辑说明d.region_name和o.channel_code是维度决定结果集粒度COUNT(DISTINCT o.order_id)是指标聚合WHERE o.dt 2024-06-01是分区过滤属于工程裁剪不是业务修饰词。参数说明如果dt也要作为时间维度应把它放进GROUP BY并跨多个分区查询只查一天时放在 WHERE 更高效。排查粒度是否膨胀可以比较总行数与维度组合数SELECT COUNT(*) AS row_cnt, COUNT(DISTINCT CONCAT(region_name, |, channel_code)) AS combo_cnt FROM ( SELECT d.region_name, o.channel_code FROM dwd_order o JOIN dim_region d ON o.region_id d.region_id WHERE o.dt 2024-06-01 GROUP BY d.region_name, o.channel_code ) t;如果row_cnt远大于combo_cnt说明维度字段可能有重复或关联表一对多需要检查维度表唯一性。这个检查在维度建模评审时很实用能提前发现“地区表一个 region_id 对应多行”的问题。3. 修饰词治理用 WHERE 和 HAVING 锁定计算范围修饰词在指标体系里经常被误用。它和维度最大的差别是修饰词只筛选数据子集不改变指标定义也不增加分析粒度。支付成功的订单量、新用户订单量、7天内复购订单量都是同一个订单量指标在特定范围下的快照。治理修饰词核心是判断它应该进 WHERE 还是 HAVING以及它是否应该下沉到指标计算逻辑。3.1 修饰词的判定标准过滤后是否改变粒度判定标准很简单过滤后结果集是否从“总体”变成“子集”但观察视角没有增加。如果是就是修饰词。比如“支付成功的订单”只是把未支付订单排除输出仍然是订单粒度“新用户订单”按用户类型过滤如果不再按用户类型分组输出仍是订单粒度。反过来如果把“新用户/老用户”放进 GROUP BY它就成了维度可以分析新老用户占比。特性维度修饰词核心作用拆解分析横向扩展限定范围纵向聚焦是否改变粒度是否SQL 位置GROUP BYWHERE / HAVING业务意义多角度洞察特定场景快照典型示例地区、渠道、类目支付成功、新用户、7天复购这个表可以当作指标评审时的检查表。任何新增字段先问一句“它是用来分组还是用来过滤”。如果是分组走维度管理如果是过滤走修饰词管理并考虑是否要进入指标定义版本。修饰词一旦进入指标名称比如“支付成功订单量”就必须固化过滤表达式不能让分析师在每张报表里各写一遍。3.2 常见修饰词与 SQL 实现支付成功、新用户、7天复购下面三类修饰词在订单指标里最常见。-- 支付成功订单量pay_status 是过滤条件不改变订单粒度 SELECT COUNT(DISTINCT order_id) AS paid_order_cnt FROM dwd_order WHERE dt 2024-06-01 AND pay_status paid; -- 新用户订单量user_type 作为修饰词不 GROUP BY user_type SELECT COUNT(DISTINCT order_id) AS new_user_order_cnt FROM dwd_order WHERE dt 2024-06-01 AND user_type new; -- 7天复购订单量用 EXISTS 限定复购子集仍按订单粒度输出 SELECT COUNT(DISTINCT o.order_id) AS repurchase_7d_cnt FROM dwd_order o WHERE o.dt 2024-06-01 AND EXISTS ( SELECT 1 FROM dwd_order p WHERE p.user_id o.user_id AND p.pay_status paid AND p.dt BETWEEN date_sub(o.dt, 7) AND date_sub(o.dt, 1) );逻辑说明三个查询都没有把修饰词字段放进 GROUP BY输出都是一行总数或按外部维度聚合后的结果。参数说明date_sub(o.dt, 7)表示向前推 7 天BETWEEN包含边界复购子查询要保证支付状态和日期分区一致。注意如果“去重用户数”只统计登录用户count(distinct user_id)和is_login1的组合会影响计算逻辑建议把这类条件固化在指标定义里而不是让分析师临时写 WHERE。3.3 修饰词不要出现在 GROUP BY一个反例排查反例很常见有人把“新用户”同时当作修饰词和维度。SELECT user_type, -- 一旦放进 GROUP BY新用户就变成了维度 d.region_name, COUNT(DISTINCT o.order_id) AS order_count FROM dwd_order o JOIN dim_region d ON o.region_id d.region_id WHERE o.dt 2024-06-01 GROUP BY user_type, d.region_name;逻辑说明这个查询输出的是新/老用户在不同地区的订单量可以做占比分析如果业务只要“新用户销售额”就不应该 GROUP BY user_type而应该 WHERE user_typenew。参数说明user_type的值域要可枚举否则维度口径会漂移。常见坑是把修饰词写在指标名称里比如“销售额_新用户_App_2023”这会导致指标爆炸。正确做法是保留基础指标“销售额”把“新用户”作为修饰词、“App”和“2023”作为维度或时间范围通过维度组合和修饰词组合生成查询。4. 指标定义落地元数据表、DSL 与 SQL 生成器概念分清之后落地要靠元数据和 SQL 生成约束。很多团队的问题不是不懂维度与修饰词而是没有系统层约束分析师手写 SQL 时随意把过滤条件塞进 GROUP BY或者把维度字段写进 WHERE导致同名指标口径漂移。常见做法是把指标、维度、修饰词拆成三份元数据再用一个 SQL 生成器拼装查询。4.1 指标元数据建模metrics、dimensions、filters 三张表CREATE TABLE metric_definition ( metric_id STRING COMMENT 指标ID如 order_count, metric_name STRING COMMENT 指标名称, expr STRING COMMENT 聚合表达式如 count(distinct order_id), table_name STRING COMMENT 事实表, status STRING COMMENT draft/online/offline ); CREATE TABLE dimension_definition ( dim_id STRING COMMENT 维度ID, dim_name STRING COMMENT 维度名称, column_expr STRING COMMENT 字段表达式如 d.region_name, dim_type STRING COMMENT 枚举/日期/层级 ); CREATE TABLE filter_definition ( filter_id STRING COMMENT 修饰词ID, filter_name STRING COMMENT 修饰词名称, where_expr STRING COMMENT 过滤表达式如 pay_status \paid\, is_logic INT COMMENT 是否下沉到计算逻辑0否 1是 );逻辑说明指标表只存聚合表达式和事实表不存 WHERE维度表只存可 GROUP BY 的字段表达式修饰词表存过滤表达式并用is_logic标记是否影响计算逻辑。参数说明expr里不要写过滤条件否则 SQL 生成器无法控制位置column_expr要带表别名避免多表关联时字段歧义is_logic1的修饰词不能简单拼 WHERE比如去重用户数、复购窗口。元数据表核心字段进入 SQL 的位置治理约束metric_definitionexpr、table_nameSELECT 聚合不包含 WHEREdimension_definitioncolumn_exprSELECT、GROUP BY必须可分组filter_definitionwhere_exprWHERE / HAVING不改变粒度4.2 用 Python 生成指标 SQL维度走 SELECT/GROUP BY修饰词走 WHEREdef build_sql(metric, dims, filters): metric: dict包含 expr 和 table_name dims: list每个元素包含 column_expr filters: list每个元素包含 where_expr dim_select , .join([d[column_expr] for d in dims]) or ALL AS dim_placeholder group_by , .join([d[column_expr] for d in dims]) or 1 where_parts [f[where_expr] for f in filters] where_sql AND .join(where_parts) if where_parts else 11 sql f SELECT {dim_select}, {metric[expr]} AS metric_value FROM {metric[table_name]} WHERE {where_sql} GROUP BY {group_by} return sql # 调用示例销售额按地区维度只看线上支付修饰词 metric {expr: sum(pay_amount), table_name: dwd_order} dims [{column_expr: d.region_name}] filters [{where_expr: pay_channel online}] print(build_sql(metric, dims, filters))逻辑说明dim_select和group_by只接收维度where_parts只接收修饰词没有维度时用ALL AS dim_placeholder和GROUP BY 1占位保证 SQL 语法完整。参数说明metric[expr]里应包含sum、count等聚合函数filters不能包含维度字段如果修饰词需要 HAVING比如过滤聚合后的销售额大于某个值要把where_expr标记为 having 并拼到HAVING子句不能混在 WHERE 里。4.3 排错清单指标爆炸、口径漂移、重复计数实际治理中下面几个问题出现频率最高。指标爆炸修饰词滥用出现销售额_新用户_App_2023这类命名。应保留基础指标用维度组合和修饰词组合查询。口径漂移同名指标在报表 A 里过滤了退款在报表 B 里没过滤。修饰词必须进入指标定义版本不能靠分析师手工写。重复计数修饰词放在 JOIN 的 ON 条件而不是 WHERE可能改变连接结果。例如-- 有风险的写法支付成功条件放在 LEFT JOIN 的 ON 里 SELECT d.region_name, COUNT(DISTINCT o.order_id) AS paid_order_cnt FROM dwd_order o LEFT JOIN dim_region d ON o.region_id d.region_id AND o.pay_status paid GROUP BY d.region_name;逻辑说明pay_status是订单表的修饰词放在 JOIN 的 ON 里不会真正限定订单集合还可能保留不匹配的维度行。参数说明修饰词如果作用于事实表应放在 WHERE如果作用于维度表才考虑放在 JOIN 的 ON 条件。排查重复计数时先确认事实表和维度表的关联键是否唯一再看过滤条件位置。5. 进阶验证维度下钻与修饰词快照校验指标一致性指标上线后最容易出问题的时间点是维度下钻和修饰词组合查询。一个基础指标加维度后汇总值应该能回到总数加修饰词后结果应该是一份范围快照不应和未限定口径直接做占比。下面给两个校验方法和一个修饰词下沉判断表。5.1 维度下钻验证同指标不同维度汇总应可回到总数-- 校验总数 SELECT COUNT(DISTINCT order_id) AS total_order_cnt FROM dwd_order WHERE dt 2024-06-01; -- 校验按渠道维度下钻后的汇总 SELECT channel_code, COUNT(DISTINCT order_id) AS order_cnt FROM dwd_order WHERE dt 2024-06-01 GROUP BY channel_code;逻辑说明如果渠道维度互斥且无重复订单按渠道汇总的order_cnt相加应等于总数。参数说明COUNT(DISTINCT)用于订单去重如果渠道关联表一对多会导致订单重复汇总值大于总数。维度下钻验证是检查维度建模是否干净的最直接手段。5.2 修饰词快照验证修饰词指标不应参与占比拆解“支付成功订单量”是订单量在支付成功范围内的快照。它可以按地区维度下钻但不能和“订单量总数”直接做占比除非两个指标都使用同一修饰词范围。常见错误是拿“新用户销售额”除以“总销售额”得出新用户占比但总销售额里包含了未支付、退款等不同口径。正确做法是先用同一基础指标和同一修饰词范围再通过维度user_type拆解。5.3 一个具体技巧修饰词下沉到指标计算逻辑的判断表修饰词场景典型写法是否下沉到计算逻辑原因支付成功订单WHERE pay_statuspaid否纯过滤聚合方式不变7天复购订单EXISTS 子查询是依赖时间窗口和用户行为去重用户数只算登录用户count(distinct user_id) is_login1是去重口径与过滤条件强绑定线上渠道销售额WHERE is_online1否但如果渠道是维度应 GROUP BY取决于业务问的是占比还是快照把这张判断表接进指标评审流程每次新增修饰词先确认它是否改变聚合逻辑再决定是拼 WHERE 还是写入指标表达式。本文还有配套的精品资源点击获取
