主页 / user-213518

Markus Wiedemann's questions

Martin Hope
Markus Wiedemann
Asked: 2021-07-30 03:26:05 +0800 CST
  • 0

我有一个 Timescale DB / PostgreSQL 表（DataTable），看起来像这样

数据表示例

我正在尝试找到一个查询，该查询将为不同的子系统/指标组合返回不同的列，如下所示

查询结果

此外，我想使用 Timescale DB 函数 time_bucket_gapfill() 减少请求的数据点的数量，以在大时间范围内保持较低的响应时间。我还想为不同的子系统/指标组合使用不同的聚合函数。我使用查询的数据在 Grafana 中绘制曲线。实现这一目标最省时的方法是什么？

目前，我对每个指标使用单独的查询，这很快就会变得非常慢。例如，查询可能看起来像这样

SELECT
    time_bucket_gapfill('30s',time,start=>'2021-07-19T09:06:26.605Z',finish=>'2021-07-19T20:11:12.340Z') AS "time",
    max(value) AS "Latitude"
FROM DataTable
WHERE
    time BETWEEN '2021-07-19T09:06:26.605Z' AND '2021-07-19T20:11:12.340Z' AND
    subsystem = 'position' AND
    metric = 'lat'
GROUP BY 1,metric,subsystem
ORDER BY time

SELECT
    time_bucket_gapfill('30s',time,start=>'2021-07-19T09:06:26.605Z',finish=>'2021-07-19T20:11:12.340Z') AS "time",
    min(value) AS "Longitude"
FROM DataTable
WHERE
    time BETWEEN '2021-07-19T09:06:26.605Z' AND '2021-07-19T20:11:12.340Z' AND
    subsystem = 'position' AND
    metric = 'lon'
GROUP BY 1,metric,subsystem
ORDER BY time

SELECT
    time_bucket_gapfill('30s',time,start=>'2021-07-19T09:06:26.605Z',finish=>'2021-07-19T20:11:12.340Z') AS "time",
    avg(value) AS "Temperature"
FROM DataTable
WHERE
    time BETWEEN '2021-07-19T09:06:26.605Z' AND '2021-07-19T20:11:12.340Z' AND
    subsystem = 'health' AND
    metric = 'temperature'
GROUP BY 1,metric,subsystem
ORDER BY time

如果有人能指出我正确的方向，我将不胜感激。

更新：

使用@Charlieface 建议的 FILTER 子句对我不起作用，因为它不会删除其他行，而是将 NULL 放在那里。这是一个包含一些真实数据的示例（之前的示例使用了简化的数据和名称）。

在此处输入图像描述

我还注意到，属于一起的指标的时间戳有微小的差异，这会阻止不同的指标位于同一行，但这很容易在将数据输入数据库的单元中进行更改。

更新 2：

时间戳问题已修复，并按照@Charlieface 的建议再次添加了 WHERE 语句，但数据中仍然存在 NULL，这使得 Grafana 绘制数据点之间没有线条。

在此处输入图像描述

我希望结果看起来像这样。

在此处输入图像描述

更新 3：

我错过了@Charlieface 只按时间分组回答。如果我这样做，那么我会得到我想要的结果。

postgresql pivot
Martin Hope
Markus Wiedemann
Asked: 2020-08-05 15:08:54 +0800 CST
  • 0

我有一个传感器在远程位置生成一个数据向量，比如说每秒（最多可调整到每秒 100 个向量）。该位置有 4G 调制解调器来发送数据。由于功率限制，我选择使用 Raspberry Pi 3+ 从传感器接收数据，对其进行解码并将其发送到我的 VPS 服务器。

在我运行 Ubuntu 18.04 的服务器上，我在 PostgreSQL 之上安装了 TimescaleDB，这是处理时间序列数据的一个很好的组合，我想将传感器数据存储在其中，然后在我的网站上发布。我很想听听您对数据流的看法，我有一些问题。

  1. 将传感器数据从 RPi 插入服务器数据库的最佳方法是什么？使用我的 C 软件中的 libpq 库直接从 RPi 插入它们是否可以保存，或者我应该以不同的方式将数据传输到服务器，然后将数据插入服务器端的数据库中？

  2. 在 RPi 方面，我希望有一个本地缓冲区，以防止与服务器的连接中断时丢失数据。我可以简单地在我的 C 程序或命名管道中使用循环缓冲区，或者我可以在本地安装 PostgreSQL/TimescaleDB，在那里插入数据，然后使用 postgres_fdw 或 dblink 将数据从那里传输到我的服务器 DB 并删除其中的条目我的本地数据库。你有什么建议？

postgresql remote