获取在表中排除日期范围后的剩余天数

Question

create table test (start date ,"end" date);
insert into test values 
('2019-05-05','2019-05-10')
,('2019-05-25','2019-06-10')
,('2019-07-05','2019-07-10')
;

我正在寻找以下输出，其中对于开始和结束之间的每个日期，该人仅在开始和结束之间可用。 考虑到 5 月份，他存在 11 天(05/05 至 05/10 和 05/25 至 05/31(，5 月份的总天数为 31。输出列应有 31-11(他工作的天数(

MonthDate------Days-
2019-05-01   20(31-11)
2019-06-01   20(30-10)
2019-07-01   26(31-5)

Answer 1

我得到的结果略有不同。

但这个想法是生成每个日期。 然后过滤掉使用的那些并聚合：

select date_trunc('month', dte) as yyyymm,
count(*) filter (where t.startd is null) as available_days
from (select generate_series(date_trunc('month', min(startd)), date_trunc('month', max(endd)) + interval '1 month - 1 day', interval '1 day') dte
from test
) d left join
test t
on d.dte between t.startd and t.endd
group by date_trunc('month', dte)
order by date_trunc('month', dte);

这是一个数据库<>小提琴。

五月的免费日是：

1
2
3
4
11
12
13
14
15
16
17
18
19
20
21
22
23
24

我数了数其中的18个。 所以，我相信这个查询的结果。

如果您不想包含结束日期(这与您使用"between"的描述相反(，则on逻辑将是：

on d.dte >= t.startd and 
d.dte < t.endd

但这只会让你在 5 月份达到 19 岁。

Answer 2

您的结果不一致。我决定使用包容性边界作为最简单的解决方案：

SELECT date_trunc('month', d)::date, count(*)
FROM (
SELECT generate_series(timestamp '2019-05-01', timestamp '2019-07-31', interval '1 day') d
EXCEPT ALL
SELECT generate_series(start_date::timestamp, end_date::timestamp, interval '1 day') x
FROM   test
) sub
GROUP  BY date_trunc('month', d);

date_trunc | 计数 -----------+------ 2019-05-01 |   18 2019-06-01 |   20 2019-07-01 |   25

db<>在这里小提琴

这将生成给定时间范围内的所有日期(在您的情况下为一年的 5 月至 7 月(，并排除从所有日期范围生成的天数。

假设至少 Postgres 10。

• SELECT 子句中多个集合返回函数的预期行为是什么？

假设表中的数据类型date。我投到timestamp以获得最佳效果。看：

• 在PostgreSQL中生成两个日期之间的时间序列

旁白：不要使用保留字start和end作为标识符。

相关：

• 选择其他表中不存在的行