prog:sql_piviot
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende Überarbeitung | |||
prog:sql_piviot [2015/10/13 20:55] – [Eine dynamische Kreuz - (Pivot) Tabelle anlegen] gpipperr | prog:sql_piviot [2015/10/13 20:56] (aktuell) – [Eine dynamische Kreuz - (Pivot) Tabelle anlegen] gpipperr | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
+ | ====== Eine dynamische Kreuz - (Pivot) Tabelle anlegen ====== | ||
+ | **8i/ | ||
+ | |||
+ | |||
+ | **Problem: | ||
+ | |||
+ | Eine Auswertung soll für jeden Tag eines Monats lückenlos den Umsatz anzeigen, falls der Umsatz für den Tag 0 ist soll trotzdem der Tag auch angezeigt werden. | ||
+ | |||
+ | |||
+ | \\ | ||
+ | **Lösung: | ||
+ | |||
+ | |||
+ | Eine, das SQL Statement treibende Tabelle muss alle Tage eines Monats enthalten. | ||
+ | |||
+ | * Lösung A: eine statische Tabelle mit allen Tagen des Jahres, des nächsten Jahres etc. anlegen | ||
+ | |||
+ | |||
+ | * Lösung B: <fc # | ||
+ | * Objekt Typ verwenden und mit pipelined PL/SQL Funktion (Ergebnis werden gleich beim Abarbeiten ausgegeben) beliebig lange Schleifen ausgeben lassen. | ||
+ | |||
+ | |||
+ | |||
+ | ====Vorbereitung: | ||
+ | |||
+ | |||
+ | - Type und Type Tabelle anlegen | ||
+ | - Funktion auf diese Tabelle erstellen und | ||
+ | - verwenden | ||
+ | |||
+ | Type anlegen: | ||
+ | <code plsql> | ||
+ | |||
+ | create type t_pivot as object( | ||
+ | / | ||
+ | |||
+ | create type pivot_table as table of t_pivot; | ||
+ | / | ||
+ | | ||
+ | </ | ||
+ | |||
+ | |||
+ | Funktion definieren: | ||
+ | <code plsql> | ||
+ | CREATE OR REPLACE function getpivot( | ||
+ | | ||
+ | ) | ||
+ | | ||
+ | is | ||
+ | | ||
+ | begin | ||
+ | for i in 1 .. p_count | ||
+ | loop | ||
+ | pipe row( t_pivot( i )); | ||
+ | end loop; | ||
+ | | ||
+ | end; | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | Beispiel: | ||
+ | |||
+ | <code plsql> | ||
+ | |||
+ | SELECT id from TABLE(getpivot(2)); | ||
+ | ------ | ||
+ | 1 | ||
+ | 2 | ||
+ | |||
+ | |||
+ | select * from TABLE(getpivot(to_number(to_char(LAST_DAY(sysdate),' | ||
+ | ----------- | ||
+ | |||
+ | 1 | ||
+ | 2 | ||
+ | ... | ||
+ | 29 | ||
+ | 30 | ||
+ | |||
+ | </ | ||
+ | |||
+ | === 2. Beispiel: === | ||
+ | |||
+ | Gleitendes Fenster für die Daten, Ziel ist es immer alle Daten der letzten vergangenen 30 Tage zu sehen. | ||
+ | In den Tagen lückenlos, auch falls für diesen Tag kein Wert existiert | ||
+ | |||
+ | Anlegen: | ||
+ | <code plsql> | ||
+ | |||
+ | create or replace type date_pivot as object( | ||
+ | | ||
+ | ); | ||
+ | / | ||
+ | |||
+ | create or replace type date_pivot_table as table of date_pivot; | ||
+ | / | ||
+ | |||
+ | CREATE OR REPLACE function getdate_pivot | ||
+ | | ||
+ | is | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | begin | ||
+ | |||
+ | p_count: | ||
+ | |||
+ | for i in 1 .. p_count | ||
+ | loop | ||
+ | pipe row ( date_pivot(v_first+i) ); | ||
+ | end loop; | ||
+ | | ||
+ | end; | ||
+ | / | ||
+ | |||
+ | |||
+ | --- | ||
+ | |||
+ | select datid from table( getdate_pivot()); | ||
+ | |||
+ | --- | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | Und dann das entsprechende Fenster aus der Pivot Tabelle ausschneiden\\ | ||
+ | |||
+ | Mit einem Outer Join mit den Wert joinen: | ||
+ | |||
+ | <code plsql> | ||
+ | select nvl(value, | ||
+ | , nvl(datum, | ||
+ | from ( select value | ||
+ | , mtime | ||
+ | from logtable | ||
+ | ) data | ||
+ | , ( select datid as pivdatum from table( getdate_pivot())) piv | ||
+ | where data.datum(+) = piv.pivdatum | ||
+ | and piv.pivdatum between sysdate - 30 and sysdate | ||
+ | order by 2 | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | | ||
+ | </ | ||
+ | |||
+ | |||
+ | So kann in einem gleitenden Fenster jetzt für alle Tage des aktuellen und des folgenden Jahre agiert werden. | ||
+ | |||
+ | |||
+ | |||
+ | === 3. Beispiel: === | ||
+ | |||
+ | |||
+ | Beispiel mit mehr als einen Rückgabewert: | ||
+ | |||
+ | <code plsql> | ||
+ | |||
+ | CREATE OR REPLACE TYPE date_pivot2 AS object( | ||
+ | datid DATE | ||
+ | , | ||
+ | ); | ||
+ | / | ||
+ | |||
+ | |||
+ | CREATE OR REPLACE TYPE pivot2_table AS TABLE OF date_pivot2; | ||
+ | / | ||
+ | |||
+ | |||
+ | -------- | ||
+ | |||
+ | CREATE OR REPLACE FUNCTION get2_pivot | ||
+ | | ||
+ | IS | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | BEGIN | ||
+ | | ||
+ | FOR i IN 1 .. p_count | ||
+ | LOOP | ||
+ | pipe ROW ( date_pivot2(v_first+i, | ||
+ | END LOOP; | ||
+ | | ||
+ | END; | ||
+ | / | ||
+ | |||
+ | ----- | ||
+ | |||
+ | SELECT datid AS pivdatum, nrid as prinr | ||
+ | FROM TABLE(get2_pivot()); | ||
+ | |||
+ | |||
+ | PIVDATUM | ||
+ | -------------- ------------ | ||
+ | ... | ||
+ | 19.08.15 00:00 595 | ||
+ | ... | ||
+ | |||
+ | |||
+ | ------ | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | |||
prog/sql_piviot.txt · Zuletzt geändert: 2015/10/13 20:56 von gpipperr