Search code examples
grafanainfluxdb

Sum of more queries in Influxdb - sum of averages

I am creating my Whole portfolio dashboard with influxdb and grafana and I am having a following problem when summing my sub portfolios:

enter image description here

Aka some of the values are getting doubled for no reason at all. The scraper I use to get data from websites runs once per hour. I have checked the data at visible times and they were not uploaded to database twice. (all group by timeframes are problematic) The solution could be summing averages for all assets by hour, which works completely ok:

enter image description here

My influxdb table is in structure as follows:

    time                              Amount    CZK_amount     Currency Portfolio
0  2020-07-11T00:01:07.386426Z         xxxxx Kč XXXXXXX.350000    CZK    Portu
1  2020-07-11T00:01:07.424887Z         $xxxxx   XXXXXX.614056     USD    Crypto
2  2020-07-11T00:01:07.435219Z         €xxxx    XXXXX.299440      EUR    Degiro
3  2020-07-11T00:01:07.447881Z          xxxx    XXXXX.000000      CZK    Zonky

Currently my query to calculate mean for single Portfolio by hour looks like this:

SELECT mean("CZK_amount") FROM "autogen"."Portfolio_status" WHERE ("Portfolio" = 'Crypto') AND $timeFilter GROUP BY time(1h) fill(linear)

Is there a way to write a query that would return a result of sum of all Portfolios?

SELECT mean("CZK_amount") FROM "autogen"."Portfolio_status" WHERE ("Portfolio" = 'Portu') AND $timeFilter GROUP BY time(1h) fill(linear)

SELECT mean("CZK_amount") FROM "autogen"."Portfolio_status" WHERE ("Portfolio" = 'Zonky') AND $timeFilter GROUP BY time(1h) fill(linear)

SELECT mean("CZK_amount") FROM "autogen"."Portfolio_status" WHERE ("Portfolio" = 'Degiro') AND $timeFilter GROUP BY time(1h) fill(linear)

Thanks for any info.

Solution

  • I would use subquery and grouping by tag:

    SELECT SUM(mean) FROM (
   SELECT MEAN("CZK_amount") 
   FROM "autogen"."Portfolio_status" 
   WHERE ("Portfolio" = 'Portu') OR ("Portfolio" = 'Degiro') 
     AND $timeFilter 
   GROUP BY time(1h), "Portfolio" fill(linear)
) GROUP BY time(1h) fill(linear)

    BTW: you have peaks, because you are grouping by minute and you may not have regular 1 minute time interval in data e.g.:

        time                              Amount    CZK_amount     Currency Portfolio
0  2020-07-11T00:01:07.386426Z         xxxxx Kč XXXXXXX.350000    CZK    Portu
0  2020-07-11T00:02:47.386426Z         xxxxx Kč XXXXXXX.350000    CZK    Portu
0  2020-07-11T00:04:01.386426Z         xxxxx Kč XXXXXXX.350000    CZK    Portu
0  2020-07-11T00:04:59.386426Z         xxxxx Kč XXXXXXX.350000    CZK    Portu
...

    So 3rd minute value will be zero and 4th minute value will be doubled for these data.