Scoping in gejointem SQL

S

schokole

Neuer Benutzer
Beiträge
2
Hallo zusammen,

ich habe ein recht komplexes Thema.
Es geht um das Überwachen von diversen Artefakten.
Die Daten für das Monitoring stehen in einer Oracle-DB und ich versuche diese möglichst clever zu aggregieren, damit die Analysen möglichst genau sind.

Nun zu dem was funktioniert (habe einiges, in meinen Augen unnützes gekürzt):

Code: 
define StartDate = ''01-01-1900 00:00:00.000000''
define EndDate = ''01-01-2099 00:00:00.000000''
define ComponentName = 'Artefakt''
 
SELECT
    COMPONENT_NAME,
    TITLE OPERATION,
    round(AVG(
     
        extract (second from (MODIFY_DATE-CREATION_DATE)) +
        extract (minute from (MODIFY_DATE-CREATION_DATE)) * 60 +
        extract (hour from (MODIFY_DATE-CREATION_DATE)) * 3600
    ),3) DURATION_AVG,
    COUNT(1) COUNT
FROM
    (    SELECT
            *
        FROM
            CUBE_INSTANCE
        WHERE
                CREATION_DATE >= TO_TIMESTAMP (&&StartDate, 'DD-MM-RRRR HH24:MI:SS.FF')
            AND CREATION_DATE < TO_TIMESTAMP (&&EndDate, 'DD-MM-RRRR HH24:MI:SS.FF')
            AND COMPONENT_NAME = &&ComponentName
        ORDER BY
            (MODIFY_DATE-CREATION_DATE) desc
    )       
WHERE
    rownum < (
        SELECT
            COUNT(*)
        FROM
            CUBE_INSTANCE
        WHERE
                CREATION_DATE >= TO_TIMESTAMP (&&StartDate, 'DD-MM-RRRR HH24:MI:SS.FF')
            AND CREATION_DATE < TO_TIMESTAMP (&&EndDate, 'DD-MM-RRRR HH24:MI:SS.FF')
            AND COMPONENT_NAME = &&ComponentName
            ) * 0.25
    GROUP BY COMPONENT_NAME, TITLE
    ORDER BY
        COMPONENT_NAME;

Wie gesagt, der abgebildete Code funktioniert - jedoch nur für ein Artefakt. Mit diesem Skript holt man sich die langsamsten 25% einer Components und bildet davon die Durchschnittslaufzeit.
Wenn man nun das "AND COMPONENT_NAME = &&ComponentName" raus nimmt, bekommt man zwar Durchschnittslaufzeiten, aber nicht auf Artefaktsbasis.

Das klingt jetzt warhscheinlich recht komisch, ein Beispiel wird Licht ins Dunkle bringen, man hat 3 Artefakte und diese sind von der Laufzeit recht unterschiedlich.

Artefakt A, Anzahl 100, Durchschnitt 5 sec, schnellste 1 sec, langsamste 15 sec
Artefakt B, Anzahl 100, Durchschnitt 10 sec, schnellste 6 sec, langsamste 35 sec
Artefakt C, Anzahl 100, Durchschnitt 2 sec, schnellste 0.5 sec, langsamste 3 sec

Wenn man nun die " AND COMPONENT_NAME = &&ComponentName" herausnimmt und über alle Artefakte geht, sind ausschliesslich Artefakte von A und B in der Zielmenge, da diese die langsamsten 25% ausmachen und das Artefakt C immer schneller ist.

Mein Ziel ist es aber dies gesondert zu sehen, also für jedes Artefakt möchte dich die langsamsten 25 sehen, nicht die 25% aller. Um es genauer zu sagen, in meinem Result soll jedes Artefakt vorhanden sein und die Durchschnittszeit seiner langsamsten 25% anzeigen.

Versucht habe ich dies mit folgendem Statement:
Code: 
define StartDate = ''01-01-1900 00:00:00.000000''
define EndDate = ''01-01-2099 00:00:00.000000''
/* Percent */
define Percentile  = 25
 
SELECT
    Outer.COMPONENT_NAME,
    Inner.DURATION_MEDIAN
   
FROM
    CUBE_INSTANCE OuterRow INNER JOIN (
        SELECT
            COMPONENT_NAME,
            round(AVG(
                extract (second from (MODIFY_DATE-CREATION_DATE)) +
                extract (minute from (MODIFY_DATE-CREATION_DATE)) * 60 +
                extract (hour from (MODIFY_DATE-CREATION_DATE)) * 3600
            ),3) DURATION_AVG, 
            COUNT(1) COUNT
        FROM
            (    SELECT
                    *
                FROM
                    CUBE_INSTANCE
                WHERE
                        CREATION_DATE >= TO_TIMESTAMP (&&StartDate, 'DD-MM-RRRR HH24:MI:SS.FF')
                    AND CREATION_DATE < TO_TIMESTAMP (&&EndDate, 'DD-MM-RRRR HH24:MI:SS.FF')
                    AND COMPONENT_NAME = Outer.COMPONENT_NAME  /* &&ComponentName */
                ORDER BY
                    (MODIFY_DATE-CREATION_DATE) desc
            )       
        WHERE
            rownum < (
                SELECT
                    COUNT(*)
                FROM
                    CUBE_INSTANCE
                WHERE
                        CREATION_DATE >= TO_TIMESTAMP (&&StartDate, 'DD-MM-RRRR HH24:MI:SS.FF')
                    AND CREATION_DATE < TO_TIMESTAMP (&&EndDate, 'DD-MM-RRRR HH24:MI:SS.FF')
                    AND COMPONENT_NAME =  Outer.COMPONENT_NAME  /* &&ComponentName */
                    ) * &&Percentile / 100
            group by COMPONENT_NAME
            ORDER BY
                COMPONENT_NAME
            ) Inner ON Inner.COMPONENT_NAME = Outer.COMPONENT_NAME

Dabei bekomme ich folgende Fehlermeldung:
ORA-00904: "OUTER"."COMPONENT_NAME": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 29 Column: 14

Problem ist, ich kann nicht vom inneren auf den äusseren Scope zugreifen.
Hat jemand dies schon mal gehabt und kann mir helfen? :)

Hoffentlich habe ich nichts vergessen - und auch nicht zu viel gemacht...



Beste Grüsse

Schoko
 
Werbung:
schokole schrieb:
Hallo zusammen,

ich habe ein recht komplexes Thema.
Es geht um das Überwachen von diversen Artefakten.
Die Daten für das Monitoring stehen in einer Oracle-DB und ich versuche diese möglichst clever zu aggregieren, damit die Analysen möglichst genau sind.

Nun zu dem was funktioniert (habe einiges, in meinen Augen unnützes gekürzt):

Code: 
define StartDate = ''01-01-1900 00:00:00.000000''
define EndDate = ''01-01-2099 00:00:00.000000''
define ComponentName = 'Artefakt''
 
SELECT
    COMPONENT_NAME,
    TITLE OPERATION,
    round(AVG(
 
        extract (second from (MODIFY_DATE-CREATION_DATE)) +
        extract (minute from (MODIFY_DATE-CREATION_DATE)) * 60 +
        extract (hour from (MODIFY_DATE-CREATION_DATE)) * 3600
    ),3) DURATION_AVG,
    COUNT(1) COUNT
FROM
    (    SELECT
            *
        FROM
            CUBE_INSTANCE
        WHERE
                CREATION_DATE >= TO_TIMESTAMP (&&StartDate, 'DD-MM-RRRR HH24:MI:SS.FF')
            AND CREATION_DATE < TO_TIMESTAMP (&&EndDate, 'DD-MM-RRRR HH24:MI:SS.FF')
            AND COMPONENT_NAME = &&ComponentName
        ORDER BY
            (MODIFY_DATE-CREATION_DATE) desc
    )   
WHERE
    rownum < (
        SELECT
            COUNT(*)
        FROM
            CUBE_INSTANCE
        WHERE
                CREATION_DATE >= TO_TIMESTAMP (&&StartDate, 'DD-MM-RRRR HH24:MI:SS.FF')
            AND CREATION_DATE < TO_TIMESTAMP (&&EndDate, 'DD-MM-RRRR HH24:MI:SS.FF')
            AND COMPONENT_NAME = &&ComponentName
            ) * 0.25
    GROUP BY COMPONENT_NAME, TITLE
    ORDER BY
        COMPONENT_NAME;

Wie gesagt, der abgebildete Code funktioniert - jedoch nur für ein Artefakt. Mit diesem Skript holt man sich die langsamsten 25% einer Components und bildet davon die Durchschnittslaufzeit.
Zum Vergrößern anklicken....

Du holst 25% der Datensätze, aber ich sehe keine Sortierung nach Geschwindigkeit, sondern nur nach COMPONENT_NAME.



Oder vielleicht blicke ich auch die 'krude' ORA-Syntax nicht, falls dem so ist, sorry.
Aber vielleicht kannst ja einfach mal eine ganz simple Testtabelle erstellen und basierend darauf, was rauskommen soll. Mir schwirrt a bissl der Kopf von dem ORA-Gebrabbl..
 
Werbung:
Hallo,

vielen Dank für die Antwort!
Die Testtabelle werde ich erstellen - dauert ein wenig...

Zu der Sortierung - diese passiert im FROM:
Code: 
ORDER BY
            (MODIFY_DATE-CREATION_DATE) desc
 

Ähnliche Themen

A
Kann Abfrage optimiert werden?
Antworten
7
Aufrufe
2K
drdimitri
D
Zurück
Oben