Как создать разрыв строк между датами в postgresql

Question

У меня есть таблица, в которой хранятся платежи. Мне нужно объединить дни, когда был произведен платеж, а когда нет.

Так что у меня будет что-то вроде:

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

У меня есть это императивное решение, но оно слишком медленное для моего случая:

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() 

Я знаю, что мог бы создать другую таблицу и хранить данные с помощью триггеров. Я хотел бы знать, существует ли эффективный способ сделать это на лету.

Я также пытаюсь сгенерировать список дат с минимальными / максимальными значениями таблицы recaudo_deuda, но тогда я не вижу, как построить результат из этого:

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$

Answer 1

Вы можете сделать это с помощью одного оператора SQL, примерно так:

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;

Answer 2

Немного безумное решение.

Простой пример:

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;

Преобразовать в ваш случай это может быть (не уверен, что я правильно назвал столбцы):

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;