Last three months on average for each month in a PostgreSQL query

Question

I am trying to build a query in Postgresql that will be used for the budget.

I currently have a list of data grouped by month.

For every month of the year I need to get the average monthly sales for the previous three months. For example, in January, I would need average monthly sales from October to December last year. So the result would be something like this:

1 12345.67 2 54321.56 3 242412.45 

This is grouped by month number.

Here is a snippet of code from my request that will give me sales this month:

 LEFT JOIN (SELECT SUM((sti.cost + sti.freight) * sti.case_qty * sti.release_qty) AS trsf_cost, DATE_PART('month', st.invoice_dt) as month FROM stransitem sti, stocktrans st WHERE sti.invoice_no = st.invoice_no AND st.invoice_dt >= date_trunc('year', current_date) AND st.location_cd = 'SLC' AND st.order_st != 'DEL' GROUP BY month) as trsf_cogs ON trsf_cogs.month = totals.month 

I need another connection that will give me the same, only averaged from the previous 3 months, but I'm not sure how to do it.

This will ALWAYS be January-December (1-12), starting in January and ending in December.

Answer 1

This is a classic problem for a window function. Here's how to solve it:

 SELECT month_nr ,(COALESCE(m1, 0) + COALESCE(m2, 0) + COALESCE(m3, 0)) / NULLIF ( CASE WHEN m1 IS NULL THEN 0 ELSE 1 END + CASE WHEN m2 IS NULL THEN 0 ELSE 1 END + CASE WHEN m3 IS NULL THEN 0 ELSE 1 END, 0) AS avg_prev_3_months -- or divide by 3 if 3 previous months are guaranteed or you don't care FROM ( SELECT date_part('month', month) as month_nr ,lag(trsf_cost, 1) OVER w AS m1 ,lag(trsf_cost, 2) OVER w AS m2 ,lag(trsf_cost, 3) OVER w AS m3 FROM ( SELECT date_part( 'month', month) as trsf_cost -- some dummy nr. for demo ,month FROM generate_series('2010-01-01 0:0'::timestamp ,'2012-01-01 0:0'::timestamp, '1 month') month ) x WINDOW w AS (ORDER BY month) ) y; 

This requires a month away ! Also, look at this answer:
How to compare current row with next and previous row in PostgreSQL?

Calculates the correct average value for each month. If only the two previous moths are divided by 2, etc. If not before. months, the result is NULL.

In your subquery use

 date_trunc('month', st.invoice_dt)::date AS month 

instead

 DATE_PART('month', st.invoice_dt) as month 

so you can easily sort the months over the years!

Additional Information

• Lag () window function
• date_trunc ()