[MySQL] Duplicate entry 1 random over houden,1 kol afwijkend

[Bananenspin]

Probleem: Ik heb door middel van een query een hoop resultaten (16.800 ) waarvan der een hoop duplicated entry's zijn die maar op één kolom verschilt met de andere duplicates die hij kan hebben. Nu wil ik dat de duplicates verdwijnen maar de waarde van deze afwijkende kolom random kiest van de mogelijke resultaten.

De waarde kan 1 t/m 9 zijn maar meestal zal bij elk unieke id er nog 2 of 3 andere duplicates bestaan.
Wellicht even ter verduidelijking:

id | naam | afwijking
2 Bert 3
2 Bert 5
2 Bert 6
3 Henk 1
3 Henk 9
4 Sjaak 3

Van de waardes die de afwijking kolom kent eentje kiezen en en de rest van de duplicates foetsie!

edit: toevallig zijn deze ook gesorteerd in de resultaten dus als ik altijd de eerste zou pak is het niet meer random

[ Voor 8% gewijzigd door Bananenspin op 01-08-2013 20:13 ]

[NMe]

"Eentje kiezen"....welke dan? Als je dat zelf niet weet: hoe verwacht je dan dat MySQL dat gaat weten.

Nouja, dat zeg ik wel, maar als je gewoon een willekeurige afwijking wil is MySQL daar als enige DBMS redelijk makkelijk in:

SQL:

SELECT id, naam, afwijking FROM tabel GROUP BY id

Je kan ze ook gewoon allemaal opvragen:

SQL:

SELECT id, naam, GROUP_CONCAT(afwijking) AS afwijkingen FROM tabel GROUP BY id

Overigens ziet deze tabel er niet goed genormaliseerd uit omdat je twee kolommen hebt met waardes die altijd bij elkaar horen (gelijk zijn).

[Bananenspin]

Met eentje kiezen bedoel ik dan random van de waardes die bestaan! Dus bij 'Bert' kan hij kiezen de 3, 5 of 6 te nemen.

Dit is overigens geen tabel maar de resultaten van een query waar ik de duplicates uit wil en deze dan gebruiken voor een nieuwe tabel. Met group by pakt hij altijd het bovenste resultaat dus in geval van Bert zal dat dan altijd 3 zijn

[ Voor 16% gewijzigd door Bananenspin op 01-08-2013 20:22 ]

[Voutloos]

Bananenspin schreef op donderdag 01 augustus 2013 @ 20:19:
Met group by pakt hij altijd het bovenste resultaat dus in geval van Bert zal dat dan altijd 3 zijn

Dat is niet per se gegarandeerd. Zonder aggregate function heb je eigenlijk geen correcte query.

Anyway, met GROUP_CONCAT en dan in code een waarde uit de lijst pakken ben je er al. Kan ook wel in mysql zelf: gebruik die voorbeeld query als derived table en pak dan in de buitenste query een waarde op basis van RAND().

[Fish]

Klinkt meer als een verkeerde join. of staan er ook duplicate entries in de table ?

[BikkelZ]

JOIN met zichzelf waarbij er een ORDER BY RAND() plaatsvindt in de binnenste query en de GROUP BY in de buitenste.

Uit de losse pols:

SQL:

SELECT t.id, t.naam, ta.afwijking
FROM Tabel t
JOIN ( SELECT afwijking FROM Tabel ta GROUP BY id ORDER BY RAND() ) ON t.id = ta.id

Ik zal het hier eens proberen met een testtabelletje

[Voutloos]

Die query is incorrect, want group by zonder aggregate functions. Had ik al voor gewaarschuwd.

De order by gaat op ná het groeperen, en op dat punt heb je dus al op onjuiste wijze de data gekozen. Het zou hoogstens correct zijn als er een specifieke 'group order by' zou zijn voor het sorteren/conflictresolutie vóór het groeperen, maar die feature bestaat niet en dat is misschien maar goed ook.

[_js_]

Bij de unieke id en naam een willekeurige waarde van afwijking kiezen die erbij hoort:

SQL:

SELECT *,
(
  SELECT afwijking
  FROM tabel AS ta
  WHERE ta.id = tb.id AND ta.naam = tb.naam
  ORDER BY RAND()
  LIMIT 0,1
)
FROM
(
  SELECT DISTINCT id, naam
  FROM tabel
) AS tb

[BikkelZ]

Voutloos schreef op vrijdag 02 augustus 2013 @ 08:22:
Die query is incorrect, want group by zonder aggregate functions. Had ik al voor gewaarschuwd.

De order by gaat op ná het groeperen, en op dat punt heb je dus al op onjuiste wijze de data gekozen. Het zou hoogstens correct zijn als er een specifieke 'group order by' zou zijn voor het sorteren/conflictresolutie vóór het groeperen, maar die feature bestaat niet en dat is misschien maar goed ook.

Je hebt gelijk, ben het nog gaan proberen gisteren maar heb hier alleen MSSQL draaien en die begon over ongeveer het zelfde te mekkeren. Hier ga je niet uit komen zonder extra code of CURSOR achtige toestanden.

[NMe]

BikkelZ schreef op vrijdag 02 augustus 2013 @ 12:16:
[...]

Je hebt gelijk, ben het nog gaan proberen gisteren maar heb hier alleen MSSQL draaien en die begon over ongeveer het zelfde te mekkeren. Hier ga je niet uit komen zonder extra code of CURSOR achtige toestanden.

Valt wel mee hoor:

MySQL:

SELECT id, naam, afwijking FROM (
    SELECT id, naam, afwijking FROM tabel ORDER BY RAND()
) AS t GROUP BY id

Niet uitgeprobeerd en werkt sowieso alleen in MySQL, maar doet volgens mij wel wat de TS wil. Dat het ranzig is hoeft niemand me uit te leggen.

[BikkelZ]

NMe schreef op vrijdag 02 augustus 2013 @ 13:11:
[...]

Valt wel mee hoor:

MySQL:

SELECT id, naam, afwijking FROM (
    SELECT id, naam, afwijking FROM tabel ORDER BY RAND()
) AS t GROUP BY id

Niet uitgeprobeerd en werkt sowieso alleen in MySQL, maar doet volgens mij wel wat de TS wil. Dat het ranzig is hoeft niemand me uit te leggen.

Nice! GROUP BY naar buiten gooien, natuurlijk!

[NMe]

Uiteindelijk is het wel een trage query en is 't de smerigste query die ik in jaren geschreven heb, maar afhankelijk van de grootte van de dataset kan het een optie zijn.

[_js_]

NMe schreef op vrijdag 02 augustus 2013 @ 15:28:
Uiteindelijk is het wel een trage query en is 't de smerigste query die ik in jaren geschreven heb, maar afhankelijk van de grootte van de dataset kan het een optie zijn.

Maar nog niet zo traag als die van mij, die blijft echt lang hangen. Volgens TS was zijn dataset 16800 rijen, dus dat valt wel mee.

[CaVeFiSh]

Bananenspin schreef op donderdag 01 augustus 2013 @ 20:11:
Nu wil ik dat de duplicates verdwijnen maar de waarde van deze afwijkende kolom random kiest van de mogelijke resultaten.

Haha de meeste DBA/DBD's gaan echt steigeren als ze dit horen. Normaal gesproken stel je je filter vast aan de hand van de meest valide data als het gaat om duplicaten. Stel dat je bijvoorbeeld 2 kolommen met bijvoorbeeld de straatnaam met elkaar vergelijkt:
Record 1: Hans de Bakkerstraat 234
Record 2: ...

In dit geval zou met jou logica ook een foutief record getoond kunnen worden en gooi je "goede" data weg. Het de-dupliceren van data hoeft qua query helemaal niet zo snel en efficiënt mogelijk te zijn omdat dit als het goed is maar een eenmalige actie is. Nadat je dit heb uitgevoerd zou je dus iets moeten maken wat voorkomt dat er duplicaten in je database worden aangemaakt.

De logica waarmee je bepaald wat een duplicaat is of niet kan ik niet voor je bepalen maar meestal bevatten databases modified_on of andere kolommen waarin wijzigingen (change tracking) worden bijgehouden. In sommige gevallen kun je deze velden dus meenemen in de check, als je bijvoorbeeld een NAW gegevens tabel heb waarbij record 1 gister is aangepast en record 2 zo een 4 jaar geleden en de waardes overeen komen met record 1 (duplicaat) dan kun je er vaak wel vanuit gaan dat record 1 het gene is dat je wilt bewaren.