Cookies op Tweakers

Tweakers maakt gebruik van cookies, onder andere om de website te analyseren, het gebruiksgemak te vergroten en advertenties te tonen. Door gebruik te maken van deze website, of door op 'Ga verder' te klikken, geef je toestemming voor het gebruik van cookies. Wil je meer informatie over cookies en hoe ze worden gebruikt, bekijk dan ons cookiebeleid.

Meer informatie
Toon posts:

SQL Count op datumvelden

Pagina: 1
Acties:

Onderwerpen

Vraag


  • 107mb
  • Registratie: juni 2004
  • Laatst online: 17:57
ik heb een tabel waarbij per productid timestamps van bewerkingen naar toe worden geschreven:

idmachine1machine2machine3
113-01-2020 12:0013-01-2020 12:05null
210-01-2020 14:0010-01-2020 14:0510-01-2020 14:10
310-01-2020 13:0010-01-2020 13:0513-01-2020 13:10


ik wil het onderstaande krijgen:

datummachine1machine2machine3
13-01-2020111
10-01-2020221


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?

Beste antwoord (via 107mb op 14-01-2020 11:28)


  • RobIII
  • Registratie: december 2001
  • Laatst online: 23:08

RobIII

Admin Devschuur®

^ Romeinse 3 ja!

Je moet je tabel normaliseren naar:

idmachinedatum
1113-10-2020 12:00
2213-10-2020 12:05
3110-10-2020 14:00
4210-10-2020 14:05
5310-10-2020 14:10
6110-10-2020 13:00
7210-10-2020 13:05
8313-10-2020 13:10
(Waarbij id optioneel is / weinig toevoegt)

Daarna zijn de queries kinderspel.

RobIII wijzigde deze reactie 13-01-2020 14:27 (93%)

There are only two hard problems in distributed systems: 2. Exactly-once delivery 1. Guaranteed order of messages 2. Exactly-once delivery.

Roses are red Violets are blue, Unexpected ‘{‘ on line 32.

Over mij

Alle reacties


Acties:
  • Beste antwoord
  • 0Henk 'm!

  • RobIII
  • Registratie: december 2001
  • Laatst online: 23:08

RobIII

Admin Devschuur®

^ Romeinse 3 ja!

Je moet je tabel normaliseren naar:

idmachinedatum
1113-10-2020 12:00
2213-10-2020 12:05
3110-10-2020 14:00
4210-10-2020 14:05
5310-10-2020 14:10
6110-10-2020 13:00
7210-10-2020 13:05
8313-10-2020 13:10
(Waarbij id optioneel is / weinig toevoegt)

Daarna zijn de queries kinderspel.

RobIII wijzigde deze reactie 13-01-2020 14:27 (93%)

There are only two hard problems in distributed systems: 2. Exactly-once delivery 1. Guaranteed order of messages 2. Exactly-once delivery.

Roses are red Violets are blue, Unexpected ‘{‘ on line 32.

Over mij


  • Wintervacht
  • Registratie: december 2016
  • Nu online

Wintervacht

☉ ‿ ⚆

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

Weet een beetje van veel dingen en veel van een paar dingen.


  • Vloris
  • Registratie: december 2001
  • Laatst online: 22:50
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
  • Registratie: juni 2004
  • Laatst online: 17:57
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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.

107mb wijzigde deze reactie 13-01-2020 14:49 (5%)


  • RobIII
  • Registratie: december 2001
  • Laatst online: 23:08

RobIII

Admin Devschuur®

^ Romeinse 3 ja!

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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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 ;)

RobIII wijzigde deze reactie 13-01-2020 15:15 (48%)

There are only two hard problems in distributed systems: 2. Exactly-once delivery 1. Guaranteed order of messages 2. Exactly-once delivery.

Roses are red Violets are blue, Unexpected ‘{‘ on line 32.

Over mij


  • Rensjuh
  • Registratie: juli 2007
  • Laatst online: 18:53
Je kunt ook de huidige tabel laten wegschrijven naar een genormaliseerde tabel.
SQL:
1
2
3
4
machineData
id INT PK,
machineId INT,
datum DATETIME


SQL:
1
2
3
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:
1
(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.

Rensjuh wijzigde deze reactie 13-01-2020 14:57 (15%)


  • RobIII
  • Registratie: december 2001
  • Laatst online: 23:08

RobIII

Admin Devschuur®

^ Romeinse 3 ja!

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.

There are only two hard problems in distributed systems: 2. Exactly-once delivery 1. Guaranteed order of messages 2. Exactly-once delivery.

Roses are red Violets are blue, Unexpected ‘{‘ on line 32.

Over mij


  • DJMaze
  • Registratie: juni 2002
  • Niet online
107mb schreef op maandag 13 januari 2020 @ 14:14:
ik heb een tabel waarbij per productid timestamps van bewerkingen naar toe worden geschreven:

idmachine1machine2machine3
En wat als er een machine bij komt?

idmachinedatetime

Dan zou je al verder komen denk ik.

Maak je niet druk, dat doet de compressor maar


  • 107mb
  • Registratie: juni 2004
  • Laatst online: 17:57
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
  • Registratie: september 2000
  • Laatst online: 23:27

.oisyn

Moderator Devschuur® / Cryptocurrencies

Demotivational Speaker

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

idmachinedatum
1113-10-2020 12:00
2213-10-2020 12:05
3110-10-2020 14:00
4210-10-2020 14:05
5310-10-2020 14:10
6110-10-2020 13:00
7210-10-2020 13:05
8313-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.

We were doomed from the start. I guess all that remains now is for the captain to go down with the ship.
- That's surprisingly noble of you, sir.
No, it's noble of you, Kif! As of now, you're in command. Congratulations, Captain!


  • RobIII
  • Registratie: december 2001
  • Laatst online: 23:08

RobIII

Admin Devschuur®

^ Romeinse 3 ja!

.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.

There are only two hard problems in distributed systems: 2. Exactly-once delivery 1. Guaranteed order of messages 2. Exactly-once delivery.

Roses are red Violets are blue, Unexpected ‘{‘ on line 32.

Over mij


  • 107mb
  • Registratie: juni 2004
  • Laatst online: 17:57
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.
Pagina: 1


Apple iPhone 11 Microsoft Xbox Series X LG OLED C9 Google Pixel 4 CES 2020 Samsung Galaxy S20 4G Sony PlayStation 5 Nintendo Switch Lite

'14 '15 '16 '17 2018

Tweakers vormt samen met Hardware Info, AutoTrack, Gaspedaal.nl, Nationale Vacaturebank, Intermediair en Independer DPG Online Services B.V.
Alle rechten voorbehouden © 1998 - 2020 Hosting door True