MySQL聚合数据IN、OUT时间

Question

我在表中得到了这样的东西:

ID | UID | ACTION | URL | TIMESTAMP

哪里...

ID - primary key
UID - user id
ACTION - IN or OUT
URL - action URL
TIMESTAMP - action TIMESTAMP

如何通过一次查询聚合所有数据？

我的意思是...作为输出，我想要带有 UID、URL、TOTAL_TIME 的表，其中 TOTAL_TIME将是给定 URL 的 IN 和 OUT 之间所有时间的总和...

我尝试了一些自定义函数，但没有成功......

示例输入(简化时间戳以显示我的意思):

1|13|IN|http://www.gógle.koń|1
2|13|OUT|http://www.gógle.koń|5
...
13454|13|IN|http://www.gógle.koń|550
...
13465|13|OUT|http://www.gógle.koń|600
...
243252|13|IN|http://www.pr0nstaff.meh|tiny_leg_finger|1200
...
245431|13|OUT|http://www.pr0nstaff.meh/tiny_leg_finger|2200

请注意，在某些情况下(而且肯定会发生)，一个 URL 的 IN - OUT 会被 IN< 破坏 或 IN - OUT 或 OUT OF OTHER...所以我们不能在不检查站点匹配的情况下简单地从 IN 数到 OUT。

例如输入(UUID = 13)的输出应该是:

13|www.gógle.koń|14
13|http://www.pr0nstaff.meh/tiny_leg_finger|1000

Answer 1

试试这个，但我不确定，如果 IN/OUT 并不总是双重的。所以请检查..

CREATE TABLE test1 (
 id INT NOT NULL,
 uid INT NOT NULL,
 action VARCHAR(3),
 url varchar(100),
 timestamp1 TIMESTAMP
);

INSERT INTO test1 VALUES
( 1 , 13 , 'IN', 'www.go.com', '2015-01-07 08:00:00'),
( 2 , 13 , 'OUT', 'www.go.com', '2015-01-07 09:00:00'),
( 3 , 14 , 'IN', 'www.go2.com', '2015-01-07 08:30:00'),
( 4 , 14 , 'OUT', 'www.go2.com', '2015-01-07 09:00:00'),
( 5 , 15 , 'IN', 'www.go3.com', '2015-01-07 09:00:00'),
( 6 , 16 , 'OUT', 'www.go3.com', '2015-01-07 09:00:00');


SELECT i.uid,i.url,SUM(TIMESTAMPDIFF(minute, i.timestamp1, o.timestamp1)) AS diff_hour
FROM   (SELECT id,uid,url,timestamp1
        FROM   test1
        WHERE  action = 'IN') i
JOIN (SELECT id,uid,url,timestamp1
        FROM   test1
       WHERE  action = 'OUT') o
  ON i.uid = o.uid
 AND i.url = o.url
 AND i.id < o.id
GROUP  BY i.uid,i.url
ORDER  BY i.uid,i.url;