sql - 在 bigQuery 中迭代表行

Question

我正在评估 bigQuery。我有一张桌子，可以为我们的员工捕捉动作。

Table (location_history):

emp String
lat String
lng String
district String
timestamp timestamp

样本：

emp  | lat     | lng    | district| timestamp
------------------------------------------------          
emp1 | 100.234 | 99.456 | ABC  | 2021-02-09 03:00:00
emp1 | 100.234 | 99.456 | XYZ  | 2021-02-09 04:00:00
emp1 | 100.234 | 99.456 | ABC  | 2021-02-09 05:00:00
emp1 | 100.234 | 99.456 | XYZ  | 2021-02-09 06:00:00
emp1 | 100.234 | 99.456 | XYZ  | 2021-02-09 07:00:00
emp1 | 100.234 | 99.456 | ABC  | 2021-02-09 08:00:00
emp1 | 100.234 | 99.456 | WWW  | 2021-02-09 09:00:00

注意：这是每个员工在地区内的移动。员工可以按照时间戳以外的顺序从一个地区移动到另一个地区。此外，即使一个地区也可能有数百个重复（相同）的行，因为我们捕获了每个 lng,lat（地区是一个多边形，超区域）。

我需要的是跟踪访问每个地区的每位员工的历史记录。所以我可以知道哪个员工整天在哪个区。这可以是整个月的轨道。我们在上表中得到的行（每天）超过 1000 亿。

两个问题：

1. 在任何存储过程（无论任何数据库）中处理大量行是一个好主意吗？与语言相比，SP 中的性能因素是什么（像 java 这样的服务器端，如果忽略获取和插入，而只关注迭代或操作行/结果集）
2. BigQuery 是否允许对行进行迭代（使用 select 语句从表中）？否则会有什么解决方案？

提前致谢。

更新： 预期结果是计算员工在每个地区花费的时间。流将执行：

1. 查询以使用 (select * from location_history order by emp,district) 获取行
2. 然后遍历每一行 cox 每隔一行取决于前一行来计算在特定区域花费的时间

更新 2： 这应该分别计算每个员工的持续时间（将它们分组），而不是根据其他员工活动计算。因此，每个员工的每个第一个活动都将具有空/零持续时间，如果找到的下一个活动将从前一行进行计算。希望这是有道理的。

标签： sqldatabasestored-proceduresgoogle-cloud-platformgoogle-bigquery

Answer 1

以下是 BigQuery 标准 SQL

select emp, district, date,
  datetime_diff(
    date, 
    lag(date) over(partition by emp order by date), 
    minute
  ) minutes_in_district
from `project.dataset.table`    

如果适用于您问题中的样本数据 - 输出是

我假设您的日期列是 DATETIME 数据类型如果它是 TIMESTAMP 数据类型 - 您应该使用 timestamp_diff 函数而不是 datetime_diff