Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Scoping in gejointem SQL

Dieses Thema im Forum "Oracle" wurde erstellt von schokole, 4 März 2013.

  1. schokole

    schokole Neuer Benutzer

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

    akretschmer Datenbank-Guru

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

    schokole Neuer Benutzer

    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
     

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden