Back to Details

lookml-fields

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Instructions

使用说明

1. Field Types Overview

1. 字段类型概述

LookML fields are the building blocks of your data model. Each type serves a specific purpose in generating SQL.
Field TypePurposeSQL Generation Phase
DimensionDescribes data (attributes). Groups results.
SELECT
and 
GROUP BY
clause.
MeasureAggregates data (metrics). Calculates results.
SELECT
clause (with aggregation).
FilterRestricts data based on conditions.
WHERE
or 
HAVING
clause (via templated filters).
ParameterCaptures user input for dynamic logic.None directly (injects values into other fields).
Dimension GroupGenerates a set of time-based dimensions.
SELECT
and 
GROUP BY
clause (multiple columns).
LookML字段是数据模型的构建模块。每种类型在生成SQL时都有特定用途。
字段类型用途SQL生成阶段
Dimension描述数据（属性），对结果进行分组。
SELECT
和 
GROUP BY
子句。
Measure聚合数据（指标），计算结果。
SELECT
子句（包含聚合函数）。
Filter根据条件限制数据。
WHERE
或 
HAVING
子句（通过模板过滤器）。
Parameter捕获用户输入以实现动态逻辑。无直接生成（将值注入其他字段）。
Dimension Group生成一组基于时间的维度。
SELECT
和 
GROUP BY
子句（多列）。

2. The Role of 
sql
Parameter

2. 
sql
参数的作用

The 
sql
parameter behaves differently strictly based on the field type.
sql
参数的行为严格取决于字段类型。

Dimensions: The "What"

Dimension：“描述内容”

  • Role: Defines the raw transformation of the column before any aggregation.
  • SQL Context: The expression is placed directly into the 
    GROUP BY
    clause.
  • Input: Can reference table columns (
    ${TABLE}.col
    ), other dimensions (
    ${dim}
    ), or raw SQL functions.
  • Example:
    lookml
    dimension: full_name {
  sql: CONCAT(${first_name}, ' ', ${last_name}) ;;
}
-- Generates: CONCAT(table.first_name, ' ', table.last_name)
  • 作用：定义列在聚合前的原始转换。
  • SQL上下文：表达式直接放入
    GROUP BY
    子句中。
  • 输入：可引用表列（
    ${TABLE}.col
    ）、其他Dimension（
    ${dim}
    ）或原生SQL函数。
  • 示例：
    lookml
    dimension: full_name {
  sql: CONCAT(${first_name}, ' ', ${last_name}) ;;
}
-- 生成SQL：CONCAT(table.first_name, ' ', table.last_name)

Measures: The "How Much"

Measure：“统计数值”

  • Role: Defines the value to be aggregated or the calculation involving other aggregates.
  • SQL Context: Puts the expression inside the aggregation function (e.g., 
    SUM(sql)
    ), or as a standalone calculation for 
    type: number
    .
  • Input:
    • For 
      type: sum/avg/min/max
      : References dimensions or columns.
    • For 
      type: number
      : References other measures.
    • For 
      type: count
      : 
      sql
      is ignored (always 
      COUNT(*)
      or 
      COUNT(primary_key)
      ).
  • Example:
    lookml
    measure: total_profit {
  type: sum
  sql: ${sale_price} - ${cost} ;; 
}
-- Generates: SUM(sale_price - cost)
  • 作用：定义要聚合的值，或涉及其他聚合结果的计算。
  • SQL上下文：将表达式放入聚合函数内部（例如
    SUM(sql)
    ），或作为
    type: number
    类型的独立计算。
  • 输入：
    • 对于
      type: sum/avg/min/max
      ：引用Dimension或列。
    • 对于
      type: number
      ：引用其他Measure。
    • 对于
      type: count
      ：
      sql
      参数会被忽略（始终为
      COUNT(*)
      或
      COUNT(primary_key)
      ）。
  • 示例：
    lookml
    measure: total_profit {
  type: sum
  sql: ${sale_price} - ${cost} ;; 
}
-- 生成SQL：SUM(sale_price - cost)

Filters: The "Which"

Filter：“筛选范围”

  • Role: Defines the condition logic, usually for Templated Filters used in Derived Tables or 
    sql_always_where
    .
  • SQL Context: The 
    sql
    parameter in a 
    filter
    field is rarely used directly in modern LookML. Instead, the input to the filter is used in 
    {% condition %}
    tags.
  • Best Practice: Identify if you need a 
    filter
    field or just a 
    parameter
    + 
    dimension
    .
  • Example (Templated Filter):
    lookml
    filter: date_filter { type: date }
-- Usage in Derived Table SQL:
-- WHERE {% condition date_filter %} created_at {% endcondition %}
  • 作用：定义条件逻辑，通常用于派生表或
    sql_always_where
    中的模板过滤器。
  • SQL上下文：在现代LookML中，
    filter
    字段的
    sql
    参数很少直接使用。而是使用过滤器的输入值放入
    {% condition %}
    标签中。
  • 最佳实践：确定你需要的是
    filter
    字段，还是仅需
    parameter
    + 
    dimension
    组合。
  • 示例（模板过滤器）：
    lookml
    filter: date_filter { type: date }
-- 在派生表SQL中的用法：
-- WHERE {% condition date_filter %} created_at {% endcondition %}

Parameters: The "User Input"

Parameter：“用户输入”

  • Role: Does NOT generate SQL itself. It holds a user-selected value to be injected into other fields.
  • SQL Context: Accessed via Liquid variables (
    {% parameter name %}
    ) inside Dimensions, Measures, or Derived Tables.
  • Input: User selects from a UI list or types a value.
  • Example:
    lookml
    parameter: timeframe_selector {
  type: unquoted
  allowed_value: { value: "month" }
  allowed_value: { value: "year" }
}
dimension: dynamic_date {
  sql: DATE_TRUNC({% parameter timeframe_selector %}, ${created_raw}) ;;
}
  • 作用：本身不生成SQL。它存储用户选择的值，以便注入到其他字段中。
  • SQL上下文：通过Liquid变量（
    {% parameter name %}
    ）在Dimension、Measure或派生表中访问。
  • 输入：用户从UI列表中选择或输入值。
  • 示例：
    lookml
    parameter: timeframe_selector {
  type: unquoted
  allowed_value: { value: "month" }
  allowed_value: { value: "year" }
}
dimension: dynamic_date {
  sql: DATE_TRUNC({% parameter timeframe_selector %}, ${created_raw}) ;;
}

Dimension Groups: The "Time Generator"

Dimension Group：“时间生成器”

  • Role: Defines the source timestamp or date column. Looker then generates multiple dimension fields based on the 
    timeframes
    list.
  • SQL Context: Casts and truncates the source column for each timeframe.
  • Input: Must be a standardized timestamp or date expression.
  • Example:
    lookml
    dimension_group: created {
  type: time
  timeframes: [date, month]
  sql: ${TABLE}.created_at ;;
}
-- Generates:
-- created_date -> CAST(table.created_at AS DATE)
-- created_month -> DATE_TRUNC(table.created_at, MONTH)
  • 作用：定义源时间戳或日期列。Looker会基于
    timeframes
    列表生成多个维度字段。
  • SQL上下文：为每个时间范围转换并截断源列。
  • 输入：必须是标准化的时间戳或日期表达式。
  • 示例：
    lookml
    dimension_group: created {
  type: time
  timeframes: [date, month]
  sql: ${TABLE}.created_at ;;
}
-- 生成字段：
-- created_date -> CAST(table.created_at AS DATE)
-- created_month -> DATE_TRUNC(table.created_at, MONTH)

3. Summary of Differences

3. 差异总结

Type
sql
references...		Can reference Measures?Aggregated?
DimensionColumns, Other DimensionsNONo
Measure (Agg)Columns, DimensionsNOYes
Measure (Num)Other MeasuresYESYes (already agg)
Filter(Rarely used)NoN/A
Parameter(None)NoN/A
Value Format(None)NoN/A
类型
sql
引用对象...		可引用Measure？是否聚合？
Dimension列、其他Dimension否否
Measure（聚合型）列、Dimension否是
Measure（数值型）其他Measure是是（已聚合）
Filter（极少使用）否不适用
Parameter（无）否不适用
Value Format（无）否不适用

Reference Skills

参考技能

For detailed standards on specific field types, refer to:
  • Dimensions: Naming, labels, and type-specific rules.
  • Measures: Aggregation types, filters, and formats.
  • Filters & Parameters: Templated filters and user input.
  • Dimension Groups: Timeframes and intervals.
  • Value Formats: Named and custom currency/number formats.
如需了解特定字段类型的详细标准，请参考：
  • Dimensions：命名、标签及类型特定规则。
  • Measures：聚合类型、过滤器及格式。
  • Filters & Parameters：模板过滤器与用户输入。
  • Dimension Groups：时间范围与间隔。
  • Value Formats：命名及自定义货币/数字格式。