Aggregating values over period

Moderator: NorbertKrupa

Post Reply
dekee
Newbie
Newbie
Posts: 2
Joined: Thu Dec 19, 2013 6:07 pm

Aggregating values over period

Post by dekee » Thu Apr 24, 2014 4:53 pm

I'm trying to aggregate values over a certain time period using the time_slice function; however, when I run the following query the first time stamp is not what I expected. In addition, the count for values in the fist period are not consistent with the count that the database is showing. Is this the correct way to aggregate values over a time period? If not, what is a better way to perform this function?

Code: Select all

SELECT COUNT(value)                 AS count, 
       TIME_SLICE(time_stamp, 2100) AS time_interval 
FROM   metrics.metrics 
WHERE  metric_definition_id = 27060341 
       AND time_stamp >= '2013-12-18 03:00:00' 
GROUP  BY TIME_SLICE(time_stamp, 2100) 
ORDER  BY time_interval; 

 count |    time_interval
-------+---------------------
    10 | 2013-12-18 02:35:00
    35 | 2013-12-18 03:10:00
    35 | 2013-12-18 03:45:00
    35 | 2013-12-18 04:20:00
    35 | 2013-12-18 04:55:00
    35 | 2013-12-18 05:30:00
    35 | 2013-12-18 06:05:00
    35 | 2013-12-18 06:40:00
    35 | 2013-12-18 07:15:00

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Aggregating values over period

Post by NorbertKrupa » Thu Apr 24, 2014 5:13 pm

It probably needs a frame. Try adding UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to your ORDER BY:

Code: Select all

ORDER  BY time_interval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING;
You can also try using TIMESERIES. Otherwise, you may want to post some sample data and the expected result.
Checkout vertica.tips for more Vertica resources.

dekee
Newbie
Newbie
Posts: 2
Joined: Thu Dec 19, 2013 6:07 pm

Re: Aggregating values over period

Post by dekee » Thu Apr 24, 2014 5:18 pm

I tried that. It doesn't seem to like the syntax. I'm getting ERROR 4856: Syntax error at or near "ROWS" at character 228.

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Aggregating values over period

Post by NorbertKrupa » Thu Apr 24, 2014 5:27 pm

Try ORDER BY time_stamp (instead of time_interval). Check out the documentation for more info.
Checkout vertica.tips for more Vertica resources.

Post Reply

Return to “Vertica SQL”