不计算时间的总和范围重叠两次 [英] Summing range of times without counting overlaps twice

问题描述

对于给定的用户ID"1"和给定的日期2018年1月2日，我想计算记录的总小时数，其中可能存在重叠.

For a given user ID "1" and a given day 2018-01-02, I want to calculate the total amount of hours logged, where overlaps can exist.

为此子集计算:

+-----+---------------------+---------------------+
| uid | time_start          | time_end            |
+-----+---------------------+---------------------+
|   1 | 2018-01-02 04:00:00 | 2018-01-02 04:30:00 |
|   1 | 2018-01-02 04:25:00 | 2018-01-02 04:35:00 |
|   1 | 2018-01-02 04:55:00 | 2018-01-02 05:15:00 |
+-----+---------------------+---------------------+

结果时间应为: 00:55 .

推荐答案

MariaDB 10.3具有窗口函数和CTE，因此您可以使用它们来生成结果. CTE通过将当前time_start与当天的最大前一个time_end进行比较，并获取它们的最大(最大)值，从会话时间中删除重叠部分，然后查询每个会话时间SUM进行分组按用户ID和日期.请注意，如果一个会话完全被另一个会话重叠，则CTE会将start和end时间都设置为重叠会话的end时间，因此有效会话长度为0.包括这种情况，以及多个重叠的会话:

MariaDB 10.3 has window functions and CTE's so you can use those to generate your results. The CTE removes the overlaps from the session times by comparing the current time_start with the maximum previous time_end for that day and taking the maximum (greatest) value of them and then the query simply SUMs each session time, grouping by user id and date. Note that if one session is completely overlapped by another, the CTE sets both start and end times to the end time of the overlapping session, resulting in an effective session length of 0. I've expanded my demo to include such a scenario, as well as multiple overlapping sessions:

WITH sessions AS 
    (SELECT uid,
            GREATEST(time_start, COALESCE(MAX(time_end) OVER (PARTITION BY DATE(time_start) ORDER BY time_start ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), '2000-01-01')) AS start,
            MAX(time_end) OVER (PARTITION BY DATE(time_start) ORDER BY time_start ROWS UNBOUNDED PRECEDING)  AS end
            FROM sessions)
SELECT uid, DATE(start) AS `date`, SEC_TO_TIME(SUM(TO_SECONDS(end) - TO_SECONDS(start))) AS totaltime
FROM sessions
GROUP BY uid, `date`

输出:

uid     date        totaltime
1       2018-01-02  00:55:00
1       2018-01-03  01:00:00
1       2018-01-04  01:15:00

dbfiddle上的演示

这篇关于不计算时间的总和范围重叠两次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！