[SQL] Voorraadsysteem met historie

[T_E_O]

Strevend naar een optimaal DB-ontwerp (mysql 5.0) voor een voorraadsysteem loop ik tegen een dilemma aan. Ik ben op zoek naar een oplossing die snel (<1sec/q) werkt met zeg 10.000 records, moeilijk of niet in een inconsistente staat te brengen is en die geen data (drie/vier/etc-)dubbel opslaat.

In de vereenvoudiging van het probleem heb ik twee tabellen. In de tabel 'partij' staat per record een partij die een referentienummer en nog een aantal vaste kenmerken heeft. In de tabel 'mutatie' staan mutaties die invloed hebben op een partij, zoals het veranderen van eigenaar of het aanpassen van de locatie of hoeveelheid. Bij veel mutaties verandert slechts één van de eigenschappen van de partij. De kolommen van eigenschappen die onveranderd blijven staan op NULL.

MySQL Workbench maakt er het volgende plaatje van:


Dit lijkt me de meest efficiënte vorm van opslag, maar levert met het raadplegen wat problemen op. Ik wil een overzicht kunnen maken van alle partijen en daarbij per partij de huidige eigenaar, locatie en hoeveelheid tonen. Doordat de mutaties op de hoeveelheid relatief zijn, kan ik met een simpele join en een sum() wel de hoeveelheid tonen, maar de eigenaar en locatie zijn lastiger tevoorschijn te halen zonder een afhankelijke subquery te gebruiken en daarmee performance in te leveren.

De eerste semi-oplossing is om door de applicatie of eventueel triggers de huidige eigenaar en locatie ook in de partij-tabel op te slaan. Dat past echter niet bij een correct database-ontwerp en het kan bovendien voorkomen dat de voorraad op een bepaalde datum moet worden opgemaakt. De extra gegevens in de partij-tabel zijn dan zinloos en de juiste eigenaar op die datum moet alsnog uit de mutatie-tabel worden gehaald.

De tweede oplossing is om in de mutatie-tabel altijd alle kolommen te vullen. Een mutatie waarbij slechts de eigenaar verandert zal dan ook de locatie bevatten die op dat moment voor de partij geldt. Doordat de hoeveelheid relatief is, kan die natuurlijk wel op 0 blijven. Het probleem hierbij is, dat het tussenvoegen van een mutatie nogal wat werk oplevert en de database makkelijk in een inconsistente staat kan worden gebracht. Bij het tussenvoegen van een mutatie moet dan van een aantal volgende mutaties bijvoorbeeld de eigenaar worden aangepast. Hetzelfde geldt voor het verwijderen van een mutatie.

Er zijn natuurlijk meer lelijke oplossingen te bedenken, maar een nette heb ik nog niet kunnen vinden. Wat zie ik hierbij over het hoofd ? Ik kwam vragen bij het zoeken wel vragen van mensen tegen die een LAST() of FIRST() aggregate function willen, maar dat zit nu eenmaal niet in MySQL en naar ik begreep moeten ook de meeste andere RDBMSen het zonder doen.

[dvvelzen]

Op zich zou je die mutatie tabel meerdere keren kunnen bevragen met een:

code:

Where <kolom> is not null
group by partij
having datumtijd = max(datumtijd)

maar ik denk dat je voor wat je wil twee dingen door elkaar heen aan het halen bent. je "mutatie" tabel is meer een soort auditing tabel van wat er is gebeurd. Vandaar dat ik de "huidige" aantallen, eigenaar, .... altijd gewoon in de originele tabel zou laten bestaan.

my 2 ct,
Dennis

[T_E_O]

De mutatie-tabel zou inderdaad in 1 query meerdere keren bevraagd moeten worden, maar er is geen fatsoenlijke aggregate functie om dan de juiste waarde eruit te plukken. Ik zou iets willen als:

code:

SELECT partij.referentienummer,
  SUM(mhvh.delta_hvh) AS hvh, 
  LAST(meigenaar.eigenaar ORDER BY meigenaar.datumtijd ASC) AS eigenaar,
  LAST(mlocatie.locatie ORDER BY mlocatie.datumtijd ASC) AS locatie
FROM partij,
  LEFT JOIN mutatie meigenaar ON meigenaar.partij=partij.id AND meigenaar.eigenaar IS NOT NULL
  LEFT JOIN mutatie mlocatie ON mlocatie.partij=partij.id AND mlocatie.locatie IS NOT NULL
GROUP BY partij.id;

(de group_concat()-functie kan intern zo'n order by uitvoeren en in mijn SQL-fantasiewereld kunnen last() en first() dat ook )

De mutatie-tabel is meer dan een auditing tabel; ik wil ook de voorraadstand op bijvoorbeeld 31 december kunnen achterhalen. De huidige waarde van eigenaar en locatie bij de partij opslaan heb ik zeker overwogen, maar dan moet er alsnog met subqueries gewerkt gaan worden om de historische standen op te halen.

[Voutloos]

Dan heeft je fantasiewereld heeft heel wat aan de zoekterm 'groupwise maximum'.

[!null]

Ik heb tijdje terug ook een voorraad systeem gemaakt, en zonder er te diep op in te gaan (concurrent? ) heb ik voor de iets mindere oplossing gekozen. Ik heb dingen zoals huidige voorraad gewoon bij het item zelf gezet. Niet zo netjes qua database ontwerp, maar werkt wel makkelijk. Iedere mutatie gaat bij mij ook in een mutatie tabel, maar wordt ook toegepast op het 'huidige voorraad' veld. Dit werkt makkelijk en snel (geen zware queries) en zal ook niet veranderen als er straks duizenden mutaties in de database staan. Bij historische voorraad aantallen kan ik op basis van een datum gewoon makkelijk terugrekenen.

Eigenaar veld heb ik niet, en locatie is bij mij per item een vast iets. Maar goed, genoeg info zo

[pedorus]

Het leukste is om eens te kijken naar mysql-udf, en een udf te schrijven voor de aggregate-functie ATMAXNOTNULL(datetime,string). Misschien komt er eens een tijd dat ie standaard in MySQL zit, en zelfs geoptimaliseerd is.

Een simpelere oplossing is die van Voutloos (groupwise maximum). Je kan eens kijken naar constructies zoals de left join die hier bijvoorbeeld is gebruikt. Het niet-null mogen zijn is hier een leuk extra probleempje. [Bedenk ook wat moet er gebeuren als er alleen maar null voorkomt. Geen rij, of null als resultaat]

[dvvelzen]

Het verdiend absoluut niet de schoonheidsprijs.......

let op
1: door de join moet er voor elke kolom per partij een waarde zijn om het naar voren te halen
2: je datumtijd kolom moet een UNIQUE bevatten dubbele tijden = dubbele aantallen.

code:

create table partij
( id     int           not null
, refnr  varchar(500)  not null
)

insert into partij values ( 1, "eerste partij" )
insert into partij values ( 2, "tweede partij" )

create table mutatie
( id         int           not null
, partij     int           not null
, datumtijd  datetime      not null
, eigenaar   int               null
, locatie    varchar(500)      null
, delta      int               null
)

insert into mutatie values ( 1, 1, '20080101', 1   , null      , null)
insert into mutatie values ( 2, 1, '20080102', null, 'loc1_old', null)
insert into mutatie values ( 3, 1, '20080103', null, null      , 100 )

insert into mutatie values ( 4, 2, '20080104', 22  , null      , null)
insert into mutatie values ( 5, 2, '20080105', null, 'loc2'    , null)
insert into mutatie values ( 6, 2, '20080106', null, null      , 200 )

insert into mutatie values ( 7, 1, '20080201', null, 'loc1_new', null)
insert into mutatie values ( 8, 1, '20080301', null, null      , -1  )
insert into mutatie values ( 9, 1, '20080401', 11  , null      , null)

insert into mutatie values (10, 2, '20080602', null, 'loc2_new', null)
insert into mutatie values (11, 2, '20080702', null, null      , -2  )
insert into mutatie values (12, 2, '20080802', 2   , null      , null)

create table dates 
( id    int       not null
, dt    datetime  not null
)

insert into dates values ( 1, '20080101' )
insert into dates values ( 2, '20080103' )
insert into dates values ( 3, '20080107' )
insert into dates values ( 4, '20080203' )
insert into dates values ( 5, '20080303' )
insert into dates values ( 6, '20080403' )
insert into dates values ( 7, '20080503' )
insert into dates values ( 8, '20080603' )
insert into dates values ( 9, '20080703' )
insert into dates values (10, '20080803' )

declare   @date  datetime
,         @i     int

select @i = min(id) from dates
while @@rowcount > 0 and @i is not null
begin
         select     @date = dt
         from       dates
         where      id = @i
         
         select     @date
         ,          convert(varchar(14),a.refnr)
         ,          convert(varchar(30),convert(char(3), ( select sum(b.delta) 
                                                           from   mutatie b 
                                                           where  a.id         = b.partij 
                                                           and    b.datumtijd <= @date    ) )
                             + ' || ' + convert(char(3), c.eigenaar)
                             + ' || ' + d.locatie 
                    )
         from       partij    a
         join       mutatie   c  on a.id             = c.partij
                                and c.eigenaar  is not null
                                and c.datumtijd     <= @date  
         join       mutatie   d  on a.id             = d.partij
                                and d.locatie   is not null
                                and d.datumtijd     <= @date                            
         group by   a.refnr
         having     c.datumtijd = max(c.datumtijd)
         and        d.datumtijd = max(d.datumtijd)
         order by   a.id
         
         select @i = min(id) 
         from   dates
         where  id > @i
end
go


OUTPUT:


 -------------------------- -------------- ------------------------------

(0 rows affected)
(1 row affected)
(1 row affected)

 -------------------------- -------------- ------------------------------
        Jan  3 2008 12:00AM eerste partij  100 || 1   || loc1_old

(1 row affected)
(1 row affected)
(1 row affected)

 -------------------------- -------------- ------------------------------
        Jan  7 2008 12:00AM eerste partij  100 || 1   || loc1_old
        Jan  7 2008 12:00AM tweede partij  200 || 22  || loc2

(2 rows affected)
(1 row affected)
(1 row affected)

 -------------------------- -------------- ------------------------------
        Feb  3 2008 12:00AM eerste partij  100 || 1   || loc1_new
        Feb  3 2008 12:00AM tweede partij  200 || 22  || loc2

(2 rows affected)
(1 row affected)
(1 row affected)

 -------------------------- -------------- ------------------------------
        Mar  3 2008 12:00AM eerste partij  99  || 1   || loc1_new
        Mar  3 2008 12:00AM tweede partij  200 || 22  || loc2

(2 rows affected)
(1 row affected)
(1 row affected)

 -------------------------- -------------- ------------------------------
        Apr  3 2008 12:00AM eerste partij  99  || 11  || loc1_new
        Apr  3 2008 12:00AM tweede partij  200 || 22  || loc2

(2 rows affected)
(1 row affected)
(1 row affected)

 -------------------------- -------------- ------------------------------
        May  3 2008 12:00AM eerste partij  99  || 11  || loc1_new
        May  3 2008 12:00AM tweede partij  200 || 22  || loc2

(2 rows affected)
(1 row affected)
(1 row affected)

 -------------------------- -------------- ------------------------------
        Jun  3 2008 12:00AM eerste partij  99  || 11  || loc1_new
        Jun  3 2008 12:00AM tweede partij  200 || 22  || loc2_new

(2 rows affected)
(1 row affected)
(1 row affected)

 -------------------------- -------------- ------------------------------
        Jul  3 2008 12:00AM eerste partij  99  || 11  || loc1_new
        Jul  3 2008 12:00AM tweede partij  198 || 22  || loc2_new

(2 rows affected)
(1 row affected)
(1 row affected)

 -------------------------- -------------- ------------------------------
        Aug  3 2008 12:00AM eerste partij  99  || 11  || loc1_new
        Aug  3 2008 12:00AM tweede partij  198 || 2   || loc2_new

(2 rows affected)
(1 row affected)
1>

maar probeer liever een andere oplossing te vinden.

gr,
Dennis

[T_E_O]

@GreenSky veel concurrentie komen we gelukkig niet tegen in onze niche, dus wees niet bang

De gegevens bij 1 item op een bepaalde datum uitrekenen is inderdaad geen probleem op die manier, maar ik wil lekker snel een overzicht-uit-het-verleden van de hele voorraad tevoorschijn kunnen toveren.

Ik denk dat ik met de term groupwise maximum er al ben. Ik heb deze zelfde pagina gisteravond zelfs nog bezocht, maar ik denk dat mijn hersenactiviteit dusdanig gedaald was, dat ik niet zag hoe deze methodiek me kon helpen.

Ik kom nu uit op een query als:

code:

SELECT partij.referentienummer, mutatie1.eigenaar, mutatie3.locatie, SUM(mutatie5.delta_hvh) AS hvh
FROM partij, mutatie AS mutatie1 
  JOIN (SELECT partij, MAX(datumtijd) AS datumtijd 
        FROM mutatie 
        WHERE eigenaar IS NOT NULL 
          AND datumtijd < '2008-01-01'
        GROUP BY mutatie.partij) AS mutatie2 
    ON mutatie1.partij=mutatie2.partij 
      AND mutatie1.datumtijd=mutatie2.datumtijd,
  mutatie AS mutatie3
  JOIN (SELECT partij, MAX(datumtijd) AS datumtijd
        FROM mutatie
        WHERE locatie IS NOT NULL
          AND datumtijd < '2008-01-01'
        GROUP BY mutatie.partij) AS mutatie4
    ON mutatie3.partij=mutatie4.partij
      AND mutatie3.datumtijd=mutatie4.datumtijd,
  mutatie AS mutatie5
WHERE partij.id=mutatie1.partij 
  AND partij.id=mutatie3.partij 
  AND partij.id=mutatie5.partij
  AND mutatie5.datumtijd < '2008-01-01'
GROUP BY partij.id;

@Voutloos: bedankt!

[!null]

Welke niche heb je het dan precies over?
Want maatwerk software is misschien al niet echt een niche markt meer haha. (al blijft het makkelijk om aan opdrachten te komen, kleinschalig dan he)

Verder zou je een nog smerigere oplossing kunnen bedenken zoals iedere dag (eventueel met een cronjob als het een linux systeem is, anders via applicatie oplossen) een punt maken, dit item had aan het begin van de dag (0:00) zoveel in voorraad. Nog minder netjes, maar wel snel data selecteren, en kun je ook zeer snel grafieken genereren mocht daar behoefte aan zijn.

[ Voor 49% gewijzigd door !null op 23-09-2008 10:46 ]

[Cartman!]

Misschien snap ik je probleem niet goed (genoeg) maar als je nu de tabel met een parentid opslaat als je een nieuwe revisie van de gegevens hebt?

Je hebt dus een row met daarin de gegevens van het bedrijf. Als het bedrijf nu een wijziging heeft dan kopieer je de record maar wijzig je die met de mutatiegegevens en je verwijst de parent naar de vorige record (waarvan je de kopie hebt gemaakt). Dus kun je toch simpel de laatste ophalen (adhv. datum bijv) en de hele geschiedenis van wijzigingen met bijhorende data opvragen?

[T_E_O]

pedorus schreef op dinsdag 23 september 2008 @ 10:28:
Het leukste is om eens te kijken naar mysql-udf, en een udf te schrijven voor de aggregate-functie ATMAXNOTNULL(datetime,string). Misschien komt er eens een tijd dat ie standaard in MySQL zit, en zelfs geoptimaliseerd is.

Een simpelere oplossing is die van Voutloos (groupwise maximum). Je kan eens kijken naar constructies zoals de left join die hier bijvoorbeeld is gebruikt. Het niet-null mogen zijn is hier een leuk extra probleempje. [Bedenk ook wat moet er gebeuren als er alleen maar null voorkomt. Geen rij, of null als resultaat]

Geen rij of null als resultaat betekent dat de partij niet in het resultaat moet voorkomen, dus dat loopt met de momenteel gebruikte oplossing goed. Een UDF is op zich leuk, maar de performance zou weleens tegen kunnen vallen. De groupwise maximum-oplossing werkt op zich perfect voor wat ik wil doen, maar de performance moet ik nog even op orde krijgen (daarover vanavond laat meer)

Ik zie niet goed hoe dit met een left join is op te lossen, maar daar zal ik morgen nog 's op puzzelen.

dvvelzen schreef op dinsdag 23 september 2008 @ 10:30:
Het verdiend absoluut niet de schoonheidsprijs.......

let op
1: door de join moet er voor elke kolom per partij een waarde zijn om het naar voren te halen
2: je datumtijd kolom moet een UNIQUE bevatten dubbele tijden = dubbele aantallen.

code:

...

maar probeer liever een andere oplossing te vinden.

gr,
Dennis

Dank voor de uitgebreide reactie, maar het doel was voornamelijk om 1 voorraadstand op 'n wilekeurig gekozen tijdstip te kunnen genereren. Een hele log zoals je voorstelt is (gelukkig) niet nodig en zou hoogstens per partij nodig zijn en dat is vrij eenvoudig te doen, omdat 't eventueel in meerdere queries gedaan kan worden.

GreenSky schreef op dinsdag 23 september 2008 @ 10:41:
Welke niche heb je het dan precies over?
Want maatwerk software is misschien al niet echt een niche markt meer haha. (al blijft het makkelijk om aan opdrachten te komen, kleinschalig dan he)

Verder zou je een nog smerigere oplossing kunnen bedenken zoals iedere dag (eventueel met een cronjob als het een linux systeem is, anders via applicatie oplossen) een punt maken, dit item had aan het begin van de dag (0:00) zoveel in voorraad. Nog minder netjes, maar wel snel data selecteren, en kun je ook zeer snel grafieken genereren mocht daar behoefte aan zijn.

Het is 90% standaardapplicatie met 10% maatwerk Onze concurrenten zijn leveranciers van SAP en andere dure oplossingen, waardoor wij het heel vaak op prijs winnen.

Een smerige oplossing had ik inderdaad ook al bedacht, maar eigenlijk al snel weer afgeschreven. Het kan voorkomen dat er achteraf een mutatie in het verleden wordt doorgevoerd, die dan ook in de historische overzichten meteen moet worden meegenomen. Dat gebeurt bijvoorbeeld bij de maand- of kwartaalafsluiting nogal eens. Als ze op de volgende dag moeten wachten tot ze het resultaat zien of eventueel handmatig een update moeten triggeren en die afwachten, dan ben ik bang dat we het alsnog afleggen tegen de SAP-leveranciers en hun collega's

Cartman! schreef op dinsdag 23 september 2008 @ 15:22:
Misschien snap ik je probleem niet goed (genoeg) maar als je nu de tabel met een parentid opslaat als je een nieuwe revisie van de gegevens hebt?

Je hebt dus een row met daarin de gegevens van het bedrijf. Als het bedrijf nu een wijziging heeft dan kopieer je de record maar wijzig je die met de mutatiegegevens en je verwijst de parent naar de vorige record (waarvan je de kopie hebt gemaakt). Dus kun je toch simpel de laatste ophalen (adhv. datum bijv) en de hele geschiedenis van wijzigingen met bijhorende data opvragen?

Het probleem daarbij is, dat er achteraf correcties uitgevoerd moeten kunnen worden. Het kan bijvoorbeeld gebeuren dat men vandaag pas ontdekt, dat een wijziging van de eigenaar in april niet is doorgevoerd. Met het database-schema dat ik in gedachten heb (en de intussen dankzij Voutloos opgezette queries), is dat geen probleem. Het is dan een kwestie van een record toevoegen met de betreffende partij, de datum in april en de juiste eigenaar.

De query die ik eerder postte (met groupwise maxima) levert precies het gewenste resultaat op, maar nu moet ik de performance nog zien op te lappen. Wanneer ik 20.000 partijen uit m'n duim zuig met ieder 15 mutaties, dan kost 't zo een aantal minuten om een voorraadstand te bepalen.

Het moet natuurlijk veel rapper kunnen; er zijn geen dependent subqueries meer, dus met de juiste indices moet het volgens mij heel snel kunnen gaan. Ik zal vanavond of anders morgen wat create tables en dergelijke plaatsen.

[Cartman!]

Het probleem daarbij is, dat er achteraf correcties uitgevoerd moeten kunnen worden. Het kan bijvoorbeeld gebeuren dat men vandaag pas ontdekt, dat een wijziging van de eigenaar in april niet is doorgevoerd.

Maar dan is er toch heel iets anders mis in je systeem

[T_E_O]

Cartman! schreef op dinsdag 23 september 2008 @ 21:27:
[...]

Maar dan is er toch heel iets anders mis in je systeem

Dat is meer een probleem bij de gebruikers Ik bedoelde eigenlijk ingevoerd in plaats van doorgevoerd. De voorraadmutaties komen op papier binnen en moeten dan ingevoerd worden, maar daar gaat weleens iets mis.

[Gomez12]

Persoonlijk zou ik gewoon een paar veldjes bij het huidige record erbij zetten voor de snelheid, deze bevatten huidige voorraad / eigenaar etc. Je mutatietabel blijft gewoon bestaan.

Het enige wat je dan nog nodig hebt is een functie die uit je mutatietabel je artikel tabel bijwerkt ( qua huidige voorraad etc ). Praktisch gezien zou je dan iets kunnen zeggen als : normale verkopen is artikeltabel + / - en mutatietabel bijwerken. Correcties betekenen mutatietabel bijwerken en artikeltabel gegevens worden opnieuw berekend.

In de praktijk kun je dan werken vanuit je huidige voorraad in je artikeltabel. Dit is supersnel.
Alle mogelijke inconsequenties die voortkomen uit dubbele opslag zijn te herstellen door een correctie programma ( je mutatietabel is altijd 100% correct is dan het uitgangspunt ).

Dan heb je supersnel een simpel getal, voor een report kun je teruggrijpen naar de mutatie tabel.

Zo zou ik het opzetten.

[!null]

Dit is dus hoe ik het ook gemaakt heb. Al was een correctie programma in mijn geval niet echt nodig, mutaties uit het verleden die later worden ingevoerd worden bij mij ook gewoon direct op de hoofdtabel bewerkt zoals iedere andere mutatie dus klopt het altijd.
Er zijn nettere constructies, maar dan heb je weer een stuk meer rekenkracht nodig voor simpele rapportjes. Bedenk een overzichtje met alle items en hun huidige voorraad, dat is meteen een ingewikkelde en zware query.

[T_E_O]

Gomez12 schreef op dinsdag 23 september 2008 @ 22:51:
Persoonlijk zou ik gewoon een paar veldjes bij het huidige record erbij zetten voor de snelheid, deze bevatten huidige voorraad / eigenaar etc. Je mutatietabel blijft gewoon bestaan.

Het enige wat je dan nog nodig hebt is een functie die uit je mutatietabel je artikel tabel bijwerkt ( qua huidige voorraad etc ). Praktisch gezien zou je dan iets kunnen zeggen als : normale verkopen is artikeltabel + / - en mutatietabel bijwerken. Correcties betekenen mutatietabel bijwerken en artikeltabel gegevens worden opnieuw berekend.

In de praktijk kun je dan werken vanuit je huidige voorraad in je artikeltabel. Dit is supersnel.
Alle mogelijke inconsequenties die voortkomen uit dubbele opslag zijn te herstellen door een correctie programma ( je mutatietabel is altijd 100% correct is dan het uitgangspunt ).

Dan heb je supersnel een simpel getal, voor een report kun je teruggrijpen naar de mutatie tabel.

Zo zou ik het opzetten.

Een correctieprogramma is juist iets waar ik weinig trek in heb. Daar krijg je (naar mijn ervaring tenminste) ook slordige code door, 'want het correctieprogramma fixt het toch wel mocht het misgaan'. Dat hoeft natuurlijk niet, maar het risico is er. Bovendien staat het opslaan van afgeleide data me principieel al niet erg aan. De applicatie zelf heeft eigenlijk nooit een overzicht van alle voorraden nodig, dus dat kan prima direct uit die mutatie-tabel gehaald worden en is niet zozeer het probleem. Het rapport moet zeker tijdens de afsluiting regelmatig geopend worden en moet dan ook rap tevoorschijn komen; zowel met actuele gegevens als met een historische stand.

GreenSky schreef op dinsdag 23 september 2008 @ 23:30:
Dit is dus hoe ik het ook gemaakt heb. Al was een correctie programma in mijn geval niet echt nodig, mutaties uit het verleden die later worden ingevoerd worden bij mij ook gewoon direct op de hoofdtabel bewerkt zoals iedere andere mutatie dus klopt het altijd.
Er zijn nettere constructies, maar dan heb je weer een stuk meer rekenkracht nodig voor simpele rapportjes. Bedenk een overzichtje met alle items en hun huidige voorraad, dat is meteen een ingewikkelde en zware query.

Ik ben er met m'n lean & mean-DB-ontwerp bijna uit. Dan hoef ik geen ingewikkelde code te schrijven om direct de hoofdtabel bij te gaan werken en dan krijg ik ook geen dubbele data in m'n database.

pedorus schreef op dinsdag 23 september 2008 @ 10:28:
Het leukste is om eens te kijken naar mysql-udf, en een udf te schrijven voor de aggregate-functie ATMAXNOTNULL(datetime,string). Misschien komt er eens een tijd dat ie standaard in MySQL zit, en zelfs geoptimaliseerd is.
.....

Met een beetje geluk krijg je wat mij betreft de hoofdprijs Met de group_concat() functie van MySQL kom ik ZO dicht in de buurt, dat ik me toch aan een UDF ga wagen. De performance van een UDF schijnt best goed te (kunnen) zijn en de query wordt veel eleganter en hopelijk vele malen sneller, dan wanneer ik met de hierboven geplakte independent subqueries aan de slag ga.

Het volgende komt zeeer dicht in de buurt:

code:

SELECT partij.referentienummer,
  GROUP_CONCAT(mutatie.locatie ORDER BY IF(mutatie.locatie is null, 0, 1) DESC, mutatie.datumtijd DESC) AS locatie,
  GROUP_CONCAT(mutatie.eigenaar ORDER BY IF(mutatie.eigenaar is null, 0, 1) DESC, mutatie.datumtijd DESC) AS eigenaar
FROM partij, mutatie 
WHERE partij.id=mutatie.partij 
  AND mutatie.datumtijd < '2008-10-01' 
GROUP BY partij.id;

Nu een UDF maken die hetzelfde doet als group_concat(), maar dan altijd slechts de waarde van 1 row teruggeeft en dat in het datatype van de oorspronkelijke kolom. Bovenstaande query doet over een dataset met 20.000 partijen die ieder 15 mutaties hebben zo'n 2 à 3 seconden en dat is gewoon acceptabel. De andere pogingen kwamen ondanks de juiste indices niet onder de 3 minuten. Het zou kunnen dat ik ze nog verder zou kunnen optimaliseren, maar de elegantie die mbv een UDF bereikt wordt zit er dan gewoon niet in.

EDIT:
SUBSTRING_INDEX blijkt ook mijn vriend te zijn. Dan is er zelfs geen UDF meer nodig:

code:

SELECT partij.referentienummer,
  SUBSTRING_INDEX(GROUP_CONCAT(mutatie.locatie ORDER BY IF(mutatie.locatie is null, 0, 1) DESC, mutatie.datumtijd DESC), ',', 1) AS locatie,
  SUBSTRING_INDEX(GROUP_CONCAT(mutatie.eigenaar ORDER BY IF(mutatie.eigenaar is null, 0, 1) DESC, mutatie.datumtijd DESC), ',' , 1) AS eigenaar
FROM partij, mutatie 
WHERE partij.id=mutatie.partij 
  AND mutatie.datumtijd < '2008-10-01' 
GROUP BY partij.id;

EDIT2:
Nee, locatie moet geen komma's bevatten Maar met een andere delimiter moet dat helemaal goedkomen.

[ Voor 7% gewijzigd door T_E_O op 24-09-2008 19:01 ]

[Gomez12]

T_E_O schreef op woensdag 24 september 2008 @ 18:54:
[...]
Een correctieprogramma is juist iets waar ik weinig trek in heb. Daar krijg je (naar mijn ervaring tenminste) ook slordige code door, 'want het correctieprogramma fixt het toch wel mocht het misgaan'.

Dat ligt puur aan je eigen instelling. Ik heb alleen correctieprogramma's nodig omdat ik bijna altijd zie bij afgeleide waarden dat er ooit iemand een quick-fix schrijft die niet alles bijwerkt ( of even een handmatige ingreep in de dbase doet etc.etc. ), officieel hoort het niet te gebeuren. Praktisch zie ik het gebeuren dus is er een fix-programma voor.

Bovendien staat het opslaan van afgeleide data me principieel al niet erg aan.

Wacht maar tot je grotere datasets gaat krijgen, dan krijg je vanzelf een punt waarop je moet kiezen tussen of een trage app hebben ( met 1 gigantische dataset ) of je halve app herschrijven naar een nog genormaliseerder datamodel, of gewoon afgeleide data gaan gebruiken...

[...]
Bovenstaande query doet over een dataset met 20.000 partijen die ieder 15 mutaties hebben zo'n 2 à 3 seconden en dat is gewoon acceptabel.
[/quote]
Hmmm, intermediate recordsets van 300.000 records zouden toch wel een paar tikkeltjes sneller moeten kunnen imho.
Sowieso is het maar net de vraag of 20.000 partijen een realistische testset is. Gemiddelde groothandel waar ik gewerkt heb deed dit per 2 maanden. Vraag dit dus even goed na...

[pedorus]

T_E_O schreef op woensdag 24 september 2008 @ 18:54:

code:

  GROUP_CONCAT(mutatie.locatie ORDER BY IF(mutatie.locatie is null, 0, 1) DESC, mutatie.datumtijd DESC) AS locatie,

He, je voegt zojuist iets toe aan mijn kennis over group_concat. Jammer dat limit nog niet wordt ondersteund. Het mooie is voor dit geval verder dat nulls toch al worden genegeerd. Je kan dus dingen doen als:

SQL:

substring_index(group_concat(
    mutatie.locatie order by mutatie.datumtijd desc 
    separator 0x0),0x0,1) locatie

Als de karakterinstellingen goedstaan dan zou 0x0 niet in locatie moeten voorkomen lijkt mij.

Verder zou ik nog even nadenken over

SQL:

set session group_concat_max_len = 500

Als je dan vervolgens

SQL:

rtrim(group_concat(dealer order by price separator '             ')) 

doet met 500(!) spaties dan zou je precies het juiste resultaat moeten krijgen. Dit zou iets sneller kunnen werken, omdat dan niet alle rijen geëvalueerd hoeven te worden. Lelijke hack natuurlijk, maar volledig volgens specs.

Gomez12 schreef op woensdag 24 september 2008 @ 19:16:
Wacht maar tot je grotere datasets gaat krijgen, dan krijg je een punt waarop je moet kiezen tussen of een trage app hebben ( met 1 gigantische dataset ) of je halve app herschrijven naar een nog genormaliseerder datamodel, of gewoon afgeleide data gaan gebruiken...

Volgens mij houdt TS zich aan de best practice op het gebied van normalisatie:
Eerst alles gewoon genormaliseerd uitproberen. Verder niet denormaliseren als het niet perse hoeft.