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: Besser: 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.

1. Type und Type Tabelle anlegen
2. Funktion auf diese Tabelle erstellen und
3. verwenden

Type anlegen:

CREATE TYPE t_pivot AS object(  id   INTEGER );
/
 
CREATE TYPE pivot_table AS TABLE OF t_pivot;
/
 

Funktion definieren:

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;
/

Beispiel:

SELECT id FROM TABLE(getpivot(2));	
------
1
2
 
 
SELECT * FROM TABLE(getpivot(TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE),'DD'))))
-----------
 
1
2
...
29
30

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:

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());
 
---

Und dann das entsprechende Fenster aus der Pivot Tabelle ausschneiden

Mit einem Outer Join mit den Wert joinen:

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

 piv.pivdatum between sysdate - 30 and sysdate 

So kann in einem gleitenden Fenster jetzt für alle Tage des aktuellen und des folgenden Jahre agiert werden.

Beispiel mit mehr als einen Rückgabewert:

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