Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:sql_piviot

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
prog:sql_piviot [2015/10/13 20:55]
gpipperr [Eine dynamische Kreuz - (Pivot) Tabelle anlegen]
prog:sql_piviot [2015/10/13 20:56] (aktuell)
gpipperr [Eine dynamische Kreuz - (Pivot) Tabelle anlegen]
Zeile 1: Zeile 1:
 +====== Eine dynamische Kreuz - (Pivot) Tabelle anlegen ======
 +**8i/9i/10g/11g/12c**
 +
 +
 +**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 #008000>**Besser:**</fc> Eine Funktion, die sich wie eine Tabelle verwenden lässt und genau die gewünschte Anzahl von Elementen zurückgibt! 
 +    * 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(  id   integer );
 +/
 +
 +create type pivot_table as table of t_pivot;
 +/
 +  
 +</code>
 +
 +
 +Funktion definieren:
 +<code plsql>
 +CREATE OR REPLACE function getpivot(
 +   p_count   integer
 +)
 +   return pivot_table pipelined
 +is
 +   out_rec   t_pivot := t_pivot( 1 );
 +begin
 +   for i in 1 .. p_count
 +   loop
 +      pipe row( t_pivot( i ));
 +   end loop;
 +   return;
 +end;
 +/
 +</code>
 +
 +Beispiel:
 +
 +<code plsql>
 +
 +SELECT id from TABLE(getpivot(2));
 +------
 +1
 +2
 +
 +
 +select * from TABLE(getpivot(to_number(to_char(LAST_DAY(sysdate),'DD'))))
 +-----------
 +
 +1
 +2
 +...
 +29
 +30
 +
 +</code>
 +
 +=== 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(
 +   datid   date
 +);
 +/
 +
 +create or replace type date_pivot_table as table of date_pivot;
 +/
 +
 +CREATE OR REPLACE function getdate_pivot
 +   return date_pivot_table pipelined
 +is
 +   v_akt_year  number:=to_number(to_char(sysdate,'YYYY'))-1;
 +   v_next_year number:=to_number(to_char(sysdate,'YYYY'))+1;
 +   v_first     date:= to_date('01.01.'||to_char(v_akt_year),'dd.mm.yyyy');
 +   p_count    number;
 +begin
 +
 +p_count:=to_number(to_char(to_date('31.12.'||to_char(v_akt_year),'dd.mm.yyyy'),'DDD'))+to_number(to_char(to_date('31.12.'||to_char(v_next_year),'dd.mm.yyyy'),'DDD'));
 +
 +   for i in 1 .. p_count
 +   loop
 +         pipe row  ( date_pivot(v_first+i) );
 +   end loop;
 +   return;
 +end;
 +/
 +
 +
 +---
 +
 +select datid from table( getdate_pivot());
 +
 +---
 +
 +</code>
 +
 +
 +Und dann das entsprechende Fenster aus der Pivot Tabelle ausschneiden\\
 +
 +Mit einem Outer Join mit den Wert joinen:
 +
 +<code plsql>
 +select nvl(value,0)
 +     , nvl(datum,pivdatum)
 +  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
 +</code>
 +
 +<code>
 + piv.pivdatum between sysdate - 30 and sysdate 
 +</code>
 +
 +
 +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
 +   ,nrid    integer
 +);
 +/
 +
 +
 +CREATE OR REPLACE TYPE pivot2_table AS TABLE OF date_pivot2;
 +/
 +
 +
 +--------
 +
 +CREATE OR REPLACE FUNCTION get2_pivot
 +   RETURN pivot2_table pipelined
 +IS
 +   v_akt_year  NUMBER:=TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))-1;
 +   v_next_year NUMBER:=TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))+1;
 +   v_first     DATE:= TO_DATE('01.01.'||TO_CHAR(v_akt_year),'dd.mm.yyyy');
 +   p_count    NUMBER;
 +BEGIN
 +   p_count:=TO_NUMBER(TO_CHAR(TO_DATE('31.12.'||TO_CHAR(v_akt_year),'dd.mm.yyyy'),'DDD'))+TO_NUMBER(TO_CHAR(TO_DATE('31.12.'||TO_CHAR(v_next_year),'dd.mm.yyyy'),'DDD'));
 +   FOR i IN 1 .. p_count
 +   LOOP
 +         pipe ROW  ( date_pivot2(v_first+i,i) );
 +   END LOOP;
 +   RETURN;
 +END;
 +/
 +
 +-----
 +
 +SELECT datid AS pivdatum, nrid as prinr 
 +   FROM TABLE(get2_pivot());
 +
 +
 +PIVDATUM              PRINR
 +-------------- ------------
 +...
 +19.08.15 00:00          595
 +...
 +
 +
 +------
 +
 +</code>
 +
 +
 +
  
"Autor: Gunther Pipperr"
prog/sql_piviot.txt · Zuletzt geändert: 2015/10/13 20:56 von gpipperr