How to downsample timeseries data with many columns in SQL?

Question

I am running a postgresql database (also with timescaledb, to be used in grafana), and have learned how to do downsampling of a simple table with the following:

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;

This code creates a VIEW with three sensors, and downsamples from a (e.g.) 1 second sample rate to a 1 minute sample rate.

This is all fine and good until I have a table with hundreds of columns that I wish to downsample. I don't want to have to write out this code with hundreds of averages appearing explicitly in the query for each sensor. I am hoping postgresql has a way that I can apply my averaging aggregation to all columns of the table at once.

I've googled quite some time for the answer, and this is the closest I could find, although is not quite the same question:

select aggregate function and all other columns

I've tried using the syntax avg(*), but received a syntax error.

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;

another attempt was

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;

which gave a syntax error.

I'm hoping there is a way to perform this query without having to write out a piece of code spanning hundreds of lines for each sensor. Thanks for any help.

score 0 · Answer 1 · answered Jun 07 '19 at 22:20

You can use the catalog table to generate the query and then execute it with \gexec in psql,

One example is

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

How to downsample timeseries data with many columns in SQL?

1 Answers1