如何在Looker中获取每位客户第10笔订单的创建时间？

问题场景 #

有一张包含所有客户订单及创建时间的数据表，需要制作可视化表格展示客户名称和其第10笔订单的创建时间。尝试用LookML的ROW_NUMBER()按客户分区后，通过filters过滤行号等于10的记录，但发现filters仅支持字符串类型，数字过滤无效，转换类型后也没解决。

原代码片段：

dimension: row_num {
    type: number
    sql: ROW_NUMBER() OVER(PARTITION BY origin_name ORDER BY order_created_at_myt);;
}

dimension_group: tenth_order {
    type: time
    timeframes: [
      raw,
      time_of_day,
      time,
      date,
      week,
      month,
      quarter,
      year
    ]
    datatype: datetime
    sql: ${order_created_at_myt_date}
    filters: [row_num: 10];;
}

可行解决方案 #

方法1：在维度SQL中直接用CASE WHEN筛选 #

不用依赖filters，直接在时间维度的SQL里判断行号是否为10，只返回符合条件的订单时间，否则返回NULL。

修改后的LookML代码：

dimension: row_num {
    type: number
    sql: ROW_NUMBER() OVER(PARTITION BY origin_name ORDER BY order_created_at_myt);;
}

dimension_group: tenth_order {
    type: time
    timeframes: [raw, time_of_day, time, date, week, month, quarter, year]
    datatype: datetime
    sql: CASE WHEN ${row_num} = 10 THEN ${order_created_at_myt_date} ELSE NULL END ;;
}

之后在探索界面，添加origin_name和tenth_order维度，再过滤tenth_order IS NOT NULL，就能只显示有第10笔订单的客户及其对应时间。

方法2：用衍生表预计算提升性能 #

如果数据量较大，预计算可以加快查询速度。创建一个衍生表，直接筛选出每个客户的第10笔订单：

view: customer_tenth_order {
  derived_table: {
    sql:
      SELECT
        origin_name,
        order_created_at_myt AS tenth_order_time
      FROM (
        SELECT
          origin_name,
          order_created_at_myt,
          ROW_NUMBER() OVER(PARTITION BY origin_name ORDER BY order_created_at_myt) AS row_num
        FROM your_order_table
      ) ranked_orders
      WHERE row_num = 10
    ;;
  }

  dimension: origin_name {
    type: string
    sql: ${TABLE}.origin_name ;;
  }

  dimension_group: tenth_order_time {
    type: time
    timeframes: [raw, time_of_day, time, date, week, month, quarter, year]
    datatype: datetime
    sql: ${TABLE}.tenth_order_time ;;
  }
}

直接用这个视图做可视化，就能得到客户和第10笔订单时间，也可以关联原表获取其他字段。

方法3：探索层直接过滤 #

保留原来的row_num维度，在探索界面添加origin_name、order_created_at_myt和row_num，设置过滤条件row_num = 10，把order_created_at_myt作为第10笔订单时间展示。这种方法不用改LookML，适合快速验证需求。

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