10. April 2007

Kreuztabellen: Jetzt wird es generisch!

Nun kommt der zweite Teil. Nach Patricks Kommentar habe ich die Lösung nochmals durchdacht und festgestellt, dass die DECODE-Variante gar nicht so schlecht ist (vor allem erzeugt sie bessere Ausführungspläne) - Vielen Dank, Patrick. Insofern habe ich die generische Lösung umgestellt ... bis auf zwei Dinge:
  • Die WITH-Klausel bleibt drin - sie erlaubt mir, das "Pivoting" mit beliebigen Abfragen durchzuführen
  • Anstelle von DECODE verwende ich CASE WHEN ... END-Ausdrücke - diese sind mächtiger als DECODE
Daraus ergibt sich folgende Funktion:
create or replace function getPivotSql(
  p_sql            in varchar2,
  p_x_col          in varchar2,
  p_x_col_type     in varchar2 default 'DATE',
  p_x_col_start    in varchar2,
  p_x_col_interval in varchar2,
  p_x_col_int_unit in varchar2 default 'DAY',
  p_x_col_count    in number,
  p_y_col          in varchar2,
  p_cell_col       in varchar2,
  p_cell_col_aggr  in varchar2 default NULL
) return varchar2
is
  v_sql varchar2(32767);
begin
  v_sql := '';
  v_sql := v_sql || 'with data as ('||chr(10);
  v_sql := v_sql || '  '||p_sql||chr(10);
  v_sql := v_sql || ')';
  if p_x_col_type = 'VARCHAR2' then
    v_sql := v_sql ||', x_dist_values as ('||chr(10);
    v_sql := v_sql ||'  select distinct '||p_x_col||' val from data order by 1'||chr(10);
    v_sql := v_sql ||'), x_values_rownum as ('||chr(10);
    v_sql := v_sql ||'  select rownum zeile, val from x_dist_values where rownum <= '||p_x_col_count||chr(10);
    v_sql := v_sql ||')'||chr(10);
  else
    v_sql := v_sql || chr(10);
  end if;
  v_sql := v_sql || 'select distinct '||chr(10);
  v_sql := v_sql || '  data.'||p_y_col||','||chr(10);
  for i in 1..p_x_col_count loop
    if p_cell_col_aggr is not null then
      v_sql := v_sql || p_cell_col_aggr||'(';
    end if;
    v_sql := v_sql || '  case when ';
    if p_x_col_type = 'VARCHAR2' then
      v_sql := v_sql || 'x.zeile = '||i;
    elsif p_x_col_type = 'NUMBER' then
      v_sql := v_sql || ' data.'||p_x_col||' >= '||p_x_col_start||
        ' + ('||(i - 1)||' * '||p_x_col_interval || ') ' ||
        ' and data.'||p_x_col||' < '||p_x_col_start||
        ' + ('||i||' * '||p_x_col_interval||') ';
    elsif p_x_col_type = 'DATE' then
      v_sql := v_sql || ' data.'||p_x_col||' >= '||p_x_col_start||
        ' + interval '''||((i - 1) * p_x_col_interval )||''' '||p_x_col_int_unit||
        ' and data.'||p_x_col||' < '||p_x_col_start||
        ' + interval '''||* p_x_col_interval ||''' '||p_x_col_int_unit;
    end if;
    v_sql := v_sql ||' then '||p_cell_col|| ' else null end';
    if p_cell_col_aggr is not null then
      v_sql := v_sql || ')';
    end if;
    v_sql := v_sql || ' as "VALUE['||i||']"';
    if i < p_x_col_count then
      v_sql := v_sql || ',';
    end if;
    v_sql := v_sql ||chr(10);
  end loop;
  v_sql := v_sql || 'from data'||chr(10);
  if p_x_col_type = 'VARCHAR2' then
    v_sql := v_sql || 'join x_values_rownum x on (data.'||p_x_col||' = x.val)'||chr(10);
  end if;
  if p_cell_col_aggr is not null then
    v_sql := v_sql || 'group by data.'||p_y_col||chr(10);
  end if;
  return v_sql;
end;
/
Sie wird wie folgt aufgerufen:
getPivotSql(
  P_SQL            => [SQL, dessen Ergebnismenge pivotiert werden soll]
  P_X_COL          => [X-Spalte]
  P_X_COL_TYPE     => [Datentyp der X-Spalte]
  P_X_COL_START    => [Bei NUMBER und DATE: Startwert der X-Spalte]
  P_X_COL_INTERVAL => [Bei NUMBER und DATE: Intervall, umwelches sich X mit jeder Spalte erhöhen soll]
  P_X_COL_INT_UNIT => [Bei DATE: Einheit des Intervalls (DAY, MONTH, YEAR)]
  P_X_COL_COUNT    => [Anzahl der X-Spalten]
  P_Y_COL          => [Y-Spalte]
  P_CELL_COL       => [Spalte, mit der die Zellen gefüllt werden sollen]
  P_CELL_COL_AGGR  => [Aggregatsfunktion für die Zellen]
)
Ein Beispiel: Auf der X-Achse sollen die Jobs, auf der Y-Achse die Departments aufgetragen werden; in den Zellen soll die Gehaltssumme stehen, so dass man sehen kann, in welchen Jobs und in welchen Abteilungen welche Gehälter gezahlt werden.
select getPivotSql(
  'select deptno, job, sal from emp', /* SQL mit den Daten                         */
  'JOB',                              /* X-Spalte                                  */
  'VARCHAR2',                         /* Datentyp der X-Spalte                     */
  null,                               /* VARCHAR2: Kein Startwert                  */
  null,                               /* VARCHAR2: Kein Intervall                  */
  null,                               /* VARCHAR2: Keine Einheit für das Intervall */
  5,                                  /* maximal 5 X-Spalten                       */
  'DEPTNO',                           /* Y-Achse (Zeilen)                          */
  'SAL',                              /* Zellenwerte (Gehalt)                      */
  'SUM'                               /* Aggregatsfunktion (Summe)                 */
)
from dual
/
Als Ergebnis kommt folgendes SQL heraus ...
with data as (
  select deptno, job, sal from emp
), x_dist_values as (
  select distinct JOB val from data order by 1
), x_values_rownum as (
  select rownum zeile, val from x_dist_values where rownum <= 5
)
select distinct
  data.DEPTNO,
SUM(  case when x.zeile = 1 then SAL else null end) as "VALUE[1]",
SUM(  case when x.zeile = 2 then SAL else null end) as "VALUE[2]",
SUM(  case when x.zeile = 3 then SAL else null end) as "VALUE[3]",
SUM(  case when x.zeile = 4 then SAL else null end) as "VALUE[4]",
SUM(  case when x.zeile = 5 then SAL else null end) as "VALUE[5]"
from data
join x_values_rownum x on (data.JOB = x.val)
group by data.DEPTNO
... und ausgeführt ergibt sich:
    DEPTNO   VALUE[1]   VALUE[2]   VALUE[3]   VALUE[4]   VALUE[5]
---------- ---------- ---------- ---------- ---------- ----------
        30                   950       2850                  5600
        20       6000       1900       2975
        10                  1300       2450       5000

3 Zeilen ausgewählt.
Andere Beispiele: Es soll anhand des Gehalts selektiert werden. Ausgehend von einem Startgehalt von 1.000 sollen drei Gruppen gebildet werden (1000 - 2500, 2500 - 4000, 4000 - 5500). Diese werden auf der X-Achse aufgetragen - Die Y-Achse bilden diesmal die Jobs:
select getPivotSql(
  'select job, sal, empno from emp',  /* SQL mit den Daten                         */
  'SAL',                              /* X-Spalte                                  */
  'NUMBER',                           /* Datentyp der X-Spalte                     */
  1000,                               /* VARCHAR2: Kein Startwert                  */
  1500,                               /* VARCHAR2: Kein Intervall                  */
  null,                               /* VARCHAR2: Keine Einheit für das Intervall */
  3,                                  /* maximal 5 X-Spalten                       */
  'JOB',                              /* Y-Achse (Zeilen)                          */
  'EMPNO',                            /* Zellenwerte (Gehalt)                      */
  'COUNT'                             /* Aggregatsfunktion (Summe)                 */
)
from dual
/
Dann kommt dieses SQL heraus:
with data as (
  select job, sal, empno from emp
)
select distinct
  data.JOB,
  COUNT(  case when  data.SAL between 1000 + (0 * 1500) and 1000 + (1 * 1500)  then EMPNO else null end) as "VALUE[1]",
  COUNT(  case when  data.SAL between 1000 + (1 * 1500) and 1000 + (2 * 1500)  then EMPNO else null end) as "VALUE[2]",
  COUNT(  case when  data.SAL between 1000 + (2 * 1500) and 1000 + (3 * 1500)  then EMPNO else null end) as "VALUE[3]"
from data
group by data.JOB
Ausgeführt ...
JOB         VALUE[1]   VALUE[2]   VALUE[3]
--------- ---------- ---------- ----------
CLERK              2          0          0
SALESMAN           4          0          0
PRESIDENT          0          0          1
MANAGER            1          2          0
ANALYST            0          2          0
Probiert es aus! Feedback bzw. Kommentare sind natürlich stets erwünscht ...

Kommentare:

Anonym hat gesagt…

Hallo,

ich bin im moment ein wenig am Verzweifeln bei dem Versuch in Oracle 10gXE eine Kreuztabelle zu erzeugen bzw. eine solche als Rückgabeobjekt zu bekommen ... :(

Ich habe die Funktion getPivotSql() genauso wie in dem Post auf in meiner DB erstellt.

Das erste was mir seltsam erscheint ist, dass ich diese Funktion über die DB-Homepage nicht sehen kann bzw. auf der Seite "Funktion ansehen" nichts ausser den Namen sichtbar ist.

Trotzdem habe ich die Funktion wie folgt aufgerufen:
select getpivotsql ('SELECT FEHLER_ID, FEHLERANZAHL, DATUM FROM WASTE.DATA',
'fehler_id',
'number',
null,
null,
null,
8,
'DATUM',
'FEHLERANZAHL',
'SUM')
from dual

Der folgende SQL-String wurde mir zurückgegeben:
with data as ( SELECT FEHLER_ID, FEHLERANZAHL, DATUM FROM WASTE.DATA )
select distinct data.DATUM,
SUM( case when then FEHLERANZAHL else null end) as "VALUE[1]",
SUM( case when then FEHLERANZAHL else null end) as "VALUE[2]",
SUM( case when then FEHLERANZAHL else null end) as "VALUE[3]",
SUM( case when then FEHLERANZAHL else null end) as "VALUE[4]",
SUM( case when then FEHLERANZAHL else null end) as "VALUE[5]",
SUM( case when then FEHLERANZAHL else null end) as "VALUE[6]",
SUM( case when then FEHLERANZAHL else null end) as "VALUE[7]",
SUM( case when then FEHLERANZAHL else null end) as "VALUE[8]" from data group by data.DATUM

Und wie man sieht kann diese net ausgeführt werden, da die Bedingungen in den "CASE WHEN" fehlen oder irre ich mich da?

Wieso fehlen diese Bedingungen?

Oder habe ich was falsch gemacht?

Die Tabelle sieht wie folgt aus:

fehler_id: Number -> Soll zur Spaltenüberschrift werden
fehleranzahl: Number -> soll Summiert werden
Datum: Date -> soll Zeilenüberschrift werden

Spielen weitere Spalten in der Tabelle irgendwie eine Rolle?

Wäre für jede Hilfe sehr dankbar!

Gruß

DiWi

Carsten Czarski hat gesagt…

Ich nehme nochmal den Aufruf von "getPivotSQL" auf:

select getpivotsql ('SELECT FEHLER_ID, FEHLERANZAHL, DATUM FROM WASTE.DATA',
'fehler_id',
'number',
null,
null,
null,
8,
'DATUM',
'FEHLERANZAHL',
'SUM')
from dual

Wenn die Spalte FEHLER_ID vom Typ NUMBER ist, muss ein STARTWERT und ein INTERVALL angegeben werden (die zwei Parameter danach) ... also:

select getpivotsql ('SELECT FEHLER_ID, FEHLERANZAHL, DATUM FROM WASTE.DATA',
'fehler_id',
'NUMBR',
0,
100,
null,
8,
'DATUM',
'FEHLERANZAHL',
'SUM')
from dual

oder so ... Wenn FEHLER_ID eine feste, überschaubare Anzahl von Werten hat, dann sollte als Datentyp eher VARCHAR2 angegeben werden (auch wenn die Spalte eigentlich ein NUMBER) ist. Dann können die zwei Parameter auch wieder auf NULL gesetzt werden.

Anonym hat gesagt…

Hallo, eine Super-Sache mit der Kreuztabelle. Wie bekomme ich aber als Psaltenüberschrift Value[1], Value[2] einen sprechenden Namen (z.B. die Job-Bezeichnung)???

Anonym hat gesagt…

Hallo,

erstmal vielen Dank für die schnelle Antwort!

Ich habe das deinem Vorschlag entsprechend ausprobiert. Das Problem mit dem Datentyp war aber nicht der Grund warum die "CASE WHEN" nicht gefüllt wurden.
Allem Anschein nach ist die Gross- und Kleinschreibung von FEHLER_ID im Aufruf (einmal klein und einmal gross geschrieben) dafür verantwortlich. :)

Das was du mit dem Datentyp angesprochen hast erzeugt einen Fehler -> "Ausdruck Fehlt" wenn es so wie bei mir in den ersten Versuchen aufgerufen wird.

Tja, schade nur dass ich das noch auf der 10g umsetzen muss, denn wie du in einem anderen Post schreibst ist "PIVOT" in der 11g schon drin.

Vielen Dank nochmal für die Hilfe!

Gruß

DiWi

Carsten Czarski hat gesagt…

Hallo,

> Anonym Hat gesagt…
>
> Hallo, eine Super-Sache mit der
> Kreuztabelle. Wie bekomme ich aber
> als Psaltenüberschrift Value[1],
> Value[2] einen sprechenden Namen
> (z.B. die Job-Bezeichnung)???

Nicht im SQL*Plus ... Dies muss man dann in der Anwendung selbst mit eigener Logik tun. In Oracle11g kann man das mit der PIVOT Klausel erreichen ...

Website Konzeption hat gesagt…

Hallo,

ich möchte als Wert für die X-Spalte und die Y-Spalte jeweils die Rückgabewerte eine Function verwenden.

Beim Ausführen des generierten SQl-Befehls erhalten ich eine Fehlermdelung, dass der Spaltenname unbekannt sei.

Mache ich etwas falsch oder das an dieser Stelle keine Function verwendet werden?

Gruß

Tobias

Carsten Czarski hat gesagt…

Hallo Tobias,

hast Du ein Beispiel für mich ...?

Am besten per Mail; die Mailadresse findest Du im Impressum

Grüße

-Carsten

Beliebte Postings