Regrouping data by matching values

Asked Nov 05 '20 at 14:58

Active Nov 05 '20 at 15:13

Viewed 23 times

I have no idea if what I'm looking for has a proper name or common way of addressing. I have a table with 2 columns. 1st one is a key, other one one match of that key :

key-match

1 - 2
1 - 3
2 - 3
3 - 5
...

What I'd like to generate is a list of grouped chains. For instance, in my data, there can be 10 lines with 1 as a key and 1 different match per row. BUT, if 1 matches with 2,3 and 3 matches with 5 and 5 matches with 16, the actual line should be :

1,2,3,5,16

In other words : all values that have a match, even non direct, should be grouped

I have absolutely no idea of how to achieve this, any help will be welcome !

edited Nov 05 '20 at 15:13

Martijn

15,791
4
36
68

asked Nov 05 '20 at 14:58

Vincent Monteil

What is your expected output? – forpas Nov 05 '20 at 15:01
@forpas One column with one actual "cluster" per line – Vincent Monteil Nov 05 '20 at 15:04
3

Fix your data model! Don't store numbers in strings! Don't store multiple values in a string! – Gordon Linoff Nov 05 '20 at 15:04
first thing I'd do is to normalize your data storing only one value per row. So you will end up with 1-2 1-6 1-18 2-3 3-2 3-5 3-7 where every x-x is a row in your database. Then you can start analyzing your data – Lelio Faieta Nov 05 '20 at 15:04
Actually @LelioFaieta that's what my source data is like ! But even like that I don't really know where to start :( – Vincent Monteil Nov 05 '20 at 15:05
*One column with one actual "cluster" per line* meaning? Edit your question with the expected result, for this sample data, in tabular format. – forpas Nov 05 '20 at 15:06
edit your question to show your sample data (real structure) and expected result. – Lelio Faieta Nov 05 '20 at 15:07
Just done @LelioFaieta – Vincent Monteil Nov 05 '20 at 15:12
Just done @forpas – Vincent Monteil Nov 05 '20 at 15:12
... expected result must match sample data fully. Not "like", not partially... – Akina Nov 05 '20 at 15:12
2

Looks like reachability matrix building from connectivity matrix. Can be easily solved using recursive CTE. – Akina Nov 05 '20 at 15:16
1

You are probably looking for a recursive query. This is available in MySQL as of version 8: https://dev.mysql.com/doc/refman/8.0/en/with.html – Thorsten Kettner Nov 05 '20 at 15:18

Regrouping data by matching values

0 Answers0