如何在BigQuery中高效使用LEAD()函数获取下一个不同标签值？

针对你提出的需求——为每条交易记录获取当前label以及跳过所有连续相同label后第一个不同的label，除了你提到的分组关联法，这里提供三种更直接的BigQuery实现方案：

方案1：连续相同Label分组 + 窗口函数（推荐，高性能） #

先为每个用户内连续的相同Label生成分组ID，再对分组使用LEAD()获取下一个分组的Label，最后将该Label同步到同组的所有记录。这种方案完全基于窗口函数，性能优异，适合大规模数据集。

示例代码 #

WITH transaction_data AS (
  -- 模拟测试数据，实际使用时替换为你的表名
  SELECT 'a' AS user, DATE('2024-01-01') AS transaction_date, 'x' AS label, 10 AS cost UNION ALL
  SELECT 'a' AS user, DATE('2024-01-02') AS transaction_date, 'x' AS label, 20 AS cost UNION ALL
  SELECT 'a' AS user, DATE('2024-01-03') AS transaction_date, 'y' AS label, 30 AS cost UNION ALL
  SELECT 'a' AS user, DATE('2024-01-04') AS transaction_date, 'y' AS label, 40 AS cost UNION ALL
  SELECT 'a' AS user, DATE('2024-01-05') AS transaction_date, 'z' AS label, 50 AS cost UNION ALL
  SELECT 'b' AS user, DATE('2024-01-01') AS transaction_date, 'm' AS label, 15 AS cost
),
label_grouped AS (
  SELECT
    *,
    -- 生成连续相同Label的分组ID：当前Label与前一条不同时，分组ID递增
    SUM(CASE WHEN LAG(label) OVER(PARTITION BY user ORDER BY transaction_date) != label THEN 1 ELSE 0 END)
      OVER(PARTITION BY user ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS label_group_id
  FROM transaction_data
),
group_next_label AS (
  SELECT
    *,
    -- 获取下一个分组的Label
    LEAD(label) OVER(PARTITION BY user ORDER BY label_group_id) AS label_next
  FROM label_grouped
)
SELECT
  user,
  transaction_date,
  cost,
  label,
  -- 将同组的label_next统一为分组对应的下一个Label
  FIRST_VALUE(label_next) OVER(PARTITION BY user, label_group_id ORDER BY transaction_date) AS label_next
FROM group_next_label
ORDER BY user, transaction_date;

方案2：关联子查询（逻辑直观，小数据集适用） #

对每条记录，通过关联子查询找到该用户当前交易日期之后，第一个与当前Label不同的记录，直接取其Label。逻辑简单易懂，但数据量较大时，子查询可能导致性能下降。

示例代码 #

WITH transaction_data AS (
  -- 模拟测试数据，实际使用时替换为你的表名
  SELECT 'a' AS user, DATE('2024-01-01') AS transaction_date, 'x' AS label, 10 AS cost UNION ALL
  SELECT 'a' AS user, DATE('2024-01-02') AS transaction_date, 'x' AS label, 20 AS cost UNION ALL
  SELECT 'a' AS user, DATE('2024-01-03') AS transaction_date, 'y' AS label, 30 AS cost UNION ALL
  SELECT 'a' AS user, DATE('2024-01-04') AS transaction_date, 'y' AS label, 40 AS cost UNION ALL
  SELECT 'a' AS user, DATE('2024-01-05') AS transaction_date, 'z' AS label, 50 AS cost UNION ALL
  SELECT 'b' AS user, DATE('2024-01-01') AS transaction_date, 'm' AS label, 15 AS cost
)
SELECT
  t1.user,
  t1.transaction_date,
  t1.cost,
  t1.label,
  (
    SELECT t2.label
    FROM transaction_data t2
    WHERE t2.user = t1.user
      AND t2.transaction_date > t1.transaction_date
      AND t2.label != t1.label
    ORDER BY t2.transaction_date ASC
    LIMIT 1
  ) AS label_next
FROM transaction_data t1
ORDER BY t1.user, t1.transaction_date;

方案3：QUALIFY + 关联过滤（写法简洁） #

利用BigQuery的QUALIFY子句，结合关联和行号过滤，直接定位每个记录对应的下一个不同Label。写法简洁，BigQuery对这类查询的优化较好。

示例代码 #

WITH transaction_data AS (
  -- 模拟测试数据，实际使用时替换为你的表名
  SELECT 'a' AS user, DATE('2024-01-01') AS transaction_date, 'x' AS label, 10 AS cost UNION ALL
  SELECT 'a' AS user, DATE('2024-01-02') AS transaction_date, 'x' AS label, 20 AS cost UNION ALL
  SELECT 'a' AS user, DATE('2024-01-03') AS transaction_date, 'y' AS label, 30 AS cost UNION ALL
  SELECT 'a' AS user, DATE('2024-01-04') AS transaction_date, 'y' AS label, 40 AS cost UNION ALL
  SELECT 'a' AS user, DATE('2024-01-05') AS transaction_date, 'z' AS label, 50 AS cost UNION ALL
  SELECT 'b' AS user, DATE('2024-01-01') AS transaction_date, 'm' AS label, 15 AS cost
)
SELECT
  t1.user,
  t1.transaction_date,
  t1.cost,
  t1.label,
  t2.label AS label_next
FROM transaction_data t1
LEFT JOIN transaction_data t2
  ON t2.user = t1.user
  AND t2.transaction_date > t1.transaction_date
  AND t2.label != t1.label
QUALIFY ROW_NUMBER() OVER(PARTITION BY t1.user, t1.transaction_date ORDER BY t2.transaction_date ASC) = 1
ORDER BY t1.user, t1.transaction_date;

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