PostgreSQL- Fill previous values with first non-null

Question

I have 2 tables on my db: 1- scheduled services 2- services realized.

On 2nd table, I have the value $ received for the service and the date the service was realized. On 1st table, I only have the scheduled dates without values.

The point is: Sometimes the scheduled service can't be done, and it have to be re-scheduled, so the realized date can be different than the scheduled date. But, even when the service wasn't realized, I want to know how much $ was scheduled every day. I've created a view (vw_test) that looks like:

vw_test:
SELECT
    t1.date,
    t1.service,
    t2.value
FROM
    scheduled AS t1
LEFT JOIN
    realized AS t2
ON CONCAT(t1.date, t1.service) = CONCAT(t2.date, t2.service)
ORDER BY
    t1.service ASC,
    t2.date ASC

vw_test:

date	service	value
2023-04-01	A	Null
2023-04-02	A	Null
2023-04-03	A	Null
2023-04-04	A	Null
2023-04-05	A	Null
2023-04-06	A	Null
2023-04-07	A	150.80
2023-04-08	A	Null
2023-04-09	A	Null
2023-04-10	A	Null
2023-04-01	B	Null
2023-04-02	B	Null
2023-04-03	B	247.17
2023-04-04	B	Null
2023-04-05	B	Null
2023-04-06	B	Null
2023-04-07	B	Null
2023-04-08	B	75.12
2023-04-09	B	Null
2023-04-10	B	Null

I'm trying to fill the Null values with the next Non-Null value and keep Null for values after the Non-Null value for every service, like the table below:

date	service	value
2023-04-01	A	150.80
2023-04-02	A	150.80
2023-04-03	A	150.80
2023-04-04	A	150.80
2023-04-05	A	150.80
2023-04-06	A	150.80
2023-04-07	A	150.80
2023-04-08	A	Null
2023-04-09	A	Null
2023-04-10	A	Null
2023-04-01	B	247.17
2023-04-02	B	247.17
2023-04-03	B	247.17
2023-04-04	B	75.12
2023-04-05	B	75.12
2023-04-06	B	75.12
2023-04-07	B	75.12
2023-04-08	B	75.12
2023-04-09	B	Null
2023-04-10	B	Null

Is that possible to achieve with PostgreSQL? I've tried ARRAY_AGG like mentioned in text and JSONB_AGG text but the result is the same as the vw_test.

Thanks for the help

GMB · Accepted Answer · 2023-07-05 08:39:05Z

Here is one way to do it with window functions:

select date, service, value, max(value) over(partition by service, grp) as new_value
from (
    select t.*, count(value) over(partition by service order by date desc) as grp
    from mytable t
) t
order by service, date

The idea is to build groups of rows that contain a single rows with a non-null value, optionnaly preceded by any number of rows with null values: that's what the window count in the subquery does.

All that is then left to do is to re-assign the (only) non-null value of the group to each row of the group.

date	service	value	new_value
2023-04-01	A	null	150.80
2023-04-02	A	null	150.80
2023-04-03	A	null	150.80
2023-04-04	A	null	150.80
2023-04-05	A	null	150.80
2023-04-06	A	null	150.80
2023-04-07	A	150.80	150.80
2023-04-08	A	null	null
2023-04-09	A	null	null
2023-04-10	A	null	null
2023-04-01	B	null	247.17
2023-04-02	B	null	247.17
2023-04-03	B	247.17	247.17
2023-04-04	B	null	75.12
2023-04-05	B	null	75.12
2023-04-06	B	null	75.12
2023-04-07	B	null	75.12
2023-04-08	B	75.12	75.12
2023-04-09	B	null	null
2023-04-10	B	null	null

fiddle

Mike Organek · Accepted Answer · 2023-07-04 20:14:35Z

I would use a gaps-and-islands approach for this.

I know that vw_test is a view for you. I simplified my example by making it a table:

with realizeds as (
  select *, 
         (value is null and lag(value) over w is not null)::int as realized
    from vw_test 
  window w as (partition by service order by date)
), islands as (
  select *, 
         sum(realized) over w as group_num
    from realizeds
  window w as (partition by service order by date)
)
select *, max(value) over w as realized_value
  from islands
window w as (partition by service order by date
          rows between current row and unbounded following);

I left the intermediate columns in there to illustrate how the query works.

Working example.

Collectives™ on Stack Overflow

PostgreSQL- Fill previous values with first non-null

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related