[SOLVED] COUNT in Time Window Aggregation Does Not Return 0 as Expected

COUNT in Time Window Aggregation Does Not Return 0 as Expected

I reviewed the GridDB development documentation, which states that the COUNT aggregation function returns 0 when no rows are available for calculation, while other aggregate functions return NULL check here However, when executing Query 1 for time window aggregation, the COUNT function does not return the expected value of 0. This could indicate a potential logical issue.

DROP TABLE t0;
CREATE TABLE IF NOT EXISTS t0(time TIMESTAMP PRIMARY KEY, c0 INTEGER);
INSERT INTO t0 VALUES(TIMESTAMP('2023-01-01T00:00:00Z'), 1);

# query 1
SELECT COUNT(c0), time FROM tsqsdb3_t0 WHERE (time) >= (TIMESTAMP('2022-01-01T16:00:10Z')) AND (time) <= (TIMESTAMP('2022-01-01T16:00:20Z')) GROUP BY RANGE(time) EVERY (10, SECOND) FILL(NONE);

# query 2
SELECT COUNT(c0), time FROM tsqsdb3_t0 WHERE (time) >= (TIMESTAMP('2022-01-01T16:00:10Z')) AND (time) <= (TIMESTAMP('2022-01-01T16:00:20Z'));

I expected Query 1 and Query 2 to return a result set of 0, but instead, Query 1 returned an empty set while Query 2 returned 0.

Solution

You can modify your query to force the creation of time buckets, even when they have no matching rows, by using:

FILL(0)

Try this instead:

SELECT COUNT(c0), time 
FROM tsqsdb3_t0 
WHERE (time) >= TIMESTAMP('2022-01-01T16:00:10Z') 
  AND (time) <= TIMESTAMP('2022-01-01T16:00:20Z') 
GROUP BY RANGE(time) EVERY (10, SECOND) FILL(0);

This will generate the bucket for the 10-second interval and return:

COUNT(c0) | time
----------|---------------------------
0         | 2022-01-01T16:00:10.000Z