Search code examples
postgresqlwindow-functionslag

PostgreSQL LAG records one year apart by partitioning

I have a db table with a bunch of records in a snapshot fashioned way, i. e. daily captures of product units availability for many years

product     units   category    expire_date report_date
pineapple   10      common      12/25/2021  12/01/2021
pineapple   8       common      12/25/2021  12/02/2021
pineapple   8       deluxe      12/28/2021  12/02/2021
grapes      45      deluxe      11/30/2022  12/01/2021
...
pineapple   21      common      12/12/2022  12/01/2022
...

What I'm trying to get from that data is something like this "lagged" version, partitioning by product and category:

product     units   category    report_date     prev_year_units_atreportdate
pineapple   10      common      12/01/2021      NULL
pineapple   21      common      12/01/2022      10
pineapple   16      common      12/01/2023      21
...

It's important to know that from time to time the cron snapshot task fails and no records are stored for days. This leads to a different number of records by product.

I've been using LAG() to no avail since I can only get previous day/month using partitioning by product, category

Can anyone help me on this?

Solution

  • I think I would use a subselect rather than a window function.

    select *, 
    (
       select units from t t2 
       where t2.report_date=t1.report_date-interval '1 year' and t2.product=t1.product and t2.category=t1.category
    ) lagged_units
from t as t1

    I'm not sure what you want to happen on leap year, though, or the year after one.