MySQL aggregate data IN, OUT times

Question

I got something like this in the table:

ID | UID | ACTION | URL | TIMESTAMP

Where...

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

How to aggregate all data with one request?

I mean ... as a conclusion, I would like to table UID, URL, TOTAL_TIMEwhere TOTAL_TIMEit would be the sum of all time between the INand OUTof this URL ..

I tried some custom functions but no luck ...

Input example (timestamp simplified to show what I mean):

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

PLEASE NOTE THAT THE CASE MAY BE (AND THERE WILL) WHERE IN - OUTONE URLBOOK INOR IN - OUTOR OUTOTHER ... so we can not simply rely on the IN to OUT without checking site of correspondence.

The output, for example, for input (for UUID = 13) should be:

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;

Answer 2

:

SELECT UID, URL, TIMESTAMPDIFF(HOUR, InTime, OutTime) AS TOTAL_TIME 
FROM (SELECT UID, URL, 
             MAX(CASE WHEN ACTION = 'IN' THEN TIMESTAMP ELSE NULL END) InTime, 
             MAX(CASE WHEN ACTION = 'OUT' THEN TIMESTAMP ELSE NULL END) OutTime
      FROM tableA 
      GROUP BY UID, URL
    ) AS A;