/* =============================================================================
--
-- Teil 4: Demostatements
--
-- Wichtig: gewuenschten Parallelitaetsgrad setzen!
--
-- =============================================================================
*/
SPOOL run_demo_stmts.log
SET linesize 200
SET pagesize 100
SET trimout ON
SET trimspool ON
SET serveroutput ON
SET echo ON
SET feedback ON
SET TIMING ON;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;
 
/* -----------------------------------------------------------------------------
-- 1. Anzahl Saetze (Durchsatz pruefen)
-- -----------------------------------------------------------------------------
*/
SELECT count(*) FROM umsaetze;

/* -----------------------------------------------------------------------------
-- 2. Umsatz je Produktsparte und Jahr
-- -----------------------------------------------------------------------------
*/
SELECT p.produktsparte
,      t.jahr_nummer
,      o.land
,      sum(u.summe)
,      sum(u.menge) 
FROM umsaetze u
,    orte     o
,    tage     t
,    produkte p
WHERE u.datum     = t.datum 
AND   u.ortnr     = o.ortnr
AND   u.produktnr = p.produktnr
GROUP BY p.produktsparte
,        t.jahr_nummer
,        o.land;

/* -----------------------------------------------------------------------------
-- 3. Top 10 Produkte
-- -----------------------------------------------------------------------------
*/
SELECT * 
FROM ( SELECT produkt
       , SUM(u.summe)  AS wert
       , RANK() OVER  (ORDER BY SUM(u.summe) DESC) AS rangfolge 
       FROM  umsaetze u
       ,     produkte p 
       WHERE u.produktnr = p.produktnr 
       GROUP BY p.produkt
     ) 
WHERE ROWNUM < 11; 

/* -----------------------------------------------------------------------------
-- 4. Top 3 Produkte je Produktgruppe
-- -----------------------------------------------------------------------------
*/
SELECT  produkt
,       produktgruppe
,       wert
,       rangfolge
FROM ( SELECT  produkt
       ,       produktgruppe
       ,       sum(u.summe)  AS wert
       ,       RANK() OVER (PARTITION BY p.produktgruppe ORDER BY SUM(u.summe) DESC) AS rangfolge
       FROM  umsaetze u
       ,     produkte p 
       WHERE u.produktnr = p.produktnr  
       GROUP BY p.produktgruppe
       ,        p.produkt 
       ORDER BY p.produktgruppe
     )
WHERE rangfolge < 4;

/* -----------------------------------------------------------------------------
-- 5. Land mit dem staerksten Umsatz je Produktgruppe
-- -----------------------------------------------------------------------------
*/
SELECT produktgruppe 
,      land 
,      umsatz 
FROM ( SELECT produktgruppe
       ,      land
       ,      SUM(summe) AS umsatz
       ,      MAX(SUM(summe)) OVER (PARTITION BY produktgruppe) AS max_ums_land 
       FROM  orte     r
       ,     produkte p
       ,     umsaetze u 
       WHERE r.ortnr     = u.ortnr 
       AND   p.produktnr = u.produktnr 
       GROUP BY produktgruppe
       ,        land 
       ORDER BY produktgruppe 
       ,        land
     )
WHERE umsatz = max_ums_land;

/* -----------------------------------------------------------------------------
-- 6. die umsatzstaerksten Produkte innerhalb einer Produktgruppe sortiert nach Rangfolge
-- -----------------------------------------------------------------------------
*/
SELECT produkt
,      produktgruppe
,      wert
,      rangfolge
FROM ( SELECT produkt
       ,      produktgruppe
       ,      SUM(u.summe)  AS wert
       ,      RANK() OVER (PARTITION BY p.produktgruppe ORDER BY SUM(u.summe) DESC) AS rangfolge
       FROM  umsaetze u
       ,     produkte p
       WHERE u.produktnr = p.produktnr 
       GROUP BY p.produktgruppe
       ,        p.produkt
       ORDER BY p.produktgruppe
     )
WHERE rangfolge < 4;

/* -----------------------------------------------------------------------------
-- 7. Anwendung CUBE  
-- -----------------------------------------------------------------------------
*/
SELECT p.produktgruppe
,      region
,      land
,      SUM(u.summe)
,      GROUPING(produktgruppe) AS ag
,      GROUPING(region)        AS re
FROM  produkte p
,     umsaetze u
,     orte     r
WHERE u.produktnr = p.produktnr
AND   r.ortnr     = u.ortnr
AND   land = 'Schleswig Holstein'
GROUP BY CUBE (produktgruppe, region, land);

/* -----------------------------------------------------------------------------
-- 8. ueber Quartale kumulierte Umsaetze pro Kunde
-- -----------------------------------------------------------------------------
*/
SELECT k.kundenname
,      z.jahr_nummer
,      z.quartals_nummer
,      SUM(u.summe) AS umsatz
,      SUM(SUM(u.summe)) OVER (PARTITION BY k.kundenname ORDER BY k.kundenname
,      z.jahr_nummer
,      z.quartals_nummer ROWS UNBOUNDED PRECEDING) AS umsatz_summe
FROM  kunden   k
,     umsaetze u
,     tage     z
WHERE k.kundennr       = u.kundennr 
AND   TO_CHAR(z.datum) = TO_CHAR(u.datum)
GROUP BY k.kundenname
,        z.jahr_nummer
,        z.quartals_nummer;

/* -----------------------------------------------------------------------------
-- 9. 1/4 der Kunden tragen zu ? % des Umsatzes bei?
-- -----------------------------------------------------------------------------
*/
SELECT SUM(umsatz)
,      anteil
,     (SUM(umsatz)*100/gesamt_umsatz) AS prozent 
FROM ( SELECT k.kundenname AS kunde
       ,      SUM(u.summe) AS umsatz
       ,      NTILE(4) OVER (ORDER BY  SUM(u.summe)) AS anteil
       FROM  kunden   k
       ,     umsaetze u
       WHERE k.kundennr = u.kundennr
       GROUP BY k.kundenname
     )
,    ( SELECT SUM(u.summe) AS gesamt_umsatz 
       FROM umsaetze u
     )
GROUP BY anteil
,        gesamt_umsatz;

/* -----------------------------------------------------------------------------
-- 10. Durchschnittliche Bestellquote eines Kunden ueber 3 Monate?                     
-- -----------------------------------------------------------------------------
*/
SELECT kundenname
,      z.jahr_nummer AS jahr
,      monats_nummer AS mon
,      SUM(u.summe)  AS umsatz
,      AVG(SUM(u.summe)) OVER (ORDER BY k.kundenname, z.jahr_nummer, z.jahr_nummer ROWS 2 PRECEDING) AS mov_3m_avg
FROM  kunden   k
,     umsaetze u
,     tage     z
WHERE k.kundennr       = u.kundennr 
AND   TO_CHAR(z.datum) = TO_CHAR(u.datum) 
AND   k.kundenname     = 'Bauer'
GROUP BY k.kundenname
,        z.jahr_nummer
,        z.monats_nummer
ORDER BY z.jahr_nummer
,        z.monats_nummer;

/* -----------------------------------------------------------------------------
-- 11. Vergleiche Umsaetze mit Vorjahreszeitraum
-- -----------------------------------------------------------------------------
*/
SELECT k.kundenname 
,      z.jahr_nummer   AS jahr
,      z.monats_nummer AS mon
,      SUM(u.summe)    AS umsatz
,      LAG(SUM(u.summe),12) OVER (ORDER BY z.jahr_nummer, z.monats_nummer) AS vorjahr 
FROM  kunden   k
,     umsaetze u
,     tage     z
WHERE k.kundennr       = u.kundennr 
AND   TO_CHAR(z.datum) = TO_CHAR(u.datum) 
AND   k.kundenname     = 'Bauer'
GROUP BY k.kundenname
,        z.jahr_nummer
,        z.monats_nummer
ORDER BY z.jahr_nummer
,        z.monats_nummer;

SPOOL OFF;

