Time series problem - pls help

kfruchtman · February 2021

Hi! I need urgent help with a time series issue in vertica.
I am trying to create a time series query that will sum me bets per parameter given in a dashboard.
The only thing I have been able to do so far is to get the first or last value according to TS_FIRST_VALUE .I want to sum it , i don't want the first or last value.I am not able to do so.can you help me? My table is:
bets | time
--------+---------------------
2100 | 2020-09-25 01:00:00
212000 | 2020-09-25 01:00:01
1000 | 2020-09-25 01:00:02
3000 | 2020-09-25 01:00:03
11000 | 2020-09-25 01:00:04
..............................................(ofcourse I have tons of rows )
I need to create a time query where I want the bets to be summed up according to time parameter in the dashboard lets say 1 hour (but it can be any option like 15 min,30 min,1day...etc)
The only query I have been able to perform is this (there is a WITH clause before that filters the bet spins according other parameters ) and this is right after:

SELECT time, sum(first_bet) FROM (
SELECT slice_time as time, TS_FIRST_VALUE(filtered_bets) AS first_bet
FROM filtered_spins
TIMESERIES slice_time AS '1 hour' OVER ( ORDER BY time)
) AS result
GROUP BY time,result.first_bet Order by time;

it gives me this:
time | sum
---------------------+---------
2020-09-25 01:00:00 | 2100
2020-09-25 02:00:00 | 5000
2020-09-25 03:00:00 | 9000
2020-09-25 04:00:00 | 2000
2020-09-25 05:00:00 | 5700
2020-09-25 06:00:00 | 10500
2020-09-25 07:00:00 | 1200
2020-09-25 08:00:00 | 64500
2020-09-25 09:00:00 | 212000
2020-09-25 10:00:00 | 16300
2020-09-25 11:00:00 | 616500
2020-09-25 12:00:00 | 71200
2020-09-25 13:00:00 | 2200
2020-09-25 14:00:00 | 105000
2020-09-25 15:00:00 | 202000
2020-09-25 16:00:00 | 4800
2020-09-25 17:00:00 | 232300
2020-09-25 18:00:00 | 231500
2020-09-25 19:00:00 | 1744700
2020-09-25 20:00:00 | 1072000
2020-09-25 21:00:00 | 61000
2020-09-25 22:00:00 | 4000
2020-09-25 23:00:00 | 601000

Vertica_Curtis · February 2021

This doesn't strike me as a time series problem. I think "time_slice" will solve this easily enough (not slice_time - yea, it's super confusing, I know). slice_time creates windows of time against which you can run time series analytics. time_slice just slices up time into discrete chunks, upon which you can perform simple aggregations (which is what you have here, I think).
https://www.vertica.com/docs/10.1.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Date-Time/TIME_SLICE.htm?zoom_highlight=time slice
So, something like:
select time_slice("time", 1, 'hour'), sum(bets) from filtered_spins group by 1 order by 1 desc ;

kfruchtman · February 2021

Curtis it worked like a charm you are a GENIUS!!!

Vertica_Curtis · February 2021

Glad to hear it! I'll be sure to put "genius" on my resume. lol

kfruchtman · February 2021

when I do netstat -a I get this:

tcp 0 0 localhost:6010 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:5434 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:39005 0.0.0.0:* LISTEN
tcp 0 0 localhost:4803 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:5444 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:5450 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:8079 0.0.0.0:* LISTEN
tcp 0 0 localhost:domain 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:ssh 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:5433 0.0.0.0:* LISTEN

So it looks open.... no?

Vertica_Curtis · February 2021

I think this is intended for a separate thread?

kfruchtman · February 2021

oh yes sorry...my misake

We're Moving!

Create My New Community Account Now

Time series problem - pls help

Answers

Leave a Comment