SQL puzzles and answers读书笔记——麻醉师计费问题

麻醉医师与外科医师工作方式的一个不同之处是：麻醉师在同一个时间段内能服务多个患者。

麻醉医师穿梭在不同的手术室，轮询检查患者的状况，调整麻醉剂量。如下为一麻醉疗程表：

其中：proc_id为麻醉疗程的ID，anest_name为麻醉师名，start_time为麻醉疗程的起始时间，end_time为麻醉疗程的结束时间。

麻醉师是按照每一个麻醉疗程来收费的。但每一个麻醉疗程的费用与最大同步疗程数相关，最大同步疗程数越大，费用越低（相应会有一个比率）。

问题的关键是如何计算最大同步疗程数，即要得到如下结果：

解决方案1（SQL Server 2008下测试通过）：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60

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下测试通过）：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

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;