Howdy - I have the following query that works when a single device is selected from the dashboard variable drop-down, but the Graph view is incorrect when I select multiple devices.
SELECT
$__time(t.recorded),
d.hostname AS metric,
((t.values ->> 'use')::bigint - LAG((t.values ->> 'use')::bigint) OVER (ORDER BY t.recorded)) * .00000028 * 60 / (900 / 60) AS value
FROM telemetry t
INNER JOIN device d ON d.id = t.device_id
WHERE
$__timeFilter(t.recorded) AND
t.device_id IN ($device)
Based upon the results when switching to the Table view, it appears that the resulting rows aren’t grouped by the selected devices(s). The problem is, when I add:
GROUP BY t.device_id
pg complains about columns in the SELECT statement not appearing in the GROUP By, despite t.device_id being the only column I want to group by. I was previously using InfluxDB which made this very operation quite easy in Grafana, but I had to switch to TimescaleDB to utilize JOINs in certain queries.
Any help would be greatly appreciated.
SQL queries are different than for specialized time series databases like InfluxDB. However, Timescale has some extra functions that can help (and there is a tutorial).
Once you add a group by then you change from just selecting raw data to aggregating the rows together. You will have to decide if you want to average or sum (or some other aggregation) the values together. See the intro to time series for more on this concept.
If you start grouping by device then you also need to start grouping by time (per minute or per hour or some other time interval) and aggregate the value. Something like this might work (the time_bucket function is a timescale function):
SELECT
time_bucket('5 minutes', t.recorded) AS "time",
t.device_id AS metric,
avg(((t.values ->> 'use')::bigint - LAG((t.values ->> 'use')::bigint) OVER (ORDER BY t.recorded)) * .00000028 * 60 / (900 / 60)) AS value
FROM telemetry t
INNER JOIN device d ON d.id = t.device_id
WHERE
$__timeFilter(t.recorded) AND
t.device_id IN ($device)
GROUP BY time, metric
ORDER BY time
Thanks for the reply! I’ll spend some time with the links you provided and see if I can’t get a little closer to a solution. I tried the snippet you pasted and was met with:
pq: aggregate function calls cannot contain window function calls
OK I figured it out. I was close the first time, and the only thing I needed to add was a PARTITION BY statement to group the window function results:
SELECT
$__time(t.recorded),
d.hostname AS metric,
((t.values ->> 'use')::bigint - LAG((t.values ->> 'use')::bigint) OVER (PARTITION BY d.hostname ORDER BY t.recorded)) * .00000028 * 60 / (900 / 60) AS value
FROM telemetry t
INNER JOIN device d ON d.id = t.device_id
WHERE
$__timeFilter(t.recorded) AND
t.device_id IN ($devices)
