Aggregatfunktionen des Datenkompositionssystems. Verwenden von Aggregatfunktionen und verschachtelten Abfragen in einer Select-Anweisung. SQL-Outer-Joins

SQL hat zusätzliche Funktionen hinzugefügt, mit denen Sie verallgemeinerte Gruppenwerte berechnen können. Um Aggregatfunktionen anzuwenden, wird eine vorläufige Gruppierungsoperation angenommen. Was ist das Wesentliche an der Gruppierungsoperation? Beim Gruppieren wird die gesamte Menge an Tupeln einer Relation in Gruppen unterteilt, in denen Tupel gesammelt werden, die dieselben Attributwerte haben, die in der Gruppierungsliste angegeben sind.

Lassen Sie uns beispielsweise die Beziehung R1 nach dem Wert der Spalte „Disziplin“ gruppieren. Wir erhalten 4 Gruppen, für die wir einige Gruppenwerte berechnen können, z. B. die Anzahl der Tupel in der Gruppe, den Maximal- oder Minimalwert der Score-Spalte.

Dies geschieht mithilfe von Aggregatfunktionen. Aggregatfunktionen berechnen einen einzelnen Wert für eine gesamte Tabellengruppe. Eine Liste dieser Funktionen ist in Tabelle 5.7 dargestellt.

Tabelle 5.7.Aggregatfunktionen


R1
	Vollständiger Name	Disziplin	Grad
Gruppe 1	Petrov F.I.	Datenbank
	Sidorov K. A.	Datenbank
	Mironov A.V.	Datenbank
	Stepanowa K. E.	Datenbank
	Krylova T. S.	Datenbank
	Vladimirov V. A.	Datenbank
Gruppe 2	Sidorov K. A.	Informationstheorie
	Stepanowa K. E.	Informationstheorie
	Krylova T. S.	Informationstheorie
	Mironov A.V.	Informationstheorie	Null
Gruppe 3	Trofimov P. A.	Netzwerke und Telekommunikation
	Ivanova E. A.	Netzwerke und Telekommunikation
	Utkina N.V.	Netzwerke und Telekommunikation
Gruppe 4	Vladimirov V. A.	Englische Sprache
	Trofimov P. A.	Englische Sprache
	Ivanova E. A.	Englische Sprache
	Petrov F.I.	Englische Sprache	ich

Aggregatfunktionen werden ähnlich wie Feldnamen in einer SELECT-Anweisung verwendet, mit einer Ausnahme: Sie verwenden den Feldnamen als Argument. Mit den Funktionen SUM und AVG können nur numerische Felder verwendet werden. Mit den Funktionen COUNT, MAX und MIN können sowohl numerische als auch Zeichenfelder verwendet werden. Bei Verwendung mit Zeichenfeldern übersetzen MAX und MIN diese in den entsprechenden ASCII-Code und verarbeiten sie in alphabetischer Reihenfolge. Einige DBMS erlauben die Verwendung verschachtelter Aggregate, dies stellt jedoch eine Abweichung vom ANSI-Standard mit allen daraus resultierenden Konsequenzen dar.

Sie können beispielsweise die Anzahl der Studierenden berechnen, die in den einzelnen Disziplinen Prüfungen abgelegt haben. Dazu müssen Sie eine nach dem Feld „Disziplin“ gruppierte Abfrage ausführen und als Ergebnis den Namen der Disziplin und die Anzahl der Zeilen in der Gruppe für diese Disziplin anzeigen. Die Verwendung des *-Zeichens als Argument für die COUNT-Funktion bedeutet, dass alle Zeilen in der Gruppe gezählt werden.

WÄHLEN Sie R1.Disziplin. ZÄHLEN(*)

GRUPPE NACH R1 Disziplin

Ergebnis:

Wenn wir die Anzahl der Personen zählen möchten, die die Prüfung in einer beliebigen Disziplin bestanden haben, müssen wir vor der Gruppierung unsichere Werte aus dem ursprünglichen Verhältnis ausschließen. In diesem Fall sieht die Anfrage so aus:

WÄHLEN Sie R1.Disziplin. ZÄHLEN(*)

VON R1 WO R1.

Die Bewertung ist NICHT NULL

GRUPPE NACH Rl.Disziplin

Wir erhalten das Ergebnis:

In diesem Fall die Leitung mit dem Schüler


Mironov A, V.	Informationstheorie	Null

werden vor der Gruppierung nicht in die Menge der Tupel aufgenommen, sodass die Anzahl der Tupel in der Gruppe für die Disziplin „Informationstheorie“ um 1 geringer ist.

Sie können Aggregatfunktionen auch ohne die Vorgruppierungsoperation verwenden. In diesem Fall wird die gesamte Beziehung als eine Gruppe behandelt und Sie können für diese Gruppe einen Wert pro Gruppe berechnen.

Wenn wir uns noch einmal der Datenbank „Session“ zuwenden (Tabellen Rl, R2, R3), finden wir die Anzahl der erfolgreich bestandenen Prüfungen:

WO-Punktzahl > 2:

Dies unterscheidet sich natürlich von der Auswahl eines Felds, da immer ein einzelner Wert zurückgegeben wird, unabhängig davon, wie viele Zeilen die Tabelle enthält. Argumente für Aggregatfunktionen können einzelne Tabellenspalten sein. Um aber beispielsweise die Anzahl der unterschiedlichen Werte einer bestimmten Spalte in einer Gruppe zu berechnen, müssen Sie das Schlüsselwort DISTINCT zusammen mit dem Spaltennamen verwenden. Berechnen wir die Anzahl der verschiedenen Noten, die in jeder Disziplin erhalten wurden:

WÄHLEN Sie Rl.Discipline.

COUNT(DISTINCT R1.Score)

WO R1.Evaluation NICHT NULL IST

GRUPPE NACH Rl.Disziplin

Ergebnis:

Das Ergebnis kann einen Gruppierungsfeldwert und mehrere Aggregatfunktionen umfassen, und Gruppierungsbedingungen können mehrere Felder verwenden. In diesem Fall werden Gruppen gemäß einer Reihe festgelegter Gruppierungsfelder gebildet. Aggregationsfunktionsoperationen können angewendet werden, um mehrere Quelltabellen zu verknüpfen. Stellen wir uns zum Beispiel die Frage: Bestimmen Sie für jede Gruppe und jede Disziplin die Anzahl der Studierenden, die die Prüfung erfolgreich bestanden haben, und die durchschnittliche Punktzahl in der Disziplin.

SELECT R2.Group. R1.Disziplin. COUNT(*), AVP(Auswertung)

WHERE Rl.vollständiger Name = R2.vollständiger Name UND

Rl.Evaluation IST NICHT NULL UND

Rl.Bewertung > 2

GRUPPE NACH R2.Gruppe. Rl.Disziplin

Ergebnis:

Wir können in der WHERE-Klausel keine Aggregatfunktionen verwenden, da Prädikate in Bezug auf eine einzelne Zeile und Aggregatfunktionen in Bezug auf Gruppen von Zeilen ausgewertet werden.

Mit der GROUP BY-Klausel können Sie eine Teilmenge der Werte in einem bestimmten Feld in Bezug auf ein anderes Feld definieren und eine Aggregatfunktion auf die Teilmenge anwenden. Dadurch ist es möglich, Felder und Aggregatfunktionen in einer einzigen SELECT-Klausel zu kombinieren. Aggregatfunktionen können sowohl im Ausdruck zur Ausgabe der Ergebnisse der SELECT-Zeile als auch im Ausdruck für die Bedingung zur Verarbeitung gebildeter HAVING-Gruppen verwendet werden. In diesem Fall wird jede Aggregatfunktion für jede ausgewählte Gruppe berechnet. Die aus der Berechnung von Aggregatfunktionen gewonnenen Werte können zur Darstellung der entsprechenden Ergebnisse oder zur Konditionierung der Gruppenauswahl genutzt werden.

Erstellen wir eine Abfrage, die Gruppen anzeigt, in denen in einer Disziplin in den Prüfungen mehr als eine schlechte Note erhalten wurde:

SELECT R2.Group

WHERE Rl.vollständiger Name = R2.vollständiger Name UND

Rl.Rating = 2

GRUPPE NACH R2.Gruppe. R1.Disziplin

MIT count(*)> 1

In Zukunft werden wir beispielsweise nicht mit der Datenbank „Session“ arbeiten, sondern mit der Datenbank „Bank“, die aus einer Tabelle F besteht, in der die Relation F gespeichert ist, die Informationen über Konten in Filialen einer bestimmten Bank enthält:

F= ;

Q = (Filiale, Stadt);

denn auf dieser Grundlage lässt sich die Arbeit mit Aggregatfunktionen und Gruppierung anschaulicher veranschaulichen.

Angenommen, wir möchten den Gesamtsaldo von Bankkonten ermitteln. Sie können für jeden von ihnen eine separate Abfrage durchführen, indem Sie in der Tabelle für jeden Zweig SUM(Saldo) auswählen. Mit GROUP BY können Sie sie jedoch alle in einem Befehl zusammenfassen:

SELECT-Zweig, SUMME

GRUPPE NACH Zweig:

GROUP BY wendet Aggregatfunktionen unabhängig für jede Gruppe an, die durch den Wert des Zweigfelds identifiziert wird. Eine Gruppe besteht aus Zeilen mit demselben Zweigfeldwert, und die SUMME-Funktion wird für jede dieser Gruppen separat angewendet, d. h. der Gesamtkontostand wird für jede Zweigstelle separat berechnet. Der Wert eines Feldes, auf das GROUP BY angewendet wird, hat per Definition nur einen Wert pro Ausgabegruppe, genau wie das Ergebnis einer Aggregatfunktion. Daher können wir ein Aggregat und ein Feld in einer Anfrage kombinieren. Sie können GROUP BY auch mit mehreren Feldern verwenden.

Nehmen wir an, wir möchten nur die gesamten Kontostände sehen, die 5.000 $ übersteigen. Um Gesamtsalden über 5.000 $ anzuzeigen, müssen Sie die HAVING-Klausel verwenden. Die HAVING-Klausel gibt die Kriterien an, die zum Entfernen bestimmter Gruppen aus der Ausgabe verwendet werden, genau wie die WHERE-Klausel dies für einzelne Zeilen tut.

Der richtige Befehl wäre:

SELECT Branch, SUM(Remaining)

GROUP BY-Zweig

HAVING SUM(Remaining) > 5000;

Argumente in einer HAVING-Klausel folgen denselben Regeln wie in einer SELECT-Klausel, die GROUP BY verwendet. Sie müssen einen Wert pro Ausgabegruppe haben.

Der folgende Befehl wird verboten:

SELECT Branch.SUM(Remaining)

VON F GRUPPE NACH Zweig

HAVINGOpenDate = 27.12.1999;

Das OpenDate-Feld kann nicht in einer HAVING-Klausel verwendet werden, da es mehr als einen Wert pro Ausgabegruppe haben kann. Um diese Situation zu vermeiden, sollte die HAVING-Klausel nur auf die von GROUP BY ausgewählten Aggregate und Felder verweisen. Es gibt eine korrekte Möglichkeit, die obige Abfrage durchzuführen:

SELECT Branch,SUM(Remaining)

WHEREOpenDate = "27.12.1999"

GROUP BY-Zweig;

Die Bedeutung dieser Abfrage ist wie folgt: Finden Sie die Summe der Salden für jeden Kontozweig, der am 27. Dezember 1999 eröffnet wurde.

Wie bereits erwähnt, kann HAVING nur Argumente annehmen, die einen Wert pro Ausgabegruppe haben. In der Praxis sind Verweise auf Aggregatfunktionen am häufigsten, aber auch mit GROUP BY ausgewählte Felder sind gültig. Wir möchten beispielsweise die Gesamtsalden auf den Konten der Filialen in St. Petersburg, Pskow und Urjupinsk sehen:

SELECT Branch.SUM(Remaining)

WO F.Branch = Q.Branch

GROUP BY-Zweig

Niederlassung in („St. Petersburg“, „Pskow“, „Urjupinsk“);

Daher können nur die Spezifikationen der Spalten, die in der GROUP BY-Klausel als Gruppierungsspalten angegeben sind, direkt in arithmetischen Prädikatenausdrücken verwendet werden, die in der Auswahlklausel der HAVING-Klausel enthalten sind. Die übrigen Spalten können nur im Rahmen der Spezifikationen der Aggregatfunktionen COUNT, SUM, AVG, MIN und MAX angegeben werden, die in diesem Fall einen Aggregatwert für die gesamte Zeilengruppe berechnen. Ähnlich verhält es sich mit Unterabfragen, die in den Prädikaten der Auswahlbedingung des HAVING-Abschnitts enthalten sind: Wenn die Unterabfrage ein Merkmal der aktuellen Gruppe verwendet, kann dieses nur durch Bezugnahme auf die Gruppierungsspalten angegeben werden.

Das Ergebnis der HAVING-Klausel ist eine gruppierte Tabelle, die nur die Zeilengruppen enthält, für die die Suchbedingung TRUE ergibt. Insbesondere wenn eine HAVING-Klausel in einem Tabellenausdruck vorhanden ist, der kein GROUP BY enthält, ist das Ergebnis ihrer Ausführung entweder eine leere Tabelle oder das Ergebnis der Ausführung der vorherigen Abschnitte des Tabellenausdrucks, die als einzelne Abschnitte behandelt werden Gruppe ohne Gruppierungsspalten.

Verschachtelte SQL-Abfragen

Kehren wir nun zur Datenbank „Session“ zurück und sehen uns deren Beispiel für die Verwendung verschachtelter Abfragen an.

Mit SQL können Sie Abfragen ineinander verschachteln. Normalerweise generiert die innere Abfrage einen Wert, der im Prädikat der äußeren Abfrage (in der WHERE- oder HAVING-Klausel) getestet wird, um festzustellen, ob er wahr oder falsch ist. In Verbindung mit einer Unterabfrage können Sie das EXISTS-Prädikat verwenden, das „true“ zurückgibt, wenn die Ausgabe der Unterabfrage nicht leer ist.

In Kombination mit den anderen Funktionen des Auswahloperators, wie z. B. der Gruppierung, ist eine Unterabfrage ein leistungsstarkes Werkzeug zum Erreichen des gewünschten Ergebnisses. Im FROM-Teil der SELECT-Anweisung ist es zulässig, Synonyme auf Tabellennamen anzuwenden, wenn wir beim Erstellen einer Abfrage mehr als eine Instanz einer bestimmten Relation benötigen. Synonyme werden mit dem Schlüsselwort AS angegeben, das ganz weggelassen werden kann. Der FROM-Teil könnte also so aussehen:

VON Rl ALS A, Rl ALS B

VON Rl A. Rl B:

Beide Ausdrücke sind äquivalent und gelten als Anwendungen der SELECT-Anweisung auf zwei Instanzen der Tabelle R1.

Lassen Sie uns zum Beispiel zeigen, wie einige Abfragen an die Datenbank „Session“ in SQL aussehen:

Liste derjenigen, die alle erforderlichen Prüfungen bestanden haben.

WHERE-Punktzahl > 2

HAVING COUNT(*) = (SELECT COUNT(*)

WHERE R2.Group=R3.Group AND vollständiger Namea.vollständiger Name)

Hier ermittelt die integrierte Abfrage die Gesamtzahl der Prüfungen, die jeder Schüler in seiner Klasse ablegen muss, und vergleicht diese Zahl mit der Anzahl der Prüfungen, die der Schüler abgelegt hat.

Eine Liste derjenigen, die die Datenbankprüfung ablegen sollten, diese aber noch nicht abgelegt haben.

SELESTFIO

WHERE R2.Fpynna=R3.Group AND Discipline = „DB“ AND NOT EXISTS

(SELECT full name FROM Rl WHERE full name=a.full name AND Discipline = „DB“)

Das Prädikat EXISTS (SubQuery) ist wahr, wenn die SubQuery-Unterabfrage nicht leer ist, d. h. sie enthält mindestens ein Tupel, andernfalls ist das Prädikat EXISTS falsch.

Das NOT EXISTS-Prädikat ist nur wahr, wenn die Unterabfrage leer ist.

Beachten Sie, dass Sie mit NOT EXISTS bei einer verschachtelten Abfrage die Beziehungsunterschiedsoperation vermeiden können. Beispielsweise kann die Formulierung einer Abfrage mit dem Wort „alle“ wie mit einer doppelten Verneinung erfolgen. Betrachten wir ein Beispiel einer Datenbank, die die Lieferung einzelner Teile durch einzelne Lieferanten modelliert; sie wird durch eine SP-Beziehung „Lieferanten-Teile“ mit dem Diagramm dargestellt

SP (Lieferantennummer. Teilenummer) P (Teilenummer. Name)

So lautet die Antwort auf die Anfrage: „Finden Sie Lieferanten, die alle Teile liefern.“

Wählen Sie eine eindeutige Anbieternummer aus SP SP1 aus, die nicht vorhanden ist

(SELECT part_number

VON P WO NICHT EXISTIERT

(AUSWÄHLEN * AUS SP SP2

WHERE SP2.supplier_number=SP1.supplier_number AND

sp2.part_number = P.part_number)):

Tatsächlich haben wir diese Forderung wie folgt umformuliert: „Finden Sie Lieferanten, bei denen es kein Teil gibt, das sie nicht liefern.“ Es ist zu beachten, dass diese Abfrage auch über Aggregatfunktionen mit einer Unterabfrage implementiert werden kann:

SELECT DISTINCT Vendor_number

GROUP BY Lieferantennummer

HAVING CounKDISTINCT part_number) =

(SELECT Count(part_number)

Der SQL92-Standard erweitert Vergleichsoperatoren auf mehrere Vergleiche mithilfe der Schlüsselwörter ANY und ALL. Diese Erweiterung wird verwendet, wenn der Wert einer bestimmten Spalte mit der von einer Unterabfrage zurückgegebenen Datenspalte verglichen wird.

Das Schlüsselwort ANY in einem Vergleichsprädikat bedeutet, dass das Vergleichsprädikat wahr ist, wenn das Vergleichsprädikat für mindestens einen Wert aus der Unterabfrage wahr ist. Das Schlüsselwort ALL erfordert, dass das Vergleichsprädikat wahr ist, wenn es mit allen Zeilen in der Unterabfrage verglichen wird.

Suchen wir zum Beispiel nach Studierenden, die alle Prüfungen mit der Note „gut“ bestanden haben. Wir arbeiten mit der gleichen „Session“-Datenbank, fügen dieser jedoch eine weitere Relation R4 hinzu, die die Erbringung von Laborarbeiten während des Semesters charakterisiert:

R 1 = (Name, Disziplin, Note);

R 2 = (vollständiger Name, Gruppe);

R 3 = (Gruppen, Disziplin)

R 4 = (Name, Disziplin, Nummer der Laborarbeit, Note);

Wählen Sie R1.Vollständiger Name aus R1, wobei 4 > = Alle (Wählen Sie Rl.Bewertung aus

Wobei R1.Vollständiger Name = R11.Vollständiger Name)

Schauen wir uns ein anderes Beispiel an:

Wählen Sie Studierende aus, deren Prüfungsnote mindestens einer Note in der von ihnen bestandenen Laborarbeit in dieser Disziplin entspricht:

Wählen Sie R1.Name aus

Von R1 Wo R1.Rating>= ANY (Wählen Sie R4.Rating

Wobei Rl.Discipline = R4. Disziplin UND R1.Vollständiger Name = R4.Vollständiger Name)

SQL Outer Joins

Der SQL2-Standard hat das Konzept eines bedingten Joins erweitert. Im SQL1-Standard wurden beim Zusammenführen von Beziehungen nur die im WHERE-Teil der SELECT-Anweisung angegebenen Bedingungen verwendet, und in diesem Fall nur die Tupel der ursprünglichen Beziehungen, die durch die angegebenen Bedingungen verkettet wurden, für die diese Bedingungen definiert und wahr waren , wurden in die resultierende Beziehung einbezogen. In der Realität ist es jedoch häufig erforderlich, Tabellen so zu verknüpfen, dass das Ergebnis alle Zeilen der ersten Tabelle enthält und anstelle der Zeilen der zweiten Tabelle, für die die Verknüpfungsbedingung nicht erfüllt ist, das Ergebnis angezeigt wird mit undefinierten Werten. Oder umgekehrt: Alle Zeilen der rechten (zweiten) Tabelle werden einbezogen und die fehlenden Zeilenteile der ersten Tabelle werden durch undefinierte Werte ergänzt. Solche Verknüpfungen wurden als äußere Verknüpfungen bezeichnet, im Gegensatz zu den im SQL1-Standard definierten Verknüpfungen, die später als innere Verknüpfungen bezeichnet wurden.

Im Allgemeinen lautet die Syntax für den FROM-Teil im SQL2-Standard wie folgt:

AUS<список исходных таблиц> |

< выражение естественного объединения > |

< выражение объединения >

< выражение перекрестного объединения > |

< выражение запроса на объединение >

<список исходных таблиц>::= <имя_таблицы_1>

[Tabelle_1 Synonymname] [...]

[,<имя_таблицы_п>[ <имя синонима таблицы_n> ] ]

<выражение естественного объединениям:: =

<имя_таблицы_1>NATÜRLICH (INNEN | VOLLSTÄNDIG | LINKS | RECHTS) VERBINDEN<имя_таблицы_2>

<выражение перекрестного объединениям: = <имя_таблицы_1>KREUZVERBINDUNG<имя_таблицы_2>

<выражение запроса на объединением:=

<имя_таблицы_1>GEWERKSCHAFTSTRETEN<имя_таблицы_2>

<выражение объединениям:= <имя_таблицы_1>( INNEN |

VOLL | LINKS | RECHTS) JOIN (EIN-Zustand)<имя_таблицы_2>

In diesen Definitionen bedeutet INNER einen Inner Join, LEFT bedeutet einen Left Join, d. h. das Ergebnis umfasst alle Zeilen der Tabelle 1, und Teile der resultierenden Tupel, für die es in Tabelle 2 keine entsprechenden Werte gab, werden mit NULL ergänzt (undefinierte) Werte. Das Schlüsselwort RIGHT bedeutet einen rechten Outer-Join, und im Gegensatz zu einem Left-Join werden in diesem Fall alle Zeilen von Tabelle 2 in die resultierende Beziehung einbezogen und die fehlenden Teile aus Tabelle 1 werden durch undefinierte Werte ergänzt. Das Schlüsselwort FULL definiert einen vollständigen Outer verbinden: sowohl links als auch rechts. Bei einem vollständigen Outer-Join werden sowohl rechte als auch linke Outer-Joins durchgeführt, und die resultierende Beziehung umfasst alle Zeilen aus Tabelle 1, aufgefüllt mit Nullen, und alle Zeilen aus Tabelle 2, ebenfalls aufgefüllt mit Nullen.

Das Schlüsselwort OUTER bedeutet „außen“, aber wenn die Schlüsselwörter FULL, LEFT, RIGHT angegeben werden, wird der Join immer als „äußer“ betrachtet.

Schauen wir uns Beispiele für die Durchführung von Outer-Joins an. Kehren wir zur Datenbank „Session“ zurück. Erstellen wir eine Beziehung, in der alle Noten stehen, die alle Schüler in allen Prüfungen erhalten haben, die sie ablegen mussten. Wenn ein Student diese Prüfung nicht bestanden hat, erhält er anstelle einer Note einen unbestimmten Wert. Dazu führen wir einen sequenziellen natürlichen internen Join der Tabellen R2 und R3 mithilfe des Gruppenattributs durch und verbinden die resultierende Beziehung mit einem linken äußeren natürlichen Join mit Tabelle R1 mithilfe der Spalten „Vollständiger Name“ und „Disziplin“. Gleichzeitig erlaubt der Standard die Verwendung einer Klammerstruktur, da das Ergebnis der Vereinigung eines der Argumente im FROM-Teil der SELECT-Anweisung sein kann.

SELECT Rl.Vollständiger Name, R1.Disziplin. Rl.Bewertung

FROM (R2 NATURAL INNER JOIN R3) LEFT JOIN Rl USING (Name. Disziplin)

Ergebnis:


Vollständiger Name	Disziplin	Grad
Petrov F.I.	Datenbank
Sidorov K. A.	Datenbank	4
Mironov L.V.	Datenbank
Stepanowa K. E.	Datenbank
Krylova T. S.	Datenbank
Vladimirov V. A.	Datenbank
Petrov F.I.	Informationstheorie	Null
Sidorov K. A.	Informationstheorie
Mironov A.V.	Informationstheorie	Null
Stepanowa K. E.	Informationstheorie
Krylova T. S.	Informationstheorie
Vladimirov V. A.	Informationstheorie	Null
Petrov F.I.	Englische Sprache
Sidorov K. A.	Englische Sprache	Null
Mironov A.V.	Englische Sprache	Null
Stepanowa K. E.	Englische Sprache	Null
Krylova T. S.	Englische Sprache	Null
Vladimirov V. A.	Englische Sprache
Trofimov P. A.	Netzwerke und Telekommunikation
Ivanova E. A.	Netzwerke und Telekommunikation

Betrachten wir ein weiteres Beispiel, hierfür nehmen wir die Datenbank „Bibliothek“. Es besteht aus drei Beziehungen; die Attributnamen werden hier in lateinischen Buchstaben eingegeben, was in den meisten kommerziellen DBMS notwendig ist.

BÜCHER (ISBN, TITL. AUTOR. COAUTOR. JAHRJZD, SEITEN)

READER(NUM_READER. NAME_READER, ADRESS. HOOM_PHONE. WORK_PHONE. BIRTH_DAY)

EXEMPLARE (INV, ISBN, YES_NO. NUM_READER. DATE_IN. DATE_DUT)

Hier beschreibt die Tabelle BÜCHER alle in der Bibliothek vorhandenen Bücher. Sie weist die folgenden Attribute auf:

ISBN – eine eindeutige Chiffre für das Buch;
TITL – Titel des Buches;
AUTOR – Nachname des Autors;
COAUTOR – Nachname des Co-Autors;
YEARIZD – Erscheinungsjahr;
SEITEN – Anzahl der Seiten.

Die READER-Tabelle speichert Informationen über alle Leser der Bibliothek und enthält die folgenden Attribute:

NUM_READER – eindeutige Bibliotheksausweisnummer;
NAME_READER – der Nachname und die Initialen des Lesers;
ADRESS – Adresse des Lesers;
HOOM_PHONE – private Telefonnummer;
WORK_PHONE – geschäftliche Telefonnummer;
BIRTH_DAY – Geburtsdatum des Lesers.

Die Tabelle EXEMPLARE enthält Informationen über den aktuellen Status aller Instanzen aller Bücher. Es enthält die folgenden Spalten:

INV – eindeutige Inventarnummer eines Buchexemplars;
ISBN – Buchchiffre, die bestimmt, um welche Art von Buch es sich handelt, und sich auf Informationen aus der ersten Tabelle bezieht;
YES_NO – ein Zeichen für die Anwesenheit oder Abwesenheit dieser Instanz in der Bibliothek zum aktuellen Zeitpunkt;
NUM_READER – Bibliotheksausweisnummer, wenn das Buch an den Leser ausgegeben wird, andernfalls Null;
DATE_IN – wenn der Leser das Buch besitzt, ist dies das Datum, an dem es an den Leser ausgegeben wurde; Ein DATE_OUT ist das Datum, an dem der Leser das Buch an die Bibliothek zurückgeben muss.

Lassen Sie uns die Liste der Bücher für jeden Leser festlegen; Wenn der Leser keine Bücher hat, ist die Buchexemplarnummer NULL. Um diese Suche durchzuführen, müssen wir einen linken äußeren Join verwenden, das heißt, wir nehmen alle Zeilen aus der READER-Tabelle und verbinden sie mit Zeilen aus der EXEMPLARE-Tabelle, wenn die zweite Tabelle keine Zeile mit der entsprechenden Bibliotheksausweisnummer hat , dann wird in der Zeile der resultierenden Beziehung das Attribut EXEMPLARE.INV einen undefinierten NULL-Wert haben:

SELECT READER.NAME_READER, EXEMPLARE.INV

VOM READER RECHTS JOIN EXEMPLARE AUF READER.NUM_READER=EXEMPLARE.NUM_READER

Die Outer-Join-Operation kann, wie bereits erwähnt, zur Bildung von Quellen in der FROM-Klausel verwendet werden, sodass beispielsweise der folgende Abfragetext akzeptabel wäre:

VON (Übrige BÜCHER BEITRETEN BEISPIEL)

LEFT JOIN (READER NATURAL JOIN BEISPIEL)

Gleichzeitig sind bei Büchern, von denen sich kein einziges Exemplar in den Händen der Leser befindet, die Werte der Bibliotheksausweisnummer und die Daten der Abholung und Rückgabe des Buchs ungewiss.

Ein Cross-Join, wie er im SQL2-Standard definiert ist, entspricht einer erweiterten kartesischen Produktoperation, also einer Join-Operation zwischen zwei Tabellen, bei der jede Zeile der ersten Tabelle mit jeder Zeile der zweiten Tabelle verbunden wird.

Betrieb Fusionsantrag entspricht der Operation der mengentheoretischen Vereinigung in der Algebra. In diesem Fall bleibt das Erfordernis der Äquivalenz der Schemata der ursprünglichen Beziehungen erhalten. Eine Beitrittsanfrage wird nach folgendem Schema durchgeführt:

AUSWÄHLEN – Anfrage

UNION SELECT – Abfrage

Alle an der Join-Operation beteiligten Abfragen dürfen keine Ausdrücke, also berechnete Felder, enthalten.

Sie müssen beispielsweise eine Liste der Leser anzeigen, die das Buch „Idiot“ oder das Buch „Verbrechen und Strafe“ in der Hand halten. So sieht die Anfrage aus:

LESER AUSWÄHLEN. NAME_READER

VON READER, EXEMPLARE.BOOKS

BOOKS.TITLE = "Idiot"!}

WÄHLEN SIE READER.NAME_READER

VOM LESER, BEISPIEL, BÜCHERN

WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND

EXEMPLRE.ISBN = BÜCHER.ISBN UND

BOOKS.TITLE = "Verbrechen und Strafe"!}

Standardmäßig werden beim Ausführen einer Join-Abfrage doppelte Tupel immer ausgeschlossen. Wenn es also Leser gibt, die beide Bücher in der Hand haben, werden sie dennoch nur einmal in der resultierenden Liste angezeigt.

Eine Join-Abfrage kann eine beliebige Anzahl ursprünglicher Abfragen zusammenführen.

Zur vorherigen Anfrage können Sie also weitere Leser hinzufügen, die das Buch „Castle“ in den Händen halten:

LESER AUSWÄHLEN. NAME_READER

VOM LESER. BEISPIEL, BÜCHER

WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND .

EXEMPLRE.ISBN = BÜCHER.ISBN UND

BOOKS.TITLE = "Castle"!}

Wenn Sie alle Zeilen aus den Quellbeziehungen beibehalten müssen, müssen Sie im Join-Vorgang das Schlüsselwort ALL verwenden. Wenn doppelte Tupel gespeichert sind, sieht der Ausführungsablauf der Join-Abfrage folgendermaßen aus:

AUSWÄHLEN – Anfrage

Das gleiche Ergebnis kann jedoch erzielt werden, indem man einfach die WHERE-Klausel des ersten Teils der ursprünglichen Abfrage ändert, die lokalen Bedingungen mit einer logischen ODER-Verknüpfung verbindet und doppelte Tupel eliminiert.

WÄHLEN SIE EINDEUTIGEN READER.NAME_READER

VOM LESER. BEISPIELBÜCHER

WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND

EXEMPLRE.ISBN = BÜCHER.ISBN UND

BOOKS.TITLE = "Idiot" OR!}

BOOKS.TITLE = "Verbrechen und Strafe" OR!}

BOOKS.TITLE = "Castle"!}

Keine der ursprünglichen Abfragen in einer UNION-Operation darf eine ORDER BY-Klausel enthalten, aber das Ergebnis der Union kann geordnet werden, indem eine ORDER BY-Klausel geschrieben wird, die die Liste der Sortierspalten nach dem Text der letzten ursprünglichen SELECT-Abfrage angibt.

Wichtig! Wenn ein Funktionsparameter vom Typ String ist und einen Feldnamen angibt, der Leerzeichen enthält, muss der Feldname in eckige Klammern eingeschlossen werden.
Zum Beispiel: „[Anzahl der Umsätze]“.

1. Betrag (Gesamt)- berechnet die Summe der Werte der Ausdrücke, die ihm als Argument für alle detaillierten Datensätze übergeben werden. Sie können ein Array als Parameter übergeben. In diesem Fall wird die Funktion auf den Inhalt des Arrays angewendet.

Beispiel:
Betrag(Sales.AmountTurnover)

2. Zählen - berechnet die Anzahl der anderen Werte als NULL. Sie können ein Array als Parameter übergeben. In diesem Fall wird die Funktion auf den Inhalt des Arrays angewendet.

Syntax:
Menge ([Verschiedene] Parameter)

Um den Empfang unterschiedlicher Werte anzuzeigen, sollten Sie Distinct vor dem Methodenparameter Quantity angeben.

Beispiel:
Menge (Sales.Counterparty)
Menge (Verschiedene Verkäufe.Gegenpartei)

3. Maximal - erhält den Maximalwert. Sie können ein Array als Parameter übergeben. In diesem Fall wird die Funktion auf den Inhalt des Arrays angewendet.

Beispiel:
Maximum (verbleibende Menge)

4. Mindestens - erhält den Mindestwert. Sie können ein Array als Parameter übergeben. In diesem Fall wird die Funktion auf den Inhalt des Arrays angewendet.

Beispiel:
Minimum (verbleibende Menge)

5. Durchschnittlich – Ruft den Durchschnitt für Nicht-NULL-Werte ab. Sie können ein Array als Parameter übergeben. In diesem Fall wird die Funktion auf den Inhalt des Arrays angewendet.

Beispiel:
Durchschnitt (verbleibende Menge)

6. Array – Bildet ein Array, das den Parameterwert für jeden detaillierten Datensatz enthält.

Syntax:
Array([Verschiedene] Ausdruck)

Als Parameter können Sie eine Wertetabelle verwenden. In diesem Fall ist das Ergebnis der Funktion ein Array, das die Werte der ersten Spalte der Wertetabelle enthält und als Parameter übergeben wird. Wenn ein Ausdruck eine Array-Funktion enthält, wird der Ausdruck als Aggregatausdruck betrachtet. Wenn das Schlüsselwort Different angegeben ist, enthält das resultierende Array keine doppelten Werte.

Beispiel:
Array(Gegenpartei)

7. Wertetabelle - erzeugt eine Wertetabelle, die so viele Spalten enthält, wie Parameter für die Funktion vorhanden sind. Detaildatensätze werden aus Datensätzen abgerufen, die zum Abrufen aller an Funktionsparameterausdrücken beteiligten Felder erforderlich sind.

Syntax:
ValueTable([Various] Expression1 [AS ColumnName1][, Expression2 [AS ColumnName2],...])

Wenn es sich bei den Funktionsparametern um Restfelder handelt, enthält die resultierende Wertetabelle Werte für Datensätze für eindeutige Dimensionskombinationen aus anderen Zeiträumen. In diesem Fall werden Werte nur für Saldenfelder, Dimensionen, Konten, Periodenfelder und deren Details ermittelt. Die Werte der verbleibenden Felder in Datensätzen aus anderen Zeiträumen werden als gleich NULL betrachtet. Wenn ein Ausdruck die ValueTable-Funktion enthält, wird dieser Ausdruck als Aggregatausdruck betrachtet. Wenn das Schlüsselwort Different angegeben ist, enthält die resultierende Wertetabelle keine Zeilen mit denselben Daten. Nach jedem Parameter kann ein optionales Schlüsselwort AS und ein Name stehen, der der Spalte der Wertetabelle zugewiesen wird.

Beispiel:
Wertetabelle (verschiedene Nomenklaturen, Merkmale der Nomenklaturen AS-Merkmale)

8. Reduzieren (GroupBy) – Entwickelt, um Duplikate aus einem Array zu entfernen.

Syntax:
Collapse(Expression, ColumnNumbers)

Optionen :

Ausdruck- ein Ausdruck vom Typ Array oder ValueTable, dessen Werte der Elemente reduziert werden müssen;
Spaltennummern- (wenn der Ausdruck vom Typ ValueTable ist) Typ String. Zahlen oder Namen (durch Kommas getrennt) der Spalten der Wertetabelle, in denen Sie nach Duplikaten suchen müssen. Der Standardwert sind alle Spalten.

Beispiel:
Collapse(ValueTable(PhoneNumber, Address) ,"PhoneNumber");

9. GetPart - Ruft eine Wertetabelle ab, die bestimmte Spalten aus der ursprünglichen Wertetabelle enthält.

Syntax:
GetPart(Expression, ColumnNumbers)

Optionen :

Ausdruck- Geben Sie Wertetabelle ein. Eine Wertetabelle, aus der Spalten abgerufen werden können;
Spaltennummern- Geben Sie String ein. Nummern oder Namen (durch Kommas getrennt) der Spalten der Wertetabelle, die abgerufen werden müssen.

Rückgabewert: Wertetabelle, die nur die im Parameter angegebenen Spalten enthält.

Beispiel:
GetPart(Collapse(ValueTable(PhoneNumber, Address) ,"PhoneNumber"),"PhoneNumber");

10. Bestellen - Entwickelt, um Array-Elemente und Wertetabellen zu organisieren.

Syntax:
Anordnen (Ausdruck, Spaltennummern)

Optionen :

Ausdruck- Array oder Wertetabelle, aus der Sie Spalten abrufen müssen;
Spaltennummern- (wenn der Ausdruck vom Typ ValueTable ist) Zahlen oder Namen (durch Kommas getrennt) der Spalten der Wertetabelle, nach der Sie sortieren möchten. Kann die Sortierrichtung und die Notwendigkeit einer automatischen Sortierung enthalten: Absteigend/Aufsteigend + Automatische Sortierung.

Rückgabewert: Array oder ValueTable mit geordneten Elementen.

Beispiel:
Arrange(ValueTable(PhoneNumber, Address, CallDate),"CallDate Descending");

11. JoinStrings - Entwickelt, um Zeichenfolgen zu einer Zeile zusammenzufassen.

Syntax:
ConnectRows(Value, ItemSeparator, ColumnSeparator)

Optionen :

Bedeutung- Ausdrücke, die in einer Zeile zusammengefasst werden müssen. Wenn es sich um ein Array handelt, werden die Elemente des Arrays zu einem String zusammengefasst. Wenn es sich um eine ValueTable handelt, werden alle Spalten und Zeilen der Tabelle zu einer Zeile zusammengefasst;
Elementtrenner– eine Zeichenfolge mit Text, der als Trennzeichen zwischen Array-Elementen und Wertetabellenzeilen verwendet werden soll. Standard – Zeilenvorschubzeichen;
Spaltentrennzeichen- eine Zeile mit Text, der als Trennzeichen zwischen den Spalten der Wertetabelle verwendet werden soll. Default "; ".

Beispiel:
ConnectRows(ValueTable(PhoneNumber, Address));

12. Gruppenverarbeitung – gibt das GroupProcessingDataCompositionData-Objekt zurück. In der Data-Eigenschaft des Objekts werden die Gruppierungswerte in Form einer Wertetabelle für jeden im Funktionsparameter Expressions angegebenen Ausdruck platziert. Bei der hierarchischen Gruppierung wird jede Ebene der Hierarchie separat verarbeitet. Werte für hierarchische Datensätze werden ebenfalls in die Daten eingefügt. Die CurrentItem-Eigenschaft des Objekts enthält die Wertetabellenzeile, für die die Funktion gerade berechnet wird.

Syntax:
GroupProcessing(Ausdrücke, Hierarchieausdrücke, Gruppenname)

Optionen :

Ausdrücke. Auszuwertende Ausdrücke. Eine Zeile mit durch Kommas getrennten Ausdrücken, die ausgewertet werden müssen. Nach jedem Ausdruck kann ein optionales Schlüsselwort HOW und der Name der Spalte der resultierenden Wertetabelle stehen. Jeder Ausdruck bildet eine Spalte in der Wertetabelle der Data-Eigenschaft des DataCompositionGroupProcessingData-Objekts.
AusdrückeHierarchien. Für hierarchische Datensätze auszuwertende Ausdrücke. Ähnlich dem Parameter „Ausdrücke“, mit dem Unterschied, dass der Parameter „Hierarchieausdrücke“ für hierarchische Datensätze verwendet wird. Wenn der Parameter nicht angegeben ist, werden die im Parameter Ausdruck angegebenen Ausdrücke zur Berechnung von Werten für hierarchische Datensätze verwendet.
Gruppenname. Der Name der Gruppierung, in der die Verarbeitungsgruppierung berechnet werden soll. Linie. Wenn nicht angegeben, erfolgt die Berechnung in der aktuellen Gruppierung. Wenn die Berechnung in einer Tabelle durchgeführt wird und der Parameter einen leeren String enthält oder nicht angegeben ist, wird der Wert für die Gruppierung berechnet – der String. Der Layout-Composer ersetzt beim Generieren eines Datenlayout-Layouts diesen Namen durch den Namen der Gruppierung im resultierenden Layout. Wenn keine Gruppierung verfügbar ist, wird die Funktion durch einen NULL-Wert ersetzt.

13. Jeder - Wenn mindestens ein Datensatz den Wert False hat, ist das Ergebnis False, andernfalls True.

Syntax:
Jeder (Ausdruck)

Parameter:

Ausdruck- Boolescher Typ.

Beispiel:
Jeden()

14. Beliebig (Beliebig)- Wenn mindestens ein Datensatz den Wert True hat, ist das Ergebnis True, andernfalls False

Syntax:
Beliebig (Ausdruck)

Parameter:

Ausdruck- Boolescher Typ.

Beispiel:
Beliebig()

15. Standardabweichung der Gesamtbevölkerung (Stddev_Pop) - berechnet die Standardabweichung der Grundgesamtheit. Berechnet mit der Formel: SQRT(Varianz der Gesamtbevölkerung (X)).

Syntax:
Standardabweichung der Gesamtbevölkerung (Ausdruck)

Parameter:

Ausdruck- Nummerntyp.

Beispiel:

X	1	2	3	4	5	6	7	8	9
Y	7	1	2	5	7	34	32	43	87

WÄHLEN Sie die Standardabweichung der Gesamtbevölkerung (Y) AUS der Tabelle aus
Ergebnis: 805.694444

16. Standardabweichung der Probe (Stddev_Samp) - berechnet die kumulative Stichprobenstandardabweichung. Berechnet mit der Formel: SQRT(Stichprobenvarianz(X)).

Syntax:
StandardDeviationSample(Ausdruck)

Parameter:

Ausdruck- Nummerntyp.

Rückgabetyp Number.

Beispiel:

X	1	2	3	4	5	6	7	8	9
Y	7	1	2	5	7	34	32	43	87

SELECT StandardDeviationSamples(Y) FROM Table
Ergebnis: 28.3847573

17. VarianceSamples (Var_Samp) - berechnet die typische Differenz einer Zahlenreihe, ohne die NULL-Werte in dieser Menge zu berücksichtigen. Berechnet mit der Formel: (Betrag(X^2) - Betrag(X)^2 / Menge(X)) / (Menge(X) - 1). Wenn Quantity(X) = 1, wird NULL zurückgegeben.

Syntax:
VarianceSamples(Ausdruck)

Parameter:

Ausdruck- Nummerntyp.

Beispiel:
SELECT Varianz der Grundgesamtheit (Y) AUS Tabelle
Ergebnis: 716.17284

19. Kovarianz der Gesamtbevölkerung (Covar_Pop) - berechnet die Kovarianz mehrerer Zahlenpaare. Berechnet mit der Formel: (Summe(Y * X) - Summe(X) * Summe(Y) / n) / n, wobei n die Anzahl der Paare (Y, X) ist, in denen weder Y noch X NULL sind.

Syntax:
Kovarianz der Bevölkerung (Y, X)

Optionen :

Y- Nummer eingeben;
X- Nummerntyp.

Beispiel:

X	1	2	3	4	5	6	7	8	9
Y	7	1	2	5	7	34	32	43	87

SELECT CovariancePopulation(Y, X) FROM Table
Ergebnis: 59.4444444

20. CovarianceSamples (Covar_Samp) - berechnet die typische Differenz einer Zahlenreihe, ohne die NULL-Werte in dieser Menge zu berücksichtigen. Berechnet mit der Formel: (Summe(Y * X) - Summe(Y) * Summe(X) / n) / (n-1), wobei n die Anzahl der Paare (Y, X) ist, in denen weder Y noch X sind NULL.

Syntax:
Kovarianzstichproben(Y, X)

Optionen :

Y- Nummer eingeben;
X- Nummerntyp.

Beispiel:

X	1	2	3	4	5	6	7	8	9
Y	7	1	2	5	7	34	32	43	87

SELECT CovarianceSamples(Y, X) FROM Table
Ergebnis: 66,875

21. Korrelation (Corr) - berechnet den Korrelationskoeffizienten mehrerer Zahlenpaare. Berechnet nach der Formel: Kovarianz der Population (Y, X) / (Standardabweichung der Population (Y) * Standardabweichung der Population (X)). Paare, in denen Y oder X NULL sind, werden nicht berücksichtigt.

Syntax:
Korrelation(Y, X)

Optionen :

Y- Nummer eingeben;
X- Nummerntyp.

Beispiel:

X	1	2	3	4	5	6	7	8	9
Y	7	1	2	5	7	34	32	43	87

SELECT Korrelation(X, Y) AUS Tabelle
Ergebnis: 0,860296149

22. RegressionSlope (Regr_Slope) - berechnet die Steigung der Linie. Berechnet nach der Formel: Kovarianz der Gesamtbevölkerung (Y, X) / Varianz der Gesamtbevölkerung (X). Berechnet ohne Berücksichtigung von Paaren, die NULL enthalten.

Syntax:
RegressionSlope(Y, X)

Optionen :

Y- Nummer eingeben;
X- Nummerntyp.

Beispiel:

X	1	2	3	4	5	6	7	8	9
Y	7	1	2	5	7	34	32	43	87

SELECT RegressionSlope(Y, X) FROM Table
Ergebnis: 8.91666667

23. RegressionIntercept (Regr_Intercept) - berechnet den Y-Schnittpunkt der Regressionsgeraden. Berechnet mit der Formel: Mittelwert(Y) – RegressionSteigung(Y, X) * Mittelwert(X). Berechnet ohne Berücksichtigung von Paaren, die NULL enthalten.

Syntax:
RegressionSegment(Y, X)

Optionen :

Y- Nummer eingeben;
X- Nummerntyp.

Beispiel:
SELECT RegressionCount(Y, X) FROM Table
Ergebnis: 9

25. RegressionR2 (Regr_R2) - berechnet das Bestimmtheitsmaß. Berechnet ohne Berücksichtigung von Paaren, die NULL enthalten.

Syntax:
RegressionR2(Y, X)

Optionen :

Y- Nummer eingeben;
X- Nummerntyp.

Rückgabewert:

Null – wenn Varianz der Gesamtbevölkerung (X) = 0;
1 – wenn Varianz der Gesamtbevölkerung (Y) = 0 UND Varianz der Gesamtbevölkerung (X)<>0;
POW(Korrelation(Y,X),2) – wenn die Varianz der allgemeinen Grundgesamtheit (Y) > 0 UND die Varianz der allgemeinen Grundgesamtheit (X)<>0.

Beispiel:

X	1	2	3	4	5	6	7	8	9
Y	7	1	2	5	7	34	32	43	87

SELECT RegressionR2(Y, X) FROM Table
Ergebnis: 0,740109464

26. RegressionAverageX (Regr_AvgX) – Berechnet den Durchschnitt von X nach Eliminierung von X- und Y-Paaren, bei denen entweder X oder Y leer ist. Average(X) wird ohne Berücksichtigung von Paaren berechnet, die NULL enthalten.

Syntax:
RegressionAverageX(Y, X)

Optionen :

Y- Nummer eingeben;
X- Nummerntyp.

Beispiel:

X	1	2	3	4	5	6	7	8	9
Y	7	1	2	5	7	34	32	43	87

SELECT RegressionMeanX(Y, X) FROM Table
Ergebnis: 5

27. RegressionAverageY (Regr_AvgY) – Berechnet den Durchschnitt von Y nach Eliminierung von X- und Y-Paaren, bei denen entweder X oder Y leer ist. Average(Y) wird ohne Berücksichtigung von Paaren berechnet, die NULL enthalten.

Syntax:
RegressionAverageY(Y, X)

Optionen :

Y- Nummer eingeben;
X- Nummerntyp.

Beispiel:

X	1	2	3	4	5	6	7	8	9
Y	7	1	2	5	7	34	32	43	87

SELECT RegressionMeanY(Y, X) FROM Table
Ergebnis: 24.2222222

28. RegressionSXX (Regr_SXX) - berechnet nach der Formel: RegressionQuantity(Y, X) * Streuung der Gesamtbevölkerung(X). Berechnet ohne Berücksichtigung von Paaren, die NULL enthalten.

Syntax:
RegressionSXX(Y, X)

Optionen :

Y- Nummer eingeben;
X- Nummerntyp.

Gibt die Summe der Quadrate der unabhängigen Ausdrücke zurück, die in einem linearen Regressionsmodell verwendet werden. Die Funktion kann verwendet werden, um die statistische Gültigkeit eines Regressionsmodells zu bewerten.

Beispiel:
SELECT RegressionSYY(Y, X) FROM Table
Ergebnis: 6445.55556

30. RegressionSXY (Regr_SXY) - berechnet nach der Formel: RegressionQuantity(Y, X) * Kovarianz der Gesamtpopulation(Y, X). Berechnet ohne Berücksichtigung von Paaren, die NULL enthalten.

Syntax:
RegressionSXY(Y,X)

Optionen :

Y- Nummer eingeben;
X- Nummerntyp.

Beispiel:

X	1	2	3	4	5	6	7	8	9
Y	7	1	2	5	7	34	32	43	87

SELECT RegressionSXY(Y, X) FROM Table
Ergebnis: 535

31. Rang

Syntax:
PlaceInOrder(Order, HierarchyOrder, GroupName)

Optionen :

Befehl– Geben Sie String ein. Enthält Ausdrücke, in deren Reihenfolge Sie Gruppendatensätze anordnen möchten, durch Kommas getrennt. Die Sortierrichtung wird über die Wörter Aufsteigend, Absteigend gesteuert. Sie können dem Feld auch die automatische Reihenfolge folgen, was bedeutet, dass Sie beim Bestellen von Links die für das referenzierte Objekt definierten Bestellfelder verwenden müssen. Wenn keine Reihenfolge angegeben ist, wird der Wert in der Gruppierungsreihenfolge berechnet.
OrderHierarchy– Geben Sie String ein. Enthält Ordnungsausdrücke für hierarchische Datensätze;
Gruppenname– Geben Sie String ein. Der Name der Gruppierung, in der die Verarbeitungsgruppierung berechnet werden soll. Wenn nicht angegeben, erfolgt die Berechnung in der aktuellen Gruppierung. Wenn die Berechnung in einer Tabelle durchgeführt wird und der Parameter einen leeren String enthält oder nicht angegeben ist, wird der Wert für die Gruppierung berechnet – der String. Der Layout-Composer ersetzt beim Generieren eines Datenlayout-Layouts diesen Namen durch den Namen der Gruppierung im resultierenden Layout. Wenn keine Gruppierung verfügbar ist, wird die Funktion durch einen NULL-Wert ersetzt.

Wenn in einer Sequenz zwei oder mehr Datensätze mit denselben Sortierfeldwerten vorhanden sind, gibt die Funktion für alle Datensätze dieselben Werte zurück.

Beispiel:
PlaceInOrder("[Anzahl der Umsätze]")

32. KlassifizierungABC

Syntax:
ClassificationABC(Wert, Anzahl der Gruppen, PercentageForGroups, GroupName)

Optionen :

Bedeutung– Geben Sie String ein. nach der die Klassifizierung berechnet werden muss. Eine Zeile, die den Ausdruck enthält;
Anzahl der Gruppen- Nummerntyp. Gibt die Anzahl der Gruppen an, in die unterteilt werden soll.
ProzentsatzFürGruppen- Geben Sie String ein. Bis zu minus 1 müssen in Gruppen unterteilt werden. Getrennt durch Kommas. Wenn nicht angegeben, dann automatisch;
Gruppenname- Geben Sie String ein. Der Name der Gruppierung, in der die Verarbeitungsgruppierung berechnet werden soll. Wenn nicht angegeben, erfolgt die Berechnung in der aktuellen Gruppierung. Wenn die Berechnung in einer Tabelle durchgeführt wird und der Parameter einen leeren String enthält oder nicht angegeben ist, wird der Wert für die Gruppierung berechnet – der String. Der Layout-Composer ersetzt beim Generieren eines Datenlayout-Layouts diesen Namen durch den Namen der Gruppierung im resultierenden Layout. Wenn keine Gruppierung verfügbar ist, wird die Funktion durch einen NULL-Wert ersetzt.

Das Ergebnis der Funktion ist die Klassennummer, beginnend bei 1, die der Klasse A entspricht.

Beispiel:
KlassifizierungABC("Amount(GrossProfit)", 3, "60, 90")

SQL hat zusätzliche Funktionen hinzugefügt, mit denen Sie verallgemeinerte Gruppenwerte berechnen können. Um Aggregatfunktionen anzuwenden, wird eine vorläufige Gruppierungsoperation angenommen. Beim Gruppieren wird die gesamte Menge an Tupeln einer Relation in Gruppen unterteilt, in denen Tupel gesammelt werden, die dieselben Attributwerte haben, die in der Gruppierungsliste angegeben sind.

Dies geschieht mithilfe von Aggregatfunktionen. Aggregatfunktionen berechnen einen einzelnen Wert für eine gesamte Tabellengruppe. Eine Liste dieser Funktionen ist in der Tabelle dargestellt. 7.

SELECT R1.Discipline, COUNT(*)FROM R1GROUP BY R1.Discipline

Ergebnis:

Beispiel. Erhalten Sie eine Liste der Disziplinen, in denen mindestens 5 Personen die Prüfung bestanden haben:

SELECT R1.DisciplineFROM R1GROUP BY R1.DisciplineHAVING COUNT(*) >= 5 Ergebnis: Hier wählt die HAVING-Anweisung Gruppen aus, die die gegebene Bedingung erfüllen.

Verschachtelte Abfragen

Verhältnis D (Details)

PD-Verhältnis (Angebot)

1. Erhalten Sie eine Liste von Lieferanten, deren Status unter dem maximalen Status in der Lieferantentabelle liegt (Vergleich mit einer Unterabfrage):

WO P.STATYS<

(MAX(P.STATUS) AUSWÄHLEN

Kommentar. Weil Wird das Feld P.STATUS mit dem Ergebnis der Unterabfrage verglichen, muss die Unterabfrage so formuliert werden, dass sie eine Tabelle zurückgibt, die besteht genau eine Zeile und eine Spalte.

Kommentar

Ausführen einmal verschachtelte Unterabfrage und erhalten Sie den maximalen Statuswert.
Durchsuchen Sie die Lieferantentabelle P, vergleichen Sie jedes Mal den Wert des Lieferantenstatus mit dem Ergebnis der Unterabfrage und wählen Sie nur die Zeilen aus, in denen der Status kleiner als das Maximum ist.

2. Verwendung eines Prädikats IN

(SELECT DISTINCT PD.PNUM

WO PD.DNUM = 2);

Kommentar. In diesem Fall kann eine verschachtelte Unterabfrage eine Tabelle mit mehreren Zeilen zurückgeben.

Kommentar. Das Ergebnis der Ausführung der Anfrage entspricht dem Ergebnis der folgenden Aktionsfolge:

Ausführen einmal verschachtelte Unterabfrage und erhalten Sie eine Liste der Lieferantennummern, die Teilenummer 2 liefern.
Scannen Sie die Lieferantentabelle P und prüfen Sie jedes Mal, ob die Lieferantennummer im Ergebnis der Unterabfrage enthalten ist.

3. Verwendung eines Prädikats EXISTIERT . Erhalten Sie eine Liste der Lieferanten, die Teil Nr. 2 liefern:

PD.PNUM = P.PNUM UND

Kommentar. Das Ergebnis der Ausführung der Anfrage entspricht dem Ergebnis der folgenden Aktionsfolge:

Lieferantentabelle P scannen, jedes Mal, wenn Sie eine Unterabfrage ausführen mit dem neuen Wert der Lieferantennummer aus Tabelle P.
Beziehen Sie in das Abfrageergebnis nur die Zeilen aus der Lieferantentabelle ein, für die die verschachtelte Unterabfrage einen nicht leeren Satz von Zeilen zurückgegeben hat.

Kommentar. Im Gegensatz zu den beiden vorherigen Beispielen enthält die verschachtelte Unterabfrage einen Parameter (externer Link), der von der Hauptanfrage übergeben wird – die Anbieternummer P.PNUM. Solche Unterabfragen werden aufgerufen korreliert (korreliert ). Eine externe Referenz kann für jede von der Unterabfrage ausgewertete Kandidatenzeile einen anderen Wert annehmen, daher muss die Unterabfrage für jede in der Hauptabfrage ausgewählte Zeile erneut ausgeführt werden. Solche Unterabfragen sind typisch für das EXISTS-Prädikat, können aber auch in anderen Unterabfragen verwendet werden.

Kommentar. Es scheint, dass Abfragen mit korrelierten Unterabfragen langsamer sind als Abfragen mit nicht korrelierten Unterabfragen. Tatsächlich ist dies nicht der Fall, denn... die Art und Weise, wie der Benutzer die Anfrage formuliert hat, definiert nicht wie diese Anfrage ausgeführt wird. Die SQL-Sprache ist nicht prozedural, sondern deklarativ. Das bedeutet, dass der Benutzer, der die Anfrage formuliert, lediglich beschreibt, Was soll das Ergebnis der Abfrage sein?, und wie dieses Ergebnis erzielt wird, liegt in der Verantwortung des DBMS selbst.

4. Verwendung eines Prädikats EXISTIERT NICHT . Erhalten Sie eine Liste der Lieferanten, die Teil Nr. 2 nicht liefern:

WO NICHT EXISTIERT

PD.PNUM = P.PNUM UND

Kommentar. Genau wie im vorherigen Beispiel wird hier eine Unterabfrage verwendet. Der Unterschied besteht darin, dass die Hauptabfrage diejenigen Zeilen aus der Lieferantentabelle auswählt, für die die verschachtelte Unterabfrage keine einzige Zeile zurückgibt.

5. Ermitteln Sie die Namen der Lieferanten, die alle Teile liefern:

WÄHLEN SIE EINDEUTIGEN PNAME

WO NICHT EXISTIERT

PD.DNUM = D.DNUM UND

PD.PNUM = P.PNUM));

Kommentar. Diese Abfrage enthält zwei verschachtelte Unterabfragen und implementiert eine relationale Operation Aufteilung der Beziehungen.

Die innerste Unterabfrage wird durch zwei Parameter (D.DNUM, P.PNUM) parametrisiert und hat folgende Bedeutung: Auswahl aller Zeilen, die Daten zu Lieferungen des Lieferanten mit der PNUM-Nummer des Teils mit der DNUM-Nummer enthalten. Die Verneinung von NOT EXISTS zeigt an, dass der Lieferant das Teil nicht liefert. Die dazu externe Unterabfrage, die selbst ein verschachtelter und parametrisierter Parameter P.PNUM ist, macht Sinn: um eine Liste von Teilen auszuwählen, die nicht vom PNUM-Lieferanten geliefert werden. Die Negation von NOT EXISTS gibt an, dass es für einen Lieferanten mit einer PNUM-Nummer keine Teile geben sollte, die nicht von diesem Lieferanten geliefert werden. Das bedeutet genau, dass die externe Abfrage nur die Lieferanten auswählt, die alle Teile liefern.

Externe Verbindungen

Häufig ist es erforderlich, Tabellen so zu verknüpfen, dass das Ergebnis alle Zeilen der ersten Tabelle enthält und anstelle der Zeilen der zweiten Tabelle, für die die Join-Bedingung nicht erfüllt ist, undefinierte Werte im Ergebnis enthalten sind. Oder umgekehrt: Alle Zeilen der rechten (zweiten) Tabelle werden einbezogen und die fehlenden Zeilenteile der ersten Tabelle werden durch undefinierte Werte ergänzt. Solche Assoziationen wurden als extern bezeichnet.

Im Allgemeinen lautet die Syntax für den FROM-Teil im SQL2-Standard wie folgt:

AUS<список исходных таблиц>< выражение естественного соединения >< выражение соединения >< выражение перекрестного соединения >< выражение запроса на объединение ><список исходных таблиц>::= <имя_таблицы_1>[ table_1 Synonymname] [ …] [,<имя_таблицы_n>[ <имя синонима таблицы_n> ] ]<выражение естественного соединения>:: =<имя_таблицы_1>NATÜRLICH (INNEN | VOLLSTÄNDIG LINKS | RECHTS) VERBINDEN<имя_таблицы_2><выражение перекрестного соединения>:: = <имя_таблицы_1>KREUZVERBINDUNG<имя_таблицы_2><выражение запроса на объединение>::=<имя_таблицы_1>GEWERKSCHAFTSTRETEN<имя_таблицы_2><выражение соединения>::= <имя_таблицы_1>( INNERFULL | LEFT | RIGHT ) JOIN ( ON-Bedingung | )<имя_таблицы_2>

In diesen Definitionen bedeutet INNER einen internen (natürlichen) Join, LEFT bedeutet einen linken Join, das heißt, das Ergebnis umfasst alle Zeilen von Tabelle 1 und Teile der resultierenden Tupel, für die es in Tabelle 2 keine entsprechenden Werte gab ergänzt durch NULL-Werte (undefiniert). Das Schlüsselwort RIGHT bedeutet einen rechten äußeren Join. Im Gegensatz zu einem linken Join werden in diesem Fall alle Zeilen von Tabelle 2 in die resultierende Beziehung einbezogen und die fehlenden Teile von Tabelle 1 werden mit Nullwerten ergänzt. Das Schlüsselwort FULL gibt einen vollständigen Outer-Join an: sowohl links als auch rechts. Bei einem vollständigen Outer-Join werden sowohl rechte als auch linke Outer-Joins durchgeführt, und die resultierende Beziehung umfasst alle Zeilen aus Tabelle 1, aufgefüllt mit Nullen, und alle Zeilen aus Tabelle 2, ebenfalls aufgefüllt mit Nullen.

Das Schlüsselwort OUTER bedeutet außen, aber wenn die Schlüsselwörter FULL, LEFT, RIGHT angegeben sind, wird die Verbindung immer als außen betrachtet.

Schauen wir uns Beispiele für die Durchführung externer Verknüpfungen an. Kehren wir zur Datenbank „Session“ zurück. Erstellen wir eine Beziehung, in der alle Noten stehen, die alle Schüler in allen Prüfungen erhalten haben, die sie ablegen mussten. Wenn ein Student diese Prüfung nicht bestanden hat, erhält er anstelle einer Note einen unbestimmten Wert. Dazu führen wir einen sequenziellen natürlichen internen Join der Tabellen R2 und R3 mithilfe des Gruppenattributs durch und verbinden die resultierende Beziehung mit einem linken externen natürlichen Join mit Tabelle R1 mithilfe der Spalten „Vollständiger Name“ und „Disziplin“. Der Standard erlaubt jedoch die Verwendung einer Klammerstruktur, da das Ergebnis der Verbindung eines der Argumente im FROM-Teil der SELECT-Anweisung sein kann.

SELECT R1.Name, R1.Discipline, R1.RatingFROM (R2 NATURAL INNER JOIN R3) LEFT JOIN R1 USING (Name, Disziplin)

Ergebnis:

Vollständiger Name	Disziplin	Grad
Petrov F.I.	Datenbank
Sidorov K. A.	Datenbank
Mironov A.V.	Datenbank
Stepanowa K. E.	Datenbank
Krylova T. S.	Datenbank
Vladimirov V. A.	Datenbank
Petrov F.I.	Informationstheorie	Null
Sidorov K. A.	Informationstheorie
Mironov A.V.	Informationstheorie	Null
Stepanowa K. E.	Informationstheorie
Krylova T. S.	Informationstheorie

Viele Datenbankabfragen erfordern nicht den Granularitätsgrad, den die in den vorherigen Beispielen besprochenen SQL-Abfragen bieten. Bei allen unten aufgeführten Abfragen müssen Sie also nur einen oder mehrere Werte ermitteln, die die in der Datenbank enthaltenen Informationen zusammenfassen:

1) Wie hoch ist das Einkommen aller Einwohner?
2) Was ist das höchste und niedrigste Gesamteinkommen eines einzelnen Einwohners?
3) Wie hoch ist das durchschnittliche Pro-Kopf-Einkommen eines Einwohners von Selenograd?
4) Wie hoch ist das durchschnittliche Pro-Kopf-Einkommen der Bewohner jeder Wohnung?
5) Wie viele Bewohner gibt es in jeder Wohnung?

In SQL können diese Arten von Abfragen mithilfe von Aggregatfunktionen und den in der SELECT-Anweisung verwendeten GROUP BY- und HAVING-Klauseln erstellt werden.

Verwenden von Aggregatfunktionen

Um die in der Datenbank enthaltenen Informationen zusammenzufassen, stellt SQL Aggregatfunktionen bereit. Eine Aggregatfunktion verwendet eine ganze Datenspalte als Argument und gibt einen einzelnen Wert zurück, der diese Spalte auf eine bestimmte Weise zusammenfasst.

Beispielsweise nimmt die Aggregatfunktion AVG() eine Zahlenspalte als Argument und berechnet deren Durchschnitt.

Um das durchschnittliche Pro-Kopf-Einkommen eines Einwohners von Selenograd zu berechnen, benötigen Sie die folgende Abfrage:

WÄHLEN Sie „DURCHSCHNITTLICHES PRO-KOPF-EINKOMMEN, AVG(SUMD) VON PERSON.“

SQL verfügt über sechs Aggregatfunktionen, mit denen Sie verschiedene Arten von zusammenfassenden Informationen erhalten können (Abbildung 3.16):

SUM() berechnet die Summe aller in einer Spalte enthaltenen Werte;

AVG() berechnet den Durchschnitt der in einer Spalte enthaltenen Werte;

- MIN() findet den kleinsten aller in der Spalte enthaltenen Werte;
- MAX() findet den größten aller in der Spalte enthaltenen Werte;
- COUNT() zählt die Anzahl der in einer Spalte enthaltenen Werte;

COUNT(*) zählt die Anzahl der Zeilen in der Abfrageergebnistabelle.

Das Argument einer Aggregatfunktion kann ein einfacher Spaltenname sein, wie im vorherigen Beispiel, oder ein Ausdruck, wie in der folgenden Abfrage, der die Berechnung der durchschnittlichen Pro-Kopf-Steuer angibt:

SELECT AVG(SUMD*0,13)

Reis. 3.16.

Diese Abfrage erstellt eine temporäre Spalte mit den Werten (SUMD*0,13) für jede Zeile der PERSON-Tabelle und berechnet dann den Durchschnitt der temporären Spalte.

Die Höhe des Einkommens für alle Einwohner von Selenograd kann mit der Aggregatfunktion SUM berechnet werden:

WÄHLEN SIE SUM(SUMD) VON PERSON

Eine Aggregatfunktion kann auch verwendet werden, um Gesamtsummen aus einer Ergebnistabelle zu berechnen, die durch die Verknüpfung mehrerer Quelltabellen entsteht. Sie können beispielsweise den Gesamtbetrag des Einkommens berechnen, das die Bewohner aus einer Quelle namens „Stipendium“ erhalten haben:

SUMME (GELD) WÄHLEN

AUS GEWINN, HAVE_D

WHERE PROFIT.ID=HAVE_D.ID

AND PROFIT.SOURCE^Stipendium‘

Mit den Aggregatfunktionen MIN() und MAX() können Sie den kleinsten bzw. größten Wert in einer Tabelle ermitteln. Die Spalte kann numerische oder Zeichenfolgewerte oder Datums- oder Uhrzeitwerte enthalten.

Sie können beispielsweise Folgendes definieren:

(a) das niedrigste Gesamteinkommen der Einwohner und die höchste zu zahlende Steuer:

WÄHLEN SIE MIN(SUMD), MAX(SUMD*0,13)

(b) Geburtsdaten des ältesten und jüngsten Bewohners:

WÄHLEN SIE MIN(RDATE), MAX(RDATE)

WÄHLEN SIE MIN(FIO), MAX(FIO)

Bei der Verwendung dieser Aggregatfunktionen müssen Sie bedenken, dass numerische Daten mithilfe arithmetischer Regeln verglichen werden, Datumsangaben sequentiell verglichen werden (frühere Datumswerte gelten als kleiner als spätere) und Zeitintervalle basierend auf ihrer Dauer verglichen werden.

Bei Verwendung der Funktionen MIN() und MAX() mit String-Daten hängt das Ergebnis des Vergleichs zweier Strings von der verwendeten Zeichenkodierungstabelle ab.

Die Aggregatfunktion COUNT() zählt die Anzahl der Werte in einer Spalte beliebigen Typs:

(a) Wie viele Wohnungen gibt es im 1. Mikrobezirk?

ZÄHLER AUSWÄHLEN (ADR)

WO ADR LIKE *%, 1_

(b) Wie viele Einwohner haben Einkommensquellen?

ANZAHL AUSWÄHLEN (EINDEUTIGE NOM)

ANZAHL AUSWÄHLEN (EINDEUTIGE ID)

Das Schlüsselwort „DISTINCT“ gibt an, dass nicht doppelte Werte in einer Spalte gezählt werden.

Die spezielle Aggregatfunktion COUNT(*) zählt die Zeilen in der Ergebnistabelle, nicht die Datenwerte:

(a) Wie viele Wohnungen gibt es im 2. Mikrobezirk?

WO ADR LIKE „%, 2_-%’

(b) Wie viele Einnahmequellen hat Iwan Iwanowitsch Iwanow?

VON PERSON, HAVE_D

WO FIO = „Iwanow Iwan Iwanowitsch“

AND PERSON.NOM = HAVE_D.NOM

SELECT COUNT(*) FROM PERSON WHERE ADR = „Zelenograd, 1001-45’

Eine Möglichkeit zu verstehen, wie Zusammenfassungsabfragen mit Aggregatfunktionen ausgeführt werden, besteht darin, sich die Abfrageausführung in zwei Teile aufzuteilen. Zunächst bestimmen wir, wie die Abfrage ohne Aggregatfunktionen funktionieren würde und mehrere Ergebniszeilen zurückgibt. Anschließend werden Aggregatfunktionen auf die Abfrageergebnisse angewendet, die eine einzelne Ergebniszeile zurückgeben.

Betrachten Sie beispielsweise die folgende komplexe Abfrage: Ermitteln Sie das durchschnittliche Pro-Kopf-Gesamteinkommen, die Summe des Gesamteinkommens der Einwohner und den durchschnittlichen Quellenertrag als Prozentsatz des Gesamteinkommens des Einwohners. Der Betreiber gibt die Antwort

SELECT AVG(SUMD), SUM(SUMD), (100*AVG(MONEY/SUMD))

FROM PERSON, PROFIT, HAVE_D WHERE PERSON.NOM = HAVE_D.NOM AND HAVE_D.ID = PROFIT.ID

Ohne Aggregatfunktionen würde die Abfrage so aussehen:

WÄHLEN SIE SUMD, SUMD, M0NEY/SUMD AUS PERSON, PROFIT, HAVE_D, WO PERSON.NOM = HAVE_D.NOM UND HAVE_D.ID = PROFIT.ID

und würde eine Ergebniszeile für jeden Einwohner und jede bestimmte Einkommensquelle zurückgeben. Aggregatfunktionen verwenden die Spalten der Ergebnistabelle der Abfrage, um eine einzeilige Tabelle mit den zusammenfassenden Ergebnissen zu erstellen.

Sie können in der zurückgegebenen Spaltenzeile anstelle eines beliebigen Spaltennamens eine Aggregatfunktion angeben. Es kann beispielsweise Teil eines Ausdrucks sein, der die Werte zweier Aggregatfunktionen addiert oder subtrahiert:

MAX(SUMD)-MIN(SUMD) AUSWÄHLEN

Eine Aggregatfunktion kann jedoch kein Argument für eine andere Aggregatfunktion sein, d. h. Verschachtelte Aggregatfunktionen sind verboten.

Außerdem kann die Liste der zurückgegebenen Spalten nicht gleichzeitig Aggregatfunktionen und reguläre Spaltennamen verwenden, da dies keinen Sinn ergibt, zum Beispiel:

SELECT FIO, SUM(SUMD)

Hier weist das erste Element der Liste das DBMS an, eine Tabelle zu erstellen, die aus mehreren Zeilen besteht und eine Zeile für jeden Bewohner enthält. Das zweite Element der Liste fordert das DBMS auf, einen einzelnen Ergebniswert zu erhalten, der die Summe der Werte in der SUMD-Spalte ist. Diese beiden Anweisungen widersprechen einander, was zu einem Fehler führt.

Das Vorstehende gilt nicht für die Verarbeitung von Unterabfragen und Abfragen mit Gruppierung.

Beispiel 21. Gesamtzahl der Lieferanten abrufen (Stichwort ZÄHLEN ):

WÄHLEN SIE COUNT(*) ALS N

Das Ergebnis ist eine Tabelle mit einer Spalte und einer Zeile, die die Anzahl der Zeilen aus Tabelle P enthält:

Verwendung von Aggregatfunktionen mit Gruppierungen

Beispiel 23 . Ermitteln Sie für jedes Teil die gelieferte Gesamtmenge (Stichwort GRUPPIERE NACH …):

SUM(PD.VOLUME) AS SM

GRUPPE NACH PD.DNUM;

Diese Anfrage wird wie folgt ausgeführt. Zunächst werden die Zeilen der Quelltabelle so gruppiert, dass jede Gruppe Zeilen mit denselben DNUM-Werten enthält. Dann wird innerhalb jeder Gruppe das VOLUME-Feld summiert. Eine Zeile aus jeder Gruppe wird in die resultierende Tabelle aufgenommen:

Kommentar. In die Liste der ausgewählten Felder einer SELECT-Anweisung, die einen GROUP BY-Abschnitt enthält, können Sie Folgendes aufnehmen nur Aggregatfunktionen und -felder, die in der Gruppierungsbedingung enthalten sind. Die folgende Abfrage löst einen Syntaxfehler aus:

SUM(PD.VOLUME) AS SM

GRUPPE NACH PD.DNUM;

Der Grund für den Fehler liegt darin, dass die Liste der ausgewählten Felder das Feld PNUM enthält Ausgeschlossen zum Abschnitt GROUP BY. Tatsächlich kann jede resultierende Zeilengruppe mehrere Zeilen mit enthalten verschieden Werte des PNUM-Feldes. Aus jeder Zeilengruppe wird eine Gesamtzeile generiert. Allerdings gibt es keine eindeutige Antwort auf die Frage, welchen Wert man für das PNUM-Feld in der letzten Zeile wählen soll.

Kommentar. Einige SQL-Dialekte betrachten dies nicht als Fehler. Die Abfrage wird ausgeführt, es ist jedoch nicht vorhersehbar, welche Werte in das PNUM-Feld der resultierenden Tabelle eingegeben werden.

Beispiel 24 . Erhalten Sie Teilenummern, deren gesamte Liefermenge 400 übersteigt (Stichwort HABEN …):

Kommentar. Die Bedingung, dass die Gesamtliefermenge größer als 400 sein muss, kann in der WHERE-Klausel nicht formuliert werden, weil In diesem Abschnitt können Sie keine Aggregatfunktionen verwenden. Bedingungen, die Aggregatfunktionen verwenden, sollten in einem speziellen HAVING-Abschnitt platziert werden:

SUM(PD.VOLUME) AS SM

GRUPPE NACH PD.DNUM

MIT SUM(PD.VOLUME) > 400;

Als Ergebnis erhalten wir die folgende Tabelle:

Kommentar. Eine Abfrage kann sowohl Zeilenauswahlbedingungen im WHERE-Abschnitt als auch Gruppenauswahlbedingungen im HAVING-Abschnitt enthalten. Gruppenauswahlbedingungen können nicht vom Abschnitt HAVING in den Abschnitt WHERE verschoben werden. Ebenso können Zeilenauswahlbedingungen nicht vom Abschnitt WHERE in den Abschnitt HAVING verschoben werden, mit Ausnahme von Bedingungen, die Felder aus der Gruppierungsliste GROUP BY enthalten.

Unterabfragen verwenden

Ein sehr praktisches Tool, mit dem Sie Abfragen verständlicher formulieren können, ist die Möglichkeit, in der Hauptabfrage verschachtelte Unterabfragen zu verwenden.

Beispiel 25 . Erhalten Sie eine Liste von Lieferanten, deren Status unter dem maximalen Status in der Lieferantentabelle liegt (Vergleich mit einer Unterabfrage):

WO P.STATYS<

(MAX(P.STATUS) AUSWÄHLEN

Kommentar

Ausführen einmal verschachtelte Unterabfrage und erhalten Sie den maximalen Statuswert.

Durchsuchen Sie die Lieferantentabelle P, vergleichen Sie jedes Mal den Wert des Lieferantenstatus mit dem Ergebnis der Unterabfrage und wählen Sie nur die Zeilen aus, in denen der Status kleiner als das Maximum ist.

Beispiel 26 . Verwendung eines Prädikats IN

(SELECT DISTINCT PD.PNUM

WO PD.DNUM = 2);

Kommentar. In diesem Fall kann eine verschachtelte Unterabfrage eine Tabelle mit mehreren Zeilen zurückgeben.

Kommentar. Das Ergebnis der Ausführung der Anfrage entspricht dem Ergebnis der folgenden Aktionsfolge:

Ausführen einmal verschachtelte Unterabfrage und erhalten Sie eine Liste der Lieferantennummern, die Teilenummer 2 liefern.

Scannen Sie die Lieferantentabelle P und prüfen Sie jedes Mal, ob die Lieferantennummer im Ergebnis der Unterabfrage enthalten ist.

Beispiel 27 . Verwendung eines Prädikats EXISTIEREN . Erhalten Sie eine Liste der Lieferanten, die Teil Nr. 2 liefern:

PD.PNUM = P.PNUM UND

Kommentar. Das Ergebnis der Ausführung der Anfrage entspricht dem Ergebnis der folgenden Aktionsfolge:

Lieferantentabelle P scannen, jedes Mal, wenn Sie eine Unterabfrage ausführen mit dem neuen Wert der Lieferantennummer aus Tabelle P.

Beziehen Sie in das Abfrageergebnis nur die Zeilen aus der Lieferantentabelle ein, für die die verschachtelte Unterabfrage einen nicht leeren Satz von Zeilen zurückgegeben hat.

Kommentar. Im Gegensatz zu den beiden vorherigen Beispielen enthält die verschachtelte Unterabfrage einen Parameter (externer Link), der von der Hauptanfrage übergeben wird – die Anbieternummer P.PNUM. Solche Unterabfragen werden aufgerufen korreliert (korreliert ). Eine externe Referenz kann für jede von der Unterabfrage ausgewertete Kandidatenzeile einen anderen Wert annehmen, daher muss die Unterabfrage für jede in der Hauptabfrage ausgewählte Zeile erneut ausgeführt werden. Solche Unterabfragen sind typisch für das EXIST-Prädikat, können aber auch in anderen Unterabfragen verwendet werden.

Beispiel 28 . Verwendung eines Prädikats NICHT EXISTIEREN . Erhalten Sie eine Liste der Lieferanten, die Teil Nr. 2 nicht liefern:

PD.PNUM = P.PNUM UND

Kommentar. Genau wie im vorherigen Beispiel wird hier eine korrelierte Unterabfrage verwendet. Der Unterschied besteht darin, dass die Hauptabfrage diejenigen Zeilen aus der Lieferantentabelle auswählt, für die die verschachtelte Unterabfrage keine einzige Zeile zurückgibt.

Beispiel 29 . Erhalten Sie die Namen der Lieferanten, die alle Teile liefern:

WÄHLEN SIE EINDEUTIGEN PNAME

PD.DNUM = D.DNUM UND

PD.PNUM = P.PNUM));

Kommentar. Diese Abfrage enthält zwei verschachtelte Unterabfragen und implementiert eine relationale Operation Aufteilung der Beziehungen.

Die innerste Unterabfrage wird durch zwei Parameter (D.DNUM, P.PNUM) parametrisiert und hat folgende Bedeutung: Auswahl aller Zeilen, die Daten zu Lieferungen des Lieferanten mit der PNUM-Nummer des Teils mit der DNUM-Nummer enthalten. Die Verneinung NOT EXIST zeigt an, dass dieser Lieferant dieses Teil nicht liefert. Die dazu externe Unterabfrage, die selbst ein verschachtelter und parametrisierter Parameter P.PNUM ist, macht Sinn: um eine Liste von Teilen auszuwählen, die nicht vom PNUM-Lieferanten geliefert werden. Die Negation NOT EXIST gibt an, dass es für einen Lieferanten mit einer PNUM-Nummer keine Teile geben soll, die nicht von diesem Lieferanten geliefert werden. Das bedeutet genau, dass die externe Abfrage nur die Lieferanten auswählt, die alle Teile liefern.