不计算重叠的时间范围相加两次
对于给定的用户 ID1"和给定的日期 2018-01-02，我想计算记录的总小时数，其中可能存在重叠.
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 进行比较并取它们的最大(最大)值，然后查询只需 SUMs 每个会话时间，按用户 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 演示
相关文章