如何在LookML中计算同时发生登录与购买事件的去重用户数

我有一个针对BigQuery表events的LookML视图，目前可查看任意日期内发生购买或登录事件的去重用户数；但我需要统计同一日期内既发生购买又发生登录事件的用户数量。我想知道是否可以通过求distinct_purchase_users_list和distinct_login_users_list这两个数组度量的交集，来统计每日或每月同时产生这两类事件的用户数。

以下示例中，若创建包含date维度和distinct_login_and_purchase_users度量的探索，用户ID#1因在10/1同时有登录和购买行为，应被计入该度量。

示例数据 #

现有LookML视图代码 #

view: user_events {
  sql_table_name: `project.dataset.events` ;; 

  dimension: date {
    type: date
    sql: ${TABLE}.date ;; 
  }

  dimension: month {
    type: date
    sql: DATE_TRUNC(${TABLE}.date, MONTH) ;; 
  }

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

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

  measure: distinct_purchase_users {
    type: count_distinct
    sql: CASE WHEN ${event_name} = 'purchase' THEN ${user_id} END ;; 
  }

  measure: distinct_login_users {
    type: count_distinct
    sql: CASE WHEN ${event_name} = 'login' THEN ${user_id} END ;; 
  }

  measure: distinct_login_and_purchase_users {
    type: count_distinct
    sql: ??? ;; 
  }

  measure: distinct_purchase_users_list {
    type: string
    sql: ARRAY_AGG(CASE WHEN ${event_name} = 'purchase' THEN ${user_id} END) ;; 
  }

  measure: distinct_login_users_list {
    type: string
    sql: ARRAY_AGG(CASE WHEN ${event_name} = 'login' THEN ${user_id} END) ;; 
  }
}

方法一：直接用SQL条件筛选（推荐，性能更优） #

不需要依赖数组交集，直接通过EXISTS子查询或聚合条件筛选出同时存在两类事件的用户：

方式1：使用EXISTS子查询

measure: distinct_login_and_purchase_users {
  type: count_distinct
  sql: 
    ${user_id} 
    WHERE EXISTS (
      SELECT 1 FROM ${TABLE} AS e2 
      WHERE e2.user_id = ${TABLE}.user_id 
        AND e2.date = ${TABLE}.date 
        AND e2.event_name = 'login'
    )
    AND EXISTS (
      SELECT 1 FROM ${TABLE} AS e3 
      WHERE e3.user_id = ${TABLE}.user_id 
        AND e3.date = ${TABLE}.date 
        AND e3.event_name = 'purchase'
    ) ;;
}

方式2：使用聚合条件判断

measure: distinct_login_and_purchase_users {
  type: count_distinct
  sql: 
    CASE 
      WHEN COUNT(DISTINCT CASE WHEN ${event_name} = 'login' THEN ${event_name} END) = 1 
        AND COUNT(DISTINCT CASE WHEN ${event_name} = 'purchase' THEN ${event_name} END) = 1 
      THEN ${user_id} 
    END ;;
}

方法二：通过数组交集实现（不推荐，性能较差） #

如果一定要用数组交集的方式，需要先对数组去重，再计算交集的长度：

首先修改数组度量，确保数组内无重复值：

measure: distinct_purchase_users_list {
  type: string
  sql: ARRAY_AGG(DISTINCT CASE WHEN ${event_name} = 'purchase' THEN ${user_id} END) ;; 
}

measure: distinct_login_users_list {
  type: string
  sql: ARRAY_AGG(DISTINCT CASE WHEN ${event_name} = 'login' THEN ${user_id} END) ;; 
}

然后实现交集计数：

measure: distinct_login_and_purchase_users {
  type: number
  sql: 
    ARRAY_LENGTH(
      ARRAY(
        SELECT DISTINCT user_id FROM UNNEST(${distinct_purchase_users_list}) AS user_id
        INTERSECT DISTINCT
        SELECT DISTINCT user_id FROM UNNEST(${distinct_login_users_list}) AS user_id
      )
    ) ;;
}

方法对比 #

• 方法一：直接基于原表数据筛选，避免数组的额外计算，在数据量大时性能更稳定。
• 方法二：依赖数组操作，需要先聚合再拆分计算交集，数据量较大时会增加查询开销，仅适合小数据集场景。

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