Inconsistency in vertica query results
I have a relatively complicated query that is supposed to calculate standard deviation of BidPrice differences within each hour of a day:
with ticks as (
select *, TickTime at time zone 'Europe/London' at time zone 'utc' as TickTimeGMT from TickHistory.Futures
where (TickType = 'Quote' and BidPrice < AskPrice)
and RIC in ('FGBLM0') and TickTime > '2020-03-19 00:00:00 UTC' and TickTime < '2020-03-20 00:00:00 UTC'
bars as (
SELECT TimeSlice, TS_LAST_VALUE(BidPrice, 'const') as BidPrice
TIMESERIES TimeSlice AS '1 seconds' over (ORDER BY TickTimeGMT)
differences as (
SELECT TimeSlice, (LEAD(BidPrice) over (order by TimeSlice) - BidPrice) as BidPriceDiff
SELECT DISTINCT TIME_SLICE(TimeSlice, 3600, 'SECOND') at time zone 'Europe/London' at time zone 'utc' as BarTime,
STDDEV(BidPriceDiff) OVER (PARTITION BY TIME_SLICE(TimeSlice, 3600, 'SECOND')) as Volatility
Everything in this query seems deterministic. Nevertheless, every time I execute this query I get slightly but noticeably different results. What could be the reason for it and is there a way to improve the query to get stable results?