✔ Oracle: Abfrage der 10 häufigsten Einträge

[Gray]

Ich habe eine Liste mit Servern und ausgefallenen Ports, nun möchte ich die 10 Server mit den häufigsten Ausfällen mir ausgeben lassen, jeder Eintrag enthält ein Feld mit "server down" oder "port down" anhand dessen man die Anzahl ermitteln könnte, nun kann aber jeder Server mehrfach drin stehen in der Liste (mehrere Ausfälle eben) und hier komm ich beim besten willen nicht auf die Abfrage die ich hierfür machen soll.

So sieht die Liste aus:

ID | Server  | Port   | Ausfall
1  | Sv1     |  0     | server down
2  | Sv1     |  0     | server down
3  | Sv2     |  110   | port down
4  | Sv13    |  25    | port down
5  | Sv3     |  3306  | port down
7  | Sv50    |  0     | server down

Und nun sollen nur die 10 häufigsten Einträge angezeigt werden.

 

[Exceptionfault]

Wenn die Art des Ausfalls egal ist, also nur ein Port oder der ganze Server, dann würde einfach dieses Statement ausreichen:

SELECT   *
FROM     ( 
   SELECT   SERVER,
            COUNT(*) AS AUSFAELLE
   FROM     <table>
   GROUP    BY SERVER
   ORDER    BY COUNT(*) DESC
)
WHERE    ROWNUM <= 10;

Wenn dich nur komplette Serverausfälle interessieren, müssten in das innere SELECT noch eine WHERE Klausel die auf Ausfall = 'server down' einschränkt.

 

[Gray]

Vielen Dank, es klappt

 

[Gray]

Jetzt hab ich doch noch ein Problem, ich will ja nur die Ausfälle haben, es stehen jedoch auch die "Backs" in der Datenbank (z.B.: Port up oder Server up) und diese dürfen nicht mit beachten werden hierbei, wie kann ich diese ausfiltern?

 

[MeinerEiner_80]

Dann füge wie schon vorgeschlagen eine zusätzliche Where Klausel hinzu...
also etwas derart

SELECT   *
FROM     ( 
   SELECT   SERVER,
            COUNT(*) AS AUSFAELLE
   FROM     <table>
   Where    Ausfall <> 'server up' and Ausfall <> 'port up' 
   -- oder Where    Ausfall = 'server down' or Ausfall = 'port down' 
   -- bzw. für alles was kein up beinhaltet 
  --  Where upper(Ausfall) not like upper(%up%)
GROUP    BY SERVER
   ORDER    BY COUNT(*) DESC
)
WHERE    ROWNUM <= 10;

*grüssle*
MeinerEiner

 

[StephanHö]

Hallo MeinerEiner,

wie wäre es mit:

... where Ausfall like '%down%' > 0

oder

where Ausfall not like '%down%'...?

Grüße

Stephan

 

[Exceptionfault]

Sicher gibt es für diese Frage mehrere Lösungen. Aus Performance Sicht aber nur eine wirklich gute. Die Spalte Ausfall sollte in jedem Fall indiziert werden, um bei der Einschränkung über WHERE einen schnelleren Zugriff zu haben. Ohne Index muss die komplette Tabelle gelesen werden, und das können wir uns schenken wenn wir nur die downs haben wollen.

Where Ausfall <> 'server up' and Ausfall <> 'port up'
Schlecht, da bei einem "ungleich" selten bis niemals ein Index genutzt werden kann.

upper(Ausfall) not like upper(%up%)
like '%down%' > 0
not like '%down%'
Noch schlechter. Upper(Ausfall) verhindert schonmal grundsätzlich dass ein Index genutzt werden kann, da der Index die Werte Ausfall indiziert, und nicht Upper(Ausfall)
(Ausnahme wären Function Based Indizes..) Und die Klauses NOT LIKE %xxx% erzwingt sowie ein Full Table Scan. Grundsätzlich gilt: LIKE so gut es geht vermeiden. Wenn man es doch brauch: Niemals ein % an den Anfang stellen. Und nach Möglichkeit auch nie negieren (NOT).

Mein Ansatz wäre folgender:
Entweder du gibst die gewünschten Ausfalltypen direkt im Statement an mit:
Where Ausfall = 'server down' or Ausfall = 'port down'
Das funktioniert zwar, ist aber nicht sonderlich wartungsfreundlich. Ich würde mir eine zusätzliche Tabelle erstellen, in denen ich alle Ausfallgründe auflisten würde, die für dieses Statement relevant sind und dann in meiner Abfrage mir dieser Tabelle joinen.

SELECT   *
FROM     ( 
   SELECT   t.SERVER,
            COUNT(*) AS AUSFAELLE
   FROM     <table> t
   JOIN       <ausfallgruende> ag
   ON         t.AUSFALL = ag.AUSFALL
GROUP    BY t.SERVER
   ORDER    BY COUNT(*) DESC
)
WHERE    ROWNUM <= 10;