aws-iot - 如何查询物联网设备的总活跃时间？

问题描述

我是 SQL 和 AWS Timestream 的新手，我想编写一个查询，该查询将为我提供设备处于活动状态的总时间。然后，我想根据设备的 kWh 额定值将其转化为能源使用量。

数据点的时间间隔不是固定的。数据看起来像这样：

时间戳	活动（布尔）
1617697080 (10h18)	错误的
1617697920 (10h32)	真的
1617698280 (10h38)	false（活动 6 分钟）
1617699000 (10h50)	真的
1617699120 (10h52)	false（激活 2 分钟）
等等

在上面的总活跃时间是8分钟。

我想问的问题是这样的，

上个月（或其他时间段）的总活跃时间（能源使用量）
过去一个月每天的总活动时间（能源使用量）

什么查询会给我这个信息并容忍可变间隔？

有两条路我正在寻找但还没有完全弄清楚，

插入数据并填充值以获得具有一致间隔的新时间流（然后就像计算值一样简单），或者
使用一些日期/时间函数来查看数据点之间的时间戳并将其活动的总时间相加。

我一直在尝试获得插入数据的查询权，但尚未成功。我正在遵循 AWS Timestream SQL 文档中的模式，但还不太了解它。

我什至不知道从哪里开始或从哪里寻找对时间戳差异求和的示例。逻辑过程是这样的，

if (this_point == true) then
    total_active_time += next_point_timestamp - this_point_timestamp

可能的解决方案#1：插值

插值在一定范围内工作得很好，尽管它感觉对于解决方案来说有点过分了。设备的开/关分辨率为 10 秒，因此如果插值采样时间过大，数据就会开始出现偏差。这会在插值时间序列超过 10k 点时出现问题 - AWS Timestream 抛出错误“序列函数的结果不能超过 10000 个条目”。因此，在短时间内，这是可行的，但我想弄清楚一个月内的能源使用情况，那么它永远不会奏效（至少不是 10 秒的分辨率）。使用 1m 分辨率可以让我在 6 天以上的时间内工作的准确度足够高。可以在不同的日期范围内多次使用此查询来获取数据。

WITH active_timeseries AS (
SELECT time, COUNT_IF(measure_value::boolean) AS Active
FROM "my_db"."data"
WHERE measure_name = 'active'
  AND time > ago(6d)
GROUP BY time
), interpolated_timeseries AS (
SELECT INTERPOLATE_LOCF(
  CREATE_TIME_SERIES(time, Active),
      SEQUENCE(min(time), max(time), 1m)) AS interpolated_active
FROM active_timeseries
), new_timeseries AS (
SELECT time, value
FROM interpolated_timeseries
CROSS JOIN UNNEST(interpolated_active)
)
-- where "1" = 1kWh
SELECT bin(time, 1d) as binned_ts, COUNT_IF(value > 0) / 60.0 * 1 as Daily
FROM new_timeseries
GROUP BY bin(time, 1d)
ORDER BY binned_ts

可能的解决方案#2：计算时间差

在应用程序中，开和关时间可能有多个“开”和多个“关”样本。可以使用 LAG 函数确定从开到关和从关到开的转换样本。起初我无法让 LAG 和 LEAD 时间函数在时间戳上工作，但它突然似乎在我没有更改某些内容的情况下工作......不知道该怎么做。然后可以使用 LEAD 来确定时间间隔。把它们放在一起看起来像这样：

with active_timeseries AS (
  SELECT time, 
    measure_value::boolean as active, 
    LAG(measure_value::boolean, 1, NULL) OVER (ORDER BY time ASC) AS last_active
  FROM "my_db"."data"
  where measure_name = 'active'
    AND time > ago(6d)
  ORDER by time ASC
)
SELECT time, active, last_active,
  (LEAD(time, 1, NULL) OVER (ORDER BY time ASC) - time) AS time_interval
FROM active_timeseries
where (active = true AND last_active = false) OR
      (active = false AND last_active = true)

然后，这将为您提供一个看起来像这样的数据的间隔（但时间戳为原生“时间戳”类型）

时间戳	活动（布尔）	时间间隔
1617697080 (10h18)	错误的	840
1617697920 (10h32)	真的	360
1617698280 (10h38)	false（活动 6 分钟）	720
1617699000 (10h50)	真的	120
1617699120 (10h52)	false（激活 2 分钟）	等等
等等

这很棒！几乎是我正在寻找的......但现在我无法将时间间隔（“时间戳”类型）转换为可用的东西。我需要对其进行一些基本操作以将其转换为 kWh 使用量，例如，

kWh = number_of_seconds_active(s) / seconds_in_an_hour(s) * power(kW)

经过一些搅动和测试，我发现了 SQL EXTRACT()，它允许我提取天、小时、分钟和秒。所以我可以这样做：

with active_timeseries AS (
  SELECT time, 
    measure_value::boolean as active, 
    LAG(measure_value::boolean, 1, NULL) OVER (ORDER BY time ASC) AS last_active
  FROM "my_db"."data"
  where measure_name = 'active'
    AND time > ago(6d)
  ORDER by time ASC
), interval_timeseries AS (
SELECT time, active, last_active,
  (LEAD(time, 1, NULL) OVER (ORDER BY time ASC) - time) AS time_interval
FROM active_timeseries
where (active = true AND last_active = false) OR
      (active = false AND last_active = true)
)
SELECT time, active, last_active, time_interval,
  EXTRACT(hour from time_interval) * 3600 + EXTRACT(minute from time_interval) * 60 + EXTRACT(second from time_interval) as interval_time,
  (EXTRACT(hour from time_interval) * 3600 + EXTRACT(minute from time_interval) * 60 + EXTRACT(second from time_interval)) / 3600.0 * 3.0 as kWh
from interval_timeseries
where active = true

这给了我能源使用量！

标签： aws-iotamazon-timestream

aws-iot - 如何查询物联网设备的总活跃时间？

问题描述

解决方案

推荐阅读