如何从InfluxDB获取时间间隔值报告
使用 InfluxDB：是否有任何方法可以构建表示随时间持续存在的状态的字段值的分时段报告？理想情况下使用 InfluxQL 查询语言
更具体地举个例子：测量包含报告灯泡状态（开/关）变化的点。它们可以是 0 和 1（如下例所示），也可以是任何其他值。例如：
time light
---- -----
2022-03-18T00:00:00Z 1
2022-03-18T01:05:00Z 0
2022-03-18T01:55:00Z 0
2022-03-18T02:30:00Z 1
2022-03-18T04:06:00Z 0
结果应该是一个间隔列表，指示该灯在每个时间间隔（例如小时）内是否打开或关闭，或者该灯打开的时间百分比。对于给定的示例，如果按小时分组，结果应为：
| Hour | Value |
|---|---|
| 2022-03-18 00:00 | 1.00 |
| 2022-03-18 01:00 | 0.17 |
| 2022-03-18 02:00 | 0.50 |
| 2022-03-18 03:00 | 1.00 |
| 2022-03-18 04:00 | 0.10 |
请注意：
- 对于凌晨 1 点时段，即使灯以开启状态开始和结束，在 60 分钟内仅开启 10 次，因此该值较低 (10/60)
- ，更重要的是从凌晨 3 点到凌晨 04:00 的时段凌晨 4 点的值为“1”，因为灯自上一个时间段以来一直亮着，即使此时间段内没有任何变化。这排除了在
GROUP BY TIME()上使用简单聚合（例如MEAN）的可能性，因为没有任何方法可以知道空/丢失的存储桶是否对应于On 或 Off 状态，因为它仅取决于该时间段之前的最后报告值。
有没有一种方法可以在纯 InfluxQL 中实现它，而无需检索潜在的大数据集（点）并在客户端中迭代它们？
Using InfluxDB: Is there any way to build a time-bucketed report of a field value representing a state that persists over time? Ideally in InfluxQL query language
More specifically as an example: Say a measurement contains points that report changes in the light bulb state (On / Off). They could be 0s and 1s as in the example below, or any other value. For example:
time light
---- -----
2022-03-18T00:00:00Z 1
2022-03-18T01:05:00Z 0
2022-03-18T01:55:00Z 0
2022-03-18T02:30:00Z 1
2022-03-18T04:06:00Z 0
The result should be a listing of intervals indicating if this light was on or off during each time interval (e.g. hours), or what percentage of that time it was on. For the given example, the result if grouping hourly should be:
| Hour | Value |
|---|---|
| 2022-03-18 00:00 | 1.00 |
| 2022-03-18 01:00 | 0.17 |
| 2022-03-18 02:00 | 0.50 |
| 2022-03-18 03:00 | 1.00 |
| 2022-03-18 04:00 | 0.10 |
Note that:
- for 1am bucket, even if the light starts and ends in On state, it was On for only 10 over 60 minutes, so the value is low (10/60)
- and more importantly the bucket from 3am to 4am has value "1" as the light was On since the last period, even if there was no change in this time period. This rules out usage of simple aggregation (e.g.
MEAN) over aGROUP BY TIME(), as there would not be any way to know if an empty/missing bucket corresponds to an On or Off state as it only depends on the last reported value before that time bucket.
Is there a way to implement it in pure InfluxQL, without retrieving potentially big data sets (points) and iterating through them in a client?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我认为原始数据可以通过查询获得：
其中“test3”是您的测量名称，$timeFilter 是从...到...时间段。
在这种情况下，我们需要使用一个子查询来填充我们的数据，让我们将分组（解析）时间视为 1 秒：
此查询每 1 秒为我们提供 1/0 值。我们将使用它作为子查询。
注意：您应该被告知，这种方式不考虑 $timeFilter 内数据周期的开始是否是在灯亮或灭的情况下开始的。这种方式不会提供 $timeFilter 内任何值之前的任何数据。
在下一步中，您应该对从子查询获得的数据使用积分（）函数，如下所示：
这就是它在图表上的样子：
结果数据的外观一个 桌子：
这不是让它工作的完美方法，但我希望它能解决您的问题。
I consider that raw data could be obtained by query:
Where "test3" is your measurement name and $timeFilter is from... to... time period.
In this case we need to use a subquery which will fill our data, let's consider grouping (resolution) time as 1s:
This query gives us 1/0 value every 1s. We will use it as a subquery.
NOTE: You should be informed that this way does not consider if beginning of data period within $timeFilter has been started with light on or off. This way will not provide any data before hour with any value within $timeFilter.
In next step you should use integral() function on data you got from subquery, like this:
This is how it looks on charts:
And how Result data looks in a table:
This is not a perfect way of getting it to work but I hope it resolves your problem.