如何根据入院和出院日期计算占用率

Question

假设我有如下示例所示的患者入院/索赔数据。patient_id和hosp_id列的数据类型为VARCHAR

表名claims

<表类> rec_no patient_id hosp_id admn_date discharge_date tbody><<tr>11101-01-202010-01-202022131-12-201911-01-202031111-01-202015-01-202043104-01-202010-01-202051216-01-202017-01-202064201-01-202010-01-202075202-01-202011-01-202086203-01-202012-01-202097204-01-202013-01-2020102131-12-201910-01-2020

您可以使用generate_series()在Postgres中做一些非常类似的事情。入住日期:

select c.hosp_id, gs.date, count(*) as occupanyc
from claims c cross join lateral
generate_series(admn_date, discharge_date, interval '1 day') gs(date)
group by c.hosp_id, gs.date;

然后将其用作子查询以获取超过阈值的日期:

select hd.*, b.strength
from (select c.hosp_id, gs.date, count(*) as occupancy
from claims c cross join lateral
generate_series(c.admn_date, c.discharge_date, interval '1 day') gs(date)
group by c.hosp_id, gs.date
) hd join
beds b
using (hosp_id)
where h.occupancy > b.strength