# Calculate 12 months rolling / moving average, median, min, max, percentiles, etc. as single query in Postgres

My question has two parts:

. Firstly, how to adjust the code below to calculate 12 month moving avg, median etc.? As is, the query will return a list of daily, weekly, monthly or annual values (depending on what is specified) but only for a single 'period'. That is, if 'year' is specified, summary stats will be calculated for whole years 2019, 2018, 2017, etc. however back the data goes. If 'month' then summary stats will be only for a given month, like Oct-2019, Sep-2019, Aug-2019, etc... But how to calculate eg. single values for 3 months Aug-2019 to Oct-2019, then Jul-2019 to Sep-2019, etc.?

. Secondly, what would be a generic version of the code to be able to use any time interval + number of periods, like '4 weeks', '6 months', '12 months', '2 years'?

SELECT
date_trunc('year', t.time2), -- or hour, day, week, month, year
count(1),
percentile_cont(0.25) within group (order by t.price) as Q1,
percentile_cont(0.5) within group (order by t.price) as Q2,
percentile_cont(0.75) within group (order by t.price) as Q3,
avg(t.price) as A,
min(t.price) as Mi,
max(t.price) as Mx

FROM my_table AS t
GROUP BY 1
ORDER BY date_trunc


Data table consists of a list of individual transactions (date -> time2 as timestamp; and price as bigint).

• It's usually better to ask one question at a time. Read about window functions and date/time operators in the manual. Oct 23, 2019 at 19:13
• It's really only one question. I am giving specific example (ie. 12m moving avg, etc.) but asking for answer to be generic to accommodate various combinations of time periods (ie. week, month, year) and multiples (ie. 1,2,3,4... up to 12) to define the range over which to calculate stats. Thanks for that extra references but I still didn't find the answer :-( Oct 24, 2019 at 10:57

Generic code for calculating 1,2,3,4,..6,...12 years / quarters / months / weeks / days /hours moving average, median, percentiles, etc. summary stats where table contains a list of individual time records (like sales transactions,etc)

WITH grid AS (
SELECT end_time, start_time
FROM (

SELECT end_time
, lag(end_time, 12, 'infinity') OVER (ORDER BY end_time) AS start_time
FROM (

SELECT
generate_series(date_trunc('month', min(time2))
, date_trunc('month', max(time2)) + interval '1 month', interval '1 month') AS end_time
FROM   my_table

) sub

) sub2

WHERE end_time > start_time

)

SELECT
to_char(date_trunc('month',a.end_time - interval '1 month'), 'YYYY-MM') as d
, count(e.time2)
, percentile_cont(0.25) within group (order by e.price) as Q1
, percentile_cont(0.5) within group (order by e.price) as median
, percentile_cont(0.75) within group (order by e.price) as Q3
, avg(e.price) as Aver
, min(e.price) as Mi
, max(e.price) as Mx

FROM grid a

LEFT JOIN my_table e ON e.time2 >= a.start_time

AND e.time2 <  a.end_time

GROUP  BY end_time
ORDER  BY d DESC


The answer looks 'simple' but it is very disappointing nobody was prepared to lend a hand with some code snippets to put me on the right track...

 to_char(date_trunc('month',a.end_time - interval '1 month'), 'YYYY-MM') as d