Comments

• Curtis_1

  March 2016

  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.

   

  0
• KG2

  March 2016

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

  0
• Curtis_1

  March 2016

  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

   

   

  0
• Ralf_S Community Edition User ✭

  November 2022

  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.

  0
• VValdar Vertica Employee Employee

  November 2022 edited November 2022

  Hi Ralf_S,

  You can do the trick involving a complementary self-join.

  This is far from perfect I agree but still working:

    select t1.symbol, t1.date, t1.volume
         , approximate_median(t2.volume)
      from MyTable as t1
      join MyTable as t2  on t2.symbol  = t1.symbol
                         and t2.date   >= t1.date - interval '20 days'
                         and t2.date   <= t1.date
  group by t1.symbol, t1.date, t1.volume
  order by 1, 2;
  
  
    select distinct
           t1.symbol, t1.date, t1.volume
         , median(t2.volume) over(partition by t1.symbol, t1.date)
      from MyTable as t1
      join MyTable as t2  on t2.symbol  = t1.symbol
                         and t2.date   >= t1.date - interval '20 days'
                         and t2.date   <= t1.date
  order by 1, 2;
  

  Beware of the volume generated by the join.

  1
• Ralf_S Community Edition User ✭

  November 2022

  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!

  0