prog:sql_oracle_pivot
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende Überarbeitung | |||
prog:sql_oracle_pivot [2019/07/29 22:39] – [Beispiel 5 - Übersicht über die Log Switche] gpipperr | prog:sql_oracle_pivot [2019/07/29 22:39] (aktuell) – [Preise mit gestaffelten Rabatten berechnen] gpipperr | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
+ | ===== SQL Pivot Funktion ===== | ||
+ | ** ab 11g ** | ||
+ | |||
+ | ** erstellt März 2015 ** | ||
+ | |||
+ | |||
+ | Pivot => französisch: | ||
+ | |||
+ | |||
+ | Mit der SQL Pviot Funktion können Werte aus Zeilen in Spalten transponiert werden. | ||
+ | |||
+ | |||
+ | Für gegenteilige Funktion siehe [[prog: | ||
+ | === Beispiel 1 === | ||
+ | |||
+ | In diesem Bespiel erzeugen wir eine Liste der Abteilungen mit der Anzahl der Angestellten. | ||
+ | |||
+ | Diese Listensicht soll in einer Zeile dargestellt werden. | ||
+ | |||
+ | {{ : | ||
+ | |||
+ | Beispiel: | ||
+ | <code sql> | ||
+ | select * | ||
+ | from ( select d.dname | ||
+ | , COUNT (*) as percount | ||
+ | from emp e | ||
+ | , dept d | ||
+ | where d.deptno = e.deptno | ||
+ | group by d.dname | ||
+ | ) | ||
+ | pivot ( | ||
+ | MAX (percount) | ||
+ | for dname | ||
+ | in (' | ||
+ | ,' | ||
+ | ,' | ||
+ | ) | ||
+ | ) | ||
+ | / | ||
+ | |||
+ | |||
+ | ACCOUNTING | ||
+ | ---------- ---------- ---------- | ||
+ | | ||
+ | |||
+ | |||
+ | </ | ||
+ | |||
+ | Es können auch mehr als eine Spalte ausgewählt werden, einfach die Aggregat Funktion auf die Spalte durch komma getrennt hintereiander schrieben und je einen alias vergeben. Der Alias wird dann zu den Aliase in der " | ||
+ | |||
+ | Beispiel: | ||
+ | <code sql> | ||
+ | |||
+ | SQL> | ||
+ | 2 FROM ( SELECT d.dname | ||
+ | 3 , count (*) AS percount | ||
+ | 4 , sum(sal) | ||
+ | 5 , loc | ||
+ | 6 FROM emp e | ||
+ | 7 , dept d | ||
+ | 8 WHERE d.deptno = e.deptno | ||
+ | 9 GROUP BY d.dname, | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | |||
+ | LOC | ||
+ | --------------------------------------- ------------------- ----------------- ----------------- --------------- -------------- ------------ | ||
+ | NEW YORK | ||
+ | Düsseldorf | ||
+ | DALLAS | ||
+ | CHICAGO | ||
+ | |||
+ | oder auch: | ||
+ | |||
+ | |||
+ | .. | ||
+ | ( MAX (CURRENT_UTILIZATION) as CUR_UTL , MAX (MAX_UTILIZATION) as MAX_UTL, max(LIMIT_VALUE) As MAX_LIMIT | ||
+ | FOR RESOURCE_NAME | ||
+ | IN ( ' | ||
+ | ,' | ||
+ | ) | ||
+ | |||
+ | |||
+ | INSTANCE_NUMBER BEGIN_INTERVAL_T PROC_CUR_UTL PROC_MAX_UTL PROC_MAX_L SESS_CUR_UTL SESS_MAX_UTL SESS_MAX_L | ||
+ | --------------- ---------------- ------------ ------------ ---------- ------------ ------------ ---------- | ||
+ | .... | ||
+ | | ||
+ | </ | ||
+ | === Beispiel 2 - Darstellung mit XML === | ||
+ | |||
+ | Mit dem **pivot XML** kann eine sub query verwendt werden, damit wird das ganze deutlich dynamischer | ||
+ | |||
+ | <code sql> | ||
+ | select * | ||
+ | from ( select d.dname | ||
+ | , COUNT (*) as percount | ||
+ | from emp e | ||
+ | , dept d | ||
+ | where d.deptno = e.deptno | ||
+ | group by d.dname | ||
+ | ) | ||
+ | pivot XML ( | ||
+ | MAX (percount) as counter | ||
+ | for dname | ||
+ | in (select dname from dept where deptno > 5) | ||
+ | ) | ||
+ | |||
+ | -- Erzeugt: | ||
+ | < | ||
+ | < | ||
+ | < | ||
+ | < | ||
+ | < | ||
+ | </ | ||
+ | |||
+ | </ | ||
+ | |||
+ | Das Ergebniss kann dann wieder mit **select * from XMLTABLE( .. ** ausgewertet werden. | ||
+ | |||
+ | Mit dem Ausdruck **ANY** kann auch auf die Subquery verzichtet werden | ||
+ | |||
+ | <code sql> | ||
+ | select * | ||
+ | from ( select d.dname | ||
+ | , COUNT (*) as percount | ||
+ | from emp e | ||
+ | , dept d | ||
+ | where d.deptno = e.deptno | ||
+ | group by d.dname | ||
+ | ) | ||
+ | pivot XML ( | ||
+ | MAX (percount) | ||
+ | for dname | ||
+ | in ( ANY ) | ||
+ | ) | ||
+ | |||
+ | </ | ||
+ | |||
+ | === Beispiel 3 - zum Filtern und transponieren === | ||
+ | |||
+ | Zum Beispiel bei Filtern von Statistiken für Überwachungszwecke: | ||
+ | <code sql> | ||
+ | |||
+ | select * | ||
+ | from (select SN.NAME as statname | ||
+ | , GS.VALUE | ||
+ | from GV$SYSSTAT gs, gv$statname sn | ||
+ | where GS.INST_ID = SN.INST_ID | ||
+ | and GS.STATISTIC# | ||
+ | pivot ( | ||
+ | SUM (VALUE) | ||
+ | for statname | ||
+ | in ( | ||
+ | ' | ||
+ | , 'file io wait time' as file_io_wait_time | ||
+ | ) | ||
+ | ) | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | === Beispiel 4 - Alle Spalten eines mehrspaltigen Indexes nebeneinander anzeigen lassen === | ||
+ | |||
+ | Eine Liste aller Indexes mit mehr als einer Spalte des Schemas ist zu erstellen, die Spalten des Index sollen nebeneinander bis zur 9. Stelle dargestellt werden: | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | select * from ( | ||
+ | select * from ( | ||
+ | select | ||
+ | index_owner | ||
+ | , table_name | ||
+ | , index_name | ||
+ | , column_name | ||
+ | , column_position | ||
+ | from dba_ind_columns | ||
+ | where index_owner like '&& | ||
+ | order by index_owner, | ||
+ | ) | ||
+ | pivot ( | ||
+ | min (column_name) | ||
+ | for column_position | ||
+ | in (' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ) | ||
+ | ) | ||
+ | ) | ||
+ | where pos2 is not null | ||
+ | / | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | ==== Beispiel 5 - Übersicht über die Log Switche ==== | ||
+ | |||
+ | Ziel ist es ein Übersicht über die Log Switche der letzen Tage einer Datenbank: | ||
+ | <code sql> | ||
+ | set linesize 130 | ||
+ | |||
+ | column T01 format a3 heading " | ||
+ | column T02 format a3 heading " | ||
+ | column T03 format a3 heading " | ||
+ | column T04 format a3 heading " | ||
+ | column T05 format a3 heading " | ||
+ | column T06 format a3 heading " | ||
+ | column T07 format a3 heading " | ||
+ | column T08 format a3 heading " | ||
+ | column T09 format a3 heading " | ||
+ | column T10 format a3 heading " | ||
+ | column T11 format a3 heading " | ||
+ | column T12 format a3 heading " | ||
+ | column T13 format a3 heading " | ||
+ | column T14 format a3 heading " | ||
+ | column T15 format a3 heading " | ||
+ | column T16 format a3 heading " | ||
+ | column T17 format a3 heading " | ||
+ | column T18 format a3 heading " | ||
+ | column T19 format a3 heading " | ||
+ | column T20 format a3 heading " | ||
+ | column T21 format a3 heading " | ||
+ | column T22 format a3 heading " | ||
+ | column T23 format a3 heading " | ||
+ | column T24 format a3 heading " | ||
+ | column slday format a5 heading " | ||
+ | |||
+ | select to_char(to_date(to_char(slday),' | ||
+ | , decode(nvl(T01, | ||
+ | , decode(nvl(T02, | ||
+ | , decode(nvl(T03, | ||
+ | , decode(nvl(T04, | ||
+ | , decode(nvl(T05, | ||
+ | , decode(nvl(T06, | ||
+ | , decode(nvl(T07, | ||
+ | , decode(nvl(T08, | ||
+ | , decode(nvl(T09, | ||
+ | , decode(nvl(T10, | ||
+ | , decode(nvl(T11, | ||
+ | , decode(nvl(T12, | ||
+ | , decode(nvl(T13, | ||
+ | , decode(nvl(T14, | ||
+ | , decode(nvl(T15, | ||
+ | , decode(nvl(T16, | ||
+ | , decode(nvl(T17, | ||
+ | , decode(nvl(T18, | ||
+ | , decode(nvl(T19, | ||
+ | , decode(nvl(T20, | ||
+ | , decode(nvl(T21, | ||
+ | , decode(nvl(T22, | ||
+ | , decode(nvl(T23, | ||
+ | , decode(nvl(T24, | ||
+ | from ( | ||
+ | select sum( decode( | ||
+ | ,0,0 | ||
+ | ,1) | ||
+ | ) as slog | ||
+ | , dr.dr as slday | ||
+ | , dr.dh as slhour | ||
+ | --, to_char(lh.FIRST_TIME,' | ||
+ | | ||
+ | v$log_history lh | ||
+ | ,( select td.dr||th.hr as dg , th.hr as dh , td.dr as dr | ||
+ | from (select ltrim(to_char(rownum,' | ||
+ | , (select ltrim(to_char(sysdate-(rownum-1),' | ||
+ | ) dr | ||
+ | where dr.dg = to_char(lh.FIRST_TIME (+),' | ||
+ | group by to_char(lh.FIRST_TIME,' | ||
+ | , dr.dg , | ||
+ | ) | ||
+ | pivot ( | ||
+ | sum (slog) | ||
+ | FOR slhour | ||
+ | IN (' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ) | ||
+ | ) | ||
+ | / | ||
+ | </ | ||
+ | Komplettes Beispiel siehe hier [[https:// | ||
+ | |||
+ | Ergebniss: | ||
+ | {{: | ||
+ | |||
+ | Als Pivot Tabelle für das Datum dienen die beiden Abfragen th, td mit der für die letzen 20 Tage alle Stunden aufgelistet werden, über den Outjoin mit der v$log_history werden dann die Logs pro Stunde und Tag gezählt. Das Ergebnis wird nach rechts mit dem Pviot Statement transponiert. | ||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== Preise mit gestaffelten Rabatten berechnen===== | ||
+ | |||
+ | |||
+ | Rabatte sind gestaffelt und sollen mehrstufig in einer Zeile angezeigt werden. | ||
+ | Der Rabattwert in % wird dabei je nach Stufe abgezogen, das End Ergebnis soll angezeigt werden. | ||
+ | |||
+ | |||
+ | <code sql> | ||
+ | |||
+ | drop table T_RABAT; | ||
+ | |||
+ | create table T_RABAT ( nr number, pos number, beitrag number, sum_beitrag number, rabat number, | ||
+ | |||
+ | |||
+ | insert into T_RABAT values (10 , | ||
+ | insert into T_RABAT values (10 , | ||
+ | insert into T_RABAT values (10 , | ||
+ | insert into T_RABAT values (10 , | ||
+ | insert into T_RABAT values (10 , | ||
+ | insert into T_RABAT values (10 , | ||
+ | insert into T_RABAT values (10 , | ||
+ | insert into T_RABAT values (10 , | ||
+ | commit; | ||
+ | |||
+ | |||
+ | |||
+ | select nr | ||
+ | , beitrag | ||
+ | , | ||
+ | , RABATT2 | ||
+ | , RABATT3 | ||
+ | , RABATT4 | ||
+ | , RABATT5 | ||
+ | as gesamt_preis | ||
+ | from | ||
+ | ( | ||
+ | select | ||
+ | , POS | ||
+ | , BEITRAG | ||
+ | , SUM_BEITRAG | ||
+ | , RABATT1 | ||
+ | , RABATT2 | ||
+ | , RABATT3 | ||
+ | , RABATT4 | ||
+ | , RABATT5 | ||
+ | from ( | ||
+ | SELECT | ||
+ | NR | ||
+ | , POS | ||
+ | , BEITRAG | ||
+ | , SUM_BEITRAG | ||
+ | , rabat | ||
+ | , rabat_row | ||
+ | FROM T_RABAT | ||
+ | GROUP by NR | ||
+ | , POS | ||
+ | , BEITRAG | ||
+ | , SUM_BEITRAG | ||
+ | , rabat | ||
+ | , rabat_row | ||
+ | ) | ||
+ | pivot ( | ||
+ | MAX (rabat) | ||
+ | FOR rabat_ROW | ||
+ | IN (' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ) | ||
+ | ) | ||
+ | ) | ||
+ | |||
+ | |||
+ | ----------------------- | ||
+ | select nr | ||
+ | , beitrag | ||
+ | , beitrag1 | ||
+ | , beitrag2 | ||
+ | , beitrag3 | ||
+ | , beitrag4 | ||
+ | , (beitrag4 -((beitrag4 / 100 )*nvl( abs(RABATT5), | ||
+ | from | ||
+ | ( | ||
+ | select nr | ||
+ | , beitrag | ||
+ | , beitrag1 | ||
+ | , beitrag2 | ||
+ | , beitrag3 | ||
+ | , (beitrag3 -((beitrag3 / 100 )*nvl( abs(RABATT4), | ||
+ | , RABATT5 | ||
+ | from | ||
+ | ( | ||
+ | select nr | ||
+ | , beitrag | ||
+ | , beitrag1 | ||
+ | , beitrag2 | ||
+ | , (beitrag2 -((beitrag2 / 100 )*nvl( abs(RABATT3), | ||
+ | , RABATT4 | ||
+ | , RABATT5 | ||
+ | from | ||
+ | ( | ||
+ | select nr | ||
+ | , beitrag | ||
+ | , beitrag1 | ||
+ | , (beitrag1 -((beitrag1 / 100 )*nvl( abs(RABATT2), | ||
+ | , RABATT3 | ||
+ | , RABATT4 | ||
+ | , RABATT5 | ||
+ | from | ||
+ | ( | ||
+ | select nr | ||
+ | , beitrag | ||
+ | , | ||
+ | , RABATT2 | ||
+ | , RABATT3 | ||
+ | , RABATT4 | ||
+ | , RABATT5 | ||
+ | from | ||
+ | ( | ||
+ | select | ||
+ | , POS | ||
+ | , BEITRAG | ||
+ | , SUM_BEITRAG | ||
+ | , RABATT1 | ||
+ | , RABATT2 | ||
+ | , RABATT3 | ||
+ | , RABATT4 | ||
+ | , RABATT5 | ||
+ | from ( | ||
+ | SELECT | ||
+ | NR | ||
+ | , POS | ||
+ | , BEITRAG | ||
+ | , SUM_BEITRAG | ||
+ | , rabat | ||
+ | , rabat_row | ||
+ | FROM T_RABAT | ||
+ | GROUP by NR | ||
+ | , POS | ||
+ | , BEITRAG | ||
+ | , SUM_BEITRAG | ||
+ | , rabat | ||
+ | , rabat_row | ||
+ | ) | ||
+ | pivot ( | ||
+ | MAX (rabat) | ||
+ | FOR rabat_ROW | ||
+ | IN | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ,' | ||
+ | ) | ||
+ | ) | ||
+ | ) | ||
+ | ) | ||
+ | ) | ||
+ | ) | ||
+ | ) | ||
+ | ; | ||
+ | |||
+ | |||
+ | -- Ergebnis | ||
+ | |||
+ | |||
+ | NR BEITRAG | ||
+ | ------------ ------------ ------------ ------------ ------------ ------------ ------------ | ||
+ | 10 115.3 111.05696 | ||
+ | 10 13.16 | ||
+ | | ||
+ | | ||
+ | </ | ||
+ | |||
+ | |||
+ | ==== Quellen ==== | ||
+ | |||
+ | * http:// | ||
+ | * http:// |
prog/sql_oracle_pivot.txt · Zuletzt geändert: 2019/07/29 22:39 von gpipperr