Fonctions agrégées du système de composition des données. Utilisation de fonctions d'agrégation et de requêtes imbriquées dans une instruction Select Jointures externes SQL

SQL a ajouté des fonctions supplémentaires qui vous permettent de calculer des valeurs de groupe généralisées. Pour appliquer les fonctions d'agrégation, une opération de regroupement préalable est supposée. Quelle est l’essence de l’opération de regroupement ? Lors du regroupement, l'ensemble des tuples d'une relation est divisé en groupes dans lesquels sont collectés les tuples ayant les mêmes valeurs d'attribut que celles spécifiées dans la liste de regroupement.

Par exemple, regroupons la relation R1 par la valeur de la colonne Discipline. Nous obtiendrons 4 groupes pour lesquels nous pourrons calculer certaines valeurs de groupe, telles que le nombre de tuples dans le groupe, la valeur maximale ou minimale de la colonne Score.

Cela se fait à l'aide de fonctions d'agrégation. Les fonctions d'agrégation calculent une valeur unique pour un groupe de tables entier. Une liste de ces fonctions est présentée dans le tableau 5.7.

Tableau 5.7.Fonctions d'agrégation


R1
	Nom et prénom	Discipline	Grade
Groupe 1	Petrov F.I.	Base de données
	Sidorov K.A.	Base de données
	Mironov A.V.	Base de données
	Stepanova K.E.	Base de données
	Krylova T.S.	Base de données
	Vladimirov V.A.	Base de données
Groupe 2	Sidorov K.A.	Théorie de l'information
	Stepanova K.E.	Théorie de l'information
	Krylova T.S.	Théorie de l'information
	Mironov A.V.	Théorie de l'information	Nul
Groupe 3	Trofimov P.A.	Réseaux et télécommunications
	Ivanova E.A.	Réseaux et télécommunications
	Utkina N.V.	Réseaux et télécommunications
Groupe 4	Vladimirov V.A.	langue anglaise
	Trofimov P.A.	langue anglaise
	Ivanova E.A.	langue anglaise
	Petrov F.I.	langue anglaise	je

Les fonctions d'agrégation sont utilisées de la même manière que les noms de champ dans une instruction SELECT, à une exception près : elles prennent le nom du champ comme argument. Seuls les champs numériques peuvent être utilisés avec les fonctions SUM et AVG. Les champs numériques et caractères peuvent être utilisés avec les fonctions COUNT, MAX et MIN. Lorsqu'ils sont utilisés avec des champs de caractères, MAX et MIN les traduiront en code ASCII équivalent et les traiteront par ordre alphabétique. Certains SGBD autorisent l'utilisation d'agrégats imbriqués, mais il s'agit d'un écart par rapport à la norme ANSI avec toutes les conséquences qui en découlent.

Par exemple, vous pouvez calculer le nombre d’étudiants ayant passé des examens dans chaque discipline. Pour cela, vous devez lancer une requête regroupée par le champ « Discipline » et afficher comme résultat le nom de la discipline et le nombre de lignes du groupe pour cette discipline. Utiliser le caractère * comme argument de la fonction COUNT signifie compter toutes les lignes du groupe.

SELECT R1.Discipline. COMPTER(*)

GROUPER PAR Discipline R1

Résultat:

Si nous voulons compter le nombre de personnes qui ont réussi l'examen dans n'importe quelle discipline, nous devons alors exclure les valeurs incertaines du ratio d'origine avant de les regrouper. Dans ce cas, la requête ressemblera à ceci :

SELECT R1.Discipline. COMPTER(*)

DE R1 OÙ R1.

L'évaluation n'est pas nulle

GROUPE PAR Rl.Discipline

On obtient le résultat :

Dans ce cas, la ligne avec l'étudiant


Mironov A, V.	Théorie de l'information	Nul

ne sera pas inclus dans l'ensemble des tuples avant le regroupement, donc le nombre de tuples dans le groupe pour la discipline « Théorie de l'information » sera de 1 de moins.

Vous pouvez également utiliser des fonctions d'agrégation sans opération de pré-regroupement, auquel cas la relation entière est traitée comme un seul groupe et pour ce groupe, vous pouvez calculer une valeur par groupe.

En revenant à la base de données « Session » (tableaux Rl, R2, R3), on retrouve le nombre d'examens réussis :

OÙ Score > 2 :

Ceci est bien sûr différent de la sélection d'un champ, car cela renvoie toujours une seule valeur, quel que soit le nombre de lignes du tableau. Les arguments pour agréger les fonctions peuvent être des colonnes de tableau individuelles. Mais pour calculer, par exemple, le nombre de valeurs distinctes d'une certaine colonne dans un groupe, vous devez utiliser le mot-clé DISTINCT avec le nom de la colonne. Calculons le nombre de notes différentes reçues dans chaque discipline :

SELECT Rl.Discipline.

COUNT(DISTINCT R1.Score)

OÙ R1.Evaluation N’EST PAS NULLE

GROUPE PAR Rl.Discipline

Résultat:

Le résultat peut inclure une valeur de champ de regroupement et plusieurs fonctions d'agrégation, et les conditions de regroupement peuvent utiliser plusieurs champs. Dans ce cas, les groupes sont formés selon un ensemble de champs de regroupement spécifiés. Les opérations de fonction d’agrégation peuvent être appliquées pour joindre plusieurs tables sources. Par exemple, posons la question : déterminer pour chaque groupe et chaque discipline le nombre d’étudiants ayant réussi l’examen et la note moyenne dans la discipline.

SELECT R2.Groupe. R1.Discipline. COUNT(*), AVP(Évaluation)

OÙ Rl.nom complet = R2.nom complet ET

Rl.Evaluation N'EST PAS NULLE ET

Rl.Rating > 2

GROUPE PAR R2.Groupe. Rl.Discipline

Résultat:

Nous ne pouvons pas utiliser de fonctions d'agrégation dans la clause WHERE car les prédicats sont évalués en termes d'une seule ligne et les fonctions d'agrégation sont évaluées en termes de groupes de lignes.

La clause GROUP BY vous permet de définir un sous-ensemble de valeurs dans un champ particulier par rapport à un autre champ et d'appliquer une fonction d'agrégation au sous-ensemble. Cela permet de combiner des champs et des fonctions d'agrégation dans une seule clause SELECT. Les fonctions d'agrégation peuvent être utilisées à la fois dans l'expression de sortie des résultats de la ligne SELECT et dans l'expression de la condition de traitement des groupes HAVING formés. Dans ce cas, chaque fonction d'agrégation est calculée pour chaque groupe sélectionné. Les valeurs obtenues à partir du calcul des fonctions d'agrégation peuvent être utilisées pour afficher les résultats correspondants ou pour conditionner la sélection des groupes.

Créons une requête qui affiche les groupes dans lesquels plus d'une mauvaise note a été obtenue dans une discipline aux examens :

SELECT R2.Groupe

OÙ Rl.nom complet = R2.nom complet ET

Rl.Rating = 2

GROUPE PAR R2.Groupe. R1.Discipline

AVOIR compte(*)> 1

À l'avenir, à titre d'exemple, nous travaillerons non pas avec la base de données « Session », mais avec la base de données « Banque », constituée d'une table F, qui stocke la relation F contenant des informations sur les comptes dans les succursales d'une certaine banque :

F= ;

Q = (succursale, ville) ;

puisque sur cette base, il est possible d'illustrer plus clairement le travail avec des fonctions d'agrégation et de regroupement.

Par exemple, supposons que nous souhaitions connaître le solde total des comptes bancaires. Vous pouvez effectuer une requête distincte pour chacun d'eux en sélectionnant SUM(Balance) dans le tableau de chaque branche. GROUP BY, cependant, vous permettra de tous les mettre en une seule commande :

Branche SELECT, SOMME

GROUPER PAR Branche :

GROUP BY applique les fonctions d'agrégation indépendamment pour chaque groupe identifié par la valeur du champ Branche. Un groupe est constitué de lignes avec la même valeur de champ Branche et la fonction SOMME est appliquée séparément pour chacun de ces groupes, c'est-à-dire que le solde total du compte est calculé séparément pour chaque branche. La valeur d'un champ auquel GROUP BY est appliqué n'a, par définition, qu'une seule valeur par groupe de sortie, tout comme le résultat d'une fonction d'agrégation. Par conséquent, nous pouvons combiner un agrégat et un champ en une seule requête. Vous pouvez également utiliser GROUP BY avec plusieurs champs.

Disons que nous aimerions voir uniquement les soldes totaux des comptes qui dépassent 5 000 $. Pour voir les soldes totaux supérieurs à 5 000 $, vous devez utiliser la clause HAVING. La clause HAVING spécifie les critères utilisés pour supprimer certains groupes de la sortie, tout comme la clause WHERE le fait pour les lignes individuelles.

La commande correcte serait :

Branche SELECT, SUM (restant)

GROUPE PAR Branche

AYANT UNE SOMME (Reste) > 5000 ;

Les arguments d'une clause HAVING suivent les mêmes règles que dans une clause SELECT qui utilise GROUP BY. Ils doivent avoir une valeur par groupe de sorties.

La commande suivante sera interdite :

SELECT Branche.SUM (Remainant)

FROM F GROUP BY Branche

HAVINGOpenDate = 27/12/1999 ;

Le champ OpenDate ne peut pas être utilisé dans une clause HAVING car il peut avoir plusieurs valeurs par groupe de sortie. Pour éviter cette situation, la clause HAVING ne doit référencer que les agrégats et champs sélectionnés par GROUP BY. Il existe une manière correcte d'effectuer la requête ci-dessus :

Branche SELECT, SOMME (restant)

OÙDateOuverte = "27/12/1999"

GROUPER PAR Branche ;

La signification de cette requête est la suivante : trouver la somme des soldes de chaque succursale de comptes ouverts au 27 décembre 1999.

Comme indiqué précédemment, HAVING ne peut prendre que des arguments qui ont une valeur par groupe de sortie. En pratique, les références aux fonctions d'agrégation sont les plus courantes, mais les champs sélectionnés à l'aide de GROUP BY sont également valides. Par exemple, nous voulons voir les soldes totaux des comptes des succursales de Saint-Pétersbourg, Pskov et Uryupinsk :

SELECT Branche.SUM (Remainant)

OÙ F.Branche = Q.Branche

GROUPE PAR Branche

AVOIR une succursale À ("Saint-Pétersbourg". "Pskov". "Uryupinsk");

Par conséquent, seules les spécifications des colonnes spécifiées comme colonnes de regroupement dans la clause GROUP BY peuvent être directement utilisées dans les expressions arithmétiques de prédicat incluses dans la clause de sélection de la clause HAVING. Les colonnes restantes ne peuvent être spécifiées que dans les spécifications des fonctions d'agrégation COUNT, SUM, AVG, MIN et MAX, qui dans ce cas calculent une valeur globale pour l'ensemble du groupe de lignes. La situation est similaire avec les sous-requêtes incluses dans les prédicats de la condition de sélection de la section HAVING : si la sous-requête utilise une caractéristique du groupe courant, alors elle ne peut être spécifiée qu'en faisant référence aux colonnes de regroupement.

Le résultat de la clause HAVING est une table groupée contenant uniquement les groupes de lignes pour lesquels la condition de recherche est évaluée à TRUE. En particulier, si une clause HAVING est présente dans une expression de table qui ne contient pas de GROUP BY, alors le résultat de son exécution sera soit une table vide, soit le résultat de l'exécution des sections précédentes de l'expression de table, traitée comme une seule. groupe sans regrouper les colonnes.

Requêtes SQL imbriquées

Revenons maintenant à la base de données « Session » et regardons son exemple d'utilisation de requêtes imbriquées.

Avec SQL, vous pouvez imbriquer des requêtes les unes dans les autres. Généralement, la requête interne génère une valeur qui est testée dans le prédicat de la requête externe (dans la clause WHERE ou HAVING) pour déterminer si elle est vraie ou fausse. En conjonction avec une sous-requête, vous pouvez utiliser le prédicat EXISTS, qui renvoie vrai si la sortie de la sous-requête n'est pas vide.

Lorsqu'elle est combinée avec les autres fonctionnalités de l'opérateur de sélection, telles que le regroupement, une sous-requête est un outil puissant pour obtenir le résultat souhaité. Dans la partie FROM de l'instruction SELECT, il est permis d'appliquer des synonymes aux noms de tables si, lors de la formation d'une requête, nous avons besoin de plus d'une instance d'une certaine relation. Les synonymes sont spécifiés à l'aide du mot-clé AS, qui peut être complètement omis. La partie FROM pourrait donc ressembler à ceci :

DE Rl COMME A, Rl COMME B

DE Rl A. Rl B :

les deux expressions sont équivalentes et sont considérées comme des applications de l'instruction SELECT à deux instances de la table R1.

Par exemple, montrons à quoi ressemblent certaines requêtes sur la base de données « Session » en SQL :

Liste de ceux qui ont réussi tous les examens requis.

OÙ Score > 2

AVOIR COUNT(*) = (SÉLECTIONNER COUNT(*)

OÙ R2.Group=R3.Group ET nom completa.nom complet)

Ici, la requête intégrée détermine le nombre total d'examens que chaque étudiant de la classe de l'étudiant doit passer et compare ce nombre avec le nombre d'examens que l'étudiant a passés.

Une liste de ceux qui étaient censés passer l'examen de base de données, mais qui ne l'ont pas encore passé.

SÉLESTFIO

OÙ R2.Fpynna=R3.Group ET Discipline = "DB" ET N'EXISTE PAS

(SELECT nom complet FROM Rl OÙ nom complet = a.nom complet ET Discipline = "DB")

Le prédicat EXISTS (SubQuery) est vrai lorsque la sous-requête SubQuery n'est pas vide, c'est-à-dire qu'elle contient au moins un tuple, sinon le prédicat EXISTS est faux.

Le prédicat NOT EXISTS est vrai uniquement lorsque la sous-requête est vide.

Remarquez comment NOT EXISTS avec une requête imbriquée vous permet d'éviter l'opération de différence de relation. Par exemple, formuler une requête avec le mot « tout » peut se faire comme avec une double négation. Considérons un exemple de base de données qui modélise la fourniture de pièces individuelles par des fournisseurs individuels ; elle est représentée par une relation SP « Fournisseurs-pièces » avec le schéma

SP (numéro_fournisseur. numéro_pièce) P (numéro_pièce. nom)

C’est ainsi que se formule la réponse à la demande : « Trouver des fournisseurs qui fournissent toutes les pièces ».

SÉLECTIONNEZ DISTINCT VENDOR_NUMBER FROM SP SP1 OÙ N'EXISTE PAS

(SÉLECTIONNEZ le numéro de pièce

DE P OÙ N'EXISTE PAS

(SÉLECTIONNER * DE SP SP2

OÙ SP2.supplier_number=SP1.supplier_number ET

sp2.part_number = P.part_number)) :

En fait, nous avons reformulé cette demande ainsi : « Trouver des fournisseurs tels qu’il n’y ait aucune pièce qu’ils ne fournissent pas ». Il est à noter que cette requête peut également être implémentée via des fonctions d'agrégation avec une sous-requête :

SELECT DISTINCT Numéro_fournisseur

GROUPER PAR Numéro_fournisseur

HAVING CoKDISTINCT numéro de pièce) =

(SELECT Nombre (numéro_de pièce)

La norme SQL92 étend les opérateurs de comparaison à plusieurs comparaisons à l'aide des mots-clés ANY et ALL. Cette extension est utilisée lors de la comparaison de la valeur d'une colonne spécifique avec la colonne de données renvoyée par une sous-requête.

Le mot-clé ANY placé dans n'importe quel prédicat de comparaison signifie que le prédicat sera vrai si pour au moins une valeur de la sous-requête, le prédicat de comparaison est vrai. Le mot clé ALL nécessite que le prédicat de comparaison soit vrai lorsqu'il est comparé à toutes les lignes de la sous-requête.

Par exemple, recherchons les étudiants qui ont réussi tous les examens avec une note au moins « bien ». Nous travaillons avec la même base de données « Session », mais y ajoutons une relation supplémentaire R4, qui caractérise la réalisation des travaux de laboratoire au cours du semestre :

R 1 = (Nom, Discipline, Grade) ;

R 2 = (nom complet, groupe) ;

R 3 = (Groupes, Discipline)

R 4 = (Nom, Discipline, Numéro de travail de laboratoire, Grade) ;

Sélectionnez R1.Nom complet De R1 Où 4 > = Tous (Sélectionnez Rl.Rating

Où R1.Nom complet = R11.Nom complet)

Regardons un autre exemple :

Sélectionner les étudiants dont la note à l'examen n'est pas inférieure à au moins une note du travail de laboratoire qu'ils ont réussi dans cette discipline :

Sélectionnez R1.Nom

De R1 Où R1.Rating>= ANY (Sélectionnez R4.Rating

Où Rl.Discipline = R4. Discipline ET R1.Nom complet = R4.Nom complet)

Jointures externes SQL

La norme SQL2 a élargi le concept de jointure conditionnelle. Dans le standard SQL1, lors de la jointure de relations, seules les conditions spécifiées dans la partie WHERE de l'instruction SELECT étaient utilisées, et dans ce cas, uniquement les tuples des relations d'origine concaténées par les conditions spécifiées, pour lesquelles ces conditions étaient définies et vraies , ont été inclus dans la relation résultante. Cependant, en réalité, il est souvent nécessaire de joindre des tables de telle manière que le résultat inclut toutes les lignes de la première table, et au lieu des lignes de la deuxième table pour lesquelles la condition de jointure n'est pas remplie, le résultat finit par avec des valeurs indéfinies. Ou vice versa, toutes les lignes du tableau de droite (deuxième) sont incluses et les parties manquantes des lignes du premier tableau sont complétées par des valeurs non définies. De telles jointures étaient appelées jointures externes, par opposition aux jointures définies par la norme SQL1, appelées jointures internes.

En général, la syntaxe de la partie FROM dans le standard SQL2 est la suivante :

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

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

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

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

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

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

[nom du synonyme table_1] [...]

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

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

<имя_таблицы_1>NATUREL (INTÉRIEUR | COMPLET | GAUCHE | DROITE) REJOINDRE<имя_таблицы_2>

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

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

<имя_таблицы_1>ADHÉSION AU SYNDICAT<имя_таблицы_2>

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

COMPLET | GAUCHE | À DROITE) REJOINDRE (ON condition)<имя_таблицы_2>

Dans ces définitions, INNER signifie une jointure interne, LEFT signifie une jointure gauche, c'est-à-dire que le résultat inclut toutes les lignes du tableau 1, et les parties des tuples résultants pour lesquels il n'y avait pas de valeurs correspondantes dans le tableau 2 sont complétées par NULL. (non définies) valeurs. Le mot clé RIGHT signifie une jointure externe droite, et contrairement à une jointure gauche, dans ce cas toutes les lignes du tableau 2 sont incluses dans la relation résultante, et les parties manquantes du tableau 1 sont complétées par des valeurs non définies. Le mot clé FULL définit une jointure externe complète. rejoindre : à gauche et à droite. Avec une jointure externe complète, les jointures externes droite et gauche sont effectuées et la relation résultante inclut toutes les lignes du tableau 1, complétées par des valeurs nulles, et toutes les lignes du tableau 2, également complétées par des valeurs nulles.

Le mot-clé OUTER signifie externe, mais si les mots-clés FULL, LEFT, RIGHT sont donnés, alors la jointure est toujours considérée comme externe.

Examinons des exemples d'exécution de jointures externes. Revenons à la base de données "Session". Créons une relation dans laquelle seront valables toutes les notes obtenues par tous les étudiants à tous les examens qu'ils ont dû passer. Si un étudiant n'a pas réussi cet examen, au lieu d'une note, il aura une valeur indéfinie. Pour ce faire, effectuons une jointure interne naturelle séquentielle des tables R2 et R3 à l'aide de l'attribut Group, et connectons la relation résultante avec une jointure naturelle externe gauche avec la table R1 à l'aide des colonnes Nom complet et Discipline. Dans le même temps, la norme autorise l'utilisation d'une structure entre parenthèses, puisque le résultat de l'union peut être l'un des arguments de la partie FROM de l'instruction SELECT.

SELECT Rl.Nom complet, R1.Discipline. Rl.Rating

DEPUIS (R2 JOINTURE INTÉRIEURE NATUREL R3) REJOIGNEZ À GAUCHE Rl EN UTILISANT (Nom. Discipline)

Résultat:


Nom et prénom	Discipline	Grade
Petrov F.I.	Base de données
Sidorov K.A.	Base de données	4
Mironov L.V.	Base de données
Stepanova K.E.	Base de données
Krylova T.S.	Base de données
Vladimirov V.A.	Base de données
Petrov F.I.	Théorie de l'information	Nul
Sidorov K.A.	Théorie de l'information
Mironov A.V.	Théorie de l'information	Nul
Stepanova K.E.	Théorie de l'information
Krylova T.S.	Théorie de l'information
Vladimirov V.A.	Théorie de l'information	Nul
Petrov F.I.	langue anglaise
Sidorov K.A.	langue anglaise	Nul
Mironov A.V.	langue anglaise	Nul
Stepanova K.E.	langue anglaise	Nul
Krylova T.S.	langue anglaise	Nul
Vladimirov V.A.	langue anglaise
Trofimov P.A.	Réseaux et télécommunications
Ivanova E.A.	Réseaux et télécommunications

Prenons un autre exemple, pour cela nous prenons la base de données « Bibliothèque ». Il se compose de trois relations ; les noms d'attributs ici sont saisis en lettres latines, ce qui est nécessaire dans la plupart des SGBD commerciaux.

LIVRES(ISBN, TITL. AUTOR. COAUTEUR. ANNÉE JZD, PAGES)

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

EXEMPLAIRE (INV, ISBN, OUI_NO. NUM_READER. DATE_IN. DATE_DUT)

Ici la table BOOKS décrit tous les livres présents dans la bibliothèque, elle possède les attributs suivants :

ISBN - un chiffre unique pour le livre ;
TITL - titre du livre ;
AUTOR - nom de famille de l'auteur ;
COAUTEUR - nom du co-auteur ;
YEARIZD - année de publication ;
PAGES - nombre de pages.

La table READER stocke des informations sur tous les lecteurs de la bibliothèque et contient les attributs suivants :

NUM_READER - numéro unique de carte de bibliothèque ;
NAME_READER - le nom et les initiales du lecteur ;
ADRESSE - adresse du lecteur ;
HOOM_PHONE - numéro de téléphone personnel ;
WORK_PHONE - numéro de téléphone professionnel ;
BIRTH_DAY - date de naissance du lecteur.

La table EXEMPLARE contient des informations sur l'état actuel de toutes les instances de tous les livres. Il comprend les colonnes suivantes :

INV - numéro d'inventaire unique d'un exemplaire de livre ;
ISBN - chiffre du livre, qui détermine de quel type de livre il s'agit et fait référence aux informations du premier tableau ;
OUI_NON - un signe de la présence ou de l'absence de cette instance dans la bibliothèque à l'heure actuelle ;
NUM_READER - numéro de carte de bibliothèque si le livre est délivré au lecteur, et Null sinon ;
DATE_IN - si le lecteur possède le livre, alors il s'agit de la date à laquelle il a été délivré au lecteur ; a DATE_OUT est la date à laquelle le lecteur doit restituer le livre à la bibliothèque.

Déterminons la liste des livres pour chaque lecteur ; si le lecteur n'a pas de livres, alors le numéro d'exemplaire du livre est NULL. Pour effectuer cette recherche, nous devons utiliser une jointure externe gauche, c'est-à-dire que nous prenons toutes les lignes de la table READER et les joignons aux lignes de la table EXEMPLARE, si la deuxième table n'a pas de ligne avec le numéro de carte de bibliothèque correspondant. , alors dans la ligne de la relation résultante, l'attribut EXEMPLARE.INV aura une valeur NULL non définie :

SELECT READER.NAME_READER, EXEMPLARE.INV

DEPUIS LECTEUR À DROITE REJOIGNEZ EXEMPLARE SUR READER.NUM_READER=EXEMPLARE.NUM_READER

L'opération de jointure externe, comme nous l'avons déjà mentionné, peut être utilisée pour former des sources dans la clause FROM, ainsi, par exemple, le texte de requête suivant serait acceptable :

DE (LIVRES À GAUCHE REJOINDRE L'EXEMPLE)

JOINTURE À GAUCHE (EXEMPLE DE JOINTURE NATURELLE DU LECTEUR)

Parallèlement, pour les livres dont pas un seul exemplaire n'est entre les mains des lecteurs, les valeurs du numéro de carte de bibliothèque et les dates de retrait et de restitution du livre seront incertaines.

Une jointure croisée, telle que définie dans la norme SQL2, correspond à une opération de produit cartésien étendu, c'est-à-dire une opération de jointure entre deux tables dans laquelle chaque ligne de la première table est jointe à chaque ligne de la deuxième table.

Opération demande de fusionéquivaut à l'opération de l'union théorique des ensembles en algèbre. Dans ce cas, l’exigence d’équivalence des schémas des relations originelles est préservée. Une demande de jointure s'effectue selon le schéma suivant :

SELECT - demande

UNION SELECT - requête

Toutes les requêtes participant à l'opération de jointure ne doivent pas contenir d'expressions, c'est-à-dire de champs calculés.

Par exemple, vous devez afficher une liste de lecteurs qui détiennent le livre « Idiot » ou le livre « Crime and Punishment ». Voici à quoi ressemblera la demande :

SÉLECTIONNEZ LE LECTEUR. NAME_READER

DU LECTEUR, EXEMPLAIRE.LIVRES

LIVRES.TITLE = "Idiot"!}

SELECT READER.NAME_READER

DU LECTEUR, EXEMPLAIRE, LIVRES

OÙ EXEMPLARE.NUM_READER= READER.NUM_READER ET

EXEMPLRE.ISBN = LIVRES.ISBN ET

BOOKS.TITLE = "(!LANG : Crime et châtiment"!}

Par défaut, lors de l'exécution d'une requête de jointure, les tuples en double sont toujours exclus. Par conséquent, s’il y a des lecteurs qui ont les deux livres entre les mains, ils n’apparaîtront toujours qu’une seule fois dans la liste résultante.

Une requête de jointure peut joindre n'importe quel nombre de requêtes d'origine.

Ainsi, à la demande précédente, vous pouvez ajouter d'autres lecteurs qui tiennent le livre « Château » entre leurs mains :

SÉLECTIONNEZ LE LECTEUR. NAME_READER

DU LECTEUR. EXEMPLAIRE, LIVRES

OÙ EXEMPLARE.NUM_READER= READER.NUM_READER ET .

EXEMPLRE.ISBN = LIVRES.ISBN ET

BOOKS.TITLE = "Château"!}

Dans le cas où vous devez conserver toutes les lignes des relations sources, vous devez utiliser le mot-clé ALL dans l'opération de jointure. Si des tuples en double sont stockés, le flux d'exécution de la requête de jointure ressemblera à ceci :

SELECT - demande

Cependant, le même résultat peut être obtenu en modifiant simplement la clause WHERE de la première partie de la requête d'origine, en connectant les conditions locales avec une opération OU logique et en éliminant les tuples en double.

SÉLECTIONNEZ DISTINCT READER.NAME_READER

DU LECTEUR. EXEMPLAIRE.LIVRES

OÙ EXEMPLARE.NUM_READER= READER.NUM_READER ET

EXEMPLRE.ISBN = LIVRES.ISBN ET

LIVRES.TITLE = "Idiot" OR!}

BOOKS.TITLE = "Crime et Châtiment" OR!}

BOOKS.TITLE = "Château"!}

Aucune des requêtes d'origine d'une opération UNION ne doit contenir de clause ORDER BY, mais le résultat de l'union peut être ordonné en écrivant une clause ORDER BY spécifiant la liste des colonnes de classement après le texte de la dernière requête SELECT d'origine.

Important! Si un paramètre de fonction est de type String et qu'il spécifie un nom de champ contenant des espaces, le nom du champ doit être placé entre crochets.
Par exemple : « [Nombre de CA] ».

1. Montant (Total)- calcule la somme des valeurs des expressions qui lui sont transmises en argument pour tous les enregistrements détaillés. Vous pouvez passer un Array en paramètre. Dans ce cas, la fonction sera appliquée au contenu du tableau.

Exemple:
Montant (Sales.AmountTurnover)

2. Compter - calcule le nombre de valeurs autres que NULL. Vous pouvez passer un Array en paramètre. Dans ce cas, la fonction sera appliquée au contenu du tableau.

Syntaxe:
Quantité (Paramètre [Divers])

Pour indiquer la réception de valeurs différentes, vous devez spécifier Distinct avant le paramètre Méthode Quantity.

Exemple:
Quantité (Ventes. Contrepartie)
Quantité (Diverses ventes. Contrepartie)

3. Maximale - obtient la valeur maximale. Vous pouvez passer un Array en paramètre. Dans ce cas, la fonction sera appliquée au contenu du tableau.

Exemple:
Maximum (quantité restante)

4. Minimum - obtient la valeur minimale. Vous pouvez passer un Array en paramètre. Dans ce cas, la fonction sera appliquée au contenu du tableau.

Exemple:
Minimum (quantité restante)

5. Moyenne - Obtient la moyenne des valeurs non NULL. Vous pouvez passer un Array en paramètre. Dans ce cas, la fonction sera appliquée au contenu du tableau.

Exemple:
Moyenne (quantité restante)

6. Tableau - forme un tableau contenant la valeur du paramètre pour chaque enregistrement détaillé.

Syntaxe:
Tableau([Divers] Expression)

Vous pouvez utiliser un tableau de valeurs comme paramètre. Dans ce cas, le résultat de la fonction sera un tableau contenant les valeurs de la première colonne du tableau des valeurs, passées en paramètre. Si une expression contient une fonction Array, elle est alors considérée comme une expression agrégée. Si le mot-clé Divers est spécifié, le tableau résultant ne contiendra pas de valeurs en double.

Exemple:
Tableau (Contrepartie)

7. Table de valeurs - génère un tableau de valeurs contenant autant de colonnes qu'il y a de paramètres pour la fonction. Les enregistrements détaillés sont obtenus à partir des ensembles de données nécessaires pour obtenir tous les champs impliqués dans les expressions de paramètres de fonction.

Syntaxe:
ValueTable([Divers] Expression1 [AS NomColonne1][, Expression2 [AS NomColonne2],...])

Si les paramètres de la fonction sont des champs résiduels, le tableau de valeurs résultant contiendra les valeurs des enregistrements pour des combinaisons uniques de dimensions d'autres périodes. Dans ce cas, les valeurs sont obtenues uniquement pour les champs de solde, les dimensions, les comptes, les champs de période et leurs détails. Les valeurs des champs restants dans les enregistrements d'autres périodes sont considérées comme égales à NULL. Si une expression contient la fonction ValueTable, alors cette expression est considérée comme une expression agrégée. Si le mot-clé Divers est spécifié, alors le tableau de valeurs résultant ne contiendra pas de lignes contenant les mêmes données. Après chaque paramètre il peut y avoir un mot-clé optionnel AS et un nom qui sera attribué à la colonne de la table de valeurs.

Exemple:
Tableau des valeurs (Diverses Nomenclatures, Caractéristiques des Nomenclatures AS Caractéristiques)

8. Réduire (GroupBy) - conçu pour supprimer les doublons d'un tableau.

Syntaxe:
Réduire (Expression, Numéros de colonnes)

Possibilités :

Expression- une expression de type Array ou ValueTable dont les valeurs des éléments doivent être réduites ;
Numéros de colonnes- (si l'expression est de type ValueTable) tapez String. Numéros ou noms (séparés par des virgules) des colonnes de la table de valeurs, parmi lesquelles vous devez rechercher les doublons. La valeur par défaut est toutes les colonnes.

Exemple:
Collapse(ValueTable(PhoneNumber, Adresse) ,"PhoneNumber");

9. Obtenir une partie - obtient une table de valeurs contenant certaines colonnes de la table de valeurs d'origine.

Syntaxe:
GetPart (Expression, Numéros de colonnes)

Possibilités :

Expression- tapez Tableau de valeurs. Un tableau de valeurs à partir duquel obtenir des colonnes ;
Numéros de colonnes- tapez Chaîne. Numéros ou noms (séparés par des virgules) des colonnes du tableau des valeurs à obtenir.

Valeur de retour : table de valeurs, qui contient uniquement les colonnes spécifiées dans le paramètre.

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

10. Commande - conçu pour organiser les éléments du tableau et le tableau des valeurs.

Syntaxe:
Organiser (Expression, Numéros de colonnes)

Possibilités :

Expression- Tableau ou table de valeurs à partir duquel vous devez obtenir des colonnes ;
Numéros de colonnes- (si l'expression est de type ValueTable) les numéros ou noms (séparés par des virgules) des colonnes de la table de valeurs sur lesquelles vous souhaitez trier. Peut contenir le sens de commande et la nécessité d'une commande automatique : Descendant/Ascendant + Commande automatique.

Valeur de retour : Array ou ValueTable, avec des éléments ordonnés.

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

11. JoinStrings - conçu pour combiner des chaînes en une seule ligne.

Syntaxe:
ConnectRows (Valeur, ItemSeparator, ColumnSeparator)

Possibilités :

Signification- les expressions qui doivent être combinées sur une seule ligne. S'il s'agit d'un tableau, alors les éléments du tableau seront combinés en une chaîne. S'il s'agit d'un ValueTable, alors toutes les colonnes et lignes du tableau seront combinées en une ligne ;
Séparateur d'éléments- une chaîne contenant du texte à utiliser comme séparateur entre les éléments du tableau et les lignes du tableau de valeurs. Par défaut – caractère de saut de ligne ;
Séparateurs de colonnes- une ligne contenant du texte qui doit servir de séparateur entre les colonnes du tableau de valeurs. Défaut "; ".

Exemple:
ConnectRows (ValueTable (Numéro de téléphone, Adresse));

12. Traitement de groupe - renvoie l'objet GroupProcessingDataCompositionData. Dans la propriété Data de l'objet, les valeurs de regroupement sont placées sous la forme d'un tableau de valeurs pour chaque expression spécifiée dans le paramètre de fonction Expressions. Lors de l'utilisation du regroupement hiérarchique, chaque niveau de la hiérarchie est traité séparément. Les valeurs des enregistrements hiérarchiques sont également placées dans les données. La propriété CurrentItem de l'objet contient la ligne du tableau de valeurs pour laquelle la fonction est actuellement calculée.

Syntaxe:
GroupProcessing (Expressions, Expressions de hiérarchie, GroupName)

Possibilités :

Expressions. Expressions à évaluer. Une ligne contenant des expressions séparées par des virgules qui doivent être évaluées. Après chaque expression, il peut y avoir un mot-clé facultatif COMMENT et le nom de la colonne du tableau de valeurs résultant. Chaque expression forme une colonne dans le tableau des valeurs de la propriété Data de l'objet DataCompositionGroupProcessingData.
ExpressionsHiérarchies. Expressions à évaluer pour les enregistrements hiérarchiques. Similaire au paramètre Expressions, à la différence que le paramètre Hierarchy Expressions est utilisé pour les enregistrements hiérarchiques. Si le paramètre n'est pas spécifié, les expressions spécifiées dans le paramètre Expression sont utilisées pour calculer les valeurs des enregistrements hiérarchiques.
Nom de groupe. Nom du regroupement dans lequel calculer le regroupement de traitement. Doubler. S'il n'est pas spécifié, le calcul s'effectue dans le regroupement actuel. Si le calcul est effectué dans un tableau et que le paramètre contient une chaîne vide ou n'est pas spécifié, alors la valeur est calculée pour le regroupement - la chaîne. Le compositeur de mise en page, lors de la génération d'une mise en page de données, remplace ce nom par le nom du regroupement dans la mise en page résultante. Si le regroupement n'est pas disponible, la fonction sera remplacée par une valeur NULL.

13. Tout le monde - si au moins un enregistrement a la valeur False, alors le résultat est False, sinon True.

Syntaxe:
Chacun(Expression)

Paramètre :

Expression- Type booléen.

Exemple:
Chaque()

14. N'importe lequel (Tous)- si au moins un enregistrement a la valeur True, alors le résultat est True, sinon False

Syntaxe:
N'importe lequel (expression)

Paramètre :

Expression- Type booléen.

Exemple:
N'importe lequel()

15. Écart type de la population générale (Stddev_Pop) - calcule l'écart type de la population. Calculé à l'aide de la formule : SQRT (Variance de la population générale (X)).

Syntaxe:
Écart-type de la population générale (expression)

Paramètre :

Expression- Type de numéro.

Exemple:

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

SELECT Écart type de la population générale (Y) FROM Tableau
Résultat : 805.694444

16. Écart type de l'échantillon (Stddev_Samp) - calcule l'écart type de l'échantillon cumulé. Calculé à l'aide de la formule : SQRT (Sample Variance (X)).

Syntaxe:
Échantillon d'écart standard (expression)

Paramètre :

Expression- Type de numéro.

Type de retour Numéro.

Exemple:

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

SELECT StandardDeviationSamples(Y) FROM Table
Résultat : 28.3847573

17. VarianceSamples (Var_Samp) - calcule la différence typique d'une série de nombres sans prendre en compte les valeurs NULL de cet ensemble. Calculé à l'aide de la formule : (Montant(X^2) - Montant(X)^2 / Quantité(X)) / (Quantité(X) - 1). Si Quantité(X) = 1, alors NULL est renvoyé.

Syntaxe:
Échantillons de variance (expression)

Paramètre :

Expression- Type de numéro.

Exemple:
SELECT Variance de la population (Y) FROM Table
Résultat : 716.17284

19. Covariance de la population générale (Covar_Pop) - calcule la covariance d'un certain nombre de paires numériques. Calculé à l'aide de la formule : (Sum(Y * X) - Sum(X) * Sum(Y) / n) / n, où n est le nombre de paires (Y, X) dans lesquelles ni Y ni X ne sont NULL.

Syntaxe:
Covariance de la population (Y, X)

Possibilités :

Oui- tapez Numéro ;
X- Type de numéro.

Exemple:

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

SELECT CovariancePopulation(Y, X) FROM Table
Résultat : 59.4444444

20. CovarianceSamples (Covar_Samp) - calcule la différence typique d'une série de nombres sans prendre en compte les valeurs NULL de cet ensemble. Calculé à l'aide de la formule : (Somme(Y * X) - Somme(Y) * Somme(X) / n) / (n-1), où n est le nombre de paires (Y, X) dans lesquelles ni Y ni X sont NULL.

Syntaxe:
CovarianceÉchantillons (Y, X)

Possibilités :

Oui- tapez Numéro ;
X- Type de numéro.

Exemple:

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

SELECT CovarianceSamples(Y, X) FROM Table
Résultat : 66.875

21. Corrélation (Corr) - calcule le coefficient de corrélation d'un certain nombre de paires numériques. Il est calculé à l'aide de la formule : Covariance de la population (Y, X) / (Écart type de la population (Y) * Écart type de la population (X)). Les paires dans lesquelles Y ou X sont NULL ne sont pas prises en compte.

Syntaxe:
Corrélation (Y, X)

Possibilités :

Oui- tapez Numéro ;
X- Type de numéro.

Exemple:

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

SELECT Corrélation (X, Y) FROM Table
Résultat : 0,860296149

22. RégressionSlope (Regr_Slope) - calcule la pente de la ligne. Calculé à l'aide de la formule : Covariance de la population générale (Y, X) / Variance de la population générale (X). Calculé sans prendre en compte les paires contenant NULL.

Syntaxe:
Pente de régression (Y, X)

Possibilités :

Oui- tapez Numéro ;
X- Type de numéro.

Exemple:

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

SELECT RegressionSlope(Y, X) FROM Table
Résultat : 8.91666667

23. RégressionIntercept (Regr_Intercept) - calcule le point Y d'intersection de la droite de régression. Calculé à l'aide de la formule : Mean(Y) - RegressionSlope(Y, X) * Mean(X). Calculé sans prendre en compte les paires contenant NULL.

Syntaxe:
Segment de régression (Y, X)

Possibilités :

Oui- tapez Numéro ;
X- Type de numéro.

Exemple:
SELECT RegressionCount(Y, X) FROM Table
Résultat : 9

25. RégressionR2 (Regr_R2) - calcule le coefficient de détermination. Calculé sans prendre en compte les paires contenant NULL.

Syntaxe:
RégressionR2(Y, X)

Possibilités :

Oui- tapez Numéro ;
X- Type de numéro.

Valeur de retour :

Nul - si variance de la population générale (X) = 0 ;
1 - si Variance de la Population Générale (Y) = 0 ET Variance de la Population Générale (X)<>0;
POW(Corrélation(Y,X),2) - si la Variance de la Population Générale(Y)>0 ET la Variance de la Population Générale(X)<>0.

Exemple:

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

SELECT RégressionR2(Y, X) FROM Table
Résultat : 0,740109464

26. RégressionAverageX (Regr_AvgX) - calcule la moyenne de X après avoir éliminé les paires X et Y où X ou Y est vide. Average(X) est calculé sans prendre en compte les paires contenant NULL.

Syntaxe:
RégressionMoyenneX(Y, X)

Possibilités :

Oui- tapez Numéro ;
X- Type de numéro.

Exemple:

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

SELECT RegressionMeanX(Y, X) FROM Table
Résultat : 5

27. RégressionMoyenneY (Regr_AvgY) - calcule la moyenne de Y après avoir éliminé les paires X et Y où X ou Y est vide. Average(Y) est calculé sans prendre en compte les paires contenant NULL.

Syntaxe:
RégressionMoyenneY(Y, X)

Possibilités :

Oui- tapez Numéro ;
X- Type de numéro.

Exemple:

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

SELECT RegressionMeanY(Y, X) FROM Table
Résultat : 24.2222222

28. RégressionSXX (Regr_SXX) - calculé à l'aide de la formule : RegressionQuantity(Y, X) * Dispersion de la population générale (X). Calculé sans prendre en compte les paires contenant NULL.

Syntaxe:
RégressionSXX(Y, X)

Possibilités :

Oui- tapez Numéro ;
X- Type de numéro.

Renvoie la somme des carrés des expressions indépendantes utilisées dans un modèle de régression linéaire. La fonction peut être utilisée pour évaluer la validité statistique d'un modèle de régression.

Exemple:
SELECT RégressionSYY(Y, X) FROM Table
Résultat : 6445.55556

30. RégressionSXY (Regr_SXY) - calculé à l'aide de la formule : RegressionQuantity(Y, X) * Covariance de la population générale (Y, X). Calculé sans prendre en compte les paires contenant NULL.

Syntaxe:
RégressionSXY(Y,X)

Possibilités :

Oui- tapez Numéro ;
X- Type de numéro.

Exemple:

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

SELECT RégressionSXY(Y, X) FROM Table
Résultat : 535

31. Rang

Syntaxe:
PlaceInOrder (Ordre, Ordre Hiérarchique, Nom de Groupe)

Possibilités :

Commande– tapez Chaîne. Contient les expressions dans l'ordre desquelles vous souhaitez organiser les enregistrements de groupe, séparés par des virgules. Le sens de commande est contrôlé à l'aide des mots Ascendant, Descendant. Vous pouvez également suivre le champ avec Commande automatique, ce qui signifie que lorsque vous commandez des liens, vous devez utiliser les champs de commande définis pour l'objet référencé. Si aucune séquence n'est spécifiée, la valeur est calculée dans la séquence de regroupement ;
Hiérarchie des commandes– tapez Chaîne. Contient des expressions de classement pour les enregistrements hiérarchiques ;
Nom de groupe– tapez Chaîne. Nom du regroupement dans lequel calculer le regroupement de traitement. S'il n'est pas spécifié, le calcul s'effectue dans le regroupement actuel. Si le calcul est effectué dans un tableau et que le paramètre contient une chaîne vide ou n'est pas spécifié, alors la valeur est calculée pour le regroupement - la chaîne. Le compositeur de mise en page, lors de la génération d'une mise en page de données, remplace ce nom par le nom du regroupement dans la mise en page résultante. Si le regroupement n'est pas disponible, la fonction sera remplacée par une valeur NULL.

S'il y a deux enregistrements ou plus dans une séquence avec les mêmes valeurs de champ de classement, la fonction renvoie les mêmes valeurs pour tous les enregistrements.

Exemple:
PlaceInOrder("[Nombre de CA]")

32. ClassementABC

Syntaxe:
ClassificationABC (Valeur, Nombre de groupes, PourcentageForGroups, GroupName)

Possibilités :

Signification– tapez Chaîne. par lequel le classement doit être calculé. Une ligne contenant l'expression ;
Nombre de groupes- Type de numéro. Spécifie le nombre de groupes à diviser ;
PourcentagePourGroupes- tapez Chaîne. Il faut diviser en groupes jusqu'à moins 1. Séparés par des virgules. Si non spécifié, alors automatiquement ;
Nom de groupe- tapez Chaîne. Nom du regroupement dans lequel calculer le regroupement de traitement. S'il n'est pas spécifié, le calcul s'effectue dans le regroupement actuel. Si le calcul est effectué dans un tableau et que le paramètre contient une chaîne vide ou n'est pas spécifié, alors la valeur est calculée pour le regroupement - la chaîne. Le compositeur de mise en page, lors de la génération d'une mise en page de données, remplace ce nom par le nom du regroupement dans la mise en page résultante. Si le regroupement n'est pas disponible, la fonction sera remplacée par une valeur NULL.

Le résultat de la fonction sera le numéro de classe, à partir de 1, qui correspond à la classe A.

Exemple:
ClassificationABC("Montant(GrossProfit)", 3, "60, 90")

SQL a ajouté des fonctions supplémentaires qui vous permettent de calculer des valeurs de groupe généralisées. Pour appliquer les fonctions d'agrégation, une opération de regroupement préalable est supposée. Lors du regroupement, l'ensemble des tuples d'une relation est divisé en groupes dans lesquels sont collectés les tuples ayant les mêmes valeurs d'attribut que celles spécifiées dans la liste de regroupement.

Par exemple, vous pouvez calculer le nombre d’étudiants ayant passé des examens dans chaque discipline. Pour cela, vous devez lancer une requête regroupée par le champ "Discipline" et afficher comme résultat le nom de la discipline et le nombre de lignes du groupe pour cette discipline. Utiliser le caractère * comme argument de la fonction COUNT signifie compter toutes les lignes du groupe.

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

Résultat:

Exemple. Obtenez une liste des disciplines dans lesquelles au moins 5 personnes ont réussi l'examen :

SELECT R1.DisciplineFROM R1GROUP BY R1.DisciplineHAVING COUNT(*) >= 5 Résultat : ici, l'instruction HAVING sélectionne les groupes qui satisfont à la condition donnée.

Requêtes imbriquées

Rapport D (Détails)

Ratio PD (approvisionnement)

1. Obtenir la liste des fournisseurs dont le statut est inférieur au statut maximum dans la table des fournisseurs (comparaison avec une sous-requête) :

OÙ P.STATYS<

(SÉLECTIONNER MAX(P.STATUT)

Commentaire. Parce que le champ P.STATUS est comparé au résultat de la sous-requête, puis la sous-requête doit être formulée pour renvoyer une table composée exactement une ligne et une colonne.

Commentaire

Exécuter une fois sous-requête imbriquée et obtenez la valeur d'état maximale.
Parcourez la table des fournisseurs P, en comparant à chaque fois la valeur du statut du fournisseur avec le résultat de la sous-requête, et sélectionnez uniquement les lignes dans lesquelles le statut est inférieur au maximum.

2. Utiliser un prédicat DANS

(SÉLECTIONNEZ PD.PNUM DISTINCT

OÙ PD.DNUM = 2);

Commentaire. Dans ce cas, une sous-requête imbriquée peut renvoyer une table contenant plusieurs lignes.

Commentaire. Le résultat de l'exécution de la requête sera équivalent au résultat de la séquence d'actions suivante :

Exécuter une fois sous-requête imbriquée et obtenez une liste des numéros de fournisseurs qui fournissent le numéro de pièce 2.
Parcourez la table des fournisseurs P en vérifiant à chaque fois si le numéro du fournisseur est contenu dans le résultat de la sous-requête.

3. Utiliser un prédicat EXISTE . Obtenez une liste des fournisseurs fournissant la pièce numéro 2 :

PD.PNUM = P.PNUM ET

Commentaire. Le résultat de l'exécution de la requête sera équivalent au résultat de la séquence d'actions suivante :

Scanner le tableau des fournisseurs P, chaque fois que vous exécutez une sous-requête avec la nouvelle valeur du numéro de fournisseur issue du tableau P.
Incluez dans le résultat de la requête uniquement les lignes de la table des fournisseurs pour lesquelles la sous-requête imbriquée a renvoyé un ensemble de lignes non vide.

Commentaire. Contrairement aux deux exemples précédents, la sous-requête imbriquée contient un paramètre (lien externe) transmis depuis la requête principale - le numéro de fournisseur P.PNUM. De telles sous-requêtes sont appelées corrélé (corrélé ). Une référence externe peut prendre une valeur différente pour chaque ligne candidate évaluée par la sous-requête, la sous-requête doit donc être réexécutée pour chaque ligne sélectionnée dans la requête principale. De telles sous-requêtes sont typiques du prédicat EXISTS, mais peuvent être utilisées dans d'autres sous-requêtes.

Commentaire. Il peut sembler que les requêtes contenant des sous-requêtes corrélées seront plus lentes que les requêtes contenant des sous-requêtes non corrélées. En fait, ce n'est pas le cas, parce que... la façon dont l'utilisateur a formulé la demande, ne définit pas comment cette demande sera exécutée. Le langage SQL n'est pas procédural, mais déclaratif. Cela signifie que l'utilisateur qui formule la demande décrit simplement, quel devrait être le résultat de la requête, et la manière dont ce résultat sera obtenu relève de la responsabilité du SGBD lui-même.

4. Utiliser un prédicat N'EXISTE PAS . Obtenez une liste des fournisseurs qui ne fournissent pas la pièce numéro 2 :

OÙ N'EXISTE PAS

PD.PNUM = P.PNUM ET

Commentaire. Tout comme dans l’exemple précédent, une sous-requête est utilisée ici. La différence est que la requête principale sélectionnera les lignes de la table des fournisseurs pour lesquelles la sous-requête imbriquée ne renvoie pas une seule ligne.

5. Obtenez les noms des fournisseurs fournissant toutes les pièces :

SÉLECTIONNER UN NOM DISTINCT

OÙ N'EXISTE PAS

PD.DNUM = D.DNUM ET

PD.PNUM = P.PNUM));

Commentaire. Cette requête contient deux sous-requêtes imbriquées et implémente une opération relationnelle divisions des relations.

La sous-requête la plus interne est paramétrée par deux paramètres (D.DNUM, P.PNUM) et a la signification suivante : sélectionner toutes les lignes contenant des données sur les livraisons du fournisseur avec le numéro PNUM de la pièce avec le numéro DNUM. La négation de NOT EXISTS indique que le fournisseur ne fournit pas la pièce. La sous-requête qui lui est externe, qui est elle-même un paramètre P.PNUM imbriqué et paramétré, a du sens : pour sélectionner une liste de pièces qui ne sont pas fournies par le fournisseur PNUM. La négation de NOT EXISTS indique qu'il ne devrait y avoir aucune pièce pour un fournisseur avec un numéro PNUM qui ne soit pas fournie par ce fournisseur. Cela signifie exactement que la requête externe sélectionne uniquement les fournisseurs qui fournissent toutes les pièces.

Connexions externes

Il est souvent nécessaire de joindre des tables de telle manière que le résultat inclut toutes les lignes de la première table, et au lieu des lignes de la deuxième table pour lesquelles la condition de jointure n'est pas remplie, le résultat contient des valeurs non définies. Ou vice versa, toutes les lignes du tableau de droite (deuxième) sont incluses et les parties manquantes des lignes du premier tableau sont complétées par des valeurs non définies. De telles associations étaient dites externes.

En général, la syntaxe de la partie FROM dans le standard SQL2 est la suivante :

DEPUIS<список исходных таблиц>< выражение естественного соединения >< выражение соединения >< выражение перекрестного соединения >< выражение запроса на объединение ><список исходных таблиц>::= <имя_таблицы_1>[ nom du synonyme table_1] [ …] [,<имя_таблицы_n>[ <имя синонима таблицы_n> ] ]<выражение естественного соединения>:: =<имя_таблицы_1>NATUREL (INTÉRIEUR | PLEINE GAUCHE | DROITE) REJOINDRE<имя_таблицы_2><выражение перекрестного соединения>:: = <имя_таблицы_1>JOINTION CROISÉE<имя_таблицы_2><выражение запроса на объединение>::=<имя_таблицы_1>ADHÉSION AU SYNDICAT<имя_таблицы_2><выражение соединения>::= <имя_таблицы_1>(INNERFULL | GAUCHE | DROITE) REJOINDRE (ON condition |)<имя_таблицы_2>

Dans ces définitions, INNER signifie une jointure interne (naturelle), LEFT signifie une jointure gauche, c'est-à-dire que le résultat inclut toutes les lignes du tableau 1, et les parties des tuples résultants pour lesquels il n'y avait pas de valeurs correspondantes dans le tableau 2 sont complété par des valeurs NULL (non définies). Le mot-clé RIGHT signifie une jointure externe droite, et contrairement à une jointure gauche, dans ce cas, toutes les lignes du tableau 2 sont incluses dans la relation résultante, et les parties manquantes du tableau 1 sont complétées par des valeurs nulles. Le mot-clé FULL spécifie une jointure externe complète : à gauche et à droite. Avec une jointure externe complète, les jointures externes droite et gauche sont effectuées et la relation résultante inclut toutes les lignes du tableau 1, complétées par des valeurs nulles, et toutes les lignes du tableau 2, également complétées par des valeurs nulles.

Le mot-clé OUTER signifie externe, mais si les mots-clés FULL, LEFT, RIGHT sont spécifiés, alors la connexion est toujours considérée comme externe.

Examinons des exemples de jointures externes. Revenons à la base de données "Session". Créons une relation dans laquelle seront valables toutes les notes obtenues par tous les étudiants à tous les examens qu'ils ont dû passer. Si un étudiant n'a pas réussi cet examen, au lieu d'une note, il aura une valeur indéfinie. Pour ce faire, nous allons effectuer une jointure interne naturelle séquentielle des tables R2 et R3 à l'aide de l'attribut Group, et connecter la relation résultante avec une jointure naturelle externe gauche à la table R1 à l'aide des colonnes Nom complet et Discipline. Cependant, la norme autorise l'utilisation d'une structure entre crochets, puisque le résultat de la connexion peut être l'un des arguments de la partie FROM de l'instruction SELECT.

SELECT R1.Name, R1.Discipline, R1.RatingFROM (R2 NATURAL INNER JOIN R3) GAUCHE JOIN R1 EN UTILISANT (Nom, Discipline)

Résultat:

Nom et prénom	Discipline	Grade
Petrov F.I.	Base de données
Sidorov K.A.	Base de données
Mironov A.V.	Base de données
Stepanova K.E.	Base de données
Krylova T.S.	Base de données
Vladimirov V.A.	Base de données
Petrov F.I.	Théorie de l'information	Nul
Sidorov K.A.	Théorie de l'information
Mironov A.V.	Théorie de l'information	Nul
Stepanova K.E.	Théorie de l'information
Krylova T.S.	Théorie de l'information

De nombreuses requêtes de base de données ne nécessitent pas le niveau de granularité fourni par les requêtes SQL évoquées dans les exemples précédents. Ainsi, dans toutes les requêtes listées ci-dessous, vous n'avez besoin de connaître qu'une ou plusieurs valeurs qui résument les informations contenues dans la base de données :

1) quel est le montant des revenus de tous les résidents ?
2) quel est le revenu total le plus élevé et le plus bas d'un résident individuel ?
3) quel est le revenu moyen par habitant d'un habitant de Zelenograd ?
4) quel est le revenu moyen par habitant des résidents de chaque appartement ?
5) combien y a-t-il de résidents dans chaque appartement ?

En SQL, ces types de requêtes peuvent être créés à l'aide de fonctions d'agrégation et des clauses GROUP BY et HAVING utilisées dans l'instruction SELECT.

Utilisation des fonctions d'agrégation

Pour résumer les informations contenues dans la base de données, SQL fournit des fonctions d'agrégation. Une fonction d'agrégation prend une colonne entière de données comme argument et renvoie une valeur unique qui résume cette colonne d'une manière spécifique.

Par exemple, la fonction d'agrégation AVG() prend une colonne de nombres comme argument et calcule leur moyenne.

Pour calculer le revenu moyen par habitant d'un résident de Zelenograd, vous avez besoin de la requête suivante :

SÉLECTIONNEZ « REVENU MOYEN PAR HABITANT, MOYEN (SUMD) DE LA PERSONNE »

SQL dispose de six fonctions d'agrégation qui vous permettent d'obtenir différents types d'informations récapitulatives (Figure 3.16) :

SUM() calcule la somme de toutes les valeurs contenues dans une colonne ;

AVG() calcule la moyenne des valeurs contenues dans une colonne ;

- MIN() trouve la plus petite parmi toutes les valeurs contenues dans la colonne ;
- MAX() trouve la plus grande parmi toutes les valeurs contenues dans la colonne ;
- COUNT() compte le nombre de valeurs contenues dans une colonne ;

COUNT(*) compte le nombre de lignes dans le tableau des résultats de la requête.

L'argument d'une fonction d'agrégation peut être un simple nom de colonne, comme dans l'exemple précédent, ou une expression, comme dans la requête suivante, qui spécifie le calcul de la taxe moyenne par habitant :

SÉLECTIONNER MOYENNE(SOMME*0,13)

Riz. 3.16.

Cette requête crée une colonne temporaire contenant les valeurs (SUMD*0,13) pour chaque ligne de la table PERSON, puis calcule la moyenne de la colonne temporaire.

Le montant du revenu de tous les résidents de Zelenograd peut être calculé à l'aide de la fonction agrégée SUM :

SELECT SUM(SUMD) DE LA PERSONNE

Une fonction d'agrégation peut également être utilisée pour calculer des totaux à partir d'un tableau de résultats obtenu en joignant plusieurs tableaux sources. Par exemple, vous pouvez calculer le montant total des revenus que les résidents ont reçus d'une source appelée « Bourse » :

SÉLECTIONNER SOMME (ARGENT)

DU PROFIT, HAVE_D

OÙ PROFIT.ID=HAVE_D.ID

ET PROFIT.SOURCE^Bourse'

Les fonctions d'agrégation MIN() et MAX() vous permettent de trouver respectivement les valeurs les plus petites et les plus grandes d'un tableau. La colonne peut contenir des valeurs numériques ou de chaîne, ou des valeurs de date ou d'heure.

Par exemple, vous pouvez définir :

(a) le revenu total le plus faible perçu par les résidents et l'impôt à payer le plus élevé :

SÉLECTIONNER MIN(SOMME), MAX(SOMME*0,13)

b) les dates de naissance du résident le plus âgé et le plus jeune :

SÉLECTIONNER MIN(RDATE), MAX(RDATE)

c) les noms, prénoms et patronymes des tout premier et dernier résidents de la liste, classés par ordre alphabétique :

SÉLECTIONNER MIN(FIO), MAX(FIO)

Lorsque vous utilisez ces fonctions d'agrégation, vous devez vous rappeler que les données numériques sont comparées à l'aide de règles arithmétiques, que les dates sont comparées de manière séquentielle (les valeurs de date antérieures sont considérées comme plus petites que les valeurs ultérieures) et que les intervalles de temps sont comparés en fonction de leur durée.

Lors de l'utilisation des fonctions MIN() et MAX() avec des données de chaîne, le résultat de la comparaison de deux chaînes dépend de la table de codage de caractères utilisée.

La fonction d'agrégation COUNT() compte le nombre de valeurs dans une colonne de n'importe quel type :

(a) combien y a-t-il d'appartements dans le 1er microquartier ?

SÉLECTIONNER LE COMPTE (ADR)

OÙ ADR COMME *%, 1_

(b) combien de résidents ont des sources de revenus ?

SÉLECTIONNER LE COMPTE (NOM DISTINCT)

SELECTIONNER LE COMPTE (ID DISTINCT)

Le mot-clé "DISTINCT" précise que les valeurs non dupliquées dans une colonne sont comptées.

La fonction d'agrégation spéciale COUNT(*) compte les lignes du tableau des résultats, pas les valeurs des données :

(a) combien y a-t-il d'appartements dans le 2ème microquartier ?

OÙ ADR COMME "%, 2_-%'

(b) de combien de sources de revenus Ivan Ivanovitch Ivanov dispose-t-il ?

DE LA PERSONNE, HAVE_D

OÙ FIO = "Ivanov Ivan Ivanovitch"

ET PERSONNE.NOM = HAVE_D.NOM

SELECT COUNT(*) FROM PERSON WHERE ADR = "Zelenograd, 1001-45'

Une façon de comprendre comment les requêtes récapitulatives avec des fonctions d’agrégation sont exécutées consiste à considérer l’exécution des requêtes comme divisée en deux parties. Tout d’abord, nous déterminons comment la requête fonctionnerait sans fonctions d’agrégation, renvoyant plusieurs lignes de résultats. Les fonctions d'agrégation sont ensuite appliquées aux résultats de la requête, renvoyant une seule ligne résultante.

Par exemple, considérons la requête complexe suivante : recherchez le revenu total moyen par habitant, la somme du revenu total des résidents et le rendement moyen de la source en pourcentage du revenu total du résident. L'opérateur donne la réponse

SÉLECTIONNEZ MOYENNE(SUMD), SOMME(SUMD), (100*AVG(ARGENT/SUMD))

DE PERSONNE, PROFIT, HAVE_D OÙ PERSONNE.NOM = HAVE_D.NOM ET HAVE_D.ID = PROFIT.ID

Sans fonctions d'agrégation, la requête ressemblerait à ceci :

SELECT SUMD, SUMD, M0NEY/SUMD FROM PERSON, PROFIT, HAVE_D OÙ PERSON.NOM = HAVE_D.NOM ET HAVE_D.ID = PROFIT.ID

et renverrait une ligne de résultats pour chaque résident et source de revenu spécifique. Les fonctions d'agrégation utilisent les colonnes du tableau des résultats de la requête pour produire un tableau à une seule ligne avec les résultats récapitulatifs.

Vous pouvez spécifier une fonction d'agrégation dans la ligne de colonne renvoyée au lieu de n'importe quel nom de colonne. Par exemple, il peut faire partie d'une expression qui ajoute ou soustrait les valeurs de deux fonctions d'agrégation :

SÉLECTIONNER MAX(SOMME)-MIN(SUMD)

Cependant, une fonction d'agrégation ne peut pas être un argument pour une autre fonction d'agrégation, c'est-à-dire Les fonctions d'agrégation imbriquées sont interdites.

De plus, la liste des colonnes renvoyées ne peut pas utiliser simultanément des fonctions d'agrégation et des noms de colonnes normaux, car cela n'a aucun sens, par exemple :

SÉLECTIONNER FIO, SOMME(SUMD)

Ici, le premier élément de la liste demande au SGBD de créer un tableau composé de plusieurs lignes et contenant une ligne pour chaque résident. Le deuxième élément de la liste demande au SGBD d'obtenir une seule valeur de résultat, qui est la somme des valeurs de la colonne SUMD. Ces deux instructions se contredisent, entraînant une erreur.

Ce qui précède ne s'applique pas aux cas de traitement de sous-requêtes et de requêtes avec regroupement.

Exemple 21. Obtenez le nombre total de fournisseurs (mot-clé COMPTER ):

SÉLECTIONNER LE COMPTE(*) COMME N

Le résultat est un tableau avec une colonne et une ligne contenant le nombre de lignes du tableau P :

Utiliser des fonctions d'agrégation avec des regroupements

Exemple 23 . Pour chaque pièce, obtenez la quantité totale fournie (mot clé PAR GROUPE …):

SOMME(PD.VOLUME) COMME SM

GROUPER PAR PD.DNUM ;

Cette demande sera exécutée comme suit. Tout d'abord, les lignes de la table source seront regroupées de manière à ce que chaque groupe contienne des lignes avec les mêmes valeurs DNUM. Ensuite, au sein de chaque groupe, le champ VOLUME sera additionné. Une ligne de chaque groupe sera incluse dans le tableau résultant :

Commentaire. Dans la liste des champs sélectionnés d'une instruction SELECT contenant une section GROUP BY, vous pouvez inclure seulement agréger des fonctions et des champs, qui sont inclus dans la condition de regroupement. La requête suivante générera une erreur de syntaxe :

SOMME(PD.VOLUME) COMME SM

GROUPER PAR PD.DNUM ;

La raison de l'erreur est que la liste des champs sélectionnés inclut le champ PNUM, qui Excluà la section GROUPE PAR. En effet, chaque groupe de lignes résultant peut contenir plusieurs lignes avec divers valeurs du champ PNUM. Une ligne totale sera générée à partir de chaque groupe de lignes. Cependant, il n'y a pas de réponse claire à la question de savoir quelle valeur choisir pour le champ PNUM dans la dernière ligne.

Commentaire. Certains dialectes SQL ne considèrent pas cela comme une erreur. La requête sera exécutée, mais il est impossible de prédire quelles valeurs seront saisies dans le champ PNUM du tableau résultant.

Exemple 24 . Obtenez les numéros de pièces dont la quantité totale fournie dépasse 400 (mot clé AYANT …):

Commentaire. La condition selon laquelle la quantité totale fournie doit être supérieure à 400 ne peut pas être formulée dans la clause WHERE, car Vous ne pouvez pas utiliser de fonctions d'agrégation dans cette section. Les conditions utilisant des fonctions d'agrégation doivent être placées dans une section HAVING spéciale :

SOMME(PD.VOLUME) COMME SM

GROUPER PAR PD.DNUM

AYANT SOMME(PD.VOLUME) > 400 ;

En conséquence, nous obtenons le tableau suivant :

Commentaire. Une requête peut contenir à la fois des conditions de sélection de ligne dans la section WHERE et des conditions de sélection de groupe dans la section HAVING. Les conditions de sélection de groupe ne peuvent pas être déplacées de la section HAVING vers la section WHERE. De même, les conditions de sélection de lignes ne peuvent pas être déplacées de la section WHERE vers la section HAVING, à l'exception des conditions qui incluent des champs de la liste de regroupement GROUP BY.

Utiliser des sous-requêtes

Un outil très pratique qui vous permet de formuler des requêtes de manière plus compréhensible est la possibilité d'utiliser des sous-requêtes imbriquées dans la requête principale.

Exemple 25 . Obtenir une liste des fournisseurs dont le statut est inférieur au statut maximum dans la table des fournisseurs (comparaison avec une sous-requête) :

OÙ P.STATYS<

(SÉLECTIONNER MAX(P.STATUT)

Commentaire

Exécuter une fois sous-requête imbriquée et obtenez la valeur d'état maximale.

Parcourez la table des fournisseurs P, en comparant à chaque fois la valeur du statut du fournisseur avec le résultat de la sous-requête, et sélectionnez uniquement les lignes dans lesquelles le statut est inférieur au maximum.

Exemple 26 . Utiliser un prédicat DANS

(SÉLECTIONNEZ PD.PNUM DISTINCT

OÙ PD.DNUM = 2);

Commentaire. Dans ce cas, une sous-requête imbriquée peut renvoyer une table contenant plusieurs lignes.

Commentaire. Le résultat de l'exécution de la requête sera équivalent au résultat de la séquence d'actions suivante :

Exécuter une fois sous-requête imbriquée et obtenez une liste des numéros de fournisseurs qui fournissent le numéro de pièce 2.

Parcourez la table des fournisseurs P en vérifiant à chaque fois si le numéro du fournisseur est contenu dans le résultat de la sous-requête.

Exemple 27 . Utiliser un prédicat EXISTER . Obtenez une liste des fournisseurs fournissant la pièce numéro 2 :

PD.PNUM = P.PNUM ET

Commentaire. Le résultat de l'exécution de la requête sera équivalent au résultat de la séquence d'actions suivante :

Scanner le tableau des fournisseurs P, chaque fois que vous exécutez une sous-requête avec la nouvelle valeur du numéro de fournisseur issue du tableau P.

Incluez dans le résultat de la requête uniquement les lignes de la table des fournisseurs pour lesquelles la sous-requête imbriquée a renvoyé un ensemble de lignes non vide.

Commentaire. Contrairement aux deux exemples précédents, la sous-requête imbriquée contient un paramètre (lien externe) transmis depuis la requête principale - le numéro de fournisseur P.PNUM. De telles sous-requêtes sont appelées corrélé (corrélé ). Une référence externe peut prendre une valeur différente pour chaque ligne candidate évaluée par la sous-requête, la sous-requête doit donc être réexécutée pour chaque ligne sélectionnée dans la requête principale. De telles sous-requêtes sont typiques du prédicat EXIST, mais peuvent être utilisées dans d'autres sous-requêtes.

Exemple 28 . Utiliser un prédicat N'EXISTE PAS . Obtenez une liste des fournisseurs qui ne fournissent pas la pièce numéro 2 :

PD.PNUM = P.PNUM ET

Commentaire. Tout comme dans l’exemple précédent, une sous-requête corrélée est utilisée ici. La différence est que la requête principale sélectionnera les lignes de la table des fournisseurs pour lesquelles la sous-requête imbriquée ne renvoie pas une seule ligne.

Exemple 29 . Obtenez les noms des fournisseurs fournissant toutes les pièces :

SÉLECTIONNER UN NOM DISTINCT

PD.DNUM = D.DNUM ET

PD.PNUM = P.PNUM));

Commentaire. Cette requête contient deux sous-requêtes imbriquées et implémente une opération relationnelle divisions des relations.

La sous-requête la plus interne est paramétrée par deux paramètres (D.DNUM, P.PNUM) et a la signification suivante : sélectionner toutes les lignes contenant des données sur les livraisons du fournisseur avec le numéro PNUM de la pièce avec le numéro DNUM. La négation NOT EXIST indique que ce fournisseur ne fournit pas cette pièce. La sous-requête qui lui est externe, qui est elle-même un paramètre P.PNUM imbriqué et paramétré, a du sens : pour sélectionner une liste de pièces qui ne sont pas fournies par le fournisseur PNUM. La négation NOT EXIST indique qu'il ne devrait y avoir aucune pièce pour un fournisseur avec un numéro PNUM qui ne soit pas fournie par ce fournisseur. Cela signifie exactement que la requête externe sélectionne uniquement les fournisseurs qui fournissent toutes les pièces.