PostgreSQL - How to get value previous month & week?-CodePudding

Right now I'm getting an average for each month

SELECT EXTRACT(MONTH FROM date_time) AS month, EXTRACT(YEAR FROM date_time) AS year, avg("total") FROM my_table GROUP BY EXTRACT(MONTH FROM date_time), EXTRACT(YEAR FROM date_time)

But the SQL query needs to adjust so the total value current month - previous month

Is it possible?

For weekly

SELECT EXTRACT(WEEK FROM date_time) AS week, EXTRACT(YEAR FROM date_time) AS year, avg("total") FROM my_table GROUP BY EXTRACT(WEEK FROM date_time), EXTRACT(YEAR FROM date_time)

CodePudding user response：

Yes, it's possible:

SELECT t1.month, t2.year, t1.tot - t2.tot
(
SELECT EXTRACT(MONTH FROM date_time) AS month, EXTRACT(YEAR FROM date_time) AS year, avg("total") AS tot
FROM my_table GROUP BY EXTRACT(MONTH FROM date_time), EXTRACT(YEAR FROM date_time)
) t1
join (
SELECT EXTRACT(MONTH FROM date_time) AS month, EXTRACT(YEAR FROM date_time) AS year, avg("total") AS tot
FROM my_table GROUP BY EXTRACT(MONTH FROM date_time), EXTRACT(YEAR FROM date_time)
) t2
on ((t1.year = t2.year) and (t1.month = t2.month   1)) or
   ((t1.year = t2.year   1) and (t1.month = 1) and (t2.month = 12))

I have taken your select and converted it into two subselects, named them as t1 and t2 respectively and joined them by the criteria of left join.

Note that the very first month will not have a pair currently and if you need it nevertheless, then you can use left join and coalesce to make sure that even an unpaired item has a "pair" and a NULL for tot is defaulted to 0.

Note further that you can convert this subquery to a view for better readability.

CodePudding user response：

If I get that correctly, you can first group avg(total) by yer and month, and the use LAG() window function to get previous month value, something like:

with my_table(date_time, total) as (
    values
    ('2022-03-29', 10),
    ('2022-04-29', 12),
    ('2022-05-30', 20),
    ('2022-05-31', 30)
)
,grouped as (
    SELECT EXTRACT('MONTH' FROM date_time::timestamp) AS month, EXTRACT('YEAR' FROM date_time::timestamp) AS year, avg("total") AS total
    FROM my_table 
    GROUP BY EXTRACT('MONTH' FROM date_time::timestamp) , EXTRACT('YEAR' FROM date_time::timestamp)
)
SELECT *,  LAG(total) OVER(ORDER BY year, month) as prev_month_total
FROM grouped