在postgresql中按月生成两个日期之间的日期序列和按月生成avrage

Question

我想为两个日期之间的每个月创建一行，每个月的第一天应该是开始日期或每月的第一天，最后一天应该是每个月的最后一天或结束日期，我的表具有平均值(如果日期start=15，那么平均值应该是15/30(。

输入：

product_id | date_start | date_end
1          | 16-01-2020 | 15-03-2020
2          | 07-01-2020 | 22-04-2020

结果应该是：

product_id | date_start | date_end    | average
1          | 16-01-2020 | 31-01-2020  | 0.5
1          | 01-02-2020 | 29-02-2020  | 1
1          | 01-03-2020 | 15-03-2020  | 0.5
2          | 07-01-2020 | 31-01-2020  | 0.76  -- (30-07)/30
2          | 01-02-2020 | 29-02-2020  | 1
2          | 01-03-2020 | 31-03-2020  | 1
2          | 01-04-2020 | 22-04-2020  | 0.76

我试着使用生成序列和日期截断和联合

SELECT (date_trunc('month', dt) + INTERVAL '1 MONTH' ):: DATE AS date_start , 
(date_trunc('month', dt) + INTERVAL '2 MONTH - 1 day' ):: DATE AS date_end
FROM generate_series( DATE '2020-01-15', DATE '2020-05-21', interval '1 MONTH' ) AS dt
union select '2020-01-15' as date_start, 
(date_trunc('month', '2020-01-15'::date) + INTERVAL '1 MONTH - 1 day' ):: DATE AS date_end
union select (date_trunc('month', '2020-05-21'::date) ):: DATE AS date_start ,
'2020-05-21' AS date_end
order by date_start

加上平均值，我计算两个日期之间的差异

SELECT (date_trunc('month', dt) + INTERVAL '1 MONTH' ):: DATE AS date_start , 
(date_trunc('month', dt) + INTERVAL '2 MONTH - 1 day' ):: DATE AS date_end,
((date_trunc('month', dt) + INTERVAL '2 MONTH - 1 day' ) - (date_trunc('month', dt) + INTERVAL '1 MONTH' ):: DATE )
FROM generate_series( DATE '2020-01-15', DATE '2020-05-21', interval '1 MONTH' ) AS dt

就这样，我好像被撞墙了。

Answer 1

下面给出的结果与您想要的结果大致相同，只是平均值有偏差。我相信这源于你们计算中的不一致，有些日期包括在内，而另一些日期不包括开始或结束日期，我在所有情况下都包括在内。另一个不同的地方是，我使用了一个月中的实际天数来计算分母，而不是30。这对于2月份的平均值为1是必要的，否则最大值将为0.97，而31天的整个月的平均值将为1.03。

with product_dates(product_id, date_start, date_end) as  
( values (1,'2020-01-16'::date,'2020-03-15'::date)
, (2,'2020-01-07'::date,'2020-04-22'::date)
) 
select product_id, start_date, end_date, round((end_date-start_date+1 ) * 1.0 / (eom-som+1),2) average   
from (select product_id
, greatest(date_start,dt::date) start_date 
, least(date_end,  (dt+interval '1 month' -interval '1 day')::date) end_date
, dt::date som
, (dt+interval '1 month' -interval '1 day')::date eom     
from product_dates 
cross join generate_series(date_trunc('month', date_start)
,date_trunc('month', date_end) + interval '1 month' - interval '1 day' 
,interval '1 month'
) gs(dt)
) s1;

核心是直接处理日期的generate_series，注意日期操作，以确保我有一个月的第一天和最后一天。然后在任务的外部，我选择了这些日期或参数日期或生成的日期(最大和最小函数(，