时间戳

首页 > 解决方案 > 如何引用 BigQuery SQL 中先前行的列值，以便执行操作或计算？

sql - 如何引用 BigQuery SQL 中先前行的列值，以便执行操作或计算？

问题描述

我已经按开始时间对数据进行了排序，并且我想创建一个新字段来汇总与前几行开始时间和结束时间重叠的开始时间的数据。

更具体地说，我想编写逻辑，对于给定的记录 X，如果开始时间介于上一行的开始和结束时间之间，我想为记录 X 赋予与前一行相同的新字段值. 如果开始时间发生在前一行的结束时间之后，它将为新字段获得一个新值。

BigQuery SQL 中是否有可能发生这样的事情？正在考虑可能是滞后或窗口功能，但不太确定。下面是基本表的示例以及我想要的最终表的示例。

原始表

所需输出表

任何见解表示赞赏！

标签： sqlgoogle-bigquery

解决方案

以下是 BigQuery 标准 SQL 

#standardSQL
SELECT recordID, startTime, endTime,
  COUNTIF(newRange) OVER(ORDER BY startTime) AS newRecordID
FROM (
  SELECT *, 
    startTime >= MAX(endTime) OVER(ORDER BY startTime ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS newRange
  FROM `project.dataset.table`
)

您可以使用您问题中的示例数据进行测试，使用上面的示例数据，如下例所示 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 recordID, TIME '12:35:00' startTime, TIME '12:50:00' endTime UNION ALL
  SELECT 2, '12:46:00', '12:59:00' UNION ALL
  SELECT 3, '14:27:00', '16:05:00' UNION ALL
  SELECT 4, '15:48:00', '16:35:00' UNION ALL
  SELECT 5, '16:18:00', '17:04:00' 
)
SELECT recordID, startTime, endTime,
  COUNTIF(newRange) OVER(ORDER BY startTime) AS newRecordID
FROM (
  SELECT *, 
    startTime >= MAX(endTime) OVER(ORDER BY startTime ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS newRange
  FROM `project.dataset.table`
)
-- ORDER BY startTime

结果 

Row recordID    startTime   endTime     newRecordID  
1   1           12:35:00    12:50:00    0    
2   2           12:46:00    12:59:00    0    
3   3           14:27:00    16:05:00    1    
4   4           15:48:00    16:35:00    1    
5   5           16:18:00    17:04:00    1

推荐阅读