0

I have a Timescale DB / PostgreSQL table (DataTable) which looks like this

DataTable example

I'm trying to find a query which would return different columns for different subsystem/metrics combination like this

Query Result

Furthermore I would like to reduce the amount of requested data points using the Timescale DB function time_bucket_gapfill() to keep the response time low for big time ranges. I would also like to use different aggregation functions for different subsystem/metric combinations. I use the queried data to plot the curves in Grafana. What is the most time efficient way to achieve that?

At the moment I use separate queries for each metric which quickly gets very slow. For the example the queries could look like that

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

I would much appreciate if somebody could point me in the right direction.

Update:

Using the FILTER clause as suggested by @Charlieface doesn't work for me, because it doesn't remove the other rows but instead places NULLs there. Here an example with some real data (the example before was using simplified data and names).

enter image description here

I also noticed that the timestamps of the metrics belonging together have tiny differences which would prevent different metrics being on the same row, but that is easy to changes in the unit which feeds the data into the DB.

Update 2:

The timestamps issue was fixed and the WHERE statement was added again as suggested by @Charlieface, but there are still NULL in the data which makes Grafana plot the data points without lines in between.

enter image description here

I would like the result to look like this instead.

enter image description here

Update 3:

I missed that @Charlieface answer only groups by time. If I do that, then I get the result I wanted.

1 Answer 1

1

You can use conditional aggregation for this.

In PostgreSQL you can use the FILTER clause.

In other DBMSs you can use a condition inside the aggregation
avg(case when ... then value end)

SELECT
    time_bucket_gapfill('30s', time, start => '2021-07-19T09:06:26.605Z', finish => '2021-07-19T20:11:12.340Z') AS "time",
    max(value) FILTER (WHERE subsystem = 'position' AND metric = 'lat') AS "Latitude",
    min(value) FILTER (WHERE subsystem = 'position' AND metric = 'lon') AS "Longitude",
    avg(value) FILTER (WHERE subsystem = 'health' AND metric = 'temperature') AS "Temperature"
FROM DataTable
WHERE
    time BETWEEN '2021-07-19T09:06:26.605Z' AND '2021-07-19T20:11:12.340Z'
  AND (subsystem = 'position' AND metric IN ('lat', 'lon') OR
       subsystem = 'health' AND metric = 'temperature')
GROUP BY 1
ORDER BY time;
5
  • The FILTER clause doesn't work for me, because it adds NULL for all subsystem/metrics rows which were filtered out (see "Update" in my Question). The NULLs in the data causes Grafana to plot data points not connected by lines. Jul 30, 2021 at 5:58
  • Oh then you need to add the WHERE filters back in, but include all of the ones you want together. Have modified Jul 30, 2021 at 8:12
  • There are still NULL in the query reply (see "Update 2" in my question). Jul 30, 2021 at 13:44
  • You haven't copied my query properly, I only have GROUP BY 1 not GROUP BY 1, metric, subsystem. This is obviously dependent on those timestamps being exact;y identical Jul 30, 2021 at 15:39
  • Oh, I missed that. Grouping only by time gives me the exact query result I wanted. Thank you very much. This was very helpful. Jul 31, 2021 at 13:24

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .