Get aggregate over n last values in vertica

Question

We have table that has the columns dates,sales and item.

An item's price can be different at every sale, and we want to find the price of an item, averaged over its most recent 50 sales.

Is there a way to do this using analytical functions in Vertica?

For a popular item, all these 50 sales could be from this week. For another, we may need to have a 3 month window.

Can we know what these windows are, per item ?

show some sample data and the expected result as well as your attempt. — Vamsi Prabhala, Oct 13 '17 at 23:08
Yes, selecting the relevant set of rows would fall under [tag:greatest-n-per-group] (where `n = 50`), so you can use [the standard answer](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group). Given [this answer](https://stackoverflow.com/a/44209336/812837) there may be a simpler way to write the query, although I can't confirm that syntax is valid (not finding it listed in the documentation. — Clockwork-Muse, Oct 13 '17 at 23:34

score 1 · Answer 1 · answered Oct 14 '17 at 01:33

You would use a window-frame clause to get the value on every row:

select t.*,
       avg(t.price) over (partition by item
                          order by t.date desc
                          rows between 49 preceding and current row
                         ) as avg_price_50
from t;

On re-reading the question, I suspect you want a single row per item. For that, use row_number():

select t.item, avg(t.price)
from (select t.*,
             row_number() over (partition by item order by t.date desc) as seqnum
      from t
     ) t
where seqnum <= 50
group by item;

Get aggregate over n last values in vertica

1 Answers1