Calculating running sum starting x years before

postgresql, select, sql, sum

Solution

Here's an approach that uses the ability to use the `sum` aggregate as a window function with a range-based window frame - see `SUM(...) OVER (PARTITION BY name ORDER BY year ROWS 2 PRECEDING)` and window framing.

WITH name_years(gen_name, gen_year) AS (
  SELECT gen_name, s
  FROM generate_series(
    (SELECT min(year) FROM entity_year_activity),
    (SELECT max(year) FROM entity_year_activity)
  ) s CROSS JOIN (SELECT DISTINCT name FROM entity_year_activity) n(gen_name)
),
windowed_history(name, year,act_num,last3_actnum) AS (
  SELECT
    gen_name, gen_year, coalesce( act_num, 0),
    SUM(coalesce(act_num,0)) OVER (PARTITION BY gen_name ORDER BY gen_year ROWS 2 PRECEDING)
  FROM name_years 
  LEFT OUTER JOIN entity_year_activity ON (gen_name = name AND gen_year = year)
)
SELECT name, year, act_num, sum(last3_actnum) as total_3_years
FROM windowed_history
GROUP BY name, year, act_num
HAVING sum(last3_actnum) <> 0
ORDER BY name, year;

See SQLFiddle.

The need to generate entries for years that have no entry themselves complicates this query. I generate a table of all (name, year) pairs, then `left outer join entity_year_activity` on it before doing the window sum, so all years for all name sets are represented. That's why this is so complicated. Then I filter the aggregated result to exclude entries with zero in the sum.

Problem

Original source