Median in the moving window



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 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,



  • 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).

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.