32
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SQL Requêtes avancées : imbrication de requêtes, agrégats BD4 S.B. F.C. N. G.d.R. Licence MASS, Master ISIFAR, Paris-Diderot Février 2015 BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 1/45 Février 2015 1 / 45

SQL Requêtes avancées : imbrication de requêtes, agrégatsstephane-v-boucheron.fr/.../uploads/2015/02/beamer_SQL_2_tennis.pdf · SQL Requêtes avancées : imbrication de requêtes,

Embed Size (px)

Citation preview

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

SQLRequêtes avancées : imbrication de requêtes,

agrégats

BD4 S.B. F.C. N. G.d.R.

Licence MASS, Master ISIFAR, Paris-Diderot

Février 2015

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 1/45 Février 2015 1 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Requêtes avancées

Manipulations ensemblistes

Le résultat d’une requête SQL est un ensemble de tuples... doncune relation (virtuelle)On peut utiliser ces résultats comme données d’une autre requêteMoyens : utiliser des fonctions ensemblistes dans la clause WHERE.

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 2/45 Février 2015 2 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Requêtes avancées Opérateur IN

Utilisation de sous-requêtes : IN

Opérateur IN déjà vu pour exprimer que l’attribut est à valeur dansune certain liste.Nouvelle utilisation de IN avec des sous-requêtes.

<attribut > [ NOT ] IN (<sous - requête >)

Evaluée à vraie si <attribut> appartient au résultat de la sous-requête.

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 3/45 Février 2015 3 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Requêtes avancées Opérateur IN

Utilisation de sous-requêtes : IN

ExemplesListe des joueurs cancadiens.

SELECT DISTINCT last_name , first_nameFROM playerWHERE code IN ( SELECT code FROM

country_codesWHERE country = ’ Canada ’ ) ;

est équivalente à :

SELECT DISTINCT last_name , first_nameFROM player p, country_codes ccWHERE (p. code = cc. code )

AND (cc. country = ’ Canada ’)

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 4/45 Février 2015 4 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Requêtes avancées Opérateur IN

Utilisation de sous-requêtes : IN

Exemples : Lister les tournois auxquels a participé Federer

SELECT DISTINCT ON ( name ) name , location ,date_part ( ’YEAR ’, startdate ), surface

FROM tournament_big WHERE tid IN( SELECT tid

FROM played_in_bigWHERE registrnum IN

( SELECT registrnumFROM registration_bigWHERE pid IN

( SELECT pidFROM player_bigWHERE last_name

= ’ Federer ’)))

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 5/45 Février 2015 5 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Requêtes avancées Opérateur IN

Utilisation de sous-requêtes : IN

Pratique à utiliser sous la forme NOT IN

Les tournois auxquels Federer n’a pas participé

SELECT DISTINCT ON ( name ) name , location ,date_part ( ’YEAR ’, startdate ), surface

FROM tournament WHERE tid NOT IN( SELECT tid

FROM played_inWHERE registrnum IN

( SELECT registrnumFROM registrationWHERE pid IN

( SELECT pidFROM playerWHERE last_name

= ’ Federer ’)))

.... Une des façons d’introduire la Différence en SQLBD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 6/45 Février 2015 6 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Requêtes avancées Opérateur ALL et ANY

Utilisation de sous-requêtes : ANY, ALL

Deux nouveaux opérateurs manipulant des sous-requêtes : ANY, ALL

<attributs > =| < >| <=| <| >|= > ANY (<sous - requête >)

évaluée à vraie si au moins un des résultats de la sous requête vérifie lacomparaison avec <attributs>

<attributs > =| < >| <=| <| >|= > ALL (<sous - requête >)

évaluée à vraie si tous les résultats de la sous requête vérifient lacomparaison avec <attributs>

Nota< attributs > : peut être une liste d’attributs.

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 7/45 Février 2015 7 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Requêtes avancées Opérateur ALL et ANY

Utilisation de sous-requêtes : ANY, ALL

Exemple (alternative au IN) :

SELECT DISTINCT ON ( name ) name , location ,date_part ( ’YEAR ’, startdate ), surface

FROM tournament_big WHERE tid = ANY( SELECT tid

FROM played_in_bigWHERE registrnum IN

( SELECT registrnumFROM registration_bigWHERE pid IN

( SELECT pidFROM player_bigWHERE last_name = ’ Federer ’)))

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 8/45 Février 2015 8 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Opérateur EXISTS

Utilisation de sous-requêtes : EXISTS

[NOT] EXISTS (<sous-requête>)évaluée à vraie si la sous-requête renvoie au moins un résultat.Exemple : Nom, Prenom des joueurs défaits par Nadal

Il faut d’abord déterminer les rencontres où Nadal a gagné et endéduire les numéro d’inscription des perdants de ces rencontres.

Différence avec ANY

utilisation de EXISTS s’apparente à une condition Booléenne. Pas detest sur la valeur d’un attribut particulier.

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 11/45 Février 2015 11 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Opérateur EXISTS

Les joueurs vaincus par Nadal

SELECT DISTINCT ON ( last_name , first_name ) *FROM atp . player p2WHERE EXISTS

( SELECT *FROM atp . registration r2 JOIN atp . game gON (r2. registrnum =g. registrnum2 )JOIN atp . registration r1ON (r1. registrnum =g. registrnum1 )WHERE p2. pid =r2. pidAND r1. pid =1)

Remarquespid=1 est le numéro de Rafael Nadal et dans la relation game. Dansl’état actuel de la base, le numéro d’inscription du vainqueur estregistrnum1.

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 12/45 Février 2015 12 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Opérateur EXISTS Intersection, Union, Différence

Opérateurs ensemblistes

On peut disposer (suivant les implémentations) des opérateursensemblistes

Intersection

<requête1 > INTERSECT <requête2 >

Union

<requête1 > UNION <requête2 >

Différence

<requête1 > EXCEPT <requête2 >

En PostgreSQL : UNION, INTERSECT et EXCEPT sont proposés.

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 14/45 Février 2015 14 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Opérateur EXISTS Intersection, Union, Différence

NOT EXISTS versus NOT IN

Sur le papier ...L’utisation de NOT EXISTS et des sous-requêtes corrélées permetd’émuler complètement la différence. NOT IN convient lorsque lecritère d’élimination porte sur une seule colonne.

Le procédé NOT EXISTS peut s’avérer couteux si les tables ne sont pasconvenablement indexées.

Si les deux tables disposent d’une clé primaire commune, NOT INopérant sur les clés primaires suffit.

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 17/45 Février 2015 17 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Sous requêtes : WITH

Sous-requêtes nommées, clause WITH

WITH with_query_name [ ( column_name [, ...] ) ]AS ( SELECT ... )

SELECT ....FROM ...WHERE ...GROUP BY ...HAVING ...ORDER BY ...

Rôle de la sous-requête nommée

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 18/45 Février 2015 18 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

La division

Division

Rappel d’algèbre relationnelle : division de R par S- relation R de schéma R(A1,A2, . . . ,Ak)

- relation S de schéma S(Ap+1, . . . ,Ak) (Schema de S ⊂ Schema de R)La division (ou le quotient) de R par S est une relation T de schémaT (A1, . . . ,Ap) formée des tuples qui, concaténés à chaque tuple de S,donnent un tuple de R.On note

T = R ÷ S

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 20/45 Février 2015 20 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

La division

Définition de la divisionArbre de requêtes- R de schéma R(A1,A2, . . . ,Ak), S de schéma S(Ap+1, . . . ,Ak)est la relation T = R ÷ S de schéma T (A1, . . . ,Ap).

πA1,...,Ap

SπA1,...,Ap

R

πA1,...,Ap

R

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 21/45 Février 2015 21 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

La division

Division en SQL (PostgreSQL)

WITH Rp AS (SELECT A1 ,... , ApFROM R

)SELECT * FROM RpEXCEPTSELECT A1 ,... , Ap

FROM (SELECT * FROM Rp , SEXCEPTSELECT * FROM R) R2 ;

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 22/45 Février 2015 22 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

La division

Exemple

WITH R AS( SELECT tid , pid

FROM atp . participationsWHERE date_part =2008 AND surface = ’Clay ’),

S AS( SELECT DISTINCT tid

FROM R),Rp AS

( SELECT DISTINCT pid FROM R)SELECT pidFROM RpEXCEPTSELECT pid

FROM( SELECT tid , pid

FROM Rp ,SEXCEPTSELECT *FROM R) U ;

La construction WITH permet de simplifier l’écriture.

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 23/45 Février 2015 23 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

La division

Illustration de la division

Lister les joueurs qui participé à tous les tournois sur terre battue en 2008On construit une table intermédiaire, la table des couples pid,tid oùtid est le numéro d’un tournoi disputé en 2008 sur terre battue et pid .

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 25/45 Février 2015 25 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

La division

Exemples

Nom, prénom des joueurs qui ont participé à tous les tournois à plus de 5 tours disputés en 2008 surterre battue.

SELECT last_name , first_name , pidFROM atp . playerWHERE pid IN ( WITH R AS( SELECT tid , pid

FROM atp . participationsWHERE date_part =2008 AND surface = ’Clay ’ AND numrounds >5) ,

S AS( SELECT DISTINCT tidFROM R),Rp AS( SELECT DISTINCT pid FROM R)SELECT pid FROM RpEXCEPTSELECT pid FROM

( SELECT tid , pid FROM Rp ,SEXCEPT

SELECT * FROM R) U)ORDER by last_name;

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 26/45 Février 2015 26 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Agrégats Fonctions SUM et AVG

Fonctions d’agrégation en SQL

Possibilité de compter, de faire des moyennes, de prendre unmaximum en SQL (contrairement à l’algèbre relationnelle”classique”)Possibilité de partitionner et regrouper les données

Dans la partie SELECT

SUM ( DISTINCT | ALL < nomattribut >)

Somme des valeurs prises par nomattribut

AVG ( DISTINCT | ALL < nom_attribut >)

Moyenne des valeurs prises par nomattributLe type des attributs doit être un nombre ou un entier (sinon, paspossible de compter...).

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 27/45 Février 2015 27 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Agrégats Fonctions SUM et AVG

Fonctions d’agrégation : SUM et AVG

Exemple : nombre moyen de sets par rencontre pour les tournoisdisputés sur terre battue

SELECT AVG ( numsets )FROM tournament t JOIN

game g ON (t. tid =g. tid ) JOINmatch_results m ON (g. mid =m. mid )

WHERE t. surface = ’Clay ’ ;

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 28/45 Février 2015 28 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Agrégats Fonctions MAX et MIN

Fonctions de calcul : MAX,MIN

Usage

MAX (< nomattribut >)

maximum des valeurs prises par <nomattribut>

MIN (< nomattribut >)

minimum des valeurs prises par <nomattribut>

Nombre maximal de jeux disputés dans un set.

SELECT MAX ( winnergames + losergames )FROM setscore

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 30/45 Février 2015 30 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Agrégats Fonction COUNT

Fonctions de calcul : COUNT

On peut aussi compter le nombre de tuples dans un résultat de requête

COUNT (*|[ ALL | DISTINCT < nomattribut >])

nombre de valeurs prises par le résultat

DISTINCT : sans les doublonsALL : avec les doublons* : y compris les valeurs nulles

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 31/45 Février 2015 31 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Agrégats Fonction COUNT

Exemple : Nombre de tournois disputés parFederer

SELECT COUNT (*)FROM tournament_big WHERE tid = ANY

( SELECT tidFROM played_in_bigWHERE registrnum IN

( SELECT registrnumFROM registration_bigWHERE pid IN

( SELECT pidFROM player_bigWHERE last_name

= ’ Federer ’)))

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 32/45 Février 2015 32 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Partition et groupe GROUP BY

Partition de résultats de requêtes

GROUP BY < nomattribut1 >, ... , < nomattributn >

GROUP BY permet de regrouper par valeur de certains attributsl’ensemble des résultats d’une requêteForme des sous-relations auxquelles on peut appliquer desopérateurs (SUM, MAX, ...) renvoyant un résultat unique par groupe.

Exemple d’utilisation : regrouper les livraisons par numéro de fournisseuret prendre la quantité maximum livrée par fournisseur.

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 35/45 Février 2015 35 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Partition et groupe GROUP BY

Partition de résultats de requêtes

Quel est le sens de cette requête ?

SELECT first_name , COUNT (*)FROM atp . playerGROUP BY first_name

Les attributs présents dans le SELECT sont forcément présents dans leGROUP BY. Pourquoi ? i.e. cette requête a-t-elle un sens ?

SELECT last_name , first_name , MAX ( code )FROMatp . playerGROUP BY first_name

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 38/45 Février 2015 38 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Partition et groupe GROUP BY

Partition de résultats de requêtes

La clause HAVING permet de poser une condition portant surchacune des sous-relations générées par le GROUP BY

Les sous-relations ne vérifiant pas la condition sont écartées durésultat.

Exemple : liste des pays qui comptent plus de 3 joueurs

SELECT codeFROM atp . playerGROUP BY codeHAVING COUNT (*) >= 3

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 39/45 Février 2015 39 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Partition et groupe Tri des résultats

Présentation/tri de résultats

La clause ORDER BY permet de trier le résultat de la requête, enfournissant la liste des attributs sur lesquels effectuer le tri et enspécifiant le sens du tri (ascendant ou descendant)

Exemple : liste des tournois sur terre battue par date decommencement décroissante salaire

SELECT name , location , startdateFROM tournamentWHERE surface = ’Clay ’ORDER BY startdate DESC

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 40/45 Février 2015 40 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Partition et groupe Tri des résultats

Regroupement, tri, etc : exemple

Exemple : Que fait cette requête ?

SELECT surface ,date_part ( ’MONTH ’, startdate ) mois ,COUNT (*) nombre

FROM atp . tournamentWHERE numrounds >5 AND ttype LIKE ’ Singles ’GROUP BY surface , date_part (’ MONTH ’, startdate )HAVING COUNT (*) >2ORDER BY mois ;

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 42/45 Février 2015 42 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Partition et groupe Tri des résultats

Une sortie

surface mois nombreHard 1 16Hard 3 30Clay 4 32Clay 5 44Grass 6 31Clay 7 18Hard 7 16Hard 8 61Hard 9 3Carpet 10 6Hard 10 24Hard 11 3

(12 rows)

Pour chaque mois calendaire,chaque surface, le nombre detournois disputés en simple,comprenant plus de 5 tours (si cenombre est supérieur à 2), trié parmois.

Que serait le nombre moyen detournois disputés en simple, parmois et par surface ?

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 43/45 Février 2015 43 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Partition et groupe Tri des résultats

Une réponse

SELECT surface ,date_part ( ’MONTH ’, startdate )

mois ,COUNT (*)/

COUNT ( DISTINCTdate_part ( ’YEAR ’, startdate ))

moyenneFROM atp . tournamentWHERE ttypeLIKE ’ Singles ’GROUP BY surface ,

date_part (’MONTH ’, startdate )ORDER BY mois ;

surface mois moyenneCarpet 1 1Clay 1 1Hard 1 6Carpet 2 1Clay 2 3Hard 2 6Clay 3 1Hard 3 2Clay 4 6Clay 5 4Clay 6 1Grass 6 5Clay 7 6Grass 7 1Hard 7 2Clay 8 1Hard 8 4

(25 rows)

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 44/45 Février 2015 44 / 45

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

...

.

Conclusion

Résumé

Syntaxe générale

SELECT <attributs >FROM <relations >[ WHERE <condition > ][ GROUP BY < attributs de partitionnement >[ HAVING <condition >] ][ ORDER BY <critere >]

SELECT : attributs du résultat (avec agrégats éventuels)WHERE : condition de sélection indépendante du Group ByHAVING : condition de sélection portant sur les agrégats

BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 45/45 Février 2015 45 / 45