トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

9-14 多重度の最大値

SQLパズル

TaskListテーブル
TaskID  ExeName   StartTime       EndTime
------  --------  --------------  --------------
     1  cmd       2007/1/4 07:00  2007/1/4 08:00
     2  notepad   2007/1/4 08:00  2007/1/4 11:00
     3  exp       2007/1/4 08:00  2007/1/4 13:30
     4  calc      2007/1/4 09:00  2007/1/4 13:00
     5  excel     2007/1/4 09:00  2007/1/4 15:30
     6  imp       2007/1/4 10:00  2007/1/4 11:30
     7  sqlldr    2007/1/4 12:30  2007/1/4 13:30
     8  sqlplus   2007/1/4 13:30  2007/1/4 14:30
     9  sqlplusW  2007/1/4 18:00  2007/1/4 19:00
    10  telnet    2007/1/4 18:20  2007/1/4 18:40
    11  tnsping   2007/1/4 20:00  2007/1/4 21:00

TaskIDごとの最大多重度を求める。

例1 cmdは、2007/1/4 08:00に、cmdとnotepadとexpが起動しているので、最大多重度は3
例2 notepadは、2007/1/4 10:00に、notepadとexpとcalcとexcelとimpが起動しているので、最大多重度は5
例3 sqlplusWは、2007/1/4 18:20に、sqlplusWとtelnetが起動しているので、最大多重度は2

出力結果
TaskID  ExeName   StartTime       EndTime         最大多重度
------  --------  --------------  --------------  ----------
     1  cmd       2007/1/4 07:00  2007/1/4 08:00           3
     2  notepad   2007/1/4 08:00  2007/1/4 11:00           5
     3  exp       2007/1/4 08:00  2007/1/4 13:30           5
     4  calc      2007/1/4 09:00  2007/1/4 13:00           5
     5  excel     2007/1/4 09:00  2007/1/4 15:30           5
     6  imp       2007/1/4 10:00  2007/1/4 11:30           5
     7  sqlldr    2007/1/4 12:30  2007/1/4 13:30           4
     8  sqlplus   2007/1/4 13:30  2007/1/4 14:30           4
     9  sqlplusW  2007/1/4 18:00  2007/1/4 19:00           2
    10  telnet    2007/1/4 18:20  2007/1/4 18:40           2
    11  tnsping   2007/1/4 20:00  2007/1/4 21:00           1

SQLパズル(日本語版)のパズル3 [麻酔] を参考にさせていただきました
SQLパズル 第2版のパズル3 [忙しい麻酔医] を参考にさせていただきました

データ作成スクリプト

create table TaskList(
TaskID    number(2),
ExeName   varchar2(8),
StartTime date,
EndTime   date);

insert into TaskList values( 1,'cmd    ' ,to_date('2007/1/4 07:00','fmyyyy/mm/dd fmHH24:MI')
                                         ,to_date('2007/1/4 08:00','fmyyyy/mm/dd fmHH24:MI'));
insert into TaskList values( 2,'notepad' ,to_date('2007/1/4 08:00','fmyyyy/mm/dd fmHH24:MI')
                                         ,to_date('2007/1/4 11:00','fmyyyy/mm/dd fmHH24:MI'));
insert into TaskList values( 3,'exp'     ,to_date('2007/1/4 08:00','fmyyyy/mm/dd fmHH24:MI')
                                         ,to_date('2007/1/4 13:30','fmyyyy/mm/dd fmHH24:MI'));
insert into TaskList values( 4,'calc'    ,to_date('2007/1/4 09:00','fmyyyy/mm/dd fmHH24:MI')
                                         ,to_date('2007/1/4 13:00','fmyyyy/mm/dd fmHH24:MI'));
insert into TaskList values( 5,'excel'   ,to_date('2007/1/4 09:00','fmyyyy/mm/dd fmHH24:MI')
                                         ,to_date('2007/1/4 15:30','fmyyyy/mm/dd fmHH24:MI'));
insert into TaskList values( 6,'imp'     ,to_date('2007/1/4 10:00','fmyyyy/mm/dd fmHH24:MI')
                                         ,to_date('2007/1/4 11:30','fmyyyy/mm/dd fmHH24:MI'));
insert into TaskList values( 7,'sqlldr'  ,to_date('2007/1/4 12:30','fmyyyy/mm/dd fmHH24:MI')
                                         ,to_date('2007/1/4 13:30','fmyyyy/mm/dd fmHH24:MI'));
insert into TaskList values( 8,'sqlplus' ,to_date('2007/1/4 13:30','fmyyyy/mm/dd fmHH24:MI')
                                         ,to_date('2007/1/4 14:30','fmyyyy/mm/dd fmHH24:MI'));
insert into TaskList values( 9,'sqlplusW',to_date('2007/1/4 18:00','fmyyyy/mm/dd fmHH24:MI')
                                         ,to_date('2007/1/4 19:00','fmyyyy/mm/dd fmHH24:MI'));
insert into TaskList values(10,'telnet'  ,to_date('2007/1/4 18:20','fmyyyy/mm/dd fmHH24:MI')
                                         ,to_date('2007/1/4 18:40','fmyyyy/mm/dd fmHH24:MI'));
insert into TaskList values(11,'tnsping' ,to_date('2007/1/4 20:00','fmyyyy/mm/dd fmHH24:MI')
                                         ,to_date('2007/1/4 21:00','fmyyyy/mm/dd fmHH24:MI'));
commit;

SQL

select a.TaskID,a.ExeName,
to_char(a.StartTime,'fmyyyy/mm/dd fmHH24:MI') as StartTime,
to_char(a.EndTime,  'fmyyyy/mm/dd fmHH24:MI') as EndTime,
max((select count(*) from TaskList c
  where b.CheckTime between c.StartTime and c.EndTime)) as 最大多重度
  from TaskList a,(select d.StartTime as CheckTime from TaskList d
             union select d.EndTime from TaskList d) b
where b.CheckTime between a.StartTime and a.EndTime
group by a.TaskID,a.ExeName,
         to_char(a.StartTime,'fmyyyy/mm/dd fmHH24:MI'),
         to_char(a.EndTime,  'fmyyyy/mm/dd fmHH24:MI')
order by a.TaskID;

解説

インラインビューでunionを使って、
最大多重度となる時間の候補の和集合を作成し、結合してます。