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

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

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

其中：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;