Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:sql_oracle_pivot

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
prog:sql_oracle_pivot [2019/07/29 22:39]
gpipperr [Beispiel 5 - Übersicht über die Log Switche]
prog:sql_oracle_pivot [2019/07/29 22:39] (aktuell)
gpipperr [Preise mit gestaffelten Rabatten berechnen]
Zeile 1: Zeile 1:
 +===== SQL Pivot Funktion =====
  
 +** ab 11g ** 
 +
 +** erstellt März 2015 **
 +
 +
 +Pivot => französisch: Angelpunkt
 +
 +
 +Mit der SQL Pviot Funktion können Werte aus Zeilen in Spalten transponiert werden.
 +
 +
 +Für gegenteilige Funktion siehe [[prog:sql_oracle_unpivot|SQL UNPivot Funktion]].
 +=== Beispiel 1  ===
 +
 +In diesem Bespiel erzeugen wir eine Liste der Abteilungen mit der Anzahl der Angestellten.
 +
 +Diese Listensicht soll in einer Zeile dargestellt werden.
 +
 +{{ :prog:sql_pivot_transpont_table_v01.png?300 | Tabelle soll transponiert 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' as ACCOUNTING
 +            ,'RESEARCH' as RESEARCH
 +            ,'SALES' as SALES
 +            )
 +)
 +/
 +
 +
 +ACCOUNTING   RESEARCH      SALES
 +---------- ---------- ----------
 +       296          5          6
 +       
 +
 +</code>
 +
 +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 "IN" Klause hinzugefügt. 
 +
 +Beispiel:
 +<code sql>
 +
 +SQL>SELECT *
 +  2    FROM (  SELECT d.dname
 +  3                  , count (*) AS percount
 +  4                             , sum(sal)  as persum
 +  5                             , loc
 +  6              FROM emp e
 +  7                 , dept d
 +  8             WHERE d.deptno = e.deptno
 +  9             GROUP BY d.dname,d.loc
 + 10          )
 + 11   pivot (
 + 12       max (percount) as percount, sum(persum) as persum
 + 13          FOR dname
 + 14          IN  ('ACCOUNTING' AS ACCOUNTING
 + 15              ,'RESEARCH' AS RESEARCH
 + 16              ,'SALES' AS SALES
 + 17              )
 + 18  )
 + 19  /
 +
 +LOC                                     ACCOUNTING_PERCOUNT ACCOUNTING_PERSUM RESEARCH_PERCOUNT RESEARCH_PERSUM SALES_PERCOUNT SALES_PERSUM
 +--------------------------------------- ------------------- ----------------- ----------------- --------------- -------------- ------------
 +NEW YORK
 +Düsseldorf
 +DALLAS                                                                                        5           10875
 +CHICAGO                                                                                                                      6         9400
 +
 +oder auch:
 +
 +
 +..
 +(  MAX (CURRENT_UTILIZATION) as CUR_UTL , MAX (MAX_UTILIZATION) as MAX_UTL, max(LIMIT_VALUE) As MAX_LIMIT
 +        FOR RESOURCE_NAME
 +        IN  ( 'processes' AS proc
 +             ,'sessions'  AS sess       
 +            )
 +
 +
 +INSTANCE_NUMBER BEGIN_INTERVAL_T PROC_CUR_UTL PROC_MAX_UTL PROC_MAX_L SESS_CUR_UTL SESS_MAX_UTL SESS_MAX_L
 +--------------- ---------------- ------------ ------------ ---------- ------------ ------------ ----------
 +          ....
 +          
 +</code>
 +=== 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:
 +<PivotSet>
 + <item><column name = "DNAME">ACCOUNTING</column><column name = "COUNTER">296</column></item>
 + <item><column name = "DNAME">OPERATIONS</column><column name = "COUNTER"></column></item>
 + <item><column name = "DNAME">RESEARCH</column>  <column name = "COUNTER">5</column></item>
 + <item><column name = "DNAME">SALES</column>     <column name = "COUNTER">6</column></item>
 +</PivotSet>
 +
 +</code>
 +
 +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 )
 +)
 +
 +</code>
 +
 +=== 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# = SN.STATISTIC#
 +pivot (
 +   SUM (VALUE)
 +   for statname
 +    in (
 +           'session logical reads' as session_logical_reads
 +         , 'file io wait time' as file_io_wait_time
 +       )
 +)
 +/
 +</code>
 +
 +=== 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 '&&USERNAME.%'  
 +   order by index_owner,table_name
 + )  
 + pivot ( 
 + min (column_name)
 + for column_position
 + in  ('1' as pos1
 + ,'2' as pos2
 + ,'3' as pos3
 + ,'4' as pos4
 + ,'5' as pos5
 + ,'6' as pos6
 + ,'7' as pos7
 + ,'8' as pos8
 + ,'9' as pos9
 + )
 + )
 +
 +where pos2 is not null
 +/
 + 
 +</code>
 +
 +
 +==== 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 "01" JUSTIFY CENTER
 +column T02 format a3 heading "02" JUSTIFY CENTER
 +column T03 format a3 heading "03" JUSTIFY CENTER
 +column T04 format a3 heading "04" JUSTIFY CENTER
 +column T05 format a3 heading "05" JUSTIFY CENTER
 +column T06 format a3 heading "06" JUSTIFY CENTER
 +column T07 format a3 heading "07" JUSTIFY CENTER
 +column T08 format a3 heading "08" JUSTIFY CENTER
 +column T09 format a3 heading "09" JUSTIFY CENTER
 +column T10 format a3 heading "10" JUSTIFY CENTER 
 +column T11 format a3 heading "11" JUSTIFY CENTER
 +column T12 format a3 heading "12" JUSTIFY CENTER
 +column T13 format a3 heading "13" JUSTIFY CENTER
 +column T14 format a3 heading "14" JUSTIFY CENTER
 +column T15 format a3 heading "15" JUSTIFY CENTER
 +column T16 format a3 heading "16" JUSTIFY CENTER
 +column T17 format a3 heading "17" JUSTIFY CENTER
 +column T18 format a3 heading "18" JUSTIFY CENTER
 +column T19 format a3 heading "19" JUSTIFY CENTER
 +column T20 format a3 heading "20" JUSTIFY CENTER
 +column T21 format a3 heading "21" JUSTIFY CENTER
 +column T22 format a3 heading "22" JUSTIFY CENTER
 +column T23 format a3 heading "23" JUSTIFY CENTER
 +column T24 format a3 heading "24" JUSTIFY CENTER
 +column slday format a5 heading "Day"  JUSTIFY LEFT
 +
 +select to_char(to_date(to_char(slday),'yyyymmdd'),'DD.MM') as slday
 + ,  decode(nvl(T01,0),0,'-',to_char(T01))  T01
 + ,  decode(nvl(T02,0),0,'-',to_char(T02))  T02
 + ,  decode(nvl(T03,0),0,'-',to_char(T03))  T03
 + ,  decode(nvl(T04,0),0,'-',to_char(T04))  T04
 + ,  decode(nvl(T05,0),0,'-',to_char(T05))  T05
 + ,  decode(nvl(T06,0),0,'-',to_char(T06))  T06
 + ,  decode(nvl(T07,0),0,'-',to_char(T07))  T07
 + ,  decode(nvl(T08,0),0,'-',to_char(T08))  T08
 + ,  decode(nvl(T09,0),0,'-',to_char(T09))  T09
 + ,  decode(nvl(T10,0),0,'-',to_char(T10))  T10
 + ,  decode(nvl(T11,0),0,'-',to_char(T11))  T11
 + ,  decode(nvl(T12,0),0,'-',to_char(T12))  T12
 + ,  decode(nvl(T13,0),0,'-',to_char(T13))  T13
 + ,  decode(nvl(T14,0),0,'-',to_char(T14))  T14
 + ,  decode(nvl(T15,0),0,'-',to_char(T15))  T15
 + ,  decode(nvl(T16,0),0,'-',to_char(T16))  T16
 + ,  decode(nvl(T17,0),0,'-',to_char(T17))  T17
 + ,  decode(nvl(T18,0),0,'-',to_char(T18))  T18
 + ,  decode(nvl(T19,0),0,'-',to_char(T19))  T19
 + ,  decode(nvl(T20,0),0,'-',to_char(T20))  T20
 + ,  decode(nvl(T21,0),0,'-',to_char(T21))  T21
 + ,  decode(nvl(T22,0),0,'-',to_char(T22))  T22
 + ,  decode(nvl(T23,0),0,'-',to_char(T23))  T23
 + ,  decode(nvl(T24,0),0,'-',to_char(T24))  T24
 +from (
 +select sum(  decode(  nvl(to_char(lh.FIRST_TIME,'yyyymmddhh24'),0) 
 + ,0,0
 +     ,1)
 + ) as slog
 + , dr.dr as slday 
 + , dr.dh as slhour
 +   --, to_char(lh.FIRST_TIME,'yyyymmddhh24'     
 + from 
 +      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,'09')) as hr from all_objects where rownum < 25) th
 +          , (select ltrim(to_char(sysdate-(rownum-1),'yyyymmdd')) as dr from all_objects where rownum < 20) td
 + )  dr
 +where   dr.dg = to_char(lh.FIRST_TIME (+),'yyyymmddhh24'
 +group by  to_char(lh.FIRST_TIME,'yyyymmddhh24')  
 + ,  dr.dg ,dr.dh,dr.dr
 +)
 +pivot ( 
 +     sum (slog)
 +        FOR slhour
 +        IN  ('01'  AS T01
 +            ,'02'  AS T02
 +            ,'03'  AS T03
 + ,'04'  AS T04
 + ,'05'  AS T05
 + ,'06'  AS T06
 + ,'07'  AS T07
 + ,'08'  AS T08
 + ,'09'  AS T09
 + ,'10'  AS T10
 + ,'11'  AS T11
 + ,'12'  AS T12
 + ,'13'  AS T13
 + ,'14'  AS T14
 + ,'15'  AS T15
 + ,'16'  AS T16
 + ,'17'  AS T17
 + ,'18'  AS T18
 + ,'19'  AS T19
 + ,'20'  AS T20
 + ,'21'  AS T21
 + ,'22'  AS T22
 + ,'23'  AS T23
 + ,'24'  AS T24
 +            )
 +)
 +/
 +</code>
 +Komplettes Beispiel siehe hier [[https://orapowershell.codeplex.com/SourceControl/latest#sql/redo.sql|redo.sql]]
 +
 +Ergebniss:
 +{{:prog:redo_log_switch_diagramm.png?500|Redo Log Switch Diagramm - SQL Script}}
 +
 +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,rabat_row number);
 +
 +
 +insert into T_RABAT values (10 ,2200 ,115.3 ,513.83136 ,-3.68 ,1 );
 +insert into T_RABAT values (10 ,2200 ,115.3 ,513.83136 ,-20   ,2 );
 +insert into T_RABAT values (10 ,2200 ,115.3 ,513.83136 ,-5    ,3 );
 +insert into T_RABAT values (10 ,2200 ,115.3 ,513.83136 ,-10   ,4 );
 +insert into T_RABAT values (10 ,3100 ,13.16 ,513.83136 ,-5    ,1 );
 +insert into T_RABAT values (10 ,3100 ,13.16 ,513.83136 ,-3.68 ,2 );
 +insert into T_RABAT values (10 ,3100 ,13.16 ,513.83136 ,-10   ,3 );
 +insert into T_RABAT values (10 ,3100 ,13.16 ,513.83136 ,-20   ,4 );
 +commit;
 +
 +
 +
 +select nr
 + , beitrag
 + ,(Beitrag -((Beitrag / 100 )*nvl( abs(RABATT1),0))) as beitrag1
 + , RABATT2 
 + , RABATT3
 + , RABATT4
 + , RABATT5
 +as gesamt_preis
 +from 
 +
 +select  NR
 +       , 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  ('1' AS RABATT1
 +            ,'2' AS RABATT2
 +            ,'3' AS RABATT3
 + ,'4' AS RABATT4
 + ,'5' as RABATT5
 +            )
 +)
 +)
 +
 +
 +-----------------------
 +select nr
 + , beitrag
 + , beitrag1
 + , beitrag2 
 + , beitrag3 
 + , beitrag4 
 + , (beitrag4 -((beitrag4 / 100 )*nvl( abs(RABATT5),0))) as gesamtpreis
 +from 
 +(
 +select nr
 + , beitrag
 + , beitrag1
 + , beitrag2 
 + , beitrag3 
 + , (beitrag3 -((beitrag3 / 100 )*nvl( abs(RABATT4),0))) as beitrag4 
 + , RABATT5
 +from 
 +(
 +select nr
 + , beitrag
 + , beitrag1
 + , beitrag2 
 + , (beitrag2 -((beitrag2 / 100 )*nvl( abs(RABATT3),0))) as beitrag3 
 + , RABATT4
 + , RABATT5
 +from 
 +(
 +select nr
 + , beitrag
 + , beitrag1
 + , (beitrag1 -((beitrag1 / 100 )*nvl( abs(RABATT2),0))) as beitrag2 
 + , RABATT3
 + , RABATT4
 + , RABATT5
 +from 
 +(
 +select nr
 + , beitrag
 + ,(Beitrag -((Beitrag / 100 )*nvl( abs(RABATT1),0))) as beitrag1
 + , RABATT2 
 + , RABATT3
 + , RABATT4
 + , RABATT5
 +from 
 +
 +select  NR
 +       , 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  ('1' AS RABATT1
 + ,'2' AS RABATT2
 + ,'3' AS RABATT3
 + ,'4' AS RABATT4
 + ,'5' as RABATT5
 + )
 + )
 +)
 +)
 +)
 +)
 +)
 +;
 +
 +
 +-- Ergebnis
 +
 +
 +          NR      BEITRAG     BEITRAG1     BEITRAG2     BEITRAG3     BEITRAG4  GESAMTPREIS
 +------------ ------------ ------------ ------------ ------------ ------------ ------------
 +          10        115.3    111.05696    88.845568   84.4032896  75.96296064  75.96296064
 +          10        13.16       12.502   12.0419264  10.83773376  8.670187008  8.670187008
 +          
 +          
 +</code>
 +
 +
 +====  Quellen ====
 +
 +  * http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html
 +  * http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#CHDCEJJE
"Autor: Gunther Pipperr"
prog/sql_oracle_pivot.txt · Zuletzt geändert: 2019/07/29 22:39 von gpipperr