Grafana dashboards combine two SQL queries-CodePudding

I have one Grafana dashboard with 2 queries that sum certain data from a postgres (timescale) db. Both queries are nearly identical, is it possible to combine these two queries into one as well as combining the sums?

Query one:

SELECT
    $__timeGroup(time, '1m'),
    SUM(value*2.119) as "Trane VAVs"
FROM 
    slipstream_volttron
WHERE
    $__timeFilter("time") AND
    (metric ~ 'slipstream_internal/slipstream_hq/.*/Discharge Air Flow$')
GROUP BY 1
ORDER BY 1

Query two:

SELECT
    $__timeGroup(time, '1m'),
    SUM(value) as "JCI VAVs"
FROM 
    slipstream_volttron
WHERE
    $__timeFilter("time") AND
    (metric ~ 'slipstream_internal/slipstream_hq/.*/SA-F$')
GROUP BY 1
ORDER BY 1

For example this screenshot is what it looks like hoping to combine these queries and SUMS where there would be only 1 line not two. One line of all of the data summed up.

CodePudding user response：

This can be accomplished in Postgres with an aggregate FILTER(). Something like:

SELECT
    $__timeGroup(time, '1m'),
    SUM(value*2.119) FILTER(WHERE metric ~ 'slipstream_internal/slipstream_hq/.*/Discharge Air Flow$') as "Trane VAVs",
    SUM(value) FILTER(WHERE metric ~ 'slipstream_internal/slipstream_hq/.*/SA-F$') as "JCI VAVs"
FROM 
    slipstream_volttron
WHERE
    $__timeFilter("time")
GROUP BY 1
ORDER BY 1

That said, without knowing the schema of slipstream_volttron (column types, indexes, etc.) there might be some additional efficiencies that would improve the query speed, etc. FILTER() is applied to the all rows that match the query predicates so if you have lots of raw data, you would try to add a WHERE clause on the main query that would use indexes to return fewer rows (in addition to the time column that you're filtering on)

Also, depending on what type of your metric column is, the cardinality, and how it's indexed, you could probably get better query performance with a few more tweaks. Regex is a powerful tool but not always the most efficient during real-time aggregation.

CodePudding user response：

In addition to Ryan's answer that uses a query to combine the two results, I want to share a way to accomplish your goal with Grafana.

You can use a Transformation. Go to the tab Transform, select Add field from calculation, and chose Mode: Binary operation. Then you can select your two query results, choose as operator, give an alias and choose to hide the inital fields if you want.