Hi,
I noticed something strange with regards to integrating in pieces, then summing those pieces, vs. integrating over a longer duration.
In theory, the sum of smaller integrals over range A → B should equal the integral over the entire range A->B.
My “smaller integrals” are continuous queries that are executed in 1 minute, 3 minute, and 5 minute intervals:
CREATE CONTINUOUS QUERY cq_home_energy_1m ON power_monitor BEGIN SELECT integral(power) / 3600000 AS energy INTO power_monitor.rp_1min.home_energy_1m FROM power_monitor.rp_raw.home_load GROUP BY time(1m) END
CREATE CONTINUOUS QUERY cq_home_energy_3m ON power_monitor BEGIN SELECT integral(power) / 3600000 AS energy INTO power_monitor.rp_3min.home_energy_3m FROM power_monitor.rp_raw.home_load GROUP BY time(3m) END
CREATE CONTINUOUS QUERY cq_home_energy_5m ON power_monitor BEGIN SELECT integral(power) / 3600000 AS energy INTO power_monitor.rp_5min.home_energy_5m FROM power_monitor.rp_raw.home_load GROUP BY time(5m) END
Now, in theory, the sum of the queries over a duration that each query can equally cover should equal each other, but they don’t:
> select sum(energy) from "rp_1min"."home_energy_1m" WHERE time >= '2021-02-10T20:00:00Z' and time <='2021-02-11T19:00:00Z';
name: home_energy_1m
time sum
---- ---
1612987200000000000 20.006106449474597
> select sum(energy) from "rp_3min"."home_energy_3m" WHERE time >= '2021-02-10T20:00:00Z' and time <='2021-02-11T19:00:00Z';
name: home_energy_3m
time sum
---- ---
1612987200000000000 20.866431913797186
> select sum(energy) from "rp_5min"."home_energy_5m" WHERE time >= '2021-02-10T20:00:00Z' and time <='2021-02-11T19:00:00Z';
name: home_energy_5m
time sum
---- ---
1612987200000000000 21.0209095327668
And, even worse, the integral of the raw data over the same range is vastly different:
select integral("power") / 3600000 FROM "home_load" WHERE time >= '2021-02-10T20:00:00Z' and time <='2021-02-11T19:00:00Z';
name: home_load
time integral
---- --------
0 27.311974124863287
Why are these results so different?
If we define the integral to be the sum of the area under the curve from X(0) → X(1), X(1) → X(2), X(3) → X(4)… X(n-1) → X(n) , where the difference in each X is simply ΔX…
Could it be that my GROUP BY attributes in the CQs are changing the size of ΔX?
Or, could it be that some data points are being left out because their timestamps don’t quite fit into the group by attributes?
This issue came up because I was intending on using the sum of integrals to get larger aggregate totals, like weekly, monthly, and annual energy values, as opposed of trying to integrate high resolution data over those same intervals.
