这是可以使用的查询。
它依赖于相当沉重的假设：

• 所有班次均包含在同一天内，
• 每个开放移位包含不多于一个闭合移位，
• 闭合移位完全包含在开放对应物内，
• 不存在没有对应的打开的移位的闭合移位。

With
    pairs as (
        select
            jc.user,
            jo.timestart o_start,
            jc.timestart c_start,
            jc.timeend c_end,
            jo.timeend o_end
        from
            journal jo
            join journal jc on (
                j1.user = j2.user
                and trunc (j1.timestart) = trunc (j2.timestart)
                and jc.type = 'Closed'
            )
        Where
            jo.type = 'Open'
    ),
    open_only as (
        --Only open ones
        select
            j1.user,
            j1.timestart,
            jc.timeend
        from
            journal j1
        where
            type = 'Open' not exists (
                select
                    1
                from
                    journal j2
                where
                    j1.user = j2.user
                    and trunc (j1.timestart) = trunc (j2.timestart)
                    and j2.type = 'Closed'
            )
    )
select
    user,
    timestart,
    timeend
from
    open_only
UNION ALL -- First part of shift
select
    user,
    o_start,
    c_start
from
    pairs
UNION ALL -- First part of shift
select
    user,
    c_end,
    o_end
from
    pairs

此查询合并了三个部分：
1.只开放班次
1.移位的第一部分（从打开部分的开始到关闭部分的开始）
1.移位的第二部分（从闭合部分结束到打开部分结束）

您可以UNPIVOT时间，然后使用分析函数来计算开放和封闭的开始时间是否多于结束时间，并过滤仅具有开放范围的一部分而不是封闭范围的一部分的那些范围。
(Note：这将处理：开放范围内的多个封闭范围;相同类型的重叠范围;跨越天的范围;以及没有包含开放范围的封闭范围）。

SELECT user_name,
       time_start,
       time_end
FROM   (
  SELECT user_name,
         dt AS time_start,
         LEAD(dt) OVER (PARTITION BY user_name ORDER BY dt) AS time_end,
         SUM(CASE type WHEN 'Open' THEN is_open END )
           OVER (PARTITION BY user_name ORDER BY dt) AS is_open,
         SUM(CASE type WHEN 'Closed' THEN is_open ELSE 0 END )
           OVER (PARTITION BY user_name ORDER BY dt) AS is_closed
  FROM   table_name
  UNPIVOT ( dt FOR is_open IN ( time_start AS 1, time_end AS -1 ) )
)
WHERE  is_open > 0
AND    is_closed = 0

其中，对于示例数据：

CREATE TABLE table_name (user_name, Time_Start, Time_End, Type) AS
SELECT 'User1', DATE '2023-03-29' + INTERVAL '08:00:00' HOUR TO SECOND, DATE '2023-03-29' + INTERVAL '20:00:00' HOUR TO SECOND, 'Open'   FROM DUAL UNION ALL
SELECT 'User1', DATE '2023-03-29' + INTERVAL '12:00:00' HOUR TO SECOND, DATE '2023-03-29' + INTERVAL '16:00:00' HOUR TO SECOND, 'Closed' FROM DUAL UNION ALL
SELECT 'User2', DATE '2023-03-29' + INTERVAL '10:00:00' HOUR TO SECOND, DATE '2023-03-29' + INTERVAL '22:00:00' HOUR TO SECOND, 'Open'   FROM DUAL UNION ALL
SELECT 'User2', DATE '2023-03-29' + INTERVAL '14:00:00' HOUR TO SECOND, DATE '2023-03-29' + INTERVAL '18:00:00' HOUR TO SECOND, 'Closed' FROM DUAL UNION ALL
SELECT 'User3', DATE '2023-03-29' + INTERVAL '10:00:00' HOUR TO SECOND, DATE '2023-03-29' + INTERVAL '22:00:00' HOUR TO SECOND, 'Open'   FROM DUAL UNION ALL
SELECT 'User3', DATE '2023-03-29' + INTERVAL '11:00:00' HOUR TO SECOND, DATE '2023-03-29' + INTERVAL '12:00:00' HOUR TO SECOND, 'Closed' FROM DUAL UNION ALL
SELECT 'User3', DATE '2023-03-29' + INTERVAL '13:00:00' HOUR TO SECOND, DATE '2023-03-29' + INTERVAL '14:00:00' HOUR TO SECOND, 'Closed' FROM DUAL;

输出：
| 用户名|时间_开始|时间结束|
| --------------|--------------|--------------|
| 用户1|2023年3月29日08时00分|2023年3月29日12时00分|
| 用户1|2023-03-29 16：00：00|2023-03-29 20：00：00|
| 用户2|2023-03-29 10：00：00|2023-03-29 14：00：00|
| 用户2|2023-03-29 18：00：00|2023-03-29 22：00：00|
| 用户3|2023-03-29 10：00：00|2023-03-29 11：00：00|
| 用户3|2023年3月29日12时00分|2023年3月29日13时00分|
| 用户3|2023-03-29 14：00：00|2023-03-29 22：00：00|
fiddle

您可以使用下面的解决方案来解决您的问题。它使用解析函数LAG和LEAD来实现所需的逻辑。我假设您在开放范围内没有多个封闭范围。

SELECT User#
, CASE WHEN Time_Start < LAG(Time_End, 1)OVER(PARTITION BY User# ORDER BY Time_Start, Time_End) 
            THEN Time_End 
       ELSE Time_Start
  END Time_Start
, CASE WHEN Time_End < LAG(Time_End, 1)OVER(PARTITION BY User# ORDER BY Time_Start, Time_End) 
        AND Time_Start < LAG(Time_End, 1)OVER(PARTITION BY User# ORDER BY Time_Start, Time_End)
            THEN LAG(Time_End, 1)OVER(PARTITION BY User# ORDER BY Time_Start, Time_End)
       ELSE LEAD(Time_Start, 1, Time_End)OVER(PARTITION BY User# ORDER BY Time_Start, Time_End)
  END Time_End
from sample_data_tab t
ORDER BY User#, Time_Start, Time_End

demo

也许你想在一行中有一个用户的班次和休息时间。这可以使用分析函数LEAD（）和LAG（）与Case表达式的组合来完成。代码根据下面的示例数据进行了调整，并且限制在一个工作日中最多两次休息（应该足够了）

WITH    --  Sample Data
    tbl (USER_NAME, STARTS, ENDS, STATUS) AS
        ( Select 'User1', To_Date('2023-03-29 08:00:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2023-03-29 20:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Open'   From Dual Union All
            Select 'User1', To_Date('2023-03-29 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2023-03-29 16:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Closed' From Dual Union All
            Select 'User2', To_Date('2023-03-29 10:00:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2023-03-29 22:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Open'   From Dual Union All
            Select 'User2', To_Date('2023-03-29 14:00:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2023-03-29 18:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Closed' From Dual Union All
            Select 'User3', To_Date('2023-03-29 10:00:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2023-03-29 22:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Open'   From Dual Union All
            Select 'User3', To_Date('2023-03-29 11:00:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2023-03-29 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Closed' From Dual Union All
            Select 'User3', To_Date('2023-03-29 13:00:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2023-03-29 14:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Closed' FROM Dual
        ),
  breaks AS   -- cte breaks
    ( Select  ROW_NUMBER() OVER(Partition By USER_NAME, TRUNC(STARTS) Order By USER_NAME, STARTS) "BREAK_NO",
              COUNT(*) OVER(Partition By USER_NAME, TRUNC(STARTS)) "TOTAL_BREAKS",
              USER_NAME, STARTS, ENDS
      From tbl Where STATUS = 'Closed'
      Order By USER_NAME, STARTS
    )

--  M a i n   S Q L
SELECT  USER_NAME, 
        SHIFT_1, SHIFT_1_ENDS, BREAK_1, BREAK_1_UNTIL,
        SHIFT_2, SHIFT_2_ENDS, BREAK_2, BREAK_2_UNTIL,
        SHIFT_3, SHIFT_3_ENDS
FROM    (   Select      b.BREAK_NO, w.USER_NAME "USER_NAME", TRUNC(w.STARTS) "WORK_DATE",
                        To_Char(w.STARTS, 'hh24:mi') "SHIFT_1", CASE WHEN b.BREAK_NO = 1 THEN To_Char(b.STARTS, 'hh24:mi') END "SHIFT_1_ENDS",
                        To_Char(b.STARTS, 'hh24:mi') "BREAK_1", CASE WHEN b.BREAK_NO = 1 THEN To_Char(b.ENDS, 'hh24:mi') END "BREAK_1_UNTIL",
                        --
                        To_Char(b.ENDS, 'hh24:mi') "SHIFT_2",
                        CASE  WHEN b.TOTAL_BREAKS = 1 And b.BREAK_NO = 1 
                              THEN To_Char(w.ENDS, 'hh24:mi') 
                        ELSE  CASE WHEN b.TOTAL_BREAKS = 2 And b.BREAK_NO = 1 THEN  LEAD(To_Char(b.STARTS, 'hh24:mi')) OVER(Partition By b.USER_NAME, TRUNC(b.STARTS) Order By b.USER_NAME, b.STARTS) END 
                        END "SHIFT_2_ENDS",
                        --
                        CASE WHEN b.TOTAL_BREAKS = 2 And b.BREAK_NO = 1 THEN  LEAD(To_Char(b.STARTS, 'hh24:mi')) OVER(Partition By b.USER_NAME, TRUNC(b.STARTS) Order By b.USER_NAME, b.STARTS)  END "BREAK_2",
                        CASE WHEN b.TOTAL_BREAKS = 2 And b.BREAK_NO = 1 THEN  LEAD(To_Char(b.ENDS, 'hh24:mi')) OVER(Partition By b.USER_NAME, TRUNC(b.STARTS) Order By b.USER_NAME, b.STARTS)  END "BREAK_2_UNTIL",
                        --
                        CASE WHEN b.TOTAL_BREAKS = 2 And b.BREAK_NO = 1 THEN  LEAD(To_Char(b.ENDS, 'hh24:mi')) OVER(Partition By b.USER_NAME, TRUNC(b.STARTS) Order By b.USER_NAME, b.STARTS)  END "SHIFT_3",
                        CASE WHEN b.TOTAL_BREAKS = 2 And b.BREAK_NO = 1 THEN  To_Char(w.ENDS, 'hh24:mi')  END "SHIFT_3_ENDS"
            From        tbl w
            Inner Join  breaks b ON(b.USER_NAME = w.USER_NAME And TRUNC(b.STARTS) = TRUNC(w.STARTS) And w.STATUS = 'Open')
        )
Where   BREAK_NO = 1

--
--  R e s u l t :
USER_NAME SHIFT_1 SHIFT_1_ENDS BREAK_1 BREAK_1_UNTIL SHIFT_2 SHIFT_2_ENDS BREAK_2 BREAK_2_UNTIL SHIFT_3 SHIFT_3_ENDS
--------- ------- ------------ ------- ------------- ------- ------------ ------- ------------- ------- ------------
User1     08:00   12:00        12:00   16:00         16:00   20:00                                                   
User2     10:00   14:00        14:00   18:00         18:00   22:00                                                   
User3     10:00   11:00        11:00   12:00         12:00   13:00        13:00   14:00         14:00   22:00

如果你想要just working hours-你所要做的就是在主SQL的Select列表中只选择它们：

SELECT  USER_NAME, 
        SHIFT_1, SHIFT_1_ENDS, --BREAK_1, BREAK_1_UNTIL,   breaks excluded
        SHIFT_2, SHIFT_2_ENDS, --BREAK_2, BREAK_2_UNTIL,
        SHIFT_3, SHIFT_3_ENDS
FROM ...  ...  ...  ...

--
--  R e s u l t :
USER_NAME SHIFT_1 SHIFT_1_ENDS SHIFT_2 SHIFT_2_ENDS SHIFT_3 SHIFT_3_ENDS
--------- ------- ------------ ------- ------------ ------- ------------
User1     08:00   12:00        16:00   20:00                             
User2     10:00   14:00        18:00   22:00                             
User3     10:00   11:00        12:00   13:00        14:00   22:00