sql - 按事件和非事件日期计算单位

Question

我有一个具有以下结构的表

table
unit      date_active  date_inactive
a         2018-01-01   NULL
b         2018-01-01   2020-07-05
c         2019-02-01   2020-01-01

我需要一个查询来生成从第一次事件到今天的每月事件单位总数。输出需要遵循以下原则:

2018-01-01 2
2018-02-01 2
....

当 date inactive 为 NULL 时，当然该单元今天仍然有效。我正在使用 PostgreSQL

Answer 1

生成一系列日期，将其加入表格并计算每个日期的单位:

select m::date as month, count(unit)
from generate_series('2018-01-01'::date, '2020-12-31', '1 month') m
left join my_table on m::date between date_active and coalesce(date_inactive, 'infinity')
group by 1
order by 1

Db<>Fiddle.