I don’t know if the error message I am getting is expected behaviour.
Here’s my query, attempting to show accumulated rainfall starting from zero, for a particular period, grouped by some time buckets
SELECT
cumulative_sum(sum(nnd))
FROM (SELECT
non_negative_difference(rain) as nnd
FROM
weather
WHERE
$time_query)
GROUP BY
time(1h)
(I use non_negative_difference() to handle counter resets.)
But this gives the error:
ERR: aggregate function required inside the call to non_negative_difference
The subquery on its own shows the correct increments, but wrapped in an outer query it fails.
I cannot find an aggregate/group-by combination for the subquery that doesn’t compromise the output, apart from forcing the subquery aggregation into buckets smaller than my sensor frequency.
SELECT
cumulative_sum(sum(nnd))
FROM
(SELECT
non_negative_difference(sum(rain)) as nnd
FROM
weather
WHERE
$time_query
GROUP BY
time(1s))
GROUP BY
time(1h)
I’m just checking that this is expected behaviour and not a compiler bug.
Thanks
Rob
Background:
Here is my base query:
select
non_negative_difference(rain) as nnd
FROM
weather
WHERE
$time_query
… which yields the increment per raw data point, for example:
2018-06-01T14:21:00.926Z 0
2018-06-01T14:22:02.959Z 0.30000000000000426
2018-06-01T14:23:04.992Z 0.3999999999999986
2018-06-01T14:24:07.024Z 0.10000000000000142
2018-06-01T14:25:09.059Z 0.19999999999999574
2018-06-01T14:26:11.094Z 0
2018-06-01T14:27:13.127Z 0.10000000000000142
2018-06-01T14:28:15.158Z 0.20000000000000284
2018-06-01T14:29:20.027Z 0.09999999999999432
2018-06-01T14:30:22.476Z 0.10000000000000142
2018-06-01T14:30:53.918Z 0.6000000000000014
2018-06-01T14:31:55.968Z 0.5
2018-06-01T14:32:58.007Z 0.5
2018-06-01T14:34:00.046Z 0.20000000000000284
2018-06-01T14:35:02.075Z 0.3999999999999986
2018-06-01T14:36:04.102Z 0.3999999999999986
2018-06-01T14:37:06.136Z 0.20000000000000284
2018-06-01T14:38:08.201Z 0
So far so good.
I’m now trying to stitch these readings back to cumulative total.
I can use cumulative_sum()
for this, for example:
SELECT
cumulative_sum(nnd)
FROM
(SELECT
non_negative_difference(rain) as nnd
FROM
weather
WHERE
$time_query )
…which yields:
2018-06-01T14:21:00.926Z 0
2018-06-01T14:22:02.959Z 0.30000000000000426
2018-06-01T14:23:04.992Z 0.7000000000000028
2018-06-01T14:24:07.024Z 0.8000000000000043
2018-06-01T14:25:09.059Z 1
2018-06-01T14:26:11.094Z 1
2018-06-01T14:27:13.127Z 1.1000000000000014
2018-06-01T14:28:15.158Z 1.3000000000000043
2018-06-01T14:29:20.027Z 1.3999999999999986
2018-06-01T14:30:22.476Z 1.5
2018-06-01T14:30:53.918Z 2.1000000000000014
2018-06-01T14:31:55.968Z 2.6000000000000014
2018-06-01T14:32:58.007Z 3.1000000000000014
2018-06-01T14:34:00.046Z 3.3000000000000043
2018-06-01T14:35:02.075Z 3.700000000000003
2018-06-01T14:36:04.102Z 4.100000000000001
2018-06-01T14:37:06.136Z 4.300000000000004
2018-06-01T14:38:08.201Z 4.300000000000004
Looking good!
But, as above when I add a Aggregate+GroupBy on the outer query, it forces the same on the subquery.