php - 获取每个用户特定时间段的事件持续时间

Question

这是我的表格的一个示例。对于给定的 post_id，我在多行上有多个条目(这是帖子的元数据)。

post_id | meta_key       | meta_value
________ ________________ ____________________
        |                |
1       | _theDate       | 2016-03-31 12:03:59
1       | _email         | the@email.com
1       | _EventDuration | 32400
2       | _theDate       | 2016-01-06 14:50:22
2       | _email         | the@email.com
2       | _EventDuration | 32400
3       | _theDate       | 2017-02-14 15:32:52
3       | _email         | other@user.net
3       | _EventDuration | 32400
4       | _theDate       | 2016-10-01 22:45:55
4       | _email         | the@email.com
4       | _EventDuration | 32400
5       | _theDate       | 2016-09-25 11:01:39
5       | _email         | other@user.net
5       | _EventDuration | 32400
6       | _theDate       | 2015-11-19 19:08:45
6       | _email         | other@user.net
6       | _EventDuration | 32400

我想要实现的目标:

我想从数据库中获取这些结果，以制作一个表格，显示每个用户 (_email) 每月工作的小时数 (_EventDuration)(使用每个事件 (post_id) 的 _theDate 例如，2015-11)。我可以使用 PHP 变量来完成此操作。

因此，我可以制作 HTML 表格，以以下方式显示此信息(我不是寻找这部分的答案，例如将秒转换为小时，这纯粹是这样你知道我想用表中的所有数据来完成什么):

Hours the@email.com worked in 2015-11
Date            Hours
2015-11-01      9
2015-11-13      7
2015-11-27      5
2015-11-30      8

Hours the@email.com worked in 2015-12
Date            Hours
2015-12-01      10
2015-12-13      7
2015-12-27      3
2015-12-30      6

目前我所拥有的只是这个请求，用于计算用户在 2017 年完成的事件数量，例如:

SELECT COUNT(*) 
  FROM 
     ( SELECT post_id 
         FROM metatable 
        WHERE (meta_key = '_email' AND meta_value LIKE '%$user_email%')                 
           OR (meta_key = '_theDate' AND LEFT(meta_value, 4) LIKE '%2017%') 
        GROUP 
           BY post_id 
       HAVING COUNT(DISTINCT meta_key) = 2
     ) p

Answer 1

您可以使用子查询对表进行透视，将电子邮件、日期和时间放入每个帖子的不同列中。然后您可以使用普通的分组查询来获取每天和用户的总数。

SELECT Email, Date, SUM(Hours) AS Hours
FROM (
    SELECT MAX(IF(meta_key = '_email', meta_value, NULL)) AS Email,
           MAX(IF(meta_key = '_theDate', DATE(meta_value), NULL)) AS Date, 
           SUM(IF(meta_key = '_EventDuration', meta_value, 0))/3600 AS Hours
    FROM metatable
    GROUP BY post_id
) AS x
GROUP BY Email, Date
ORDER BY Email, Date