CODEWITHSUNDEEP
mysqlsql
belostoky
belostoky

Reputation: 974

Group records within a given time span

say I have the following data: 

id     value
uid1    20  
uid1    23  
uid1    30  
uid1    31  
uid1    32  
uid1    37  
uid1    39  
uid1    45  
uid2    33  
uid2    34

I want to group it so:

id      value
uid1    20,23
uid1    30,31,32,37,39
uid1    45
uid2    33,34

the logic is:
group records with 'consecutive' values.
by consecutive I mean val1-val2 <= 5

5 is just an example I would like to solve it for any threshold I decide...

in the example 30 & 39 are in the same group because they are part of a chain of numbers that differ in no more than 5.
Is there any way to achieve it in MySQL?

Thanks

Upvotes: 0

Views: 54

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

Gordon's query seems to be working, but I would offer an alternative using session variables. We can iterate over your table and check at each step whether a gap of 5 difference in the value occurs. If it does, we form a new group, and we also reset the gaps when the id changes.

This may seem like overkill, but if the criteria for forming gaps and islands were more complex, then this approach might be more useful.

SET @gn = 0;
SET @id = NULL;
SET @value = NULL;
SET @threshold = 5;       -- or whatever value you want here

SELECT
    t.id,
    GROUP_CONCAT(t.value ORDER BY t.value) AS value
FROM
(
    SELECT @gn:=CASE WHEN @id <> id THEN 0
                     WHEN value - @value > @threshold THEN @gn + 1
                     ELSE @gn END AS gn,
        @value:=value AS value,
        @id:=id AS id
    FROM yourTable
    ORDER BY id, value
) t
GROUP BY t.id, t.gn;

Output:

enter image description here

Demo here:

Rextester

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

It seems like some simple arithmetic will do:

select id, group_concat(value order by value) as values
from t
group by id, floor(value / 10);

Or, if value is a string:

select id, group_concat(value order by value) as values
from t
group by id, left(value, 1);

Upvotes: 1

Related Questions