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

PostgreSQL- Fill previous values with first non-null

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search