我必须将SQL Server过程转换为PostgreSQL,以便在给定日期后添加天数。我做了一些研究,找到了一个解决方案,但它只适用于正数,当我给它一个负数(从给定日期向后移动(时,它永远不会起作用。以下是功能:
CREATE OR REPLACE FUNCTION public.add_business_day(
from_date date,
num_days integer)
RETURNS date
LANGUAGE 'sql'
COST 100
VOLATILE
AS $BODY$
select d
from (
select d::date, row_number() over (order by d)
from generate_series(from_date+ 1, from_date+ num_days * 2 + 5, '1d') d
where
extract('dow' from d) not in (0, 6)
) s
where row_number = num_days
$BODY$;
对此逻辑是否有任何修复/修改或替代方案,使其同时适用于正数和负数?
演示:db<gt;小提琴
我相信您需要一个条件查询来生成日期序列。像这样:
select
d::date,
CASE
WHEN num_days >= 0 THEN row_number() over (order by d) - 1
ELSE row_number() over (order by d DESC) * -1 + 1
END as row_number,
extract('dow' from d)
from
generate_series(
CASE WHEN num_days >= 0 THEN current_date ELSE current_date + num_days * 2 - 5 END,
CASE WHEN num_days >= 0 THEN current_date + num_days * 2 + 5 ELSE current_date END,
'1d'
) d
这将切换generate_series()
函数的起始值和结束值,并更改row_number()
窗口函数的顺序,以确保其按正确顺序计数负值。
可以使用sign()
函数从generate_series()
函数中删除CASE
子句。有了这个,你可以控制你想要添加的间隔,当然可以是负的:
演示:db<gt;小提琴
select
d::date,
CASE
WHEN num_days >= 0 THEN row_number() over (order by d) - 1
ELSE row_number() over (order by d DESC) * -1 + 1
END as row_number,
extract('dow' from d)
from
generate_series(current_date, current_date + num_days * 2 + sign(num_days)::int * 5, sign(num_days)::int * interval '1d') d