我一直在尝试解决此问题的方法，但到目前为止还没有解决。我正在使用Oracle。
我有一组看起来像这样的数据：
| USER | ACTIVITY | START_TIME | END_TIME | DURATION |
|--------|------------|-----------------|-----------------|----------|
| jsmith | Front Desk | 2020-08-24 8:00 | 2020-08-24 9:30 | 90 |
| jsmith | Phones | 2020-08-24 8:15 | 2020-08-24 8:45 | 30 |
| jsmith | Phones | 2020-08-24 9:45 | 2020-08-24 9:50 | 5 |
| bjones | Phones | 2020-08-24 9:00 | 2020-08-24 9:10 | 10 |
| bjones | Front Desk | 2020-08-24 9:05 | 2020-08-24 9:15 | 10 |
| bjones | Phones | 2020-08-24 9:15 | 2020-08-24 9:45 | 30 |
可以从以下查询生成以上输出：
SELECT
USER,
ACTIVITY,
START_TIME,
END_TIME,
DURATION
FROM USER_ACTIVITIES
WHERE USER IN ('jsmith', 'bjones')
AND START_TIME BETWEEN '2020-08-24 00:00:00' AND '2020-08-25 00:00:00'
ORDER BY USER, START_TIME, END_TIME
;
考虑到某些活动相互重叠，我需要计算每个用户的总“忙”时间。使用现有查询，对于jsmith，每个用户的总持续时间为125，对于jjones，为50，但是由于某些活动重叠，因此这并不反映用户忙碌的总时间。
我正在寻找的输出是用户每天的总忙碌时间：
| USER | DATE | DURATION |
|--------|------------|----------|
| jsmith | 2020-08-24 | 95 |
| bjones | 2020-08-24 | 45 |
任何帮助，将不胜感激。
您可以先取消设置分钟数，然后通过使用来消除不重叠的间隔NOT EXISTS（由于这种情况，我没有考虑日间隔，EXTRACT( hour FROM max_end_time - min_start_time )*3600如果需要，可以添加其他计算案例）
WITH t AS
(
SELECT "user" , MIN(start_time) AS min_start_time, MAX(end_time) AS max_end_time
FROM user_activities
GROUP BY "user"
), t2 AS
(
SELECT "user", min_start_time + NUMTODSINTERVAL(level, 'minute') AS minutes
FROM t
CONNECT BY level <= EXTRACT( hour FROM max_end_time - min_start_time )*60 +
EXTRACT( minute FROM max_end_time - min_start_time )
AND PRIOR SYS_GUID() IS NOT NULL
AND PRIOR "user" = "user"
)
SELECT "user", COUNT(*) AS "Duration"
FROM t2
WHERE EXISTS ( SELECT *
FROM user_activities
WHERE minutes BETWEEN start_time and end_time
AND "user" = t2."user" )
GROUP BY "user"
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句