title: How to downsample timeseries data with many columns in SQL?
body: <p>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:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>This code creates a VIEW with three sensors, and downsamples from a (e.g.) 1 second sample rate to a 1 minute sample rate.</p>
  
  <p>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.</p>
  
  <p>I've googled quite some time for the answer, and this is the closest I could find, although is not quite the same question:</p>
  
  <p><a href="https://stackoverflow.com/questions/39611982/select-aggregate-function-and-all-other-columns">select aggregate function and all other columns</a></p>
  
  <p>I've tried using the syntax avg(*), but received a syntax error.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>another attempt was</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>which gave a syntax error.</p>
  
  <p>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.</p>