LookML中基于切换按钮实现分组图表的问题排查与修复

我拥有如下数据集：

希望创建一个切换按钮，实现按Age或Date对Amount进行分组统计，但当前编写的LookML代码存在问题：即使选择单个分组字段，SQL仍会同时调用两个字段，导致图表报错。以下是我尝试的代码：

仪表盘文件代码 #

- name: Group By
    title: Group By
    type: field_filter
    default_value: ''
    allow_multiple_values: false
    required: true
    ui_config:
      type: button_toggles
      display: inline
      options: [Age,Date]
    model: amount_summary
    explore: amount_summary_analysis
    listens_to_filters: []
    field: amount_summary_analysis.Group_By

视图文件代码 #

SELECT  {% if Group_By._parameter_value == 'Age' %}
            Age,
{% elsif Group_By._parameter_value == 'Date' %}
Date,
{%endif%}
COUNT(Amount) AS count_of_people

 FROM amount_table
GROUP BY 
{% if Group_By._parameter_value == 'Age' %}
            Age,
{% elsif Group_By._parameter_value == 'Date' %}
Date,
{%endif%}

parameter: Group_By {
    type: unquoted
    allowed_value: { value: "Date"}
    allowed_value: { value: "Age" }
  }
dimension: Age {
    type: Number
    sql: ${TABLE}.Age ;; 
  }
dimension: Date {
    type: date
    sql: ${TABLE}.Date ;; 
  }
dimension: count_of_people {
    type: number
    sql: ${TABLE}.count_of_people ;; 
  }
measure: percentage_amt {
    label: "Percentage Amt"
    type: number
    sql: {% if Group_By._parameter_value == 'Date' %}
        ROUND(100*IFNULL(${Amount}/NULLIF(${count_of_people},0),0), 2)
        {% endif %};;
    required_fields: [Date]
measure: percentage_amt_age {
    label: "Percentage Amt"
    type: number
    sql: {% if Group_By._parameter_value == 'Age' %}
        ROUND(100*IFNULL(${Amount}/NULLIF(${count_of_people},0),0), 2)
        {% endif %};;
    required_fields: [Age]

原代码的核心问题是混淆了原生SQL写法与LookML规范，同时required_fields强制引入未选中的字段，导致SQL冗余。以下是修复后的代码：

修复后的视图文件 #

view: amount_summary_analysis {
  sql_table_name: amount_table ;;

  parameter: group_by {
    type: unquoted
    allowed_value: { value: "Date" }
    allowed_value: { value: "Age" }
    default_value: "Date"
  }

  dimension: age {
    type: number
    sql: ${TABLE}.Age ;;
    hidden: {% if group_by._parameter_value != 'Age' %}yes{% endif %} ;;
  }

  dimension: date {
    type: date
    sql: ${TABLE}.Date ;;
    hidden: {% if group_by._parameter_value != 'Date' %}yes{% endif %} ;;
  }

  measure: count_of_people {
    type: count_distinct
    sql: ${Amount} ;;
  }

  measure: percentage_amt {
    label: "Percentage Amt"
    type: number
    sql: ROUND(100*IFNULL(${Amount}/NULLIF(${count_of_people},0),0), 2) ;;
    required_fields: [{% if group_by._parameter_value == 'Date' %}date{% else %}age{% endif %}]
  }
}

修复后的仪表盘文件 #

- name: Group_By
  title: 分组依据
  type: field_filter
  default_value: "Date"
  allow_multiple_values: false
  required: true
  ui_config:
    type: button_toggles
    display: inline
    options: ["Age", "Date"]
  model: amount_summary
  explore: amount_summary_analysis
  field: amount_summary_analysis.group_by

关键修复点 #

1. 移除原生SELECT语句，改用LookML标准视图结构，通过sql_table_name指定数据源
2. 给分组参数设置默认值，避免空值导致SQL生成错误
3. 利用hidden属性动态隐藏未选中的维度，确保SQL仅包含当前分组字段
4. 合并重复度量，通过参数动态控制required_fields，只引入当前分组所需字段
5. 统一命名规范为小写，符合LookML惯例

内容的提问来源于stack exchange，提问作者skye