【SQL解惑】谜题3:麻醉师谜题

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/jjjraddit/article/details/78578093
1、这段代码的作用在于获取麻醉的开始事件和结束事件

useTest
go
createview Event_s(proc_id,comparison_proc,anest_name,event_time,event_type)
asselect p1.proc_id,p2.proc_id,p1.anest_name,p2.start_time,+1
fromProcs as p1,Procsas p2
wherep1.anest_name= p2.anest_name
andnot (p2.end_time<= p1.start_timeor p2.start_time>= p1.end_time)
union
selectp1.proc_id,p2.proc_id,p1.anest_name,p2.start_time,-1as event_tyepe
fromProcs as p1,Procsas p2
wherep1.anest_name= p2.anest_name
andnot (p2.end_time<= p1.start_timeor p2.start_time>= p1.end_time)
go

对于Dow#30麻醉过程来说,Dow#40、Dow#50、Dow#6、Dow#70是开始事件,Dow#40、Dow#50、Dow#60、Dow#70也是结束事件
结合图片可以发现,以Dow#30麻醉过程为准,将所有麻醉过程与之对比,只有存在交叉的则必须统计在Dow#30麻醉过程的开始事件和结束事件。因此,只要统计“与Dow#30麻醉过程交叉的过程”的开始事件和结束事件即可。
那么,统计“与Dow#30麻醉过程交叉的过程”的相反事件即为“与Dow#30麻醉过程不交叉的过程”。“与Dow#30麻醉过程不交叉的过程”实际上只存在两种情况①②和②③
(1)对于②来说,①②③三个麻醉过程满足不交叉的条件:以②为基准,②的开始时间>=①的结束时间,②的结束时间<=③的开始时间。只要满足这个两个条件其中之一,则不会将不交叉的麻醉过程统计到最大次数的计算中。剩余的所有麻醉过程计算相关的开始事件与结束事件。
2、以下的SQL语句是把对所有开始事件和结束事件进行累加,计算出需要统计在麻醉过程中的所有中的每个节点的值。
selecte1.proc_id,e1.event_time,
       (selectsum(e2.event_type)
       fromEvent_s as e2
       wheree2.proc_id= e1.proc_id
              ande2.event_time< e1.event_time)
              asinstantaneous_count
fromEvent_s as e1
orderby e1.proc_id,e1.event_time
go

以下是原理:通过在嵌套select语句来实现累加数字。
selecta1.num,(selectsum(a2.num2)from test_a a2 where a2.num<= a1.num) as sum_count from test_a a1
select* from test_a


3、将上面计算的结果添加到一张新的表
select* into final from(
selecte1.proc_id,e1.event_time,
      (selectsum(e2.event_type)
      from Event_s as e2
      where e2.proc_id= e1.proc_id
             and e2.event_time< e1.event_time)
             as instantaneous_count
fromEvent_s as e1)
asfinal_a

4、选择其中最大的值,

selectproc_id,max(instantaneous_count)from final group by proc_id


解法二:
selectp3.proc_id,MAX(ConcurrentProcs.tally)
from(selectp1.anest_name,p1.start_time,count(*)
              fromProcs as p1
                     innerjoin
                     Procsas p2
              onp1.anest_name= p2.anest_name
                     andp2.start_time<= p1.start_time
                     andp2.end_time> p1.start_time
              groupby p1.anest_name,p1.start_time)
              asConcurrentProcs(anest_name,start_time,tally)
                     innerjoin
                     Procsas p3
                     onConcurrentProcs.anest_name= p3.anest_name
                           andp3.start_time<= ConcurrentProcs.start_time
                           andp3.end_time> ConcurrentProcs.start_time
              groupby p3.proc_id

示例图:

分解步骤:
1、中间这一步计算出每一个开始的时间点同时正在进行的麻醉过程。

selectp1.anest_name,p1.start_time,count(*)
              fromProcs as p1
                     innerjoin
                     Procsas p2
              onp1.anest_name= p2.anest_name
                     andp2.start_time<= p1.start_time
                     andp2.end_time> p1.start_time
              groupby p1.anest_name,p1.start_time
结果如下:


2、将表装到ConcurrentProcs
select* into ConcurrentProcs from (
selectp1.anest_name,p1.start_time,count(*)as num
             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)as Procs_test
             
3、筛选出每一个麻醉过程中开始时间时最大的同时进行的麻醉过程。
selectp3.proc_id,MAX(ConcurrentProcs.num)
fromConcurrentProcs
                     innerjoin
                     Procsas p3
                     onConcurrentProcs.anest_name= p3.anest_name
                           andp3.start_time<= ConcurrentProcs.start_time
                           andp3.end_time> ConcurrentProcs.start_time
              groupby p3.proc_id

4、p3.start_time<= ConcurrentProcs.start_time andp3.end_time> ConcurrentProcs.start_time
这个方法的核心在于先算出每个麻醉过程的最大数,通过以上的条件来筛选出最大值。由于这个是计算每个麻醉过程的最大值,因此也会把麻醉过程前面对应的也计算进来了。

解法三:
createview Vprocs (id1,id2,total)
asselect p1.proc_id,p2.proc_id,count(*)
       from Procs as p1,Procsas p2,Procsas p3
   where p2.anest_name= p1.anest_name
        and p3.anest_name= p1.anest_name
        and p1.start_time<= p2.start_time
        and p2.start_time< p1.end_time
        and p3.start_time<= p2.start_time
        and p2.start_time<p3.end_time
       groupby p1.proc_id,p2.proc_id
go
selectid1 as proc_id,max(total)as max_inst_count
fromVprocs
groupby id1


分解步骤:
1、先在P1查找落在P1开始时间的麻醉过程
2、再在P2的起始时间中计算正在进行的麻醉过程
3、步骤与解法二类似。
展开阅读全文

没有更多推荐了,返回首页