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;