How to use row count with timeseries clause

MS440

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!