[SQL] veel kolommen met datestamps, som per kolom per dag.

[107mb]

in een productieomgeving worden producten per bewerkingstation gescanned en de datestamp in een DB opgeslagen.

de tabel ziet er alsvolgt uit:

barcode	machine1	machine2
1	07-07-2016 15:40:01	07-07-2016 15:41:10
2	07-07-2016 15:40:11	07-07-2016 15:41:45

de tabel bevat 1,2 miljoen barcodes en 60 kolommen met elk in elke kolom een machine.

Ik wil graag voor een rapportage een query maken die voor de huidige dag het aantal scans per machine geeft:

machine1	machine2	machine3
563	487	466

onderstaande query doet dat, maar is erg traag en ik vind hem niet elegant:

SQL:

select 
count(dmachine1.barcode) as machine1,
count(dmachine2.barcode) as machine2,
count(dmachine3.barcode) as machine3
from producten d
left join producten dmachine1 on d.barcode = dmachine1.barcode and date(d.machine1 ) = current_date
left join producten dmachine2 on d.barcode = dmachine2.barcode and date(d.machine2 ) = current_date
left join producten dmachine2 on d.barcode = dmachine3.barcode and date(d.machine3 ) = current_date

zijn er andere mogelijkheden om dit te bereiken?

[ Voor 5% gewijzigd door 107mb op 07-07-2016 16:30 . Reden: lijnen om de tabellen ]

[KabouterSuper]

Heb je 60 machines, en dus 60 left joins? Dan snap ik dat het traag is. Ik gebruik in dit soort gevallen graag de diagonaalsom:

SQL:

select sum(c1), sum(c2), sum(c3)
from
(
select count(1) c1,        0 c2,        0 c3 from producten d where date(d.machine1 ) = current_date
union
select        0 c1, count(1) c2,        0 c3 from producten d where date(d.machine2 ) = current_date
union
select        0 c1,       0  c2, count(1) c3 from producten d where date(d.machine3 ) = current_date
)
)

Geen vervelende joins meer op dezelfde tabel!

[Janoz]

Het voornaamste probleem hierbij is dat het datamodel verre van optimaal is. Maar als het je puur om het aantal van een gegeven dag gaat dan zou je het volgende kunnen doen:

SQL:

SELECT
   SUM(IF(DAY(machine1) = current_date,1,0) as total1,
   SUM(IF(DAY(machine2) = current_date,1,0) as total2,
   SUM(IF(DAY(machine3) = current_date,1,0) as total3,
 ...
   SUM(IF(DAY(machine60) = current_date,1,0) as total60
FROM producten;

Elegant gaat het nooit worden. Daarvoor is het schema te ruk.

[ Voor 6% gewijzigd door Janoz op 07-07-2016 18:00 ]

[107mb]

Beide bedankt! Database is inderdaad ruk! Deze gegevens lenen zich erg goed voor rapportages, maar de manier waarop het is ontworpen is dramatisch. Nooit een max o.i,d. Er los op kunnen laten

[KabouterSuper]

Staan er indices op de kolommen? Zo ja, eerste methode gebruiken, want je vermijdt zo full table scans. Staan er geen indices op de kolommen, dan is de tweede methode (van Janoz) gebruiken, want het leest gemakkelijker, en je ontkomt toch niet aan een full table scan.

[Gomez12]

Gewoon maar even een vraag tussendoor, maar ik snap het datamodel denk ik niet (of het is rukker dan ruk)

Maar wat gebeurt er nou daadwerkelijk als machine1 2x zo hard werkt als machine2? Krijg je dan gewoon ongelijke kolommen? Dus dat machine1 ergens bij barcode 2000 is, terwijl machine2 nog bij barcode 1000 hangt?
Of zijn er momenten dat het bijgetrokken wordt, of lopen die machines 100% synchroon (en is er nooit 1 stuk)

Want je kan ook een combinatie van de gegeven oplossingen gebruiken als ze redelijk gelijk lopen. Dan moet je gewoon onderaan Janoz zijn query toevoegen where machine1>vandaag-1 en machine1<vandaag+1

Dan hoef je niet daadwerkelijk 1,2 miljoen records door te ploegen maar enkel de records van de laatste 2 dagen.

[ajakkes]

Ik heb het idee dat er heel veel NULL in de tabel aanwezig moet zijn, klopt dat?

Het lijkt er dan op dat producten op dag 1 door machine A, B en C gaan en op dag 2 door D?

In dat geval snap ik je ingewikkelde aanpak.

Maar anders kan je toch gewoon nullen tellen binnen een gegeven aantal records?

[107mb]

het datamodel is verschrikkelijk. het is 10 jaar geleden opgezet, en als bloemkool uitgegroeid. De ontwikkelaar is geniaal, maar heeft helaas geen kaas gegeten van database-design. Er zitten pareltjes in:

• klantentabel: een veld bevat mail-adressen of faxnummers
• klantentabel: een goederenontvanger is geen nieuwe gerelateerde klant, maar er zijn een paar velden in de klant opgenomen voor adresgegevens van de goederenontvanger.
• verschrikkelijk veel velden waar csv strings in staan. (programmeur komt uit het tijdperk van multivalue databases).

Wat betreft overige opmerkingen:

• er zit alleen een index op barcode en een berekend veld 'laatste scanlocatie'. Dit veld is ingevoerd omdat er met deze tabelstructuur geen max() mogelijk is.
• er is inderdaad heel veel NULL in de tabel aanwezig. Sommige machines kunnen weinig soorten bewerkingen doen, maar wel heel snel. Andere machines kunnen heel veel bewerkingen, maar heel langzaam. Sommige producten zijn erg simpel en hebben erg weinig bewerkingen nodig. Daarom heeft een product wel 30 scanmomenten, en de ander maar 10.

[ Voor 3% gewijzigd door 107mb op 08-07-2016 18:48 ]