SQLShack (Deutsch)

In den vorherigen Artikeln dieser Reihe wurde Wir haben die Grundlagen für die Erstellung eines Berichts gelegt. Wir werden noch einen Schritt weiter gehen und sehen, wie der SQL Server PIVOT-Tabellenoperator verwendet wird. Wir beginnen mit der einfachen Abfrage und gehen langsam zu dynamischem SQL und PIVOT über. Beginnen wir.

Datenmodell und die allgemeine Idee

Das Datenmodell, das wir verwenden, ist das gleiche, das wir in dieser Serie verwendet haben.

Unsere heutige Aufgabe besteht darin, einen Bericht (mit dem SQL Server PIVOT-Operator) zu erstellen, in dem sich jede Stadt in einer separaten Zeile befindet und wo wir die Anzahl aller Anrufergebnisse zählen, die sich auf jede Stadt beziehen. Daher müssen alle Ergebnisse (alle Werte aus dem Wörterbuch) Spalten in unserem Bericht sein.

Da „ein Bild mehr sagt als tausend Worte“, beschreiben wir unser endgültiges Ziel auch mit dem Bild.

Um dies zu erreichen, müssen Daten aus 4 Tabellen verwendet werden: call, call_outcome, customer und city Es wäre gut, wenn Sie sich das folgende Modell genauer ansehen und analysieren, wie diese Tabellen zusammenhängen.

Wir werden zunächst die Daten in den Tabellen untersuchen und mit jeder Abfrage einen Schritt näher heranrücken das gewünschte Ergebnis (Abfrage). Der hier verwendete Ansatz ist der gleiche wie der in Learn SQL verwendete: Erstellen Sie einen Bericht manuell mithilfe des Artikels über SQL-Abfragen.

Berichtskategorien und Daten

Die Der erste Satz von Abfragen, mit dem wir beginnen, ist derjenige, der die Daten untersucht, die derzeit in allen 4 Tabellen vorhanden sind, die wir zum Erstellen des Berichts verwenden müssen. Wir werden dies tun, damit wir bestätigen können, dass der Abschlussbericht das zurückgegeben hat, was er sollte. Danach erstellen wir Abfragen wh Ich gebe Berichtskategorien und Berichtsdaten sowie SQL Server-PIVOT-Tabellenabfragen zurück.

Diese Abfragen sind so einfach wie möglich, daher gibt es keine besonderen Kommentare zu ihrer Syntax. In Bezug auf die zurückgegebenen Daten sollten wir Folgendes beachten:

• Die Stadttabelle enthält 6 Städte, und daher sollten im Abschlussbericht 6 Zeilen enthalten sein.
• Es gibt 3 mögliche Ergebnisse in der Tabelle call_outcome, daher sollten 3 Spalten für die Ergebnisse vorhanden sein (insgesamt 4 Spalten – > eine für den Namen der Stadt)
• Der Tabellenaufruf hat 10 Zeilen (nur 8 auf dem Bild oben), daher sollte in der endgültigen Tabelle die Summe aller Ergebnisse 10 sein (jeder Anruf kann genau 1 Ergebnis haben).

Das nächste, was zu tun ist ist die Erstellung von Berichtskategorien. Wir wollen eine Kombination aller Städte und aller möglichen Anrufergebnisse. Um dies zu erreichen, verwenden wir CROSS JOIN (kartesisches Produkt).

Alle Berichtskategorien garantieren dies Der Bericht enthält eine Zeile, unabhängig davon, ob dieses Paar Daten enthält oder nicht. Und genau das möchten wir – 0 in dieser Berichtskategorie sehen und diese Kategorie nicht ganz verpassen.

Als Nächstes müssen Sie alle 4 Tabellen zusammenfügen, die die benötigten Daten enthalten.

Die Abfrage selbst ist nicht komplex. Im Ergebnis sollten wir feststellen, dass wir 10 Zeilen haben, die gleiche Anzahl wie die Anzahl der Anrufe in der Datenbank. Außerdem habe ich die DATEDIFF-Funktion verwendet, um die Dauer jedes Anrufs in Sekunden zurückzugeben. Ich könnte hier einfach call.id einfügen, aber ich wollte uns an die DATEDIFF-Funktion erinnern, die wir in diesem Artikel erwähnt haben. Wir könnten diese Dauer verwenden, wenn wir eine SUM / AVG von Sekunden benötigen, die für jede Berichtskategorie aufgewendet werden (Stadt & Aufrufergebnis).

Bericht ohne SQL Server PIVOT TABLE

Jetzt können wir Kategorien und Daten verbinden. Wir werden beide zuvor genannten Abfragen als Unterabfragen verwenden und sie mit LEFT JOIN verknüpfen (damit alle Kategorien in der endgültigen Ausgabe vorhanden sind).

Wir haben alle 18 Kombinationen (6 verschiedene Städte * 3 verschiedene Anrufergebnisse) und wir haben auch alle 10 Anrufe hier (Zeilen mit der Anrufdauer < > NULL).

Erstellen wir jetzt die SQL Server-PIVOT-Abfrage.

SQL Server-PIVOT-TABELLE (statisch)

Bisher haben wir es geschafft, die benötigten Daten abzurufen, und wir haben sie als Liste. Wir könnten Daten nach Excel exportieren und dort Transformationen vornehmen. Wir werden dies im kommenden Artikel tatsächlich tun.Noch heute möchten wir dies mit dem SQL Server PIVOT-Operator lösen. In diesem Abschnitt behandeln wir den „statischen“ SQL Server PIVOT. Schauen wir uns zuerst die verwendete Abfrage und das Ergebnis an.

Wir können feststellen, dass das Ergebnis genau das ist, was wir wollten. Wir haben jede Stadt in einer Zeile und alle 3 Kategorien für Anrufergebnisse in separaten Spalten. Wenn für ein bestimmtes Stadt-Ergebnis-Paar keine Daten vorhanden sind, diese Zelle soll den Wert 0 enthalten.

Lassen Sie uns jetzt die SQL Server PIVOT-Abfrage kommentieren. Dies sind einige Dinge, die ich hier als wichtig erachte:

• Im ersten Teil von Bei der Abfrage – report_data haben wir die Abfrage aus dem vorherigen Abschnitt kopiert und eingefügt. Die einzige Änderung bestand darin, dass wir in diesem Teil der Abfrage kein ORDER BY und GROUP BY hatten, da sie hier nicht angewendet werden können (sie sollten nachgehen) Das endgültige Abfrageergebnis wird generiert.)
• Der PIVOT-Teil der Abfrage besteht aus zwei Teilen. Im ersten Teil definieren wir, welche Aggregatfunktion wir anwenden möchten. In unserem Fall ist dies – COUNT ( call_duration). In FOR Teil der qu Wir definieren Spalten. Wir listen literarisch alle Werte auf, die wir als Spalten haben wollen. Dies ist hartcodiert. Wenn wir der Tabelle einen neuen Wert hinzufügen, hat dies keine Auswirkungen auf die Abfrage (und sollte dies auch tun).

Diese Abfrage erledigt ihre Aufgabe und funktioniert einwandfrei, wenn wir sie nie ändern (neue hinzufügen) , löschen, aktualisieren ihre Namen) Anrufergebnisse in der zugehörigen Tabelle. Dies könnte sich als Problem erweisen, wenn wir Änderungen im Wörterbuch vornehmen müssen. Wir möchten nicht darüber nachdenken, ob es eine Abfrage gibt, die nicht wie erwartet funktioniert. Um dies zu lösen, müssen wir ein neues Konzept verwenden – dynamisches SQL.

Dynamische SQL Server-PIVOT-TABELLE

Bevor wir zum Code übergehen, erklären wir kurz, was das dynamische SQL wirklich ist ist (wir werden in den kommenden Artikeln einen viel detaillierteren Überblick darüber geben). Die einfachste Erklärung ist, dass Sie in SQL Ihre Abfragen als Zeichenfolgen „erstellen“ und diese Zeichenfolge dann als Parameter für die gespeicherte SQL Server-Prozedur übergeben können. Diese Prozedur muss die in dieser Zeichenfolge gespeicherten SQL-Anweisungen ausführen (natürlich) , wenn die Syntax in Ordnung ist).

Obwohl dies wie etwas klingt, das nicht so häufig verwendet wird, gibt es einige Stellen, an denen es Ihnen das Leben erheblich erleichtert. Erstellen einer SQL Server-PIVOT-Tabellenabfrage mit einer unbekannten Die Anzahl der Spalten ist genau ein solcher Fall.

Die Idee dieses Ansatzes lautet wie folgt:

• Wir deklarieren eine Variable, in der alle Spaltennamen (@columns) gespeichert werden sollen ) und die Variable, in der die vollständige Abfrage gespeichert werden soll (@query)
• Mit der SELECT-Abfrage finden wir alle in der Spalte result_text gespeicherten Werte. Der folgende Code – @columns + = QUOTENAME (TRIM (co.outcome_text)) + ,, fügt den Spaltennamen an die Liste aller vorherigen Spaltennamen an, die von der Abfrage zurückgegeben wurden. Das Ergebnis ist, dass alle Spaltennamen in th gespeichert sind Die Variable @columns
• In der Variablen @query speichern wir die vollständige Abfrage aus dem vorherigen Abschnitt, mit Ausnahme des Teils, in dem Spalten definiert sind. Wir erhalten diesen Teil von der Variablen @columns
• . Als letztes müssen wir die vollständige Abfrage als Parameter an die SQL Server-Systemprozedur sp_executesql

Das Endergebnis ist das gleiche wie im vorherigen Abschnitt, aber diesmal sind wir sicher, dass unsere Abfrage auch dann funktioniert, wenn wir Änderungen an vornehmen die result.outcome_text Werte. Sie können diese Abfrage auch einfach ändern und andere Werte berechnen, z. B. die SUM / AVG-Anrufdauer pro Städte-Ergebnis-Paar.

Schlussfolgerung

Der SQL Server PIVOT-Operator gibt Ihnen eine völlig neue Ansicht dessen, was Sie direkt auf der Datenbankebene erreichen können. In Kombination mit dem dynamischen SQL geht dies noch weiter. Ich empfehle Ihnen dringend, damit zu spielen – es gibt keinen besseren Weg zu lernen, als es selbst zu versuchen. Im nächsten Artikel werden wir zeigen, wie wir die SQL-Abfrageausgabe verwenden und Tabellen und Grafiken in Excel erstellen können (dies soll nicht nur in SQL Server, sondern allgemein funktionieren).

Inhaltsverzeichnis

SQL lernen: CREATE DATABASE & CREATE TABLE-Operationen

SQL lernen: INSERT IN TABLE

SQL lernen: Primärschlüssel

SQL lernen: Fremdschlüssel

SQL lernen: SELECT-Anweisung

SQL lernen: INNER JOIN vs LEFT JOIN

SQL lernen: SQL-Skripte

SQL lernen: Arten von Beziehungen

SQL lernen: Join Mehrere Tabellen

SQL lernen: Aggregatfunktionen

SQL lernen: Wie schreibe ich eine komplexe SELECT-Abfrage?

SQL lernen: Die INFORMATION_SCHEMA-Datenbank

SQL lernen: SQL-Datentypen

SQL lernen: Theorie festlegen

SQL lernen: Benutzerdefinierte Funktionen

SQL lernen: Benutzerdefinierte gespeicherte Prozeduren

SQL lernen: SQL-Ansichten

SQL lernen: SQL-Trigger

SQL lernen: SQL-Abfragen üben

SQL lernen: SQL-Abfragebeispiele

SQL lernen: Erstellen Sie einen Bericht manuell mithilfe von SQL-Abfragen.

SQL lernen: SQL Server-Datums- und Uhrzeitfunktionen

SQL lernen: SQL Server-Berichte erstellen Verwenden von Datums- und Zeitfunktionen

SQL lernen: SQL Server-Pivot-Tabellen

SQL lernen: SQL Server-Export nach Excel

SQL lernen: Einführung in SQL Server-Schleifen

SQL lernen: SQL Server-Cursor

SQL lernen: SQL Best Practices zum Löschen und Aktualisieren von Daten

SQL lernen: Namenskonventionen

SQL lernen: SQL-bezogene Jobs

SQL lernen: Nicht-Equi-Joins in SQL Server

Lernen Sie SQL: SQL Injection

• Autor
• Letzte Beiträge

Emil ist ein Datenbankprofi mit mehr als 10 Jahren Erfahrung in allem, was mit Datenbanken zu tun hat. Im Laufe der Jahre arbeitete er in der IT- und Finanzbranche und arbeitet jetzt als Freiberufler.
Seine bisherigen und gegenwärtigen Engagements reichen von Datenbankdesign und -codierung bis hin zu Lehren, Beraten und Schreiben über Datenbanken. Nicht zu vergessen, BI, Algorithmen erstellen, Schach, Philatelie, 2 Hunde, 2 Katzen, 1 Frau, 1 Baby …
Sie finden ihn auf LinkedIn
Alle Beiträge von Emil Drkusic anzeigen

Neueste Beiträge von Emil Drkusic (alle anzeigen)

• SQL lernen: SQL Injection – 2. November 2020
• SQL lernen: Non-Equi Joins in SQL Server – 29. September 2020
• SQL lernen: SQL-bezogene Jobs – 1. September 2020