[SQL] Dubbele waarden verwijderen

[easydisk]

In een tabel in een MySQL database staan ruim 3 miljoen records en dat neemt met 6 record per minuut toe.. Maar veel record zijn het zelfde... dus wil wat opruimen.


Datumtijd, kanaal, waarde
2012-01-01 00:01:00, 1, 100
2012-01-01 00:02:00, 1, 150
2012-01-01 00:03:00, 1, 150
2012-01-01 00:04:00, 1, 150
2012-01-01 00:05:00, 1, 150
2012-01-01 00:06:00, 1, 150
2012-01-01 00:07:00, 1, 202
2012-01-01 00:01:00, 2, 693
2012-01-01 00:02:00, 2, 754
2012-01-01 00:03:00, 2, 754
2012-01-01 00:04:00, 2, 754
2012-01-01 00:05:00, 2, 801
2012-01-01 00:06:00, 2, 801
2012-01-01 00:07:00, 2, 801

Key waarde in de tabel is datum, kanaal.

Zoals je ziet geeft kanaal 1 meerdere keer de waarden 150

2012-01-01 00:02:00, 1, 150
2012-01-01 00:03:00, 1, 150
2012-01-01 00:04:00, 1, 150
2012-01-01 00:05:00, 1, 150
2012-01-01 00:06:00, 1, 150

dat wil ik terug brengen naar

2012-01-01 00:02:00, 1, 150
2012-01-01 00:06:00, 1, 150

De eerste en laatste waarden moet ik behouden, en voor elk kanaal apart..


Kan ik dit opruimen ook met een sql query ?

[Onbekend]

Ja, dat kan. Maar is wel wat lastig.
Je query wordt zoiets als Delete records where Datum <> MAX(Datum) AND Datum <> MIN(DATUM)

[kluyze]

Waarom wil je die weg? Hoeveel dubbele data zit er zo in, wat is de winst?

Als ik die structuur aanhoud, dus enkel die 3 kolommen, kom ik voor 3 miljoen records op een schijfruimte van minder dan 100MB uit.
Stel DATETIME = 8bytes, 2xBIGINT = 8bytes * 3000000 = ~70MB en dan is BIGINT waarschijnlijk heel ruim genomen.

Momenteel hebben we voor een project tabellen met 8 miljard rijen (data van 3 maanden of zo), de pagina's die die tabellen gebruiken, laden nog steeds onder de seconde. Dus kwa snelheid is dit op een MySQL database ook geen issue. Misschien is de keuze van indexen niet heel goed gemaakt?

Ik weet niet hoe die data nog gebruikt wordt, maar als je er straks rapporten over moet genereren, ben je misschien meer tijd kwijt om je queries daar op aan te passen.

Is het misschien een idee om per jaar een archief schema/tabel te voorzien?

Ik heb niet onmiddellijk een oplossing voor je probleem, maar ik ben momenteel bezig met rapporten te genereren over tabellen die enkel de verandering bij houden. Dat is niet altijd even makkelijk. Daarom dat ik probeer te vissen naar het waarom van deze vraag.

[easydisk]

Indexen staan goed, schijfruimte is geen probleem.

Ik ga het echter matchen met andere gegevens, eerste poging is op zelfde kanaal, echter in 25% van de gevallen is het door menselijke fouten toch op een ander kanaal.. Die 2de match maak ik in php en moet ik daar al de regels van ca 1 uur per kanaal door (60 regels.. valt mee.. maar oke) als ik dat kan inkorten scheelt weer...

[RobIII]

Onbekend schreef op donderdag 07 februari 2013 @ 21:04:
Ja, dat kan. Maar is wel wat lastig.
Je query wordt zoiets als Delete records where Datum <> MAX(Datum) AND Datum <> MIN(DATUM)

Daar doe je nogal een aanname (los van 't feit dat de query feitelijk iets ingewikkelder zal zijn ); je gaat er namelijk, zoals ik je query lees en interpreteer wat je bedoelt, nu van uit dat een kanaal, zoals kanaal 150 in TS's voorbeeld, maar 1 keer voorkomt:

code:

Datumtijd, kanaal, waarde
2012-01-01 00:01:00, 1, 100
2012-01-01 00:02:00, 1, 150
2012-01-01 00:03:00, 1, 150
2012-01-01 00:04:00, 1, 150
2012-01-01 00:05:00, 1, 150
2012-01-01 00:06:00, 1, 150
2012-01-01 00:07:00, 1, 202

Wat nu als je dit hebt:

code:

Datumtijd, kanaal, waarde
2012-01-01 00:01:00, 1, 100
2012-01-01 00:02:00, 1, 150
2012-01-01 00:03:00, 1, 999
2012-01-01 00:04:00, 1, 999
2012-01-01 00:05:00, 1, 999
2012-01-01 00:06:00, 1, 150
2012-01-01 00:07:00, 1, 202

Dan mikker je ook alles weg van 03:00 t/m 5:00.

Nee, simpel gaat deze query denk ik niet worden. Als het überhaupt al kan wordt 't een bitch van een query.

[ Voor 4% gewijzigd door RobIII op 07-02-2013 22:09 ]

[easydisk]

Heb maar een php script gemaakt, van 4.1 miljoen records terug naar 120.000 record.. omdat de match met een "between datumtijd_start and datumtijd_eind" werd gemaakt en mysql toch wat problemen heet met deze optimalisatie, ondanks juiste index scheelt het toch aardig qua tijd nu

[pedorus]

Vraag me af wat de performance van zoiets is:

SQL:

delete a from tabel a inner join tabel b on
    b.datum<a.datum and a.kanaal=b.kanaal and a.waarde=b.waarde 
    left join tabel c on    
    c.datum<a.datum and c.datum>b.datum and a.kanaal=c.kanaal
    where c.datum is null;

Let wel op dat je hiermee toch gegevens weggooit: je weet niet meer of er een echte not available was, of dat je data hebt "opgeruimd". Sowieso een backup maken natuurlijk.

[Onbekend]

RobIII schreef op donderdag 07 februari 2013 @ 22:08:
[...]

Daar doe je nogal een aanname (los van 't feit dat de query feitelijk iets ingewikkelder zal zijn );

Het is denk ik niet de bedoeling om een complete query hier uit te schrijven.
Het was meer als hint bedoeld om de TS een idee te geven.

je gaat er namelijk, zoals ik je query lees en interpreteer wat je bedoelt, nu van uit dat een kanaal, zoals kanaal 150 in TS's voorbeeld, maar 1 keer voorkomt:

Ik had het verhaal van de TS inderdaad niet op die manier gelezen. Indien het kanaal meerdere keren voorkomt met een ander kanaal ertussen, kan dat ook maar dat moet je met subquery's en evt. met temptables werken. De query wordt dan inderdaad een stuk lastiger.

[Gropah]

Je zou kunnen ovewegen alle eerste keren dat het voorkomt in een aparte tabel te zetten en de 2e keer in de 2e. Als de combinatie dan al eens voorkomt kun je die er uit gooien en de nieuwe er in dumpen. Kost wel heel wat schrijf actie's meer.

[CH4OS]

En een theoretische (denk ik) oneindig aantal tabellen... Omdat TS waarschijnlijk niet weet hoe vaak een datumtijd, kanaal en waarde voorkomen.

[ Voor 43% gewijzigd door CH4OS op 07-02-2013 23:05 ]

[Gropah]

Nee, want als je de combinatie weer voorkomt in de 2e tabel, verwijder je deze en zet je de nieuwe er in.

[Asator]

Wellicht dat het in dit geval het eenvoudigste is om 2 queries te schrijven. Een voor het ophalen van de kanaal + waarde combinatie met de laagst voorkomende datum, en een voor de hoogst voorkomende datum.

Stop de resultaten van beide queries in een nieuwe table. Zorg dat in deze table ook niet weer nieuwe dubbele waardes kunnen voorkomen. Delete de andere table en rename de nieuwe table.

EDIT: Als je 2 losse queries hebt die de hoogste en laagste datum ophalen dan zou je weer moeten controleren dat je niet twee keer dezelfde ophaalt wanneer de combinatie van kanaal en waarde maar 1 keer voor komt. Je kan beter de 2 queries direct samenvoegen met een UNION.

pedorus schreef op donderdag 07 februari 2013 @ 22:30:
Vraag me af wat de performance van zoiets is:

SQL:

delete a from tabel a inner join tabel b on
    b.datum<a.datum and a.kanaal=b.kanaal and a.waarde=b.waarde 
    left join tabel c on    
    c.datum<a.datum and c.datum>b.datum and a.kanaal=c.kanaal
    where c.datum is null;

Let wel op dat je hiermee toch gegevens weggooit: je weet niet meer of er een echte not available was, of dat je data hebt "opgeruimd". Sowieso een backup maken natuurlijk.

Deze query bewaard alleen het eerste resultaat, easydisk wilt de eerste waarde EN de laatste waarde bewaren.

[ Voor 58% gewijzigd door Asator op 08-02-2013 21:19 ]