datetime - 如何在 bigquery 中获取活动信号的持续时间？

Question

我有一个带有各种信号和 utc 时间戳的 bigquery 表。我希望将“传感器”列的值大于 1 的工作时间作为查询结果。

我的想法是以某种方式获取 Sensor > 1 的时间戳的最小值和最大值，并从 max(timestamp) 中减去 min(timestamp) 并将其聚合为 Sensor > 1 的新列中的工作时间。

timestamp,Sensor
2020-05-13 08:46:29 UTC,0.76306754
2020-05-13 08:46:28 UTC,0.76306754
2020-05-13 08:46:27 UTC,0.76306754
2020-05-13 08:46:26 UTC,0.76306754
2020-05-13 08:46:25 UTC,0.76306754
2020-05-13 08:46:24 UTC,0.76306754
2020-05-13 08:46:23 UTC,0.76306754
2020-05-13 08:46:22 UTC,1.4879817
2020-05-13 08:46:21 UTC,1.4879817
2020-05-13 08:46:20 UTC,1.4879817
2020-05-13 08:45:58 UTC,0.6486074
2020-05-13 08:45:57 UTC,0.6486074
2020-05-13 08:45:56 UTC,0.6486074
2020-05-13 08:45:55 UTC,0.6486074
2020-05-13 08:45:54 UTC,0.6486074
2020-05-13 08:45:53 UTC,0.6486074
2020-05-13 08:45:52 UTC,0.6486074
2020-05-13 08:45:51 UTC,0.6486074
2020-05-13 08:45:50 UTC,0.6486074
2020-05-13 08:45:49 UTC,0.6486074
2020-05-13 08:45:48 UTC,0.6486074
2020-05-13 08:45:47 UTC,0.6486074
2020-05-13 08:45:46 UTC,0.6486074
2020-05-13 08:45:45 UTC,0.6486074
2020-05-13 08:45:44 UTC,0.6486074
2020-05-13 08:45:43 UTC,0.6486074
2020-05-13 08:45:42 UTC,0.6486074
2020-05-13 08:45:41 UTC,0.6486074
2020-05-13 08:45:40 UTC,0.6486074
2020-05-13 08:45:39 UTC,0.6486074
2020-05-13 08:45:38 UTC,1.2972147
2020-05-13 08:45:37 UTC,1.2972147
2020-05-13 08:45:36 UTC,1.2972147
2020-05-13 08:45:35 UTC,1.2972147
2020-05-13 08:45:34 UTC,1.2972147
2020-05-13 08:45:33 UTC,1.2972147
2020-05-13 08:45:15 UTC,0.76306754
2020-05-13 08:45:14 UTC,0.76306754
2020-05-13 08:45:13 UTC,0.76306754
2020-05-13 08:45:12 UTC,0.76306754
2020-05-13 08:45:11 UTC,0.76306754
2020-05-13 08:45:10 UTC,0.76306754
2020-05-13 08:45:09 UTC,0.76306754
2020-05-13 08:45:08 UTC,0.76306754
2020-05-13 08:45:07 UTC,0.76306754
2020-05-13 08:45:06 UTC,0.76306754
2020-05-13 08:45:05 UTC,0.76306754
2020-05-13 08:45:04 UTC,1.4879817
2020-05-13 08:45:03 UTC,1.4879817
2020-05-13 08:45:02 UTC,1.4879817
2020-05-13 08:45:01 UTC,1.4879817
2020-05-13 08:44:42 UTC,0.5723007
2020-05-13 08:44:41 UTC,0.5723007
2020-05-13 08:44:40 UTC,0.5723007
2020-05-13 08:44:39 UTC,0.5723007
2020-05-13 08:44:38 UTC,0.5723007
2020-05-13 08:44:37 UTC,0.5723007
2020-05-13 08:44:36 UTC,0.5723007
2020-05-13 08:44:35 UTC,0.5723007
2020-05-13 08:44:34 UTC,0.5723007
2020-05-13 08:44:33 UTC,0.5723007
2020-05-13 08:44:32 UTC,1.5642884
2020-05-13 08:44:31 UTC,1.5642884
2020-05-13 08:44:30 UTC,1.5642884
2020-05-13 08:44:19 UTC,0.5723007
2020-05-13 08:44:18 UTC,0.5723007
2020-05-13 08:44:17 UTC,0.5723007
2020-05-13 08:44:16 UTC,0.5723007
2020-05-13 08:44:15 UTC,0.5723007
2020-05-13 08:44:14 UTC,0.5723007
2020-05-13 08:44:13 UTC,0.5723007
2020-05-13 08:44:12 UTC,0.5723007
2020-05-13 08:44:11 UTC,0.5723007
2020-05-13 08:44:10 UTC,1.4879817
2020-05-13 08:44:09 UTC,1.4879817
2020-05-13 08:44:08 UTC,1.4879817
2020-05-13 08:43:53 UTC,0.5723007
2020-05-13 08:43:52 UTC,0.5723007
2020-05-13 08:43:51 UTC,0.5723007
2020-05-13 08:43:50 UTC,0.5723007
2020-05-13 08:43:49 UTC,0.5723007
2020-05-13 08:43:48 UTC,0.5723007
2020-05-13 08:43:47 UTC,0.5723007
2020-05-13 08:43:46 UTC,0.5723007
2020-05-13 08:43:45 UTC,0.5723007
2020-05-13 08:43:44 UTC,1.5642884
2020-05-13 08:43:43 UTC,1.5642884
2020-05-13 08:43:42 UTC,1.5642884
2020-05-13 08:43:25 UTC,1.4879817
2020-05-13 08:43:24 UTC,1.4879817
2020-05-13 08:43:08 UTC,1.2590615
2020-05-13 08:43:07 UTC,1.2590615
2020-05-13 08:43:06 UTC,1.2590615
2020-05-13 08:42:50 UTC,1.2590615
2020-05-13 08:42:49 UTC,1.2590615
2020-05-13 08:42:48 UTC,1.2590615
2020-05-13 08:42:47 UTC,1.2590615
2020-05-13 08:42:34 UTC,0.53414726
2020-05-13 08:42:33 UTC,0.53414726
2020-05-13 08:42:32 UTC,0.53414726

不幸的是，我没有找到任何信息如何获取传感器大于 1 的时间戳的最小（时间）和最大（时间）并将时间戳加重到工作时间（持续时间）。我想我需要以任何方式使用 minif 和 maxif。

你能帮我或给我一点小费吗？

最好的问候，斯科蒂

标签： datetimegoogle-bigqueryduration

Answer 1

这是一个“间隙和孤岛”解决方案，它使用一系列 CTE 对传感器值 >= 1 的每组行的最小和最大时间戳之间的时间求和。第一个 CTE 生成一个标志以指示“正在运行”（ sensor >= 1) 和整个数据集以及依赖于“Operating”的数据的行号。在第二个 CTE 中，减去行号以形成值组（传感器 >= 1 或 < 1）。此 CTE 过滤掉传感器值 < 1 的组。在第三个 CTE 中，计算每个组的最小和最大时间戳之间的差异（以秒为单位），并在最终查询中对这些差异求和以给出总数工作时间。

WITH CTE AS (
  SELECT timestamp,
         CASE WHEN Sensor >= 1 THEN 1 ELSE 0 END AS Operating,
         ROW_NUMBER() OVER (ORDER BY timestamp DESC) rn,
         ROW_NUMBER() OVER (PARTITION BY CASE WHEN Sensor >= 1 THEN 1 ELSE 0 END ORDER BY timestamp DESC) rn1
  FROM data
),
CTE2 AS (
  SELECT timestamp, 
         Operating,
         rn - rn1 AS grp
  FROM CTE
  WHERE Operating = 1
),
CTE3 AS (
  SELECT DATEDIFF(SECOND, MIN(timestamp), MAX(timestamp)) AS OpTime
  FROM CTE2
  GROUP BY grp
)
SELECT SUM(OpTime) AS OperatingTime
FROM CTE3

输出（用于您的样本数据）：

OperatingTime
71

SQLFiddle 上的演示

笔记

上面的查询是用 MSSQL 编写的，以便进行演示。bigquery 需要的唯一更改是更改：

DATEDIFF(SECOND, MIN(timestamp), MAX(timestamp))

至

DATETIME_DIFF(MAX(timestamp), MIN(timestamp), SECOND)

或者可能

TIMESTAMP_DIFF(MAX(timestamp), MIN(timestamp), SECOND)

取决于您的列数据类型。