如何在大查询中获取活动信号的持续时间?

Question

我有一个包含各种信号和UTC时间戳的大查询表。我想将"传感器"列的值大于 1 的工作时间作为查询结果。

我的想法是以某种方式获取传感器> 1 的时间戳的最小值和最大值，并从最大值(时间戳(中减去最小(时间戳(，并将其聚合为新列中传感器> 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。

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

此致敬意 斯科蒂

Answer 1

我能够编写一个查询来实现您的目标。为此，我使用了 MAX(( 和 MIN(( 内置函数来获取特定传感器时间戳的最大值和最小值。这两个函数都可以与除ARRAY STRUCT以外的任何数据类型一起使用。除此之外，TIMESTAMP_DIFF(( 还用于检索两个时间戳之间的差异。

我必须指出，我将您在问题中提供的数据上传到表格中，以便我可以复制案例。下面是对 StandardSQL 的查询：

SELECT Sensor, MAX(timestamp) AS max_ts , 
MIN(timestamp) AS min_ts, 
TIMESTAMP_DIFF(MAX(timestamp), MIN(timestamp), SECOND) AS duration_ 
FROM `project_id.dataset.table` 
WHERE Sensor > 1
GROUP BY Sensor

而输出，

Row Sensor    max_ts                  min_ts                  duration_seconds
1   1.2972147 2020-05-13 08:45:38 UTC 2020-05-13 08:45:33 UTC 5
2   1.4879817 2020-05-13 08:46:22 UTC 2020-05-13 08:43:24 UTC 178
3   1.2590615 2020-05-13 08:43:08 UTC 2020-05-13 08:42:47 UTC 21
4   1.5642884 2020-05-13 08:44:32 UTC 2020-05-13 08:43:42 UTC 50

请注意，在TIMESTAMP_DIFF()内必须指定日期部分，在本例中，我选择了 SECONDS。虽然，这里还有其他可用的日期部分。