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

Question

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).

Answer 1

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...

A note about time:

. The first section of the scrip generates a list of start-end time ranges to run queries for.

. The second part calculates the stats and outputs the results for each interval of time (eg. for "12 months median" the time shown in the first column will be "12 months to the end of the given month")

. The convention in PosgreSQL is that "end of the month" is actually "0 hour" of the next month (ie. end of Oct 2019 is "2019.11.01 at 00:00:00"). Same applies for any time range (eg. end of 2019 is actually "2020.01.01 at 00:00:00").

. So, for display purposes you may wish use the following:

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

Else, if you don't include "- interval '1 month' ", the 12 months moving stats ending October 2019 will be shown as "for" 1st November 2019 at 00:00:00 (trunkaded to 2019-11).