How to use row count with timeseries clause
Let’s say I have a table with a single timestamp-type column:
CREATE TABLE tbl ( ts timestamp);
Table tbl gets some rows inserted once in a while and my time series analysis is about the number of the rows over time.
I can get the normalized counter using time_slice() function:
select time_slice(ts, 24, 'hour') as tts, count(*) as cnt
from tbl
group by tts
order by tts;
but then I’m only getting the counts for days with rows. Or, I can use timeseries clause:
select tts, count(*) as cnt
from tbl
timeseries tts as '1 day' over(order by ts);
but then I’m getting 2640: Column "tbl.ts" must appear in the GROUP BY clause or be used in an aggregate function diagnostics.
I can omit count(*) and use left join to combine those two queries:
select alldays.tts, gooddays.cnt as cnt
from (
select tts
from tbl
timeseries tts as '1 day' over(order by ts)
) as alldays
left outer join (
select time_slice(ts, 24, 'hour') as tts, count(*) as cnt
from tbl
group by tts
order by tts
) as gooddays on alldays.tts = gooddays.tts
order by alldays.tts;
and, yes, I’m getting the results I want… but is it a good solution? What am I missing in timeseries processing that would allow me to count rows using timeseries clause only?
Any suggestions?
Thanks!