Netezza SQL: Specify an offset in a window frame

Question

When making the "frame" for a windowed analytic function, one can specify a literal number of rows to "look back" over. E.g., the following will get the trailing 26 weeks weekly sales for a households.

,sum(sales) over (partition by household_id order by week_id rows 26 preceding) as x26

But... what if you wanted to look back (or forward) with an offset? E.g., if for week n, you wanted the sales for the 26 weeks that ended 8 weeks before week n? As I was typing this, it occurred to me that I could probably do it in parts. I.e.,

 ,sum(sales) over (partition by household_id order by week_id rows 34 preceding) as x34
 ,sum(sales) over (partition by household_id order by week_id rows 8 preceding) as x8

...and have trailing26_offeset8 = x34 - x8

Hm... Glad I asked. But anyway, do you know if there's an feature that will let me specify the offset right in the partition specification itself?

Thanks!

Answer 1

Try using between in the window range specification:

sum(sales) over (partition by household_id
                 order by week_id
                 rows between 34 preceding and 8 preceding
                ) as x34