CODEWITHSUNDEEP
mysql
imnobody
imnobody

Reputation: 3

mysql combine rows on conditions

I have a table like:

user | area | start | end
 1       1     12     18
 1       1     19     27
 1       1     29     55 
 1       1     80     99

means: a 'user' appeared in an 'area' from time 'start' to time 'end', areas can be overlapped.

what I want is to get a result like:

user | start-end
 1      12-18,19-27,29-55
 1      80-99

which means: combine appears with time difference less than a specified value, i.e (row2.start - row1.end < 10), and one result row stands for one 'visit' of the area for a user. Currently I can distinguish each visit and get the count of visits by comparing the same table using one sql statement. But I'm not able to find a way to get the above result. Any help is appreciated.

Explanation: The first 3 appears are linked together as only one visit because: row2.start-row1.end < 10 and row3.start-row2.end < 10, the last appear is a new visit because:80(row4.start) - 55(row3.end) >= 10 .

Upvotes: 0

Views: 155

Answers (1)

Eugen Rieck
Eugen Rieck

Reputation: 65264

We need two steps:

1 - combine a row with its predcessor to have start and last end in the same row

SELECT
  user, area, start, end, @lastend AS lastend, @lastend:=end AS ignoreme
FROM 
  tablename, 
  (SELECT @lastend:=0) AS init
ORDER BY user, area, start, end;

2 - use the difference as a grouping criterion

SELECT
  ...
FROM
  ...
  (SELECT @groupnum:=0) AS groupinit
GROUP BY
  ... ,
  IF(start-lastend>=10,@groupnum:=@groupnum+1,@groupnum)

Now let's combine it:

SELECT
  user, area,
  GROUP_CONCAT(CONCAT(start,"-",end)) AS start_end
FROM (
    SELECT
      user, area, start, end, @lastend AS lastend, @lastend:=end AS ignoreme
    FROM 
      tablename, 
      (SELECT @lastend:=0) AS init
    ORDER BY user, area, start, end
 ) AS baseview,
 (SELECT @groupnum:=0) AS groupinit
GROUP BY
  user, area,
  IF(start-lastend>=10,@groupnum:=@groupnum+1,@groupnum)

Edit

Fixed typos and verified: SQLfiddle

Upvotes: 1

Related Questions