尧图网站建设 
Lightdash 指标Metrics完整参考从 YAML 定义到仓库级验证的语义层实战指南【免费下载链接】lightdashAgentic BI. Analytics at the speed of code ⚡️项目地址: https://gitcode.com/GitHub_Trending/li/lightdashLightdash 的指标Metrics是对数据执行聚合计算得到的业务度量回答总共多少、有多少条、平均值是多少这类问题。本文以skills/developing-in-lightdash/resources/metrics-reference.md为骨架系统讲解指标在 dbt 或纯 Lightdash 项目中的两种定义位置、全部指标类型、格式化与过滤等配置项并结合仓库源码packages/common/src/types/field.ts、packages/common/src/dbt/schemas/lightdashMetadata.json验证每一处语法的真实取值读完后你可以直接写出可部署、可 lint、可复用的指标 YAML。指标是什么定义在哪里指标是对数据执行的聚合计算与用于分组/过滤的维度dimension相对。在 Lightdash 的语义层中指标定义在两类文件中dbt 项目models/*.yml元数据嵌套在meta:dbt Fusion / dbt 1.10 则嵌套在config: meta:下纯 Lightdash 项目lightdash/models/*.yml属性直接放在顶层。指标可以定义在两个位置二者适用场景不同。1. 列级指标Column-Level Metrics推荐把指标绑定到具体列上与数据源紧密关联是官方推荐做法columns: - name: amount meta: dimension: type: number metrics: total_amount: type: sum average_amount: type: average在仓库的 JSON Schema lightdashMetadata.json 中LightdashColumnMetadata明确允许每个列同时声明dimension与metricsadditionalProperties 指向LightdashMetric这就是列级指标的结构依据。2. 模型级指标Model-Level Metrics当指标不依赖某一列、需要自定义 SQL 表达式如跨列计算比率时定义在模型级别models: - name: orders meta: metrics: revenue_per_customer: type: number sql: SUM(${TABLE}.amount) / COUNT(DISTINCT ${TABLE}.customer_id)注意${TABLE}是当前模型的占位符编译时会被替换为模型对应的真实表名。指标类型全览仓库源码 field.ts 中的MetricType枚举定义了全部 17 种指标类型与文档表格一一对应聚合指标Aggregation Metrics类型说明是否需要 SQLcount统计全部行数否count_distinct统计唯一值数量否sum求和否sum_distinct按distinct_keys去重后求和否需要distinct_keys:average平均值否average_distinct按distinct_keys去重后求平均否需要distinct_keys:min最小值否max最大值否percentile百分位值否需要percentile:median中位数第 50 百分位否其中sum_distinct与average_distinct是处理宽表反规范化数据如订单总额被冗余到每一条明细行的关键能力后文单独展开。自定义 SQL 指标Custom SQL Metrics类型说明是否需要 SQLnumber自定义数值计算是string自定义字符串结果是date自定义日期结果是timestamp自定义时间戳结果是boolean自定义布尔结果是派生指标Derived Metrics类型说明percent_of_previous相对上一行的百分比变化环比percent_of_total占列总计的百分比running_total累计求和值得留意的是MetricType枚举中同时存在median与percentile两个独立类型说明仓库将其作为一等类型处理而非用通用 percentile 参数模拟。基础配置项基础配置metrics: total_revenue: type: sum label: Total Revenue description: Sum of all order amounts hidden: false对照 Schema 的LightdashMetric定义lightdashMetadata.json除type为必填外label、description、sql、hidden、round、compact、format、separator、group_label已废弃、urls、show_underlying_values、filters、percentile、distinct_keys、tags、default_time_dimension均为可选属性。格式化metrics: total_revenue: type: sum round: 2 # Decimal places format: usd # Format preset compact: millions # Compact display格式预设Format Presets来自 field.ts 的Format枚举货币usd、gbp、eur源码还额外支持jpy、dkk百分比percent距离km、miIDid不格式化其他si国际单位制紧凑显示Compact Options来自 Schema 的Compact枚举与 field.ts 的CompactConfigMap数字thousands、millions、billions、trillions也接受别名K、M、B、T字节kilobytes、megabytes、gigabytes、terabytes源码还支持petabytes以及二进制单位kibibytes、mebibytes、gibibytes、tebibytes、pebibytes及缩写KiB、MiB、GiB、TiB、PiB额外支持auto自动紧凑模式CompactConfigMap中每个配置还携带orderOfMagnitude数量级与convertFn换算函数例如millions的convertFn为value / 1000000展示层据此自动换算并追加M后缀。此外 Schema 还提供separator千分位分隔符配置取值为default、commaPeriod100,000.00、spacePeriod、periodComma100.000,00、noSeparatorPeriod、apostrophePeriod用于不同区域习惯的数字显示。百分位配置metrics: p95_response_time: type: percentile percentile: 95 # Required for percentile type label: P95 Response Timepercentile属性在 Schema 中定义为number类型仅对percentile类型指标必填。去重聚合配置Distinct Aggregationsum_distinct和average_distinct会先按一个或多个维度对行去重再对目标列聚合。典型场景是宽表订单总额被反规范化到每一行明细上如每条 line item 都带着订单总额此时希望每个order_id只被计入一次。distinct_keys接受单个维度引用或列表Schema 中定义为oneOf: string | array of stringcolumns: - name: order_total meta: dimension: type: number metrics: # Single key — sum each order_id once revenue: type: sum_distinct distinct_keys: order_id # Composite key — array form revenue_by_order_and_currency: type: sum_distinct distinct_keys: - order_id - currency_code # Same syntax for averages average_order_value: type: average_distinct distinct_keys: order_id也可以在模型级别配合自定义sql:使用不绑定单一列models: - name: order_lines meta: metrics: revenue: type: sum_distinct sql: ${TABLE}.order_total distinct_keys: order_id average_order_value: type: average_distinct sql: ${TABLE}.order_total distinct_keys: order_id指标过滤器Metric Filters为特定指标施加过滤条件用于创建指标的变体metrics: completed_orders: type: count label: Completed Orders filters: - status: completed # Multiple filter conditions high_value_completed: type: count filters: - status: completed - amount: 1000过滤器操作符相等value、value比较 100、 50、 10、 100多值[value1, value2]空值判断null、!null日期区间inThePast N days、inTheNext N months支持单位days、weeks、months、years日期区间的取值在源码 filter.ts 中得到印证IN_THE_PAST inThePast与IN_THE_NEXT inTheNext是过滤规则中的正式操作符。日期过滤示例metrics: recent_orders: type: count filters: - created_at: inThePast 30 days upcoming_renewals: type: count filters: - renewal_date: inTheNext 7 days重要限制inTheCurrent在指标定义过滤器metric definition filters中不是合法操作符它只存在于图表和仪表盘过滤器中。如果指标内需要当前周期逻辑应改用自定义 SQL 配合日期截断如DATE_TRUNC实现。显示底层明细值Show Underlying Values配置用户下钻drill into指标时展示的字段metrics: total_revenue: type: sum show_underlying_values: - order_id - customer_name - amount - created_atSchema 中该属性为字符串数组仅列出字段名即可。组织分组Organization用groups在侧边栏组织指标metrics: total_revenue: type: sum groups: - Revenue Metrics支持多层级分组按数组顺序形成层级metrics: total_revenue: type: sum groups: - Financial - Revenue注意group_label已废弃请改用groups。这一点在 Schema lightdashMetadata.json 中被显式标记为deprecated: true。URL 链接给指标值添加可点击链接metrics: order_count: type: count urls: - label: View Orders url: /orders?customer_id${row.customer_id}Schema 的Urls定义lightdashMetadata.json揭示其底层是Liquidjs 模板可用变量包括${value.raw}、${value.formatted}以及行级变量${row.table_name.field_name.raw}、${row.table_name.field_name.formatted}——这意味着 URL 可以携带任意查询中选中字段的原始值或格式化值。访问控制Access Control通过required_attributes限制谁能看到/使用该指标metrics: confidential_revenue: type: sum required_attributes: role: finance其语义是用户必须拥有role finance的用户属性才可见常配合用户属性实现行级/列级安全。Schema 中同时存在required_attributes必须满足与any_attributes满足任一即可两种定义。AI 提示AI Hintsmetrics: total_revenue: type: sum ai_hint: Primary revenue metric - use for financial reportingai_hint会进入语义层上下文帮助 AI 助手Lightdash AI 查询/Agent 场景正确选用指标避免语义相近的指标被误用。标签Tagsmetrics: total_revenue: type: sum tags: - finance - kpi标签可用于内容搜索与分类管理。默认时间维度Default Time Dimension将指标与某个时间维度关联作为该指标查询时的默认时间粒度metrics: total_revenue: type: sum default_time_dimension: field: created_at interval: MONTHSchema 的DefaultTimeDimension定义lightdashMetadata.json要求field与interval均为必填且interval只能是DAY、WEEK、MONTH、YEAR四者之一。自定义 SQL 指标实战模型级自定义指标models: - name: orders meta: metrics: # Revenue per customer revenue_per_customer: type: number sql: SUM(${TABLE}.amount) / NULLIF(COUNT(DISTINCT ${TABLE}.customer_id), 0) round: 2 format: usd # Conversion rate conversion_rate: type: number sql: COUNT(CASE WHEN ${TABLE}.status completed THEN 1 END)::float / NULLIF(COUNT(*), 0) * 100 round: 1 description: Percentage of orders that completed # Year-over-year growth (requires window functions) yoy_growth: type: number sql: | (SUM(${TABLE}.amount) - LAG(SUM(${TABLE}.amount)) OVER (ORDER BY DATE_TRUNC(year, ${TABLE}.created_at))) / NULLIF(LAG(SUM(${TABLE}.amount)) OVER (ORDER BY DATE_TRUNC(year, ${TABLE}.created_at)), 0) * 100要点使用NULLIF(x, 0)防御除零错误使用::float显式类型转换避免整数除法得到 0窗口函数如LAG可用于环比/同比计算但依赖数据仓库对窗口函数的支持。引用其他表通过表名引用实现跨表计算前提是模型间已建立 join 关系metrics: customer_order_total: type: number sql: SUM(${orders.amount}) # Reference joined table完整示例三种业务场景电商指标E-commercecolumns: - name: amount meta: dimension: type: number format: usd metrics: total_revenue: type: sum label: Total Revenue description: Sum of all order amounts format: usd round: 2 show_underlying_values: - order_id - customer_name - amount groups: - Revenue average_order_value: type: average label: Average Order Value description: Mean order amount format: usd round: 2 groups: - Revenue max_order: type: max label: Largest Order format: usd groups: - Revenue - name: order_id meta: dimension: type: string metrics: order_count: type: count label: Total Orders groups: - Volume unique_customers: type: count_distinct sql: ${TABLE}.customer_id label: Unique Customers groups: - VolumeSaaS 指标models: - name: subscriptions meta: metrics: mrr: type: sum sql: ${TABLE}.monthly_amount label: MRR description: Monthly Recurring Revenue format: usd compact: thousands arr: type: number sql: SUM(${TABLE}.monthly_amount) * 12 label: ARR description: Annual Recurring Revenue format: usd compact: millions churn_rate: type: number sql: | COUNT(CASE WHEN ${TABLE}.status churned THEN 1 END)::float / NULLIF(COUNT(*), 0) * 100 label: Churn Rate round: 2 description: Percentage of churned subscriptions avg_contract_value: type: average sql: ${TABLE}.contract_value label: ACV description: Average Contract Value format: usd columns: - name: customer_id meta: dimension: type: string metrics: customer_count: type: count_distinct label: Total Customers - name: contract_value meta: dimension: type: number format: usd metrics: total_contract_value: type: sum median_contract_value: type: median p90_contract_value: type: percentile percentile: 90营销指标Marketingmodels: - name: campaigns meta: metrics: total_spend: type: sum sql: ${TABLE}.spend format: usd total_conversions: type: sum sql: ${TABLE}.conversions cpa: type: number sql: SUM(${TABLE}.spend) / NULLIF(SUM(${TABLE}.conversions), 0) label: Cost Per Acquisition format: usd round: 2 roas: type: number sql: SUM(${TABLE}.revenue) / NULLIF(SUM(${TABLE}.spend), 0) label: ROAS description: Return on Ad Spend round: 2 ctr: type: number sql: SUM(${TABLE}.clicks)::float / NULLIF(SUM(${TABLE}.impressions), 0) * 100 label: Click-Through Rate round: 2 description: Percentage of impressions that resulted in clicks最佳实践清单优先使用列级指标将指标绑定到其来源数据列便于维护与理解添加有意义的描述帮助使用者理解计算口径设置合理的精度通常 02 位小数使用格式预设保证指标展示风格统一配置show_underlying_values提供有用的下钻能力用groups归类指标支持层级分组处理除零自定义 SQL 中使用NULLIF用过滤器创建变体同一口径的不同筛选版本添加 AI 提示帮助 AI 助手正确选用指标显式类型转换如::float确保聚合结果符合预期。验证与部署闭环指标写完后应纳入与 skills/developing-in-lightdash/SKILL.md 一致的开发流程编辑dbt 项目修改models/*.yml元数据在meta:下dbt Fusion / dbt 1.10 在config: meta:下纯 Lightdash 项目修改lightdash/models/*.yml属性置于顶层验证纯 Lightdash 项目运行lightdash lintdbt 项目运行dbt compile二者都会按上文引用的 JSON Schema 校验指标类型、必填字段与取值枚举部署lightdash deploy将语义层指标、维度同步到目标项目测试复杂改动先用lightdash preview --name my-feature创建隔离项目验证再回归。从 MetricType 枚举 到 LightdashMetric Schema仓库源码完整固化了文档中每一处 YAML 语法——写出的每一个type、format、compact、distinct_keys都有确定取值与校验约束这正是本文所述指标定义可以直接落地、可 lint、可部署的原因。【免费下载链接】lightdashAgentic BI. Analytics at the speed of code ⚡️项目地址: https://gitcode.com/GitHub_Trending/li/lightdash创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考 Lightdash 指标（Metrics）完整参考：从 YAML 定义到仓库级验证的语义层实战指南
Lightdash 指标Metrics完整参考从 YAML 定义到仓库级验证的语义层实战指南【免费下载链接】lightdashAgentic BI. Analytics at the speed of code ⚡️项目地址: https://gitcode.com/GitHub_Trending/li/lightdashLightdash 的指标Metrics是对数据执行聚合计算得到的业务度量回答总共多少、有多少条、平均值是多少这类问题。本文以skills/developing-in-lightdash/resources/metrics-reference.md为骨架系统讲解指标在 dbt 或纯 Lightdash 项目中的两种定义位置、全部指标类型、格式化与过滤等配置项并结合仓库源码packages/common/src/types/field.ts、packages/common/src/dbt/schemas/lightdashMetadata.json验证每一处语法的真实取值读完后你可以直接写出可部署、可 lint、可复用的指标 YAML。指标是什么定义在哪里指标是对数据执行的聚合计算与用于分组/过滤的维度dimension相对。在 Lightdash 的语义层中指标定义在两类文件中dbt 项目models/*.yml元数据嵌套在meta:dbt Fusion / dbt 1.10 则嵌套在config: meta:下纯 Lightdash 项目lightdash/models/*.yml属性直接放在顶层。指标可以定义在两个位置二者适用场景不同。1. 列级指标Column-Level Metrics推荐把指标绑定到具体列上与数据源紧密关联是官方推荐做法columns: - name: amount meta: dimension: type: number metrics: total_amount: type: sum average_amount: type: average在仓库的 JSON Schema lightdashMetadata.json 中LightdashColumnMetadata明确允许每个列同时声明dimension与metricsadditionalProperties 指向LightdashMetric这就是列级指标的结构依据。2. 模型级指标Model-Level Metrics当指标不依赖某一列、需要自定义 SQL 表达式如跨列计算比率时定义在模型级别models: - name: orders meta: metrics: revenue_per_customer: type: number sql: SUM(${TABLE}.amount) / COUNT(DISTINCT ${TABLE}.customer_id)注意${TABLE}是当前模型的占位符编译时会被替换为模型对应的真实表名。指标类型全览仓库源码 field.ts 中的MetricType枚举定义了全部 17 种指标类型与文档表格一一对应聚合指标Aggregation Metrics类型说明是否需要 SQLcount统计全部行数否count_distinct统计唯一值数量否sum求和否sum_distinct按distinct_keys去重后求和否需要distinct_keys:average平均值否average_distinct按distinct_keys去重后求平均否需要distinct_keys:min最小值否max最大值否percentile百分位值否需要percentile:median中位数第 50 百分位否其中sum_distinct与average_distinct是处理宽表反规范化数据如订单总额被冗余到每一条明细行的关键能力后文单独展开。自定义 SQL 指标Custom SQL Metrics类型说明是否需要 SQLnumber自定义数值计算是string自定义字符串结果是date自定义日期结果是timestamp自定义时间戳结果是boolean自定义布尔结果是派生指标Derived Metrics类型说明percent_of_previous相对上一行的百分比变化环比percent_of_total占列总计的百分比running_total累计求和值得留意的是MetricType枚举中同时存在median与percentile两个独立类型说明仓库将其作为一等类型处理而非用通用 percentile 参数模拟。基础配置项基础配置metrics: total_revenue: type: sum label: Total Revenue description: Sum of all order amounts hidden: false对照 Schema 的LightdashMetric定义lightdashMetadata.json除type为必填外label、description、sql、hidden、round、compact、format、separator、group_label已废弃、urls、show_underlying_values、filters、percentile、distinct_keys、tags、default_time_dimension均为可选属性。格式化metrics: total_revenue: type: sum round: 2 # Decimal places format: usd # Format preset compact: millions # Compact display格式预设Format Presets来自 field.ts 的Format枚举货币usd、gbp、eur源码还额外支持jpy、dkk百分比percent距离km、miIDid不格式化其他si国际单位制紧凑显示Compact Options来自 Schema 的Compact枚举与 field.ts 的CompactConfigMap数字thousands、millions、billions、trillions也接受别名K、M、B、T字节kilobytes、megabytes、gigabytes、terabytes源码还支持petabytes以及二进制单位kibibytes、mebibytes、gibibytes、tebibytes、pebibytes及缩写KiB、MiB、GiB、TiB、PiB额外支持auto自动紧凑模式CompactConfigMap中每个配置还携带orderOfMagnitude数量级与convertFn换算函数例如millions的convertFn为value / 1000000展示层据此自动换算并追加M后缀。此外 Schema 还提供separator千分位分隔符配置取值为default、commaPeriod100,000.00、spacePeriod、periodComma100.000,00、noSeparatorPeriod、apostrophePeriod用于不同区域习惯的数字显示。百分位配置metrics: p95_response_time: type: percentile percentile: 95 # Required for percentile type label: P95 Response Timepercentile属性在 Schema 中定义为number类型仅对percentile类型指标必填。去重聚合配置Distinct Aggregationsum_distinct和average_distinct会先按一个或多个维度对行去重再对目标列聚合。典型场景是宽表订单总额被反规范化到每一行明细上如每条 line item 都带着订单总额此时希望每个order_id只被计入一次。distinct_keys接受单个维度引用或列表Schema 中定义为oneOf: string | array of stringcolumns: - name: order_total meta: dimension: type: number metrics: # Single key — sum each order_id once revenue: type: sum_distinct distinct_keys: order_id # Composite key — array form revenue_by_order_and_currency: type: sum_distinct distinct_keys: - order_id - currency_code # Same syntax for averages average_order_value: type: average_distinct distinct_keys: order_id也可以在模型级别配合自定义sql:使用不绑定单一列models: - name: order_lines meta: metrics: revenue: type: sum_distinct sql: ${TABLE}.order_total distinct_keys: order_id average_order_value: type: average_distinct sql: ${TABLE}.order_total distinct_keys: order_id指标过滤器Metric Filters为特定指标施加过滤条件用于创建指标的变体metrics: completed_orders: type: count label: Completed Orders filters: - status: completed # Multiple filter conditions high_value_completed: type: count filters: - status: completed - amount: 1000过滤器操作符相等value、value比较 100、 50、 10、 100多值[value1, value2]空值判断null、!null日期区间inThePast N days、inTheNext N months支持单位days、weeks、months、years日期区间的取值在源码 filter.ts 中得到印证IN_THE_PAST inThePast与IN_THE_NEXT inTheNext是过滤规则中的正式操作符。日期过滤示例metrics: recent_orders: type: count filters: - created_at: inThePast 30 days upcoming_renewals: type: count filters: - renewal_date: inTheNext 7 days重要限制inTheCurrent在指标定义过滤器metric definition filters中不是合法操作符它只存在于图表和仪表盘过滤器中。如果指标内需要当前周期逻辑应改用自定义 SQL 配合日期截断如DATE_TRUNC实现。显示底层明细值Show Underlying Values配置用户下钻drill into指标时展示的字段metrics: total_revenue: type: sum show_underlying_values: - order_id - customer_name - amount - created_atSchema 中该属性为字符串数组仅列出字段名即可。组织分组Organization用groups在侧边栏组织指标metrics: total_revenue: type: sum groups: - Revenue Metrics支持多层级分组按数组顺序形成层级metrics: total_revenue: type: sum groups: - Financial - Revenue注意group_label已废弃请改用groups。这一点在 Schema lightdashMetadata.json 中被显式标记为deprecated: true。URL 链接给指标值添加可点击链接metrics: order_count: type: count urls: - label: View Orders url: /orders?customer_id${row.customer_id}Schema 的Urls定义lightdashMetadata.json揭示其底层是Liquidjs 模板可用变量包括${value.raw}、${value.formatted}以及行级变量${row.table_name.field_name.raw}、${row.table_name.field_name.formatted}——这意味着 URL 可以携带任意查询中选中字段的原始值或格式化值。访问控制Access Control通过required_attributes限制谁能看到/使用该指标metrics: confidential_revenue: type: sum required_attributes: role: finance其语义是用户必须拥有role finance的用户属性才可见常配合用户属性实现行级/列级安全。Schema 中同时存在required_attributes必须满足与any_attributes满足任一即可两种定义。AI 提示AI Hintsmetrics: total_revenue: type: sum ai_hint: Primary revenue metric - use for financial reportingai_hint会进入语义层上下文帮助 AI 助手Lightdash AI 查询/Agent 场景正确选用指标避免语义相近的指标被误用。标签Tagsmetrics: total_revenue: type: sum tags: - finance - kpi标签可用于内容搜索与分类管理。默认时间维度Default Time Dimension将指标与某个时间维度关联作为该指标查询时的默认时间粒度metrics: total_revenue: type: sum default_time_dimension: field: created_at interval: MONTHSchema 的DefaultTimeDimension定义lightdashMetadata.json要求field与interval均为必填且interval只能是DAY、WEEK、MONTH、YEAR四者之一。自定义 SQL 指标实战模型级自定义指标models: - name: orders meta: metrics: # Revenue per customer revenue_per_customer: type: number sql: SUM(${TABLE}.amount) / NULLIF(COUNT(DISTINCT ${TABLE}.customer_id), 0) round: 2 format: usd # Conversion rate conversion_rate: type: number sql: COUNT(CASE WHEN ${TABLE}.status completed THEN 1 END)::float / NULLIF(COUNT(*), 0) * 100 round: 1 description: Percentage of orders that completed # Year-over-year growth (requires window functions) yoy_growth: type: number sql: | (SUM(${TABLE}.amount) - LAG(SUM(${TABLE}.amount)) OVER (ORDER BY DATE_TRUNC(year, ${TABLE}.created_at))) / NULLIF(LAG(SUM(${TABLE}.amount)) OVER (ORDER BY DATE_TRUNC(year, ${TABLE}.created_at)), 0) * 100要点使用NULLIF(x, 0)防御除零错误使用::float显式类型转换避免整数除法得到 0窗口函数如LAG可用于环比/同比计算但依赖数据仓库对窗口函数的支持。引用其他表通过表名引用实现跨表计算前提是模型间已建立 join 关系metrics: customer_order_total: type: number sql: SUM(${orders.amount}) # Reference joined table完整示例三种业务场景电商指标E-commercecolumns: - name: amount meta: dimension: type: number format: usd metrics: total_revenue: type: sum label: Total Revenue description: Sum of all order amounts format: usd round: 2 show_underlying_values: - order_id - customer_name - amount groups: - Revenue average_order_value: type: average label: Average Order Value description: Mean order amount format: usd round: 2 groups: - Revenue max_order: type: max label: Largest Order format: usd groups: - Revenue - name: order_id meta: dimension: type: string metrics: order_count: type: count label: Total Orders groups: - Volume unique_customers: type: count_distinct sql: ${TABLE}.customer_id label: Unique Customers groups: - VolumeSaaS 指标models: - name: subscriptions meta: metrics: mrr: type: sum sql: ${TABLE}.monthly_amount label: MRR description: Monthly Recurring Revenue format: usd compact: thousands arr: type: number sql: SUM(${TABLE}.monthly_amount) * 12 label: ARR description: Annual Recurring Revenue format: usd compact: millions churn_rate: type: number sql: | COUNT(CASE WHEN ${TABLE}.status churned THEN 1 END)::float / NULLIF(COUNT(*), 0) * 100 label: Churn Rate round: 2 description: Percentage of churned subscriptions avg_contract_value: type: average sql: ${TABLE}.contract_value label: ACV description: Average Contract Value format: usd columns: - name: customer_id meta: dimension: type: string metrics: customer_count: type: count_distinct label: Total Customers - name: contract_value meta: dimension: type: number format: usd metrics: total_contract_value: type: sum median_contract_value: type: median p90_contract_value: type: percentile percentile: 90营销指标Marketingmodels: - name: campaigns meta: metrics: total_spend: type: sum sql: ${TABLE}.spend format: usd total_conversions: type: sum sql: ${TABLE}.conversions cpa: type: number sql: SUM(${TABLE}.spend) / NULLIF(SUM(${TABLE}.conversions), 0) label: Cost Per Acquisition format: usd round: 2 roas: type: number sql: SUM(${TABLE}.revenue) / NULLIF(SUM(${TABLE}.spend), 0) label: ROAS description: Return on Ad Spend round: 2 ctr: type: number sql: SUM(${TABLE}.clicks)::float / NULLIF(SUM(${TABLE}.impressions), 0) * 100 label: Click-Through Rate round: 2 description: Percentage of impressions that resulted in clicks最佳实践清单优先使用列级指标将指标绑定到其来源数据列便于维护与理解添加有意义的描述帮助使用者理解计算口径设置合理的精度通常 02 位小数使用格式预设保证指标展示风格统一配置show_underlying_values提供有用的下钻能力用groups归类指标支持层级分组处理除零自定义 SQL 中使用NULLIF用过滤器创建变体同一口径的不同筛选版本添加 AI 提示帮助 AI 助手正确选用指标显式类型转换如::float确保聚合结果符合预期。验证与部署闭环指标写完后应纳入与 skills/developing-in-lightdash/SKILL.md 一致的开发流程编辑dbt 项目修改models/*.yml元数据在meta:下dbt Fusion / dbt 1.10 在config: meta:下纯 Lightdash 项目修改lightdash/models/*.yml属性置于顶层验证纯 Lightdash 项目运行lightdash lintdbt 项目运行dbt compile二者都会按上文引用的 JSON Schema 校验指标类型、必填字段与取值枚举部署lightdash deploy将语义层指标、维度同步到目标项目测试复杂改动先用lightdash preview --name my-feature创建隔离项目验证再回归。从 MetricType 枚举 到 LightdashMetric Schema仓库源码完整固化了文档中每一处 YAML 语法——写出的每一个type、format、compact、distinct_keys都有确定取值与校验约束这正是本文所述指标定义可以直接落地、可 lint、可部署的原因。【免费下载链接】lightdashAgentic BI. Analytics at the speed of code ⚡️项目地址: https://gitcode.com/GitHub_Trending/li/lightdash创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考