SQL Count op datumvelden

[107mb]

ik heb een tabel waarbij per productid timestamps van bewerkingen naar toe worden geschreven:

id	machine1	machine2	machine3
1	13-01-2020 12:00	13-01-2020 12:05	null
2	10-01-2020 14:00	10-01-2020 14:05	10-01-2020 14:10
3	10-01-2020 13:00	10-01-2020 13:05	13-01-2020 13:10

ik wil het onderstaande krijgen:

datum	machine1	machine2	machine3
13-01-2020	1	1	1
10-01-2020	2	2	1

in dit voorbeeld zijn er twee dagen aanwezig. ik wil het van de afgelopen 10 dagen zien. Met de querys die ik schrijf kom ik er niet uit. De resultaten zijn niet betrouwbaar. Is er iemand die de oplossing heeft? Moet ik een datumtabel toevoegen, net zoals in Power Bi nodig is?

[RobIII]

Je moet je tabel normaliseren naar:

id	machine	datum
1	1	13-10-2020 12:00
2	2	13-10-2020 12:05
3	1	10-10-2020 14:00
4	2	10-10-2020 14:05
5	3	10-10-2020 14:10
6	1	10-10-2020 13:00
7	2	10-10-2020 13:05
8	3	13-10-2020 13:10

(Waarbij id optioneel is / weinig toevoegt)

Daarna zijn de queries kinderspel.

[ Voor 93% gewijzigd door RobIII op 13-01-2020 14:27 ]

[Wintervacht]

Het zou wel helpen om je queries te posten, dan kunnen we zien waar het mis gaat.

[Vloris]

Ja, zonder dat we weten wat voor query niet werkt is het moeilijk je in de goede richting te helpen.

Maar misschien is het heel simpel: je tabel bevat datums 13 oktober en 10 oktober, en in je gewenste resultaat praat je over 13 januari en 10 januari. Is dat alleen hier een tikfout of ook in je query?

[107mb]

de tabel is niet genormaliseerd. Helaas is het een oud (en slecht) ontwerp waar een ERP pakket gegevens uit haalt, en een dertig tal machines gegevens naar toe schrijven. Normalisatie is helaas niet mogelijk.

ik heb een foutje gemaakt in de gegevens, en zal het herstellen. Ik gebruik bijvoorbeeld de onderstaande query, maar die geeft natuurlijk alleen regels weer waar machine1 een waarde heeft.

De query die ik gebruik zit in de volgende hoek. Natuurlijk pakt hij alleen regels waar machine1 al wat in heeft weggeschreven. Als in de regel toevallig ook machine2 wat gedaan heeft, wordt deze opgeteld.

code:

SELECT 
extract (Year from machine1) || '-' || 
lpad(cast(extract(Month from machine1) as varchar),2,'0') || '-' || 
lpad(cast(extract(day from machine1) as varchar),2,'0') AS dt, 
Count(tbldeuren.machine1) AS AantalVanmachine1,
Count(tbldeuren.machine2) AS machine2
FROM tbldeuren
WHERE tbldeuren.run<'Z'
and eindcontroleexpeditie is not null

GROUP BY extract (Year from machine1) || '-' || 
lpad(cast(extract(Month from machine1) as varchar),2,'0') || '-' || 
lpad(cast(extract(day from machine1) as varchar),2,'0') 
order by extract (Year from machine1) || '-' || 
lpad(cast(extract(Month from machine1) as varchar),2,'0') || '-' || 
lpad(cast(extract(day from machine1) as varchar),2,'0') desc

limit 10;

ik heb het gevoel dat ik deze drama-tabel eerst moet transponeren zodat er een view onstaat die genormaliseerd is. Daar kan ik dan op query-en.

[ Voor 5% gewijzigd door 107mb op 13-01-2020 14:49 ]

[RobIII]

107mb schreef op maandag 13 januari 2020 @ 14:46:
ik heb het gevoel dat ik deze drama-tabel eerst moet transponeren zodat er een view onstaat die genormaliseerd is. Daar kan ik dan op query-en.

Als normaliseren geen optie is dan kan dat wel ja. Dan kun je met een union per machine(kolom) de "tabel" vormen en op die view dan de uiteindelijke query loslaten.

SQL:

CREATE VIEW [ormalizeddata]
AS
SELECT [date], [machine], COUNT(1) as [count]
FROM (
    SELECT CAST([date] as date) as [date], [machine]
    FROM (
        SELECT 1 as [machine], [machine1] as [date] FROM [mydata]
        UNION ALL
        SELECT 2 as [machine], [machine2] as [date] FROM [mydata]
        UNION ALL
        SELECT 3 as [machine], [machine3] as [date] FROM [mydata]
        ...
    ) as [base]
    WHERE not [date] is null
) as [counts]
GROUP BY [date], [machine]

De laatste pivot laat ik ter lering aan jou over

[ Voor 48% gewijzigd door RobIII op 13-01-2020 15:15 ]

[Rensjuh]

Je kunt ook de huidige tabel laten wegschrijven naar een genormaliseerde tabel.

SQL:

machineData
id INT PK,
machineId INT,
datum DATETIME

SQL:

SELECT 1, machine1
INTO machineData (machineId, datum)
FROM oldtable

Eens per zoveel tijd de genormaliseerde tabel (bij) laten vullen, d.m.v. een SQL job o.i.d. met een extra WHERE:

SQL:

(WHERE machine1 > <datum>)

Dit dien je dan echter wel voor alle 30 (???) machines te doen.

Daarna kun je hier makkelijk query's overheen gooien om te bereiken wat je wilt.

Nadeel is dan echter wel dat je je data dubbel in je database hebt staan.

[ Voor 15% gewijzigd door Rensjuh op 13-01-2020 14:57 ]

[RobIII]

Rensjuh schreef op maandag 13 januari 2020 @ 14:56:
Nadeel is dan echter wel dat je je data dubbel in je database hebt staan.

In sommige gevallen is dat prima verdedigbaar, dit is zo'n geval.

[DJMaze]

107mb schreef op maandag 13 januari 2020 @ 14:14:
ik heb een tabel waarbij per productid timestamps van bewerkingen naar toe worden geschreven:

id

machine1

machine2

machine3

En wat als er een machine bij komt?

id

machine

datetime

Dan zou je al verder komen denk ik.

[107mb]

alle machines vullen de tabel door een functieaanroep.

ik heb een nieuwe genormaliseerde tabel gemaakt. Daarnaast heb ik de functie aangepast zodat ook de nieuwe tabel gevuld wordt. Probleem = opgelost!

[.oisyn]

RobIII schreef op maandag 13 januari 2020 @ 14:18:
Je moet je tabel normaliseren naar:

id	machine	datum
1	1	13-10-2020 12:00
2	2	13-10-2020 12:05
3	1	10-10-2020 14:00
4	2	10-10-2020 14:05
5	3	10-10-2020 14:10
6	1	10-10-2020 13:00
7	2	10-10-2020 13:05
8	3	13-10-2020 13:10

(Waarbij id optioneel is / weinig toevoegt)

De id's zijn anders in deze tabel. Je kan aan het voorbeeld natuurlijk niet zien of de id's relevant zijn voor andere zaken, maar hij kan prima hetzelfde blijven.

[RobIII]

.oisyn schreef op dinsdag 14 januari 2020 @ 11:33:
De id's zijn anders in deze tabel. Je kan aan het voorbeeld natuurlijk niet zien of de id's relevant zijn voor andere zaken, maar hij kan prima hetzelfde blijven.

Klopt; dat kan ook. Maar in zo'n koppeltabel (of n:m tabel) zijn dergelijke ID's zelden relevant, ik zou ze gewoon helemaal weglaten tenzij er een goede reden voor is.

[107mb]

de volgende velden zijn aanwezig:
ID, barcode, machineID (fk), timestamp

ik had een PK van barcode en machineID kunnen maken, maar heb gekozen voor een aparte ID.