BigQuery中用户设备前置使用统计的高效SQL实现问询

核心逻辑拆解 #

要解决这个问题，关键要抓住三个核心节点，全程利用BigQuery的分区特性避免全表扫描：

1. 过滤有效数据范围：只处理当日分区的用户，以及这些用户过去6个月内的设备使用记录，同时剔除deviceId IS NULL的无效行
2. 提取关键时间锚点：对每个用户+设备组合，获取用户首次使用该设备的时间（这是判断其他用户使用时间的锚点）
3. 高效匹配符合条件的设备：对每个设备，检查是否存在其他用户在目标用户首次使用该设备的前12个月内使用过它，避免冗余计算

优化后的SQL代码 #

WITH
-- 1. 限定时间范围，获取当日分区的用户集合，以及这些用户过去6个月的有效设备使用记录
user_device_history AS (
  SELECT
    userId,
    deviceId,
    -- 获取该用户首次使用此设备的时间
    MIN(ds) OVER(PARTITION BY userId, deviceId) AS first_use_dt
  FROM
    `your-project.your-dataset.your-table`
  WHERE
    -- 只扫描当日分区 + 过去6个月的分区，利用分区剪枝
    _PARTITIONDATE BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH) AND CURRENT_DATE()
    AND deviceId IS NOT NULL
    -- 仅保留当日分区存在的用户（减少后续计算量）
    AND userId IN (SELECT DISTINCT userId FROM `your-project.your-dataset.your-table` WHERE _PARTITIONDATE = CURRENT_DATE())
),

-- 2. 去重：每个用户+设备只保留一条首次使用记录
user_device_first_use AS (
  SELECT DISTINCT
    userId,
    deviceId,
    first_use_dt
  FROM user_device_history
),

-- 3. 预计算每个设备的所有非目标用户的使用时间范围（提前过滤掉目标用户自己的记录）
device_other_users_usage AS (
  SELECT
    deviceId,
    userId AS other_userId,
    ds AS usage_dt
  FROM
    `your-project.your-dataset.your-table`
  WHERE
    deviceId IS NOT NULL
    -- 只需要扫描目标用户首次使用设备前12个月的分区，动态限定范围
    AND _PARTITIONDATE BETWEEN DATE_SUB((SELECT MIN(first_use_dt) FROM user_device_first_use), INTERVAL 12 MONTH) AND CURRENT_DATE()
)

-- 4. 最终统计：对每个用户，计算符合条件的设备数量
SELECT
  u.userId,
  COUNT(DISTINCT CASE
    WHEN EXISTS (
      SELECT 1
      FROM device_other_users_usage d
      WHERE d.deviceId = u.deviceId
        AND d.other_userId != u.userId
        AND d.usage_dt BETWEEN DATE_SUB(u.first_use_dt, INTERVAL 12 MONTH) AND DATE_SUB(u.first_use_dt, INTERVAL 1 DAY)
    ) THEN u.deviceId
    ELSE NULL
  END) AS qualified_device_count
FROM user_device_first_use u
GROUP BY u.userId

关键优化点说明 #

1. 分区剪枝（Partition Pruning）：全程用_PARTITIONDATE限定扫描的分区范围，绝对避免扫描全表，这是处理大表的核心优化手段
2. 提前过滤无效数据：一开始就剔除deviceId IS NULL的行，同时只保留当日分区存在的用户，减少后续计算的数据量
3. 去重减少计算：通过user_device_first_use去重用户+设备的首次使用记录，避免重复处理同一用户的同一设备
4. 半连接替代全关联：用EXISTS做半连接判断，比JOIN后再去重的效率高得多，因为找到匹配项就停止扫描
5. 动态时间范围限定：在device_other_users_usage中，用目标用户最早的首次使用时间来限定扫描的分区，进一步缩小数据范围

针对示例场景的验证 #

以你给出的12月1日场景：

• 用户A的user_device_first_use记录是(A, device1, 6月1日)、(A, device2, 9月1日)
• 检查device1：存在用户B在6月1日-12个月到6月1日-1天（5月31日）之间使用过，符合条件；用户C的使用时间是8月2日，在A首次使用device1之后，不符合
• 检查device2：无其他用户使用，不符合
• 最终统计结果为1，和示例一致

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