mysql - "minutes work"由用户提供。如何进行这个查询？

Question

关闭。此题需要 details or clarity 。目前不接受答案。

---

想要改进这个问题吗？通过 editing this post 添加详细信息并澄清问题.

已关闭 9 年前。

Improve this question

我有一张采用这种格式的表格:

id, user, action, date
1, user1, a1, 2013-03-20 10:00:01
2, user2, a1, 2013-03-20 10:00:03
3, user2, a1, 2013-03-20 10:00:12
4, user3, a1, 2013-03-20 10:00:20
5, user2, a1, 2013-03-20 10:00:24
....
...

并与 30 位不同的用户一起持续所有分钟和小时 (24x7)...

现在我需要知道“工作时间”...例如:

1. 如果我一整天只有一个用户，我有 24 小时 * 60 分钟 = 1440 分钟今天工作。
2. 如果全天(24 小时)有 2 位用户，则为:2880 分钟工作...

但是......用户不会整天工作(当然......)我如何进行这个查询？

一些问题的答案:

• 我需要获取所有用户的工作分钟数(所有用户的分钟数总和)用户)
• 如果用户在 5 分钟内没有进行任何操作，我认为用户将“注销”。
• 我可以使用一些 PHP 代码来完成这项工作。

Answer 1

这个查询就可以了。

SELECT user, CAST(date AS DATE) day, (SUM(worked_seconds) / 60) minutes
FROM (
      SELECT    w1.user
      ,         w1.date
      ,         UNIX_TIMESTAMP(
                  LEAST(
                    MIN(IFNULL(w2.date, w1.date + INTERVAL 5 MINUTE)),
                    CAST(w1.date AS DATE) + INTERVAL 1 DAY
                  )
                )
                - UNIX_TIMESTAMP(w1.date) worked_seconds 
      FROM      work w1
      LEFT JOIN work w2
      ON        w1.user = w2.user
      AND       w1.date < w2.date
      AND       w1.date + INTERVAL 5 MINUTE > w2.date
      AND       CAST(w1.date AS DATE) = CAST(w2.date AS DATE)
      GROUP BY  w1.user, w1.date
) work
GROUP BY user, day

查询将每个工作行与同一用户 (ON w1.user = w2.user) 的所有后续工作行在 5 分钟间隔内配对 (AND w1.date < w2.data AND w1.date + INTERVAL 5 MINUTE > w2.date)，前提是工作发生在同一天 (CAST(w1.date AS DATE) = CAST(w2.date AS DATE)。

根据定义，特定日期的最后一行不会有后面的行，并且因为我们使用 LEFT JOIN，这将导致 NULL 值。我们假设对于最后一行，根据注销规则完成了 5 分钟的工作 (IFNULL(w2.date, w1.date + INTERVAL 5 MINUTE)

对于每个(日期，用户)行，可能会找到多个较晚的行，在这种情况下，我们希望找到最早的行，以便计算两个日期之间的差异。这是使用 MIN 函数完成的。

现在，由于最后一行 + 5 分钟间隔可能会超出一天结束的时间，因此我们使用 LEAST 来防止这种情况发生。

UNIX_TIMESTAMP 用于将日期时间转换为秒，因此我们可以轻松地减去它们并获得工作秒数。

在外部查询中，最后我们对每个用户的工作量求和并除以 60，以获得从秒到分钟的结果。