Michal Michal - 3 months ago 5
SQL Question

Append select with missing month and year values

I have SELECT:

SELECT month, year, ROUND(AVG(q_overall) OVER (rows BETWEEN 10000 preceding and current row),2) as avg
FROM (
SELECT EXTRACT(Month FROM date) as month, EXTRACT(Year FROM date) as year, ROUND(AVG(q_overall),1) as q_overall
FROM fb_parsed
WHERE business_id = 1
GROUP BY year, month
ORDER BY year, month) a


output:

month year avg
-----------------
12 2012 5
1 2013 4.5
2 2013 4.1
4 2013 4.8
5 2013 4.7


And I have to append this table with missing values (in this example with 3-rd month in 2013 year). The avg must be same as in previous row, that means I need to append this table with:

3 2013 4.1


Can I do this with SELF JOINS and generate_series, or with some UNION select?

Answer

You can simplify your select. It doesn't need a subquery:

SELECT EXTRACT(Month FROM date) as month,
       EXTRACT(Year FROM date) as year,
       ROUND(AVG(q_overall), 1) as q_overall,
       ROUND(AVG(AVG(q_overall)) OVER (rows BETWEEN 10000 preceding and current row), 2)
FROM fb_parsed 
WHERE business_id = 1
GROUP BY year, month;

The windows function needs an order by. I assume you really intend:

SELECT EXTRACT(Month FROM date) as month,
       EXTRACT(Year FROM date) as year,
       ROUND(AVG(q_overall), 1) as q_overall,
       ROUND(AVG(AVG(q_overall)) OVER (ORDER BY year, month)), 2)
FROM fb_parsed 
WHERE business_id = 1
GROUP BY year, month;

Then, to fill in the values you can use generate_series():

SELECT EXTRACT(Month FROM ym.date) as month,
       EXTRACT(Year FROM ym.date) as year,
       ROUND(AVG(AVG(q_overall)) OVER (ORDER BY year, month)), 2)
FROM (SELECT generate_series(date_trunc('month', min(date)),
                             date_trunc('month', max(date)),
                             interval '1 month') as date
      FROM fb_parsed 
     ) ym LEFT JOIN
     fb_parsed p
     ON EXTRACT(year FROM ym.date) = EXTRACT(year FROM p.date) AND
        EXTRACT(month FROM ym.date) = EXTRACT(month FROM p.date) AND
        p.business_id = 1
GROUP BY year, month;

I think this will do what you want.

Comments