上课时长计算（有时间overlap）

newkid

2楼的方法是把一整个时间区间打散再聚合，如果时间范围很大，这个方法就比较低效。

hai503

yaksha1 发表于 2020-5-10 01:01
这个思路挺好的，用过的时间就不参与比较，没有用过的时间就计算在内。版主的方法是先判断重叠的时间，重叠 ...

大版的思路最开始我是想用PL/SQL实现的...

hai503

（2/20/2025）ChatGPT 聊了几轮给的答案：
WITH CTE AS
------
RawData AS
(SELECT Stu_ID, lesson_ID, start_time, end_time FROM CTE),
OrderedData AS
(SELECT Stu_ID,
         lesson_ID,
         start_time,
         end_time,
         LAG(end_time, 1, start_time) OVER(PARTITION BY Stu_ID, lesson_ID ORDER BY start_time) AS prev_end_time
    FROM RawData),
OverlappingData AS
(SELECT Stu_ID,
         lesson_ID,
         CASE
           WHEN start_time <= prev_end_time THEN
            prev_end_time
           ELSE
            start_time
         END AS effective_start_time,
         end_time,
         prev_end_time
    FROM OrderedData),
MergedData AS
(SELECT Stu_ID, lesson_ID, MIN(effective_start_time) AS start_time, end_time
    FROM (SELECT Stu_ID,
                 lesson_ID,
                 effective_start_time,
                 end_time,
                 SUM(CASE
                       WHEN effective_start_time = prev_end_time THEN
                        0
                       ELSE
                        1
                     END) OVER(PARTITION BY Stu_ID, lesson_ID ORDER BY effective_start_time) AS seg_id
            FROM OverlappingData)
   GROUP BY Stu_ID, lesson_ID, end_time, seg_id)
SELECT Stu_ID, lesson_ID, SUM(end_time - start_time) * 24 * 60 AS duration
  FROM MergedData
GROUP BY Stu_ID, lesson_ID
ORDER BY Stu_ID;

newkid

都什么年代了，该试试新玩意了：
select Stu_ID,lesson_ID,sum(DURATION) as DURATION
from cte
match_recognize(
  partition by Stu_ID,lesson_ID
  order by start_time,end_time
  measures
      (MAX(end_time)-MIN(start_time))*24*60 AS DURATION
  one row per match
  pattern(a* b)
  define a as max(end_time) >= next(start_time)
)
group by Stu_ID,lesson_ID

newkid

开始挑战AI了：我构造了三条数据，答案应该是600, 上面GPT给出的结果却是240:

WITH CTE AS
(SELECT 'A001' AS Stu_ID,
         'L2020040001' AS lesson_ID,
         to_date('4/26/2020 10:00', 'mm/dd/yyyy hh24:mi:ss') AS start_time,
         to_date('4/26/2020 20:00', 'mm/dd/yyyy hh24:mi:ss') AS end_time,
         'Mate 10 pro' AS terminal
    FROM DUAL
  UNION ALL
  SELECT 'A001' AS Stu_ID,
         'L2020040001' AS lesson_ID,
         to_date('4/26/2020 11:00', 'mm/dd/yyyy hh24:mi:ss') AS start_time,
         to_date('4/26/2020 12:00', 'mm/dd/yyyy hh24:mi:ss') AS end_time,
         'iPad' AS terminal
    FROM DUAL
  UNION ALL
  SELECT 'A001' AS Stu_ID,
         'L2020040001' AS lesson_ID,
         to_date('4/26/2020 13:00', 'mm/dd/yyyy hh24:mi:ss') AS start_time,
         to_date('4/26/2020 15:00', 'mm/dd/yyyy hh24:mi:ss') AS end_time,
         'Mate 10 pro' AS terminal
    FROM DUAL
)