0

I am calculating rolling sum as such:

select
    city,
    month_year,
    person,
    sum(total) over (partition by person,city order by month_year rows between 11 preceding and current row) rolling_one_year
from
    (select
      city,
      month_year,
      person,
      sum(amount_dollar) as total
    from db1 d

    group by 1,2,3) ;

however sometimes the not every person has a month_year value: e.g. a rolling 12 year some is as below IF we had consecutive month values:

enter image description here

but what if a month was missing for person e.g. 202208, according to the logic above it would calculate the following 202201 - 202301 which as we know 13 months.

enter image description here

How can i adapt my code above to ensure that the range of months selected is within 1 year?

Maths12
  • 852
  • 3
  • 17
  • 31
  • 1
    You could create a date table that you join to and use the date table to go back 12 months, then it wont matter if your missing a month in your data, because you will be looking at 12 months in your date lookup table for the dates, but adding up/summing up your data that could be missing a month – Brad Feb 17 '23 at 15:07

1 Answers1

1

A possible solution is to LEFT JOIN your data to the calendar table.

Here is a guide on how to create the calendar table if you don't have one. Create a date table in hive

BezginYuriy
  • 29
  • 3