CODEWITHSUNDEEP
sqlpostgresqldategroup-bygaps-and-islands
RIPOL92
RIPOL92

Reputation: 59

Generate date series including next row value

I have a table:

 ╔════════════╦════════╦════════════╗
 ║ product_id ║ amount ║ date       ║
 ╠════════════╬════════╬════════════╣
 ║ 1          ║ 100    ║ 2019-01-01 ║
 ║ 2          ║ 150    ║ 2019-01-01 ║
 ║ 1          ║ 200    ║ 2019-01-05 ║
 ║ 2          ║ 180    ║ 2019-01-03 ║
 ║ 2          ║ 150    ║ 2019-01-05 ║
 ╚════════════╩════════╩════════════╝

I need to generate rows of products based on the next row value(amount). I need something like this in result:

╔════════════╦════════╦════════════╗
║ product_id ║ amount ║ date       ║
╠════════════╬════════╬════════════╣
║ 1          ║ 100    ║ 2019-01-01 ║
║ 1          ║ 100    ║ 2019-01-02 ║
║ 1          ║ 100    ║ 2019-01-03 ║
║ 1          ║ 100    ║ 2019-01-04 ║
║ 1          ║ 200    ║ 2019-01-05 ║
║ 2          ║ 150    ║ 2019-01-01 ║
║ 2          ║ 150    ║ 2019-01-02 ║
║ 2          ║ 180    ║ 2019-01-03 ║
║ 2          ║ 180    ║ 2019-01-04 ║
║ 2          ║ 150    ║ 2019-01-05 ║
╚════════════╩════════╩════════════╝

Upvotes: 1

Views: 38

Answers (1)

GMB
GMB

Reputation: 222412

You can use generate_series() in an aggregate subquery to generate the "missing" dates.

Then, we need to bring the preceeding non-null amount on new rows - this would have been straight-forward if Postgres supported the ignore nulls option to lag() - but it doesn't. One way to work around this is to use a window count to define the groups, and then firstvalue()`:

select
    product_id, 
    dt,
    first_value(amount) over(partition by product_id, grp order by dt) amount
from (
    select 
        x.*, 
        t.amount,
        count(*) filter(where t.amount is not null) 
            over(partition by x.product_id order by x.dt) grp
    from (
        select product_id, generate_series(min(date), max(date), '1 day'::interval) dt
        from mytable
        group by product_id
    ) x
    left join mytable t on t.product_id = x.product_id and t.date = x.dt
) t
order by product_id, dt

Demo on DB Fiddle:

product_id | dt                     | amount
---------: | :--------------------- | -----:
         1 | 2019-01-01 00:00:00+00 |    100
         1 | 2019-01-02 00:00:00+00 |    100
         1 | 2019-01-03 00:00:00+00 |    100
         1 | 2019-01-04 00:00:00+00 |    100
         1 | 2019-01-05 00:00:00+00 |    200
         2 | 2019-01-01 00:00:00+00 |    150
         2 | 2019-01-02 00:00:00+00 |    150
         2 | 2019-01-03 00:00:00+00 |    180
         2 | 2019-01-04 00:00:00+00 |    180
         2 | 2019-01-05 00:00:00+00 |    150

Upvotes: 1

Related Questions