[SQL] Hulp gevraagd!

[Verwijderd]

Beste tweakers,

Mijn SQL skills zijn niet meer wat het geweest is. Kan iemand mij helpen met het volgende?

Ik heb een tabel die er als volgt uit ziet:

ID	Server	Drive	Total	Used	Free	Percent	Date
1	ServerA	C:	67.65	23.58	44.07	65	05-12-2013 16:03
2	ServerA	E:	1675.12	196.45	1478.67	88	05-12-2013 16:03
3	ServerB	C:	99.90	95.08	4.82	5	05-12-2013 16:03
4	ServerB	D:	1675.12	196.45	1478.67	88	05-12-2013 16:03
5	ServerC	C:	14.65	13.25	1.40	10	05-12-2013 16:03
5	ServerC	D:	14.65	13.25	1.40	10	05-12-2013 16:03
6	ServerA	C:	67.65	23.58	44.07	65	05-12-2013 16:33
7	ServerA	E:	1675.12	196.45	1478.67	88	05-12-2013 16:33
8	ServerB	C:	99.90	95.08	4.82	5	05-12-2013 16:33
9	ServerB	D:	1675.12	196.45	1478.67	88	05-12-2013 16:33
10	ServerC	C:	14.65	13.25	1.40	10	05-12-2013 16:33
11	ServerC	D:	14.65	13.25	1.40	10	05-12-2013 16:33

Nou wil ik, mbv SQL, een selectie maken van alle servers die in de tabel percentage een waarde van <= 10 hebben. In dit geval zal ServerB, met drive C aan deze voorwaarden voldoen. Ik dacht dit eerst met een DISTINCT op te lossen, maar wat dan als drive D van serverB ook onder de 10% komt?
En ik wil (uiteraard) alleen de meest recente melding zien.

Hoe kan ik dit netjes, en goed oplossen?

[ Voor 5% gewijzigd door Verwijderd op 05-12-2013 17:09 ]

[Voutloos]

Dus je wilt gewoon de max data waar het percentage <= 10, maar dan gegroepeerd per server?

Vertaal dit en je query staat er letterlijk

[DJMaze]

MAX() is geen oplossing als het percentage na opschonen weer boven de 10% uit komt
Dikke problemen omdat de data niet netjes is opgesplitst in 2 tabellen.

code:

SELECT Server, Drive FROM table

for each SELECT percent WHERE Server= AND Drive= ORDER BY Date DESC LIMIT 1
    IF percent <= 10

Met een sub-query in de WHERE moet dat ook wel lukken

code:

WHERE id = (SELECT id FROM table WHERE Server= AND Drive= ORDER BY Date DESC LIMIT 1)
AND percent <= 10

[ Voor 84% gewijzigd door DJMaze op 05-12-2013 17:20 ]

[Dinnesch]

Deze database is niet genormaliseerd. Je zou wel wat bij elkaar kunnen hacken, maar een goede relationele database is een veel beter beginpunt.

Er bestaat een één-op-veel relatie tussen Server en Drive. Dus één server heeft één of meer Drives.
Je database zal er genormaliseerd* dan zo uitzien:
Server (ID, naam)
Drive(ID, serverID, letter, total, used, free, percent, date)

Dan kun je deze MySQL-query uitvoeren om alle servers te kiezen die minimaal één drive onder de 10% hebben:

code:

SELECT  *
FROM    server
WHERE   (      SELECT   COUNT(*)
               FROM     drive
               WHERE    percent < 10
               AND      serverID = server.ID) > 0;

TS, ik weet niet zeker of je bedoelt dat je elke drive < 10% wilde benoemen, maar moet je er met deze informatie zelf uitkomen?

* eigenlijk is dit nog niet genormaliseerd. "free" en "percent" kunnen volgens mij in ieder denkbaar bestandssysteem berekend worden aan de hand van "total" en "used" dus horen niet in de database opgeslagen te worden.

[ Voor 4% gewijzigd door Dinnesch op 05-12-2013 17:24 . Reden: lelijke indentatie ]

[DJMaze]

Zit mij net te bedenken dat dit misschien sneller is:

code:

SELECT * FROM table
WHERE id IN (SELECT MAX(id) FROM table GROUP BY Server, Drive)
  AND Percent <= 10

Dan klopt de opmerking van Voutloos weer wel

[ Voor 12% gewijzigd door DJMaze op 05-12-2013 17:28 ]

[edeboeck]

Dinnesch schreef op donderdag 05 december 2013 @ 17:19:
Deze database is niet genormaliseerd. Je zou wel wat bij elkaar kunnen hacken, maar een goede relationele database is een veel beter beginpunt.

Er bestaat een één-op-veel relatie tussen Server en Drive. Dus één server heeft één of meer Drives.
Je database zal er genormaliseerd* dan zo uitzien:
Server (ID, naam)
Drive(ID, serverID, letter, total, used, free, percent, date)

Hoewel ik het op zich met je eens ben wat het normaliseren betreft, maakt dit voor de query van TS geen enkel verschil: zijn ServerID is gewoon de naam van de server. Punt. Als hij geen verdere info over zijn servers bijhoudt, is er zelfs geen enkel voordeel aan verder normaliseren imho.

[The Eagle]

Even ranzig maar vermoedelijk wel werkend:

SQL:

Select distinct server, sum(percentage), date from server_table a
      where date = 
          (select max(date) from server_table ab 
            where ab.id=a.id 
            and ab.server=a.server) 
            and <other joincriteria>
            and ab.date <= sysdate)
having sum(percentage) <10
group by server, date

Zo heb je altijd de meest recente dag te pakken (da's de inner join in de subselect) en die having geeft je criteria voor de sommering. Noot dat ik geen ID geselecteerd heb. Dat doe ik bewust, want anders moet je daar ook op groeperen en dat wil je nou juist niet

Denk dat je hiermee een heel eind in de goede richting zit

[Verwijderd]

DJMaze schreef op donderdag 05 december 2013 @ 17:27:
Zit mij net te bedenken dat dit misschien sneller is:

code:

SELECT * FROM table
WHERE id IN (SELECT MAX(id) FROM table GROUP BY Server, Drive)
  AND Percent <= 10

Dit ligt er natuurlijk helemaal aan hoe de tabel wordt gevuld...of juist niet.

Als dit namelijk een tabel is met een vast aantal records waarbij de waarden van de disk iedere keer worden geupdate gaat dit niet op. De MAX(ID) gaat dan namelijk niet de meest recente melding ophalen, maar de laatst toegevoegde disk van de desbetreffende server.

Vraag aan TS:
Waarom zou je eigenlijk maar 1 disk van een server willen ophalen wanneer twee disken onder de tien procent zitten? Ik zou zelf toch echt beide meldingen willen ontvangen of zien.

[Gomez12]

Dinnesch schreef op donderdag 05 december 2013 @ 17:19:
Deze database is niet genormaliseerd. Je zou wel wat bij elkaar kunnen hacken, maar een goede relationele database is een veel beter beginpunt.

Er bestaat een één-op-veel relatie tussen Server en Drive. Dus één server heeft één of meer Drives.
Je database zal er genormaliseerd* dan zo uitzien:
Server (ID, naam)
Drive(ID, serverID, letter, total, used, free, percent, date)

Ok en dan kies ik als serverids de teksten : ServerA / ServerB / ServerC etc.
Dan zeg jij dus dat er simpelweg een tabel bij moet komen met eigenlijk alleen maar :
Server(ServerA, ServerA)

Wat schiet je daarmee op? Normaliseren heeft pas zin als je meer info over die server opslaat, zolang het maar 1 gegeven is is je gegeven gewoon je ID.

The Eagle schreef op vrijdag 06 december 2013 @ 02:28:
Even ranzig maar vermoedelijk wel werkend:

SQL:

Select distinct server, sum(percentage), date from server_table a
      where date = 
          (select max(date) from server_table ab 
            where ab.id=a.id 
            and ab.server=a.server) 
            and <other joincriteria>
            and ab.date <= sysdate)
having sum(percentage) <10
group by server, date

Zo heb je altijd de meest recente dag te pakken (da's de inner join in de subselect) en die having geeft je criteria voor de sommering. Noot dat ik geen ID geselecteerd heb. Dat doe ik bewust, want anders moet je daar ook op groeperen en dat wil je nou juist niet

Denk dat je hiermee een heel eind in de goede richting zit

Herschrijf de inner query naar select max(date), server from server_tableab en je hebt de laatste dag per server (enkel laatste dag heb je namelijk niets aan, als de servers niet gelijk aanleveren)

Zoiets dus :

SQL:

Select distinct server, sum(percentage), date from server_table a
inner join 
          (select server,max(date) from server_table ab) as maxdateserver on (a.server=maxdateserver.server and a.date=maxdateserver.date)
having sum(percentage) <10
group by server, date

Vereist nog wel wat werk, maar dat mag je zelf uitzoeken

DJMaze schreef op donderdag 05 december 2013 @ 17:27:
Zit mij net te bedenken dat dit misschien sneller is:

code:

SELECT * FROM table
WHERE id IN (SELECT MAX(id) FROM table GROUP BY Server, Drive)
  AND Percent <= 10

Dan klopt de opmerking van Voutloos weer wel

MAX(id) is geen enkele garantie voor laatste datum (tenminste als id autogenerated door de db is) bijna alle db's hanteren het principe dat id verhoogd wordt totdat situatie x voorkomt (bijv id's zijn op, er vindt een db-schoonactie plaats of .. of ...) en dan wordt er gewoon gekeken naar het eerst beschikbare id en dat zal dus lager zijn dan het laatst gebruikte.

Een autogenerated id is gewoon enkel een nietszeggend id, daar moet je niks aan ophangen. Wil je er waarde aan hechten dan moet je maar zelf je id's gaan genereren.

[ Voor 17% gewijzigd door Gomez12 op 06-12-2013 21:37 ]

[nescafe]

DJMaze schreef op donderdag 05 december 2013 @ 17:27:
[...]

code:

SELECT * FROM table
WHERE id IN (SELECT MAX(id) FROM table GROUP BY Server, Drive)
  AND Percent <= 10

[...]

Verwijderd schreef op vrijdag 06 december 2013 @ 21:24:
[...]


Dit ligt er natuurlijk helemaal aan hoe de tabel wordt gevuld...of juist niet.

Als dit namelijk een tabel is met een vast aantal records waarbij de waarden van de disk iedere keer worden geupdate gaat dit niet op. De MAX(ID) gaat dan namelijk niet de meest recente melding ophalen, maar de laatst toegevoegde disk van de desbetreffende server.

Ik vind de oplossing van DJMaze eigenlijk uitblinken in eenvoud en correctheid. Als de tabel inderdaad een vast aantal records zou bevatten, wat uit het gegeven voorbeeld juist niet blijkt, dan is de subselect overbodig maar niet kwalijk. Hij zal namelijk per server per disk de meest recente melding ophalen, niet alleen per server.

Alleen impliceert de query van DJMaze dat de tabel chronologisch wordt gevuld, waarschijnlijk is het een monitoringsysteem dus dit lijkt me geen issue, maar zou dit zou het wel kunnen geven indien gegevens historisch dan wel (niet-chronologisch) batchgewijs worden ingevoerd.

(wat Gomez12 zegt dus)

[DJMaze]

Correct, de ID's hoeven inderdaad niet oplopend te zijn, en dan gaat het mis
In mijn eerdere post had ik daar dus wel rekening mee gehouden maar niet uitgewerkt omdat die langzamer is.

code:

SELECT * FROM table t
WHERE id = (SELECT id FROM table WHERE Server=t.Server AND Drive=t.Drive ORDER BY Date DESC LIMIT 1)
AND percent <= 10

[ Voor 3% gewijzigd door DJMaze op 07-12-2013 00:21 ]