SQL puzzles and answers读书笔记——麻醉师计费问题
2010-08-30 21:55 知行思新 阅读(919) 评论(0) 编辑 收藏 举报麻醉医师与外科医师工作方式的一个不同之处是:麻醉师在同一个时间段内能服务多个患者。
麻醉医师穿梭在不同的手术室,轮询检查患者的状况,调整麻醉剂量。如下为一麻醉疗程表:
其中:proc_id为麻醉疗程的ID,anest_name为麻醉师名,start_time为麻醉疗程的起始时间,end_time为麻醉疗程的结束时间。
麻醉师是按照每一个麻醉疗程来收费的。但每一个麻醉疗程的费用与最大同步疗程数相关,最大同步疗程数越大,费用越低(相应会有一个比率)。
问题的关键是如何计算最大同步疗程数,即要得到如下结果:
解决方案1(SQL Server 2008下测试通过):
with ProcEvents as ( select P1.proc_id as proc_id, P2.proc_id as comparison_proc, P1.anest_name as anest_name, P2.start_time as event_time, 1 as event_type from Procs as P1 inner join Procs as P2 on P1.anest_name = P2.anest_name and not (P2.end_time <= P1.start_time or P2.start_time >= P1.end_time) union select P1.proc_id as proc_id, P2.proc_id as comparison_proc, P1.anest_name as anest_name, P2.end_time as event_time, -1 as event_type from Procs as P1 inner join Procs as P2 on P1.anest_name = P2.anest_name and not (P2.end_time <= P1.start_time or P2.start_time >= P1.end_time) ), ConcurrentProcs as ( select E1.proc_id, E1.event_time, (select SUM(E2.event_type) from ProcEvents as E2 where E2.proc_id = E1.proc_id and E2.event_time < E1.event_time) as instantaneous_count from ProcEvents as E1 group by E1.proc_id, E1.event_time ) select proc_id, MAX(instantaneous_count) as max_inst from ConcurrentProcs group by proc_id;
解决方案2(SQL Server 2008下测试通过):
With ConcurrentProcs as ( select P1.anest_name, P1.start_time, COUNT(*) as tally from Procs as P1 inner join Procs as P2 on P1.anest_name = P2.anest_name and P2.start_time <= P1.start_time and P2.end_time > P1.start_time group by P1.anest_name, P1.start_time ) select P3.proc_id, MAX(ConcurrentProcs.tally) as max_inst from ConcurrentProcs inner join Procs as P3 on ConcurrentProcs.anest_name = P3.anest_name and P3.start_time <= ConcurrentProcs.start_time and P3.end_time > ConcurrentProcs.start_time group by P3.proc_id;