如何在PostgreSQL中的日期之间生成间隔行 [英] How generate gap rows between dates in postgresql
问题描述
我有一张存储付款的表格。我需要合并付款的日期和不付款的时间。
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.
我还尝试使用recaudo_deuda表的最小/最大值来生成日期列表,但后来我看不出如何生成结果:
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$
推荐答案
您可以使用一条SQL语句来完成此操作,例如:
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;
这篇关于如何在PostgreSQL中的日期之间生成间隔行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!