Question 89755 in PostgreSQL Question posted in
The official documentation can be found here.

How to lag over month and over year at the same time? – Postgresql

I am attempting to find percentage change per each month per company.
Table

    Year    Mon         company_id    Revenue   
    2018    2018-06-01      42          2000         
    2018    2018-07-01      42          3000
    2019    2019-06-01      42          4000         
    2019    2019-07-01      42          9000

I attempted this and failed.

select *, lag(Revenue) over(partition by company_id order by Year) from table

working to get the bellow result ( the table has multiple company_ids)

    Year    Mon         company_id     Revenue   percentage change 
    2018    2018-06-01      42          2000         
    2018    2018-07-01      42          3000
    2019    2019-06-01      42          4000         100 
    2019    2019-07-01      42          9000         200
Tags:

2

Answers


  1. 0

    Woof!

    You need to tickle out the month so that you can partition by it:

    WITH subq AS (
   SELECT year, mon, company_id, revenue,
          lag(revenue) OVER (PARTITION BY company_id, extract(month FROM mon)
                             ORDER BY year) AS prev_revenue
   FROM "table"
)
SELECT year, mon, company_id, revenue,
       (revenue - prev_revenue) * 100.0 / prev_revenue AS percent_change
FROM subq;

    This assumes that the date stored in mon only serves to identify the month, and that the "year" part of it (which is different from year) is irrelevant.

    Login or Signup to reply.
  2. 0

    You may use a window function to get exact the row with offset one year in the past.

     max(revenue) over (partition by company_id  order by mon range between '1 year' PRECEDING  and '1 year' PRECEDING ) revenue_lag

    This will work even if you have a missing data for some year (you will not match a two or more years old revenue).

    Unfortunately this does not work for me in 14.6 with LAG (not sure if per design), so I’m using MAX.

    Example

    with tab as (
select * from (values 
(2018,date'2018-06-01', 42, 2000),
(2018,date'2018-07-01', 42, 3000),
(2018,date'2018-07-15', 42, 3020),
(2019,date'2019-06-01', 42, 4000),
(2019,date'2019-07-01', 42, 9000)
) tab(year, mon, company_id, revenue)
)
select tab.*,
max(revenue) over (partition by company_id  order by mon range between '1 year' PRECEDING  and '1 year' PRECEDING ) revenue_lag,
lag(revenue) over (partition by company_id  order by mon range between '1 year' PRECEDING  and '1 year' PRECEDING ) lag
from tab
;

year|mon       |company_id|revenue|revenue_lag|lag |
----+----------+----------+-------+-----------+----+
2018|2018-06-01|        42|   2000|           |    |
2018|2018-07-01|        42|   3000|           |2000|
2018|2018-07-15|        42|   3020|           |3000|
2019|2019-06-01|        42|   4000|       2000|3020|
2019|2019-07-01|        42|   9000|       3000|4000|
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search