database - 如何在 SQL 中对具有多列的时间序列数据进行下采样?
问题描述
我正在运行一个 postgresql 数据库(也使用 timescaledb,用于 grafana),并且已经学习了如何使用以下方法对简单表进行下采样:
CREATE VIEW my_view
WITH (timescaledb.continuous) --Makes the view continuous
AS
SELECT
time_bucket('1 min', time) as bucket,
avg(sensor1),
avg(sensor2),
avg(sensor3)
FROM
my_table
GROUP BY bucket;
此代码创建一个具有三个传感器的 VIEW,并从(例如)1 秒采样率下采样到 1 分钟采样率。
这一切都很好,直到我有一个包含数百列的表,我希望对其进行下采样。我不想写出这段代码,数百个平均值显式出现在每个传感器的查询中。我希望 postgresql 有一种方法可以一次将我的平均聚合应用于表的所有列。
我已经用谷歌搜索了很长时间的答案,这是我能找到的最接近的答案,尽管不是完全相同的问题:
我尝试使用语法 avg(*),但收到语法错误。
CREATE VIEW my_view
WITH (timescaledb.continuous) --Makes the view continuous
AS
SELECT
time_bucket('1 min', time) as bucket,
avg(sensor1),
avg(sensor2),
avg(sensor3)
FROM
my_table
GROUP BY bucket;
另一种尝试是
CREATE VIEW my_view
WITH (timescaledb.continuous) --Makes the view continuous
AS
SELECT
time_bucket('1 min', time) as bucket,
avg(*)
FROM
my_table
GROUP BY bucket;
这给出了语法错误。
我希望有一种方法可以执行此查询,而不必为每个传感器写出跨越数百行的代码。谢谢你的帮助。
解决方案
您可以使用目录表生成查询,然后在 psql 中使用 \gexec 执行它,
一个例子是
with avgs as (
select string_agg('avg('||attname||')', ', ') as avg_text
from pg_attribute where attrelid ='my_table'::regclass
)·
select format(
$$·
CREATE VIEW my_view
WITH (timescaledb.continuous) --Makes the view continuous
AS
SELECT
time_bucket('1 min', time) as bucket,
%s
FROM my_table
GROUP BY bucket;
$$,
avg_text)
FROM avgs
\gexec
推荐阅读
- javascript - Node JS 会话错误:不推荐使用 express-session
- mysql - SQL有没有办法选择作为给定值开头的所有值?
- face-recognition - 地标检测与人脸识别
- javascript - 如何返回对象名称JS?
- flutter - Flutter:如何在点击 ListTile 后使用 showDialog 提示用户输入,然后使用输入
- flutter - 行内的 FutureBuilder 颤动
- xml - 如何将 Shopify 网站库存与 POS 系统库存集成?
- c# - 将 BackgroundWorker 转换为 Task,最佳实践?
- javascript - 获取表格中 td 的值
- statistics - Octave中求解算法多项式回归,最小二乘法的问题