10-342 重複する間隔をまとめて、その合計を求める

SQLパズル

LoginTable
dayC        LoginTime  LogoutTime
----------  ---------  ----------
2010-11-21  06:30:00   07:30:00
2010-11-21  06:40:00   07:20:00
2010-11-21  06:50:00   07:50:00
2010-11-21  07:30:00   08:30:00
2010-11-21  09:30:00   10:30:00
2010-11-21  13:50:00   14:20:00
2010-11-21  14:10:00   14:40:00

LoginTimeからLogoutTimeまでの重複する間隔をまとめて、その合計を求める。

06:30:00から08:30:00までと
09:30:00から10:30:00までと
13:50:00から14:40:00までなので、3時間50分となります。

出力結果
TimeSpent
-----------------------------
+000000000 03:50:00.000000000

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table LoginTable(dayC,LoginTime,LogoutTime) as
select '2010-11-21','06:30:00','07:30:00' from dual union all
select '2010-11-21','06:40:00','07:20:00' from dual union all
select '2010-11-21','06:50:00','07:50:00' from dual union all
select '2010-11-21','07:30:00','08:30:00' from dual union all
select '2010-11-21','09:30:00','10:30:00' from dual union all
select '2010-11-21','13:50:00','14:20:00' from dual union all
select '2010-11-21','14:10:00','14:40:00' from dual;

SQL

select NumToDsInterVal(sum(max(LogoutTime)-min(LoginTime)),'day') as time_spent
from (select LoginTime,LogoutTime,
      sum(willSum) over(order by LoginTime) as GID
      from (select LoginTime,LogoutTime,
            case when LoginTime
                   <= max(LogoutTime)
                      over(order by LoginTime
                           range between UnBounded preceding
                                     and InterVal '1' second preceding)
                 then 0 else 1 end as willSum
            from (select
                  to_date(dayC || LoginTime ,'yyyy-mm-ddhh24:mi:ss') as LoginTime,
                  to_date(dayC || LogoutTime,'yyyy-mm-ddhh24:mi:ss') as LogoutTime
                  from LoginTable)))
group by GID;

解説

date型 - date型の結果のNumber型を、
NumToDsInterVal関数の引数に使用し、
InterVal型で期間の合計を求めてます。

期間をまとめるロジックでは、検索case式と分析関数のrange指定を組み合わせてます。
OracleSQLパズル 9-3 開始日と終了日をまとめる

マニュアル --- NumToDsInterVal

---