We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


How to use row count with timeseries clause — Vertica Forum

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!

 

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file