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
