The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Median in the moving window
Hello,
I recently started to use Vetrica and it was great so far! I was curious if there is a way to calculate MEDIAN value in the moving window. For example, if I want to compute 20 day average volume, it can be done as:
AVG(volume) OVER (PARTITION BY symbol ORDER BY date ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) as adv20
...is there a way to compute 20 day median volume instead? From documentation it looks like that it is not possible: 'MEDIAN() does not allow the window_order_clause or window_frame_clause'., but was wondering if there is any workaround.
Thanks much in advance,
Konstantin
0
Comments
You are correct that median() won't work.
From the documentation:
MEDIAN() does not allow the window-order-clause or window-frame-clause.
You'd have to code something manually. Statistically speaking, I'm not sure a rolling median would differ significantly from a rolling average, especially if you're only looking at 20 rows. But then, I'm not a statistician.
Thanks for your response. If you think from trading perspective, 1 heavy day can easily skew your average, while median would be still in line with reality. Not sure, if it would be possible for Vertica to start supporting it, as I feel that it is a really useful feature (actually from the statistical perspective).
Check out nth_value - it might be able to give you what you want.
https://my.vertica.com/docs/7.2.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Analytic/NTH_VALUEAnalytic.htm?Highlight=nth_value
Hello -
just picking up on that,
nth_value is a good idea but ordering the values in the window frame seems not be possible.
I guess it comes down to writing a function, or similar. There is a postgres equivalent in this post
I tried to translate into vertica syntax but not luck so far.
Anyone would have advice on how to implement a rolling median?
Thanks.
Hi Ralf_S,
You can do the trick involving a complementary self-join.
This is far from perfect I agree but still working:
Beware of the volume generated by the join.
Hello VValdar,
Thanks for the response - that does work for me.
Initially, I thought solutions like self-join would blow up the data/computation too much, but it works fine with the data I have.
Thank you!