Sql 从每月生成的系列中生成每日汇率

SELECT mon_start::date + i AS the_day, round(rate/mon_days, 3) AS daily_rate
FROM   budgets b
CROSS  JOIN LATERAL (
   SELECT *,  LEAST(mon_start + interval '1 month', '2023-09-05')::date - mon_start::date AS mon_days
   FROM   generate_series (lower(timespan)::timestamp
                         , LEAST(upper(timespan)::timestamp, '2023-09-05')
                         , interval '1 month') mon_start
   ) m
     , generate_series (0, m.mon_days - 1) i
WHERE  id = 1;

fiddle个

注意，您示例中第一个月的利率是0.323%，而不是0.333，因为8月份有31天，而不是30天.

1. 在第一侧向子查询m中生成一系列月份 计算一个月或更短的费率周期内的天数:mon_days

2. 在第二个横向函数调用i中，生成与mon_days中的天数一样多的行--实际上就是CROSS JOIN LATERAL的简写.请参见:

   • What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?个

3. 计算显示日期the_day及其在外部SELECT中的daily_rate.

这种计算有很多细节:

为了避免时区与当地时间和夏令时之间的不确定情况和模棱两可，我构建了time_span的数据类型daterange，而不是您的示例数据所建议的tstzrange.

正如您的样本数据所显示的那样，我将每个月的实际开始日期设为timespan，而不是每月的第一天.

我假设您想用当前日期结束时间序列，所以我使用LEAST而不是COALESCE.(出于不同的原因，又增加了LEAST个.)

我将利率除以每个月的实际天数.

rate应为数字类型，以避免舍入误差，并且开箱即可使用round().

我假设所有的第NOT NULL列.

请注意，date - date → int和date + int → date.

我将第一个generate_series()呼叫基于timestamp，而不是date，因为:

• Generating time series between two dates in PostgreSQL个

您的实际查询将使用实际的当前日期(或时间戳)，而不是为"今天"指定的固定的2023-09-05:

SELECT mon_start::date + i AS the_day, round(rate/mon_days, 3) AS daily_rate, *
FROM   budgets b
CROSS  JOIN LATERAL (
   SELECT *,  LEAST(mon_start + interval '1 month', LOCALTIMESTAMP)::date - mon_start::date AS mon_days
   FROM   generate_series (lower(timespan)::timestamp
                         , LEAST(upper(timespan)::timestamp, LOCALTIMESTAMP)  -- !
                         , interval '1 month') mon_start
   ) m
     , generate_series (0, m.mon_days - 1) i
WHERE  id = $id;