mysql - 计算每周在场总和

Question

我来自哪里:

我有表“event”、user 和 user_event。表事件存储事件开始和结束的日期时间。 User_event 保存用户正在参加哪个事件的信息。现在，只需将所有事件持续时间相加，就可以轻松计算某个用户在事件中安排的时间总和。

但我需要一些不同的东西。假设现在是星期一，用户 A 参加上午 9 点到 10 点和下午 2 点到 3 点的事件。这些事件的总和为 2 小时。但我想计算用户参与这些事件的时间。在我的示例中，这是 6 小时(从上午 9 点到下午 3 点)。我在一天内实现了这一目标(用户和给定日期的时间差为 max(event) 和 min(event)。

我想去的地方:

但是我无法找到一个解决方案来完成对整个星期的在场情况进行总结的任务。我需要将每天的所有存在总和加起来。但是我不能使用一天给出的解决方案，因为 min() 和 max() 只会给我整个星期的唯一最小值和最大值，而不是一周中的每一天。

希望我描述得足够好。

谢谢!

Answer 1

先在天数上进行 GROUP BY，然后在周内进行:

select 
    sum(d.dayFinish - d.dayStart) as weeklyPresence, 
    week(d.day) as weekNumber
    d.userId
from (
    select 
         to_date(e.startDate) as day, 
         min(e.startDate) as dayStart
         max(e.finishDate) as dayFinish,
         e.userId
    from events e
    group by to_date(e.startDate), e.userId
) d
group by week(d.day), d.userId

(我没有遵循实际的语法，可能需要一些类型转换)。

我假设您有一个包含 startDate 和 endDate 列的 Events 表，以及一个 userId 外键指的是用户。

to_date() 函数四舍五入到一天(我们首先分组)，week() 函数确定周(这是我们的第二个分组)。