Hochschule Darmstadt Fachbereich Informatik Data Warehouse SS 2015 Praktikumsversuch 3 Prof. Dr. S. Karczewski Dipl. Inf. Dipl. Math. Y. Orkunoglu Datum: 21.05.2015 Aufgabenstellung 1. Kurzbeschreibung Dieses Praktikum besteht aus 2 Teilen: I. Vier Tabellen werden angelegt. Die Tabellen werden mit Daten gefüllt. Die Daten werden bereitgestellt. II. Die Daten werden mit SQL-Befehlen abgefragt, wobei die OLAP Funktionen von SQL geübt werden. Zum Einsatz kommt hierbei das Werkzeug: SQL Developer (Oracle-Client) 2. Lernziele Die Studierenden sollen in die Lage versetzt werden: Daten aus DB-Tabelllen abzufragen (Mit SQL-Befehle) OLAP-Funktionen GROUP BY ROLLUP; GROUP BY CUBE; GROUPING SETS (..) OVER (..) OVER (PARTITION BY …) 3. Voraussetzung Es wäre sehr angebracht, wenn Sie sich mit den folgenden OLAP-Befehlen unter SQL auseinandersetzen würden. GROUPING SETS (..) OVER (..) OVER (PARTITION BY …) RANK () PRECEDING (), FOLLOWINGS () 4. Abnahme Die Abnahme findet am Ende der Praktikumssitzung statt. 1 SQL Developer starten Starten Sie die Software SQL DEVELOPER aus dem Verzeichnis: C:\SQLDEVELOPER 4.0.3\ C:\SQLDEVELOPER.exe TEIL I Tabellen anlegen Die Tabellen sollen angelegt werden. Die Skript-Datei (TabellenAnlegen.sql) ist auszuführen. (Sie befindet sich auf der Homepage von Herrn Y. Orkunoglu) Tabellen mit Daten füllen Die Daten in der Datei insertdaten.sql müssen in die erstellten Tabellen geladen werden. (Sie befindet sich auf der Homepage von Herrn Y. Orkunoglu) TEIL II A. Der group by cube Operator Auch der cube Operator ist eine Kurzform für Anfragemuster zur Berechnung von Teil und Gesamtsummen. Allerdings verhält er sich anders als der rollup Operator. Führen Sie folgendes SQL-Statement aus: SELECT Produktreihe, Absatzgebiet, sum(Umsatz) FROM Produkt P, Vertrieb V, Umsatz U WHERE P.Produktnummer = U.Produktnummer AND V.NR_Vertriebsbeauftragten = U.NR_Vertriebsbeauftragten GROUP BY CUBE (Produktreihe, Absatzgebiet) ORDER BY Produktreihe, Absatzgebiet; Die Ausgaberelation sollte wie folgt aussehen: PRODUKTREIHE Accessoires Accessoires Accessoires Accessoires Bergsteigerausrüstung Bergsteigerausrüstung Bergsteigerausrüstung Campingausrüstung Campingausrüstung Campingausrüstung (null) (null) (null) (null) ABSATZGEBIET Asiatisch-pazifischer Raum Mitteleuropa Nord-/Lateinamerika (null) Mitteleuropa Nord-/Lateinamerika (null) Mitteleuropa Nord-/Lateinamerika (null) Asiatisch-pazifischer Raum Mitteleuropa Nord-/Lateinamerika (null) SUM(UMSATZ) 124 522 544 1190 409 665 1074 1222 3433 4655 124 2153 4642 6919 Im obenstehde SQL-Code erstetzen CUBE mit ROLLUP. Dann vergleichen Sie die Ergebnisse. Worin liegt mit Ihren eigenen Worten der Unterschied zwischen dem rollup und dem cube Operator? 2 B. Vom Ergebnis zu SQL-Abfrage Folgende Aufgaben sind mit Hilfe von group by rollup, grouping sets, group by cube zu lösen. Versuchen Sie dabei auch, Varianten auszuprobieren. Gegeben seien die folgenden Ausgaben. Formulieren Sie den passenden SQL-Befehl zu 1. und 2. 1. JAHR 1999 1999 1999 1999 1999 1999 1999 1999 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 - PRODUKTREIHE ANBIETERLAND Accessoires Accessoires Accessoires Accessoires Campingausrustung Campingausrustung Campingausrustung Accessoires Accessoires Accessoires Accessoires Bergsteigerausrustung Bergsteigerausrustung Bergsteigerausrustung Campingausrustung Campingausrustung Campingausrustung - China Deutschland USA Deutschland USA China Deutschland USA Deutschland USA Deutschland USA - 2. 3 UMSATZ 36 160 184 380 261 1388 1649 2029 47 210 184 441 272 380 652 416 1291 1707 2800 4829 JAHR 1999 1999 1999 1999 1999 1999 1999 1999 1999 1999 1999 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 - PRODUKTREIHE ANBIETERLAND Accessoires Accessoires Accessoires Accessoires Campingausrustung Campingausrustung Campingausrustung Accessoires Accessoires Accessoires Accessoires Bergsteigerausrustung Bergsteigerausrustung Bergsteigerausrustung Campingausrustung Campingausrustung Campingausrustung Accessoires Accessoires Accessoires Accessoires Bergsteigerausrustung Bergsteigerausrustung Bergsteigerausrustung Campingausrustung Campingausrustung Campingausrustung - China Deutschland USA Deutschland USA China Deutschland USA China Deutschland USA Deutschland USA Deutschland USA China Deutschland USA China Deutschland USA Deutschland USA Deutschland USA China Deutschland USA - UMSATZ 36 160 184 380 261 1388 1649 36 421 1572 2029 47 210 184 441 272 380 652 416 1291 1707 47 898 1855 2800 83 370 368 821 272 380 652 677 2679 3356 83 1319 3427 4829 3. Erläutern Sie anhand der Ergebnisse von 1. und 2., was inhaltlich ausgegeben wird. C. SQL-Abfragen 1. Lassen Sie das Jahr, Jahresumsätze anzeigen (gruppiert nach Jahr) 2. Lassen Sie das Jahr, Monat, Monatsumsätze anzeigen 3. Wie letzte Aufgabe (C.2), aber der Gesamtumsatz pro Jahr soll am Ende des letzten Monat de Jahres gezeigt wird. 4 D- OVER () und OVER (Partition by …) Befehle anwenden Führen Sie die untenstehenden Sql-Befehle aus: 1-) Select produktnummer, bestelldatum, sum(umsatz) as Summe_am_Bestelldatum from umsatz where produktnummer=12 group by (produktnummer, bestelldatum) order by produktnummer, bestelldatum DESC; 2-) Select produktnummer, bestelldatum, sum(umsatz) as Summe_am_Bestelldatum, ??? als 4.Spalte soll “Produktsumme“ angezeigt werden (mit Hilfe von OVER(PARTITION BY…) from umsatz where produktnummer=12 group by (produktnummer, bestelldatum) order by produktnummer, bestelldatum DESC; Versuchen Sie es, nachzuvollziehen, was der Befehl ODER (PARTITION BY ) macht. 3-) Lassen Sie Jahr, Monat, Umsatz des Monats und Gesamtumsatz (für alle Jahren) anzeigen. 4-) Lassen Sie Jahr, Monat, Umsatz des Monats und Gesamtumsatz (für jeweiligeJahre) anzeigen 5-) Lassen Sie Jahr, Jahresumsatz, Gesamtumsatz (gruppiert nach Jahre) anzeigen E- Sequenzbasierte Operationen Sie können hierzu in der Vorlesung bzw.im Praktikum vorgestellten Operationenverwenden: OVER(), RANK(), PRECEDING(), FOLLOWING(). Beispiel: SELECT name_vertriebsbeauftragten, sum(UMSATZ) as Umsatz_Vertriebsbeauftragten FROM VERTRIEB V,umsatz U WHERE V.NR_VERTRIEBSBEAUFTRAGTEN=U.NR_VERTRIEBSBEAUFTRAGTEN GROUP BY name_vertriebsbeauftragten order by sum(UMSATZ) desc; HINWEIS: Sie müssen Ihre Ergebnisse mit den Ergebnissen von Orkunoglu vergleichen 1. Sie ermitteln dasselbe Ergebnis (siehe Beispiel), indem Sie den Befehl OVER ( Partition by ---) verwenden. 2. Ermitteln Sie, mit welchem Produkttyp die höchsten Umsätze erzielt wurden. 3. Ermitteln Sie, welches Land die höchsten Umsätze erzielt. 4. Ermitteln Sie, welche Produkte in USA und in Los Angeles vertrieben wurden. 5. Ermitteln Sie die Anteile der Umsätze am Gesamtumsatz je Produktreihe (!). 6. Ermitteln Sie die Anteile der Umsätze am Gesamtumsatz je Monat im Jahr 1999 und 2000. 5 7. Geben Sie die kumulierten Umsätze im Jahr 2000 sortiert nach Monat aus, wobei je Produktreihe kumuliert werden soll. 8. Ranken Sie die Umsätze (je Produktreihe) nach Umsatz und Monat. 9. Ermitteln Sie die 5 stärksten Umsatzmonate. 10. Lassen Sie die Jahresumsätze und und die Summe von 3 Jahren (vor-und nach ) (Rows Between 1 Preceding And 1 Following) 6
© Copyright 2024 ExpyDoc
