Am 14. September 2017 haben wir eine überarbeitete Fassung unserer Datenschutzrichtlinie veröffentlicht. Wenn Sie video2brain.com weiterhin nutzen, erklären Sie sich mit diesem überarbeiteten Dokument einverstanden. Bitte lesen Sie es deshalb sorgfältig durch.

SQL Grundkurs 1: Die Sprache erlernen

ROLLUP und CUBE

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Zwischen- oder Endsummen gefällig? Diese lassen sich in verschiedenen Varianten mit ROLLUP und CUBE realisieren. Dieser Film zeigt Ihnen, wie Sie dies mit dem MS SQL Server und ORACLE in die Tat umsetzen.

Transkript

In diesem Video möchte ich Ihnen zeigen, wie Sie bei einer Gruppierung zusätzlich noch Zwischenwerte generieren können. In SQL gibt es dafür die zwei Anweisungen, 'ROLLUP' und 'CUBE'. Die Beispiele, die ich Ihnen nun zeige, funktionieren in dieser Syntax beim Microsoft SQL Server und bei Oracle. MySQL geht hier etwas andere Wege, die werden in einem gesonderten Video besprochen. Mit 'ROLLUP' sind Sie in der Lage, für eine Anweisung Zwischen- und Gesamtsummenwerte zu generieren. Betrachten wir uns die hier vorbereitete SQL-Anweisung. Ich habe hier einfach meine Artikel genommen, habe nach der Artikelgruppe gruppiert und die Anzahl der Artikel pro Artikelgruppe eruiert. Das ist das Ergebnis. Wenn ich nun die Gesamtanzahl zusätzlich noch bekommen möchte, kann ich jetzt hier, in der 'GROUP BY' Klausel, zusätzlich noch ein 'ROLLUP' ergänzen. ( Tippgeräusche) In Klammer wird dann hier die gruppierte Spalte eingetragen, und das bedeutet, dass für diese Spalte zusätzlich hier, in dem Fall die Artikelgruppe, zusätzlich hier eine Gesamtsumme gebildet wird. Wenn ich jetzt diese Anwendung ausführe, bekomme ich hier am Ende noch eine Zeile dazu. In der Spaltengruppe habe ich keinen Eintrag hier stehen, aber dieser Wert entspricht jetzt hier dieser Gesamtanzahl. Wenn wir so wie hier, nur nach einem Kriterium gruppieren also in dem Fall nur nach der Gruppe entspricht 'ROLLUP' quasi immer irgendeiner Gesamtanzahl, Gesamtsumme. Zwischenwerte bekommen wir erst in späteren Beispielen, wenn wir nach mehr als einer Spalte gruppieren werden. Ich verwende für das nächste Beispiel eine Anweisung, die wir bereits in einem anderen Video erzeugt haben. Wir haben mit dieser einfachen Form der Kreuztabelle eruiert, in welcher Abteilung es wie viele Damen und wie viele Herren gibt, sowie auch die Gesamtanzahl der Damen. Wir haben hier eine Case-Anweisung verwendet, um hier zu unterscheiden, zwischen Damen und Herren und diese Spalten zu generieren, und haben die Gesamtanzahl gebildet. Was wir mit 'ROLLUP' nun ergänzen können, ist hier am Ende noch eine weitere Zeile in der wir hier sehen, wie viele Damen gibt es gesamt, wie viele Herren gibt es gesamt und wie viele Mitarbeiter gibt es gesamt, sowohl Damen als auch Herren. Dazu muss ich ähnlich wie beim ersten Beispiel hier in der 'GROUP BY' Klausel 'ROLLUP' ergänzen. Ich führe die Anweisung aus und bekomme jetzt hier am Ende eine neue Zeile dazu und hier sehen wir, es gibt gesamt 8 Damen, es gibt gesamt 13 Herren, und gemeinsam sind das 21 Mitarbeiter. Unter Umständen möchten Sie dieses Ergebnis noch mit einer Sortierung ergänzen. Wenn Sie jetzt also hier eine Sortierung ergänzen, z.B. "ORDER BY Gesamt Descending" absteigend, um z.B. jene Abteilungen an den Anfang der Auflistung zu haben, die die meisten Mitarbeiter haben. Dann bewirkt das, in dem Fall natürlich, dass diese Summenzeile an eine andere Position verschoben wird. Wenn wir das jetzt ausführen, dann haben wir in dem Fall die Summenzeile am Beginn, dort wo sie eigentlich nicht hin soll. Ähnliches passiert, wenn wir z.B. aufsteigend nach der Abteilung sortieren. Da der Microsoft SQL Server NULL-Werte bei Sortierungen und hier haben wir ja einen NULL-Wert in diesem Feld, NULL-Werte immer an den Beginn der Auflistung stellt. Wie können Sie jetzt dazu jetzt vorgehen? Dafür gibt es eine elegante Lösung. Und zwar gibt es in SQL die Funktion 'GROUPING', die Sie in diesem Zusammenhang einsetzen können. In der Funktion 'GROUPING' geben Sie die Spalte nach der Sie, in dem Fall gruppiert und mit 'ROLLUP' zusammengefasst haben, an. Diese Funktion liefert einen Buhl'schen Wert, also wahr oder falsch, 0 oder 1, ob diese Zeile, die wir nun sehen, eine gruppierte ist oder nicht. Sehen wir uns das einmal an. Ich führe diese Anweisung aus und wir sehen jetzt hier, da es sich bei dieser Zeile um eine durch 'ROLLUP' generierte Zeile handelt, habe ich jetzt hier einen Einser stehen, Anführungszeichen normalen Zeilen, finden wir hier eine Null. Und genau diese Information kann ich jetzt in meine Sortierung mit einbringen. D.h. ich kann jetzt hier 'GROUPING A.Text' als erstes Sortierkriterium verwenden und bewirke damit, dass zuerst die Nuller kommen und danach der Einser. Und erst innerhalb der gleichen Werte, d.h. innerhalb dieser Nuller, wird dann aufsteigend hier sortiert. Sehen wir uns das Ergebnis an: Und damit haben wir wieder eine Sortierung so wie wir sie gerne haben. Wir haben hier definiert, wie wir sortieren, genauso könnte ich jetzt hier innerhalb der Abteilungen wieder sortieren nach "Gesamt Absteigend", und wir bekommen hier dennoch immer diese Summenzeile am Ende. Natürlich können wir jetzt letztendlich diese zusätzliche Spalte hier auch ausblenden, die müssen wir nicht mit anzeigen, aber natürlich verwenden wir sie hier beim Sortieren. Achtung, wenn ich das mit einem Kommentar erledige, muss ich natürlich dieses Komma hier auch noch auskommentieren, und jetzt haben wir ein schönes, sauberes Ergebnis. Spannend wird es, wenn man nach mehreren Spalten gruppiert und dann eben auch 'ROLLUP' verwendet. Dazu möchte ich folgendes Beispiel verwenden: Und zwar den Warenwert je Artikelgruppe. Dazu benötigen wir eine Anweisung, die ich hier schon vorbereitet habe und Ihnen kurz erläutern möchte. Für dieses Beispiel werden diese vier Tabellen miteinander "gejoint", für den Warenwert je Artikelgruppe. Damit wir je Artikelgruppe und Lager einen Warenwert errechnen können, brauchen wir mal die Artikelgruppe, weil ich möchte ja hier den langen Text verwenden. Und natürlich die Lagertabelle, weil wir hier die Bezeichnung des Lagers an der die Artikel gelagert sind, angezeigt bekommen. Über den Lagerstand erfahren wir die Menge für diesen Artikel und um auf den Warenwert zu kommen, verwenden wir den Einkaufspreis, also den Wiederbeschaffungspreis. Diese vier Tabellen habe ich hier schon miteinander "gejoint" und habe über die 'WER'-Klausel das Lager mit der Nummer 5 ausgeschlossen, dabei handelt es sich nämlich um das Kaputt-Teile-Lager, das möchte ich hier nicht mit berücksichtigen. Zusätzlich habe ich hier schon den Wert aus der Summe von Menge und Einkaufspreis errechnet und die Bezeichnung des Lagers, den Namen aus der Lagertabelle mit Aliasnamen versehen und die Bezeichnung der Artikelgruppe angeführt. Das liefert uns folgendes Zwischenergebnis: Ich habe also hier das Lager, ich habe die Artikelgruppe und den Wert. Um jetzt hier auf den Warenwert die Artikelgruppe und Lager ergänze ich hier noch den Text zu eruieren, brauche ich hier natürlich eine Gruppierung, die ich gleich ergänzen werde, indem ich hier schreibe: "GROUP BY L.NAME" Lagername und bei "G.ARTIKELGRUPPEN-TEXT". Hiervon, von der Menge mal dem Einkaufspreis bilde ich die Summe, um hier auf den Gesamtwert zu kommen. D.h., das was wir hier jetzt sehen, ist unsere Ausgangsbasis im zweiten Schritt. Wir haben jetzt hier eine Gruppierung einerseits auf Lagername und Gruppentext, d.h. pro Lager und Artikelgruppe haben wir eine Zeile mit einem Wert. Sehen wir uns nun an, was passiert, wenn ich hier ROLLUP zum Einsatz bringe. Ich ergänze jetzt hier 'ROLLUP' und nehme beide Spalten hier mit hinein in diesen Ausdruck. Momentan sehen wir, wir haben 12 Zeilen, d.h. 12 Kombinationen von Artikelgruppen und Lagerbezeichnungen treten auf. Ich führe diese Anweisung auf, und jetzt bekommen wir fünf weitere Zeilen dazu, das sind jetzt die Zwischenwerte. Und jetzt, dadurch, dass wir nach zwei Spalten gruppiert haben, bekommen wir jetzt nicht nur einen Endwert ganz am Ende, wie wir ihn bisher schon hatten, beim vorigen Beispiel, sondern wir haben jetzt auch Zwischenwerte. Und zwar Zwischenwerte immer dann pro Geschäftslager, weil wir das hier als erstes angegeben haben. Wir sehen jetzt also, dass wir im Geschäftslager für die Artikelgruppe "Haushalt" einen Wert haben, im Geschäftslager für die Artikelgruppe "Küchengeschirr", und wir bekommen dann hier einen Gesamtwert für dieses eine Lager. Und das Ganze wiederholt sich für das nachfolgende Lager, wieder für jede Artikelgruppe einen Wert und dann wieder einen Zwischengesamtwert für diese Gruppe. D.h. diese Zeilen werden durch 'ROLLUP' zusätzlich hier ergänzt. Wir haben dann ganz am Ende einen Gesamtwert, der hier angezeigt wird. Sie können jetzt auch hier die Reihenfolge vertauschen. D.h. wir könnten z.B. hergehen, ich kopiere mir die Anweisung dazu, und gebe jetzt hier zuerst den Artikelgruppentext an und danach hier, diesmal hat das auch eine Auswirkung in welcher Reihenfolge wir das in der 'GROUP BY'-Klausel anführen, und danach führe ich eben den Namen des Lagers an. Das bedeutet jetzt, dass die Zusammenfassung etwas anders ausfallen wird. Wir haben jetzt auch mehr Zwischenwerte, das ergibt sich hier so, weil wir jetzt zuerst die Artikelgruppe haben und danach, innerhalb der Artikelgruppe, nach Lager, d.h. wir haben das umgedreht. Zuerst Artikelgruppe und innerhalb der Gruppe nach Lager. Genau umgekehrt als zuerst. Das wirkt jetzt hier, vom Ergebnis, etwas unübersichtlich, weil wir jetzt hier diese NULL-Werte in der ersten Spalte hier haben. Meistens empfiehlt es sich in so einem Fall, dann auch hier in der SELECT-Klausel die Reihenfolge anzupassen, um ein saubereres Bild in der Darstellung zu bekommen. Ich tausche das jetzt aus und wiederhole diese Anweisung. Und jetzt haben wir Artikelgruppe in dem Fall haben wir nur einen Wert für die Artikelgruppe "Garten" im Hauptlager, deshalb haben wir hier nur eine Zeile, aber z.B. für die Artikelgruppe "Haushalt" haben wir hier mehrere Zeilen wo wir jetzt in unterschiedlichen Lagern einen Wert haben und wieder eine Zwischensumme. Und ganz am Ende wieder den Gesamtwert. Beim Einsatz von Sortierungen sollten Sie aber aufpassen. Da das hier alles einen Einfluss hat auf die Reihenfolge der Zeilen, und damit können auch die Zwischenwerte unter Umständen an falsche Positionen springen. Am Besten, Sie verwenden auch hier wieder die 'GROUPING'-Funktion für die Sortierung und testen hier ein wenig aus, die Varianten, bis Sie zu einer Reihenfolge kommen, die Ihnen besser gefällt, falls Ihnen diese standardmäßig generierte Reihenfolge nicht zusagt. Neben 'ROLLUP' gibt es noch die weitere Option, 'CUBE', die verwendet werden kann. Was ist der Unterschied zwischen 'CUBE' und 'ROLLUP'? Während 'ROLLUP' einen Zwischenwert auf einer Ebene macht, auf der definierten, d.h. der gesamte Wert für die Kombination dieser beiden, liefert uns 'CUBE' alle möglichen Varianten. Ich tausche jetzt hier einfach mal 'ROLLUP' durch 'CUBE' aus und wir betrachten uns das Ergebnis, dann sehen wir gleich was hier den Unterschied ausmacht. Auf den ersten Blick sieht das Ergebnis gleich aus. Nur, es werden Zwischensummen auf mehreren Ebenen oder auf allen kombinierten Ebenen generiert, d.h. wir haben hier sehr wohl zuerst, wie wir es bei ROLLUP auch hatten, zuerst die Artikelgruppe und dann das Lager, aber wir sehen, dass wir hier mehr Zeilen haben. Und wenn ich weiter hinunter scrolle, werden Sie auch sehen, warum: Weil wir hier noch zusätzliche Zeilen haben, nämlich wir haben jetzt hier auch Summenzeilen für die einzelnen Artikelgruppen alleine. Das bedeutet, dass wir eigentlich für alle Varianten eine Summe haben. Einerseits für jede Kombination einer Artikelgruppe mit einem Lager, das ist die Ausgangsvariante, die wir sowieso hatten. Dann haben wir einen Zwischenwert für jedes Lager gesamt, pro Lager. Und wir haben dann einen Zwischenwert für jede Artikelgruppe, separat. Und wir haben natürlich hier eine Gesamtsumme, sowohl für alle Artikelgruppen als auch für alle Lager gemeinsam. D.h. alle möglichen Kombinationen werden gebildet. Wenn Sie jetzt natürlich hier in der Gruppierung mehr als zwei Spalten verwenden, dann wird natürlich die Anzahl der möglichen Kombinationen und der sich daraus ergebenden Gruppen und Zwischenwerte wesentlich höher. Sie haben in diesem Video gesehen, wie Sie 'ROLLUP' und 'CUBE' dazu verwenden können, beim Microsoft SQL Server und bei Oracle Zwischensummen und Gesamtsummen bei einer Gruppierung zu erzeugen. Dazu verwenden Sie 'ROLLUP' und 'CUBE' als Ergänzung in der 'GROUP BY'-Klausel, um ein Ergebnis als Zwischensumme oder Endsumme zu bekommen. Damit Sie auf Zwischensummen kommen, müssen Sie mindestens nach zwei Spalten hier gruppieren. Um die Sortierung in der gewünschten Reihenfolge zu halten, müssen Sie unter Umständen noch die Funktion 'GROUPING' ergänzen, die Ihnen immer eine Information darüber gibt, ob es sich bei einer Zeile um eine Basiszeile, sozusagen eine normale Zeile handelt, oder um eine Zeile, die aufgrund von 'ROLLUP' oder 'CUBE' generiert worden ist. Durch das Vertauschen der Reihenfolgen der einzelnen gruppierten Spalten können Sie unterschiedliche Ergebnisse erzeugen. Im Bedarfsfall macht es Sinn, sich hier ruhig ein wenig zu spielen, um sich mit unterschiedlichen Varianten vertraut zu machen.

SQL Grundkurs 1: Die Sprache erlernen

Arbeiten Sie sich in die Grundlagen der Datenbanksprache SQL am Beispiel von Microsoft SQL Server, Oracle und MySQL ein und lassen Sie sich die praktische Nutzung erklären.

14 Std. 40 min (112 Videos)
Derzeit sind keine Feedbacks vorhanden...
 

Dieser Online-Kurs ist als Download und als Streaming-Video verfügbar. Die gute Nachricht: Sie müssen sich nicht entscheiden - sobald Sie das Training erwerben, erhalten Sie Zugang zu beiden Optionen!

Der Download ermöglicht Ihnen die Offline-Nutzung des Trainings und bietet die Vorteile einer benutzerfreundlichen Abspielumgebung. Wenn Sie an verschiedenen Computern arbeiten, oder nicht den ganzen Kurs auf einmal herunterladen möchten, loggen Sie sich auf dieser Seite ein, um alle Videos des Trainings als Streaming-Video anzusehen.

Wir hoffen, dass Sie viel Freude und Erfolg mit diesem Video-Training haben werden. Falls Sie irgendwelche Fragen haben, zögern Sie nicht uns zu kontaktieren!