Search code examples
sqlpostgresqlaggregate-functionsgrafanawindow-functions

Grafana, postgresql: aggregate function calls cannot contain window function calls

In Grafana, we want to show bars indicating maximum of 15-minut averages in the choosen time interval. Our data has regular 1-minute intervals. The database is Postgresql.

To show the 15-minute averages, we use the following query:

SELECT
  timestamp AS time,
  AVG(rawvalue) OVER(ORDER BY timestamp ROWS BETWEEN 7 PRECEDING AND 7 FOLLOWING) AS value,
  '15-min Average' AS metric
FROM database.schema
WHERE $__timeFilter(timestamp) AND device = '$Device'
ORDER BY time

To show bars indicating maximum of raw values in the choosen time interval, we use the following query:

SELECT
  $__timeGroup(timestamp,'$INTERVAL') AS time,
  MAX(rawvalue) AS value,
  'Interval Max' AS metric
FROM database.schema
WHERE $__timeFilter(timestamp) AND device = '$Device'
GROUP BY $__timeGroup(timestamp,'$INTERVAL')
ORDER BY time

A naive combination of both solutions does not work:

SELECT
  $__timeGroup(timestamp,'$INTERVAL') AS time,
  MAX(AVG(rawvalue) OVER(ORDER BY timestamp ROWS BETWEEN 7 PRECEDING AND 7 FOLLOWING)) AS value,
  'Interval Max 15-min Average' AS metric
FROM database.schema
WHERE $__timeFilter(timestamp) AND device = '$Device'
GROUP BY $__timeGroup(timestamp,'$INTERVAL')
ORDER BY time

We get error: "pq: aggregate function calls cannot contain window function calls".

There is a suggestion on SO to use "with" (Count by criteria over partition) but I do not know hot to use it in our case.

Solution

  • Use the first query as a CTE (or with) for the second one. The order by clause of the CTE and the where clause of the second query as well as the metric column of the CTE are no longer needed. Alternatively you can use the first query as a derived table in the from clause of the second one.

    with t as
(
 SELECT
   timestamp AS time,
   AVG(rawvalue) OVER(ORDER BY timestamp ROWS BETWEEN 7 PRECEDING AND 7 FOLLOWING) AS value
 FROM database.schema
 WHERE $__timeFilter(timestamp) AND device = '$Device'
)
SELECT
  $__timeGroup(time,'$INTERVAL') AS time,
  MAX(value) AS value,
  'Interval Max 15-min Average' AS metric
FROM t
GROUP BY 1 ORDER BY 1;

    Unrelated but what are $__timeFilter and $__timeGroup? Their sematics are clear but where do they come from? BTW you may find this function useful.