在BigQuery中使用LEAD函数

Question

假设我的表结构是这样的。

我计划按照“用户”和“序列”进行分组，并获取下一个序列的“LEAD时间戳”。这是我要查找的输出结果。

如果可能的话，我能否使用LEAD函数而不使用JOIN来解决这个问题？

- phaigeim

Answer 1

以下内容适用于 BigQuery Standard SQL。

我将提供两个选项 - 使用 JOIN（只是为了证明我正确理解/反向工程预期的逻辑），然后是无需 JOIN 的版本（请注意，我使用 ts 作为字段名，而不是 timestamp）。

使用 JOIN

#standardSQL
SELECT a.user, a.sequence, MIN(b.ts) ts 
FROM (
  SELECT user, sequence, MAX(ts) AS max_ts
  FROM `project.dataset.table`
  GROUP BY user, sequence
) a
LEFT JOIN `project.dataset.table` b
ON a.user = b.user AND b.sequence = a.sequence + 1
WHERE a.max_ts <= IFNULL(b.ts, a.max_ts)
GROUP BY user, sequence
-- ORDER BY user, sequence

无需JOIN的版本

#standardSQL
SELECT
  user, sequence, 
  (
    SELECT ts FROM UNNEST(arr_ts) ts 
    WHERE max_ts < ts ORDER BY ts LIMIT 1
  ) ts
FROM (
  SELECT
    user, sequence, max_ts,
    LEAD(arr_ts) OVER (PARTITION BY user ORDER BY sequence) arr_ts
  FROM (
  SELECT 
      user, sequence, MAX(ts) max_ts, 
      ARRAY_AGG(ts ORDER BY ts) arr_ts
    FROM `project.dataset.table`
    GROUP BY user, sequence
  )
)
-- ORDER BY user, sequence   

以下是可用于测试/播放上述两个版本的虚拟数据：

WITH `project.dataset.table` AS (
  SELECT 'user1' user, 2 sequence, 'T1' ts UNION ALL
  SELECT 'user1', 2, 'T2' UNION ALL
  SELECT 'user1', 1, 'T3' UNION ALL
  SELECT 'user1', 1, 'T4' UNION ALL
  SELECT 'user1', 3, 'T5' UNION ALL
  SELECT 'user1', 2, 'T6' UNION ALL
  SELECT 'user1', 3, 'T7' UNION ALL
  SELECT 'user1', 3, 'T8' 
)   

以下两个操作均会返回以下结果：

user    sequence    ts   
user1   1           T6   
user1   2           T7   
user1   3           null     

- Mikhail Berlyant

---

谢谢你分享这个。这太棒了。你能否请检查一下 https://stackoverflow.com/q/47819258/3879625 - phaigeim

你能在这里帮我吗？https://stackoverflow.com/questions/51153124/lead-analytical-functions-in-bigquery - phaigeim

@phaigeim - 当然。已回答。你可以查看一下：o) - Mikhail Berlyant

Answer 2

不确定关于bigquery，但一般在SQL中会这样写:

select user, sequence, LEAD (max_timestamp,1) OVER (PARTITION BY user ORDER BY sequence) as timestamp
from (
    select user, sequence, max(timestamp) as max_timestamp
    from table
    group by user, sequence) q1;

请注意保留字，例如table、user、timestamp等。

编辑：是的，请忘记这个答案，我没有注意到所需输出的要求。Mikhail翻译得很对！

- Edgars T.

---

@phaigeim - 我认为这不是正确的答案 - 至少它返回的不是问题中期望的结果。 - Mikhail Berlyant

@Edgars，你能否请检查一下这个链接：https://stackoverflow.com/questions/51153124/lead-analytical-functions-in-bigquery - phaigeim