[SQL] Records met zelfde emailadres mergen

[Wolf3D]

Het is weer zover. Na uren klooien gooi ik het maar weer eens in de groep.
De situatie:
Een gevulde database (300.000 records) bevat een hoop rommel die ik nu probeer op te ruimen. Een onderdeel daarvan is dat emailadressen maar 1x mogen voor komen. Oftewel: alle records met hetzelfde emailadres moeten gemerged worden, waarbij ik het record met de meeste (adres) data wil behouden.

Het geheel met één query oplossen lukt zowiezo niet, dus heb ik de query in stukjes gehakt.

1: Selecteer alle e-mailadressen die meerdere keren voorkomen in de tabel member.

code:

SELECT email FROM mem GROUP BY email HAVING ( COUNT(email) > 1 )

2: Vervolgens voer ik die e-mailadressen aan de volgende query, waarbij ik een aantal velden samenvoeg en de lengte ervan bereken.

code:

SELECT mem_id, email, MAX(LENGTH(CONCAT(initials, firstname, insertion, lastname, street, zipcode, city, phone_mobile))) AS length
FROM mem WHERE email IN ($email)
GROUP BY email
ORDER BY email

Echter bij bovenstaande query krijg ik wel de maximale lengte terug, maar niet het bijbehorende id (mem_id). Nou doe ik hier waarschijnlijk weer iets heel stoms verkeerd, maar ook met behulp van subqueries (zoals op deze site uitgelegd wordt) krijg ik het niet voor elkaar.

Help!

[justmental]

Op de manier zoals in het laatste voorbeeld moet het werken.
Dus in de vorm: where length(...) = (select max(length...

[Woy]

Ik ga er van uit dat het een eenmalig conversie script is, dus ik ga er van uit dat performance niet echt een issue is. Is het dan niet makkelijker om met een sub-select gewoon de records de selecteren, en er met code doorheen te lopen. Dan kun je ook wat complexere logica gebruiken om te mergen

SQL:

SELECT m.*
FROM mem m
WHERE m.email in ( SELECT m2.email FROM mem m2 GROUP BY m2.email HAVING ( COUNT(m2.email) > 1 ) )
ORDER BY m.email

Dan heb je een lijst met alle gegevens van records die een e-mail adres hebben die dubbel voorkomt.

[ Voor 10% gewijzigd door Woy op 26-08-2009 11:01 ]

[Wolf3D]

Woy schreef op woensdag 26 augustus 2009 @ 11:00:
Ik ga er van uit dat het een eenmalig conversie script is, dus ik ga er van uit dat performance niet echt een issue is. Is het dan niet makkelijker om met een sub-select gewoon de records de selecteren, en er met code doorheen te lopen. Dan kun je ook wat complexere logica gebruiken om te mergen

SQL:

SELECT m.*
FROM mem m
WHERE m.email in ( SELECT m2.email FROM mem m2 GROUP BY m2.email HAVING ( COUNT(m2.email) > 1 ) )
ORDER BY m.email

Dan heb je een lijst met alle gegevens van records die een e-mail adres hebben die dubbel voorkomt.

Gek genoeg heeft mijn locale mysql database erg veel moeite met deze query, waardoor ik had besloten hem in stukjes te hakken. Als ik bovenstaande query uitvoer (in Navicat) zie ik alleen 'Processing ....', maar een resultaat krijg ik nooit (of in elk geval niet na 20 minuten wachten). De database bevat ongeveer 300.000 records, met ongeveer 3000 dubbele e-mailadressen. Maar echt snappen waarom het zo gigantisch lang duurt doe ik ook niet. (Ik heb indexen aangelegd op email en mem_id.)

Het is dus idd alleen voor conversie, en performance is geen issue.

Justmental: Als ik de 'where length(...) = (select max(length()) ...'-methode gebruik, dan werkt het volgens mij niet zoals ik wil. Hij moet eigenlijk naar het id kijken, want lengte is niet uniek.

[cariolive23]

@Woy: Is er geen beperking op het aantal resultaten in de IN() ? Ik heb wel eens een query gezien waarbij er 80.000 items in een IN() werden gezet, dat werkte van geen meter. Een JOIN op een temp-table was daarbij de oplossing.

Misschien iets om naar te kijken wanneer de query fout gaat.

[Woy]

cariolive23 schreef op woensdag 26 augustus 2009 @ 11:38:
@Woy: Is er geen beperking op het aantal resultaten in de IN() ? Ik heb wel eens een query gezien waarbij er 80.000 items in een IN() werden gezet, dat werkte van geen meter. Een JOIN op een temp-table was daarbij de oplossing.

Dat lijkt me alleen een probleem om de query te parsen, en dus als je de queries los uitvoert . Met gewoon een sub-select zou het geen probleem moeten zijn. De query word door echt niet gewoon uitgeschreven en dan uitgevoerd door het RDBMS. Maar wie weet heeft MySql daar wel problemen mee.

Een oplossing met een Temp-Table is natuurlijk ook goed te doen als het een probleem blijkt te zijn, maar het idee komt op hetzelfde neer.

[ Voor 7% gewijzigd door Woy op 26-08-2009 11:51 ]

[justmental]

Wolf3D schreef op woensdag 26 augustus 2009 @ 11:37:
Justmental: Als ik de 'where length(...) = (select max(length()) ...'-methode gebruik, dan werkt het volgens mij niet zoals ik wil. Hij moet eigenlijk naar het id kijken, want lengte is niet uniek.

Dan mis je een stukje in de subquery waar je aan de hoofdquery joint (where sub.email=hoofd.email).

[Verwijderd]

justmental schreef op woensdag 26 augustus 2009 @ 12:40:
[...]

Dan mis je een stukje in de subquery waar je aan de hoofdquery joint (where sub.email=hoofd.email).

Dan ben je er nog niet. Wat als een email adres dubbel voorkomt, en beide email adressen hebben dezelfde hoeveelheid adresgegevens? Dan krijg je beide records eruit.

Ik zou de query als volgt opzetten:
Geef me per email adres de eerste id met de voor dat email adres grootste hoeveelheid adresgegevens.

Ik ben geen held in MySQL (waar dit topic waarschijnlijk over gaat), maar het zou zoiets moeten zijn:

SQL:

select m1.email, min(m1.mem_id)
from mem m1
where
length(concat(...)) = (select max(length(concat(...) from mem m2 where m2.email = m1.email)
group by m1.email
having (count(email) > 1)

Dit geeft je per email adres het eerste mem_id met de maximale hoeveelheid data.

Mocht deze query erg lang duren, dan komt dat waarschijnlijk omdat de subselect nu voor ieder record in de hoofdselect wordt uitgevoerd. Niet gehinderd door enige kennis van MySQL zou ik daarom voorstellen het om te schrijven naar een subquery, als volgt:

SQL:

select m1.email, min(m1.mem_id)
from
  mem m1,
  (
    select email, max(length(concat(...))) max_length
    from mem
    group by m2.email
    having count(email) > 1
  ) m2
where
m1.email = m2.email
and m1.length(concat(...)) = m2.max_length
group by m1.email