How generate gap rows between dates in postgresql

Question

I have a table that store payments. I need to merge the days a payment was made and when not.

So I will have something like:

DeudaId Date Value
1 2016-01-01 $100 <- This come from a table
1 2016-01-02 $0   <- This was calculated
1 2016-01-03 $0   <- This was calculated
1 2016-01-04 $100 <- This come from a table

I have this imperative solution, but is too slow for my case:

CREATE OR REPLACE FUNCTION build_dates2()
 RETURNS TABLE (id INTEGER, cobrador_id INTEGER, fecha DATE)
 LANGUAGE plpgsql
 IMMUTABLE STRICT
AS $function$DECLARE min_date DATE;
DECLARE
    r RECORD;
    fecha RECORD;
BEGIN
    for r in (SELECT * FROM recaudo_deuda) LOOP
        for fecha in (select toFecha(r.fecha) + s.a AS dates from generate_series(0, r.plazo) as s(a)) LOOP
            return query VALUES ( r.id, r.cobrador_id, fecha.dates);
        END LOOP;
    END LOOP;
END
$function$;


SELECT * from  build_dates2()

I know I could create another table and store data with triggers. I wish to know if exist a efficient way to do this on the fly.

I also try generating a list of dates with the min/max values of the recaudo_deuda table, but then I don't see how build a result from this:

CREATE OR REPLACE FUNCTION public.build_dates()
 RETURNS TABLE(dates date)
 LANGUAGE plpgsql
 IMMUTABLE STRICT
AS $function$
DECLARE min_date DATE;
DECLARE dias INTEGER;
BEGIN
    SELECT min(fecha), extract(DAY from max(fecha) - min(fecha))
        from recaudo_deuda INTO min_date, dias;

  RETURN QUERY select min_date + s.a AS dates from generate_series(0,dias) as s(a);
END
$function$

score 2 · Accepted Answer · 2016-08-03 21:27:52Z

2

You can do this with one single SQL statement, something like this:

select d.deudaid, s.a as date, coalesce(d.value, 0) as value
from (
   select min(fecha), max(fecha)
   from recaudo_deuda
) m (mi, mx)
  cross join lateral generate_series(m.mi, m.mx, interval '1' day) as s(a)
  left join recaudo_deuda d on d.fecha = s.a::date
order by s.a;

edited Aug 3, 2016 at 21:27

answered Aug 3, 2016 at 21:07

user330315

Sign up to request clarification or add additional context in comments.

4 Comments

mamcx Over a year ago

But this way I get nulls if wanna project recaudo_deuda.id. I need to associate the record with the date, so in a gap I get a sequence of rows with recaudo_deuda.id, GenerateDate, NULLs...

user330315 Over a year ago

@mamcx: you can use coalesce() for that.

mamcx Over a year ago

Is not the value the problem, is the Id. I need to preserve the Id (Build list of dates where exist or not payment for that debt) . See the sample on the question.

mamcx Over a year ago

I award this because was close to the sample code in the question. Actually, I need to do something else as select id, fecha + (generate_series(0, 60)|| ' days')::INTERVAL as fecha from recaudo_deuda;

Abelisto · Accepted Answer · 2016-08-03 22:25:14Z

0

A bit crazy solution.

Simple example:

with t(x) as (values(1),(2),(5),(10),(11))
select
  x,
  generate_series(x, coalesce(lead(x) over (order by x) - 1, x)) as x_gaps,
  x = generate_series(x, coalesce(lead(x) over (order by x) - 1, x)) as x_real
from t;

Converted to your case it could be (not sure that I was accurate with the columns names):

select
  deudaid,
  generate_series(fecha, coalesce(lead(fecha) over (order by fecha) - '1d', fecha), '1d') as fecha,
  (fecha = generate_series(fecha, coalesce(lead(fecha) over (order by fecha) - '1d', fecha), '1d'))::int * value as value
from
  recaudo_deuda;

answered Aug 3, 2016 at 22:25

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

2 Comments

mamcx Over a year ago

I manage to distill the query to select id, fecha + (generate_series(0, 60)|| ' days')::INTERVAL as fecha from recaudo_deuda; but run too slow. So, I need to repeat 60 times the deuda.id field.

Abelisto Over a year ago

@mamcx BTW, how many rows in the table without/with the filled gaps? I can guess that the most time is spend to the data retrieving. If so then you should to fill the gaps at the client instead of the server side.

Collectives™ on Stack Overflow

How generate gap rows between dates in postgresql

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related