在LookML中基于时间与值条件计算百分比值的技术问询

需求说明 #

需要计算的百分比公式：
(满足customer=2且时间戳在指定范围内的行数) / (customer=2的总行数)

现有问题 #

尝试使用SAFE_DIVIDE创建单个度量时报错，示例错误代码如下：

measure: customer_divided {
    type: number
    value_format_name: percent_1
    view_label: "view_label text"
    label: "label text"
    description: "descr text"
    sql:SAFE_DIVIDE(${Filtered_timeframe_value},(${TABLE}.customer="2")+;;
}

报错原因：

• 分母语法错误，缺少闭合括号，且未使用聚合函数统计行数
• 若Filtered_timeframe_value未做聚合处理，会导致数据类型不匹配

可行解决方案 #

方案1：单个度量直接实现 #

用COUNT_IF完成条件计数，在一个度量内完成所有计算，代码简洁：

measure: customer_2_timeframe_percentage {
    type: number
    value_format_name: percent_1
    view_label: "view_label text"
    label: "指定时间范围customer=2占比"
    description: "计算指定时间范围内customer=2行数占所有customer=2行数的百分比"
    sql: SAFE_DIVIDE(
        COUNT_IF(${TABLE}.customer = '2' AND ${TABLE}.timestamp BETWEEN {% date_start %} AND {% date_end %}),
        COUNT_IF(${TABLE}.customer = '2')
    ) ;;
}

说明：

• COUNT_IF自动统计符合条件的行数，避免手动聚合错误
• {% date_start %}和{% date_end %}对应Looker报表中选择的时间范围，若用自定义过滤字段，替换为对应条件即可
• SAFE_DIVIDE处理分母为0的情况，避免报错

方案2：拆分多个度量（更易调试复用） #

将分子、分母拆分为独立的求和度量，再计算百分比，便于单独验证数据：

1. 统计指定时间范围+customer=2的行数

measure: customer_2_filtered_count {
    type: sum
    value_format_name: number_0
    label: "指定时间范围customer=2行数"
    sql: CASE WHEN ${TABLE}.customer = '2' AND ${TABLE}.timestamp BETWEEN {% date_start %} AND {% date_end %} THEN 1 ELSE 0 END ;;
}

2. 统计所有customer=2的总行数

measure: customer_2_total_count {
    type: sum
    value_format_name: number_0
    label: "customer=2总行数"
    sql: CASE WHEN ${TABLE}.customer = '2' THEN 1 ELSE 0 END ;;
}

3. 计算百分比

measure: customer_2_percentage {
    type: number
    value_format_name: percent_1
    label: "customer=2时间范围占比"
    sql: SAFE_DIVIDE(${customer_2_filtered_count}, ${customer_2_total_count}) ;;
}

说明：

• 拆分后可单独查看分子、分母的数值，快速排查数据问题
• 若后续需要复用这两个计数度量，拆分方式更灵活

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