of 79 /79
1 Les SGBDs Supports de Décisions Witold Litwin

1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

Embed Size (px)

Citation preview

Page 1: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

1

Les SGBDs Supports de Décisions

Witold Litwin

Page 2: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

2

Besoin

• Utilisation massive de fonctions agrégats– celles de SQL– autres

• dans le langage hôte

• Exemples– La moyenne de ventes– Le commercial du jour– La corrélation entre certaines attributs

Page 3: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

3

Problèmes nouveauxParcours fréquent de tous les tuples

– d'une grande table– d'une collection de tables à joindre

L'opération peut prendre beaucoup de temps– quel que soit l'optimisation de chemins d'accès

Des requêtes peuvent être impossible à formuler en SQL seul– Classement par rang selon les valeurs de T.a– Corrélation entre T.a et T.b ?– Les attributs les plus corrélés ?

Page 4: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

4

Solutions• Nouvelles fonctions de SQL

– Fonctions agrégats• Rank, Rollup, Cube, Covariance, Correlation…

– Fonctions scalaires

• Nouveaux concepts– Donnée multidimensionnelle– Espaces des agrégations– Espace des influences– Espace des variations

Page 5: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

5

Solutions• Nouvelles techniques d'implémentation

– Calcul parallèle et scalable

– Prè-calcul partiel ou total de requêtes

– Échantillonnage

– Séparation de calculs en plusieurs bases et systèmes

Page 6: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

6

Solutions• Nouveau types de SGBD

– Entrepôts de Données• Data Warehousing

– Fouille de Données• Data Mining (DM)

– Analyse en ligne• Online Analytical Processing (OLAP)

• Les SGBD "traditionnels" sont dès lors souvent appelés– Online Transaction Processing (OTPS) Systems

• Les nouveaux constituent les– Systèmes de Support de Décisions

• Decision Support Systems

Page 7: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

7

Principaux produits• Informix

– Basé sur l'achat de Red Brick

• DB2 – SGBD– Data Warehouse CenterSQL Server– SGBD– Data Analysis Services

• OLAP Services renommés pour SQL Server 2000

• Oracle Express

Page 8: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

8

Prix de produit 123

Espace des Données

Support de Decisions

OLAP traditionnel

Page 9: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

9

Prix de produit 123

Espace des Données

Support de Decisions

Espace (OLAP)des Agrégations

Vente par produit et par mois

OLAP traditionnel

Page 10: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

10

Prix de produit 123

Espace des Données

Support de Decisions

Espace (OLAP)Des Agrégations

Espace Des Influences(Data Mining)

Vente par produit et par mois

Facteursd'influencedes ventesà Paris

OLAP traditionnel

Page 11: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

11

Prix de produit 123

Espace des Données

Support de Decisions

Espace (OLAP)des Agrégations

Espace des Influences(Data Mining)

Espace des Variations

Vente par produit et par mois

Facteursd'influencedes ventesà Paris

Changementde ventes depuis 3 mois

OLAP traditionnel

Page 12: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

12

Espaces de Données Concepts

Relations Ensembles

Agrégations Arithmétique

Influences Logique et Stats.

Variations Calcul différentiel

Page 13: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

13

Technologie• BDs parallèles, distribuées, scalables • Jointures pré-calculées

– intégrité référentielle

• Vues matérialisées• Algorithmes statistiques• Algorithmes décisionnels

– revus pour l'application aux BDs

• Systèmes multibases– Entrepôts de données

• Data warehousing

Page 14: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

14

Vue matérialisée

• create materialized view T(sum, prod) as select sum(montant) prod from Ventes groupby produit ;

• Les requêtes sont reformulées contre la vue– d'une manière transparente pour l'usager

select * from T where prod = 'p1' ;

sum prod30 p150 p227 p3

T

Page 15: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

15

Vue matérialisée

• Problème général– Cohérence entre la vue et ses tables de

base

• Algorithme de MAJ– pour toute nouvelle vente de Produit p

• add le montant à T.sum where prod = p ;

sum prod30 p150 p227 p3

T

Page 16: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

16

Vue matérialisée

• La cohérence peut être implantée par– des déclencheurs– des procédures stockées (stored procedures)

• Exercise– Que faire pour la requête

select avg (montant) prod from Ventes groupby produit

Note : plusieurs solutions existent

Page 17: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

17

Problème de scalabilité

• On peut difficilement maintenir un grand nombre de vues matérialisées– il faudrait executer des dizaines de

déclencheurs– et ne pas handicaper les perf. OLTP– et comment faire pour les avortements et la

concurrence ?

Page 18: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

18

Axes de solutions

• Systèmes multiordinateurs• Cohérence souple (ang. loose) entre les tables

de bases et les vues matérialisées– la somme de ventes peut être 0.05 % inexacte

pendant 10 sec., de temps en temps, sans que le DBA se fasse gronder

• nécessité dans les systèmes mobiles

• Les MAJ sont propagées en asynchrone après le Commit

Page 19: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

19

Les statistiques• Correlations

– DFs notamment (correlation = 1)

• Regressions• Nuées Dynamiques

– Diday

• Analyse factorielle & multi-dimensionnelle en général– Benzecri

• Aide au diagnostic – Bayes

Page 20: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

20

OLAP / Data Mining• OLAP

– Les agrégations demandées sont connues– On peut formuler les requêtes SQL

• ou SQL imbriqué

• Data Mining– on ne sais pas a priori quoi demander

• quelles sont les corrélations parmi les attributs de Ventes ?

• Cette dichotomie est floue

Page 21: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

21

Data Mining dans un espace des influences

• Le résultat peut être attendu sous forme

– d'un graphique (business graphics)

Page 22: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

22

Data Mining dans un espace des influences

• Le résultat peut être attendu sous forme

– d'un graphique (business graphics) 2-D

01020

30405060

708090

1stQtr

2ndQtr

3rdQtr

4thQtr

East

West

North

Page 23: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

23

Data Mining dans un espace des influences

• Le résultat peut être attendu sous forme

– d'un graphique (business graphics) 3-D

1stQtr

2ndQtr

3rdQtr

4thQtr

EastWest

North

020406080100

1stQtr

2ndQtr

3rdQtr

4thQtr

EastWest

North

East

West

North

Page 24: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

24

• Le résultat peut être attendu sous forme

– d'une règle logique

if customer.age < 25 and power > '9 CH' and status = 'bachelor'

then prob (accid.# / year > 1) = 0.99 ;

Data Mining dans un espace des influences

Page 25: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

25

Data Mining dans un espace des influences

• Ceci conduit au problèmes d'inférence de règles – la logique– BDs actives– BD de connaissances– BDs déductives

Page 26: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

26

• On observe les variations selon des dimensions– variation de cours de bourse

• depuis une semaine, une année, 1 Janv. 1996...

– variation de ventes (ang. lift)• selon la campagne de promotion• selon la saison

– etc

• Précalcul de variations est encore plus couteux que celui de facteurs statiques– surtout de variations temporelles

Mesures dans un espace des variations

Page 27: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

27

Mesures dans un espace des variations

1stQtr

2ndQtr

3rdQtr

4thQtr

East

West

North

0

50

100

1stQtr

2ndQtr

3rdQtr

4thQtr

East

West

NorthEast

West

North

Page 28: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

28

• On attend deux types de résultats distincts l'état de l'existant

• découverte, explication, confirmation

l'analyse de la tendance• prédictions

• Lex deux aspects sont encore en état de recherche ou, au mieux, en développent avancé

Data Mining dans un espace des Data Mining dans un espace des influencesinfluences

Page 29: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

29

Schéma en Étoile

• Table de faits– En général très

grande

• Tables de dimensions– En général très

petites

• Liens clé primaire / clé étrangère

Page 30: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

30

Architecture d'entrepôt de données IBM

Page 31: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

31

Fonctions Agrégat pour OLAPDB2

• CountBig – Pour le nombre de tuples > 2**31

• Covariance – entre des attributs ou des expressions de valeur

• Correlation– entre des attributs ou des expressions de valeur

• Regression functions– 10 fonctions– Les paramètres de la droite de régression entre des attributs ou des

expressions de valeur

• Rank, Dense_Rank– Rank en présence de duplicata : 1,2,2,4,4,6…– Dense_Rank en présence de duplicata : 1,2,2,3,3,4…

Page 32: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

32

Covariance• Soit avgexp1 le résultat de AVG(expression1) et soit avgexp2 le résultat de AVG(expression2),

• Alors COVARIANCE(expression1, expression2) est:

AVG( (expression1 - avgexp1) * (expression2 - avgexp2 )

SELECT COVARIANCE(SALARY, BONUS)

FROM EMPLOYEE

WHERE WORKDEPT = 'A00'

Page 33: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

33

Correlation

Définition :

COVARIANCE(expression1,expression2)/(STDDEV(expression1)*STDDEV(expression2))

SELECT CORRELATION(SALARY, BONUS)

FROM EMPLOYEE

WHERE WORKDEPT = 'A00'

Page 34: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

34

Fonctions Agrégat pour OLAPDB2

• Fonctions spécifiques à DB2 Data Warehouse Center:– Analyse de la variance (ANOVA)

• Distributions de Fisher-F

• Valeur P

– Sous-totaux par période de temps

– Test de Chi-carré

– Statistique T pour n paires de valeurs et à partir du coeff. de corrélation r

T = r * ((n – 2 ) / (1 - r 2 ))0.5

– T 0 indique une corrélation entre les attributs

Page 35: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

35

Fonctions Agrégat pour OLAPTeradata

• RANK

• CSUM (Cumulative (Running) Sums)

• MAVG and MSUM

• MDIFF (Moving Difference)

• QUANTILE

• QUALIFY

• SAMPLE

Page 36: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

36

SELECT EmployeeName, (HireDate - DATE) AS ServiceDays,

RANK ( ServiceDays ) as Seniority FROM Employee

ORDER BY EmployeeName;

EmployeeName Service Days Seniority

Robyn Baker 9931 2

Nick Garrison 9931 2

Kyle McVicker 9408 5

Eva O’Malley 10248 1

Norma Powers 9409 4

Page 37: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

37

SELECT Item, SalesDate, CSUM(Revenue,Item,SalesDate) as CumulativeSales FROM

(SELECT Item, SalesDate, Sum( Sales ) as Revenue FROM DailySales

WHERE StoreId=5 AND SalesDate Between ‘1/1/1999’ and ‘1/31/1999’

GROUP BY Item, SalesDate) AS ItemSales

ORDER BY SalesDate ;

Item SalesDate CumulativeSales

InstantCOW 01/01/1999 972.99

InstantCOW 01/02/1999 2361.99

InstantCOW 01/03/1999 5110.97

InstantCOW 01/04/1999 7793.91

Page 38: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

38

SELECT MarketDay,ClosingPrice, MAVG(ClosingPrice,50,MarketDay) as XYZAverage

FROM MarketDailyClosing

WHERE Ticker = ‘XYZ’

ORDERBY MarketDay;

MarketDay ClosingPrice XYZAverage

12/27/1999 89 1/16 85 1/2

12/28/1999 91 1/8 86 1/16

12/29/1999 92 3/4 86 1/2

12/30/1999 94 1/2 87

Page 39: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

39

SELECT MarketWeek, WeekVolume, MDIFF (WeekVolume,1,MarketWeek)asXYZVolumeDiff

FROM (SELECT MarketWeek, SUM(Volume) as WeekVolume

FROM MarketDailyClosing WHERE Ticker = ‘XYZ’

GROUP BY MarketWeek)

ORDERBY MarketWeek;

MarketWeek WeekVolume XYZVolumeDiff

11/29/1999 9817671 ?

12/06/1999 9945671 128000

12/13/1999 10099459 153788

12/20/1999 10490732 391273

12/27/1999 11045331 554599

Page 40: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

40

SELECT Item, Profit, QUANTILE( 10, Profit ) as Decile

FROM (SELECTItem,Sum(Sales)—(Count(Sales)*ItemCost) asProfit

FROM DailySales, Items WHERE DailySales.Item = Items.Item

GROUP BY Item) AS ItemProfit;

Item Profit Decile

High Tops 97112 9

Low Tops 74699 7

Running 69712 6

Casual 28912 3

Xtrain 100129 9

Page 41: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

41

SELECT Item, Profit, QUANTILE( 100, Profit ) AS Percentile

FROM (SELECT Item,Sum(Sales) — (Count(Sales)* Items.ItemCost) as Profit FROM DailySales, Items

WHERE DailySales.Item = Items.Item

GROUP BY Item) AS ItemProfit

QUALIFY Percentile = 99;

Item Profit Percentile

Fat Free Ice Cream 10.79 99

Sugar Free Ice Cream -100.55 99

Fake Chocolate Ice Cream -1110.67 99

Fat/Sugar Free Ice Cream -2913.88 99

Fake Ice Cream -4492.12 99

Page 42: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

42

SELECT customer_id, age, income, marital_status, …, SAMPLEID FROM customer_tableSAMPLE 0.6, 0.25, 0.15

customer_id age income marital_status ... SAMPLEID

1362549 17 0 1 1

1362650 21 17804 2 1

1362605 34 16957 2 1

1362672 50 16319 2 3

1362486 76 10701 3 1

1362500 40 56708 1 3

1362489 35 55888 3 2

1362498 60 9849 2 1

1362551 27 23085 1 1

1362503 18 5787 1 .. 2

Page 43: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

43

• Groupements multiples (super-groupes) selon une dimension– l ’ordre des attributs dans la clause a l ’importance– les attributs sont progressivement « oublies » de droite

à gauche

• Remplace plusieurs requêtes GROUP BYSELECT p#, sum (qty) as tot-qty from S,SP, P WHERE SP. P# = P.P# AND SP. S# = S.S#GROUP BY ROLLUP (P#, S.CITY, COLOR)HAVING tot-qty > 100 ;

• Problèmes avec des nulls que l’on verra plus tard

ROLLUP(DB2 & SQL-Server)

Page 44: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

44

SELECT WEEK(SALES_DATE) AS WEEK, DAYOFWEEK(SALES_DATE) AS DAY_WEEK, SALES_PERSON, SUM(SALES) AS UNITS_SOLD FROM SALES WHERE WEEK(SALES_DATE) = 13

GROUP BY ROLLUP ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON )

ORDER BY WEEK, DAY_WEEK, SALES_PERSON

WEEK DAY_WEEK SALES_PERSON UNITS_SOLD

----------- ----------- --------------- ----------- 13 6 GOUNOT 11 13 6 LEE 12 13 6 LUCCHESSI 4 13 6 - 27 13 7 GOUNOT 21 13 7 LEE 21 13 7 LUCCHESSI 4 13 7 - 46 13 - - 73 - - - 73

Page 45: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

45

• Groupements multiples selon toutes les dimensions– l ’ordre des attributs dans la clause n ’a pas d ’importance– les attributs sont progressivement « oublies » de droite à

gauche

• Remplace plusieurs requêtes GROUP BYSELECT p#, sum (qty) as tot-qty from S,SP, P WHERE SP. P# = P.P# AND SP. S# = S.S#GROUP BY CUBE (P#, S.CITY, COLOR)HAVING tot-qty > 100 ;

• Problèmes avec des nuls que l’on verra plus tard• Opération bien plus chère que Rollup

– 2N requêtes générées au lieu de N, pour N attributs

CUBE(DB2 & SQL-Server)

Page 46: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

46

SELECT WEEK(SALES_DATE) AS WEEK,

DAYOFWEEK(SALES_DATE) AS DAY_WEEK,

SALES_PERSON, SUM(SALES) AS UNITS_SOLD

FROM SALES

WHERE WEEK(SALES_DATE) = 13

GROUP BY CUBE ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON )

ORDER BY WEEK, DAY_WEEK, SALES_PERSON

CUBE(DB2 & SQL-Server)

Page 47: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

47

WEEK DAY_WEEK SALES_PERSON UNITS_SOLD ----------- ----------- --------------- ----------- 13 6 GOUNOT 11 13 6 LEE 12 13 6 LUCCHESSI 4 13 6 - 27 13 7 GOUNOT 21 13 7 LEE 21 13 7 LUCCHESSI 4 13 7 - 46 13 - GOUNOT 32 13 - LEE 33 13 - LUCCHESSI 8 13 - - 73 - 6 GOUNOT 11 - 6 LEE 12 - 6 LUCCHESSI 4 - 6 - 27 - 7 GOUNOT 21 - 7 LEE 21 - 7 LUCCHESSI 4 - 7 - 46 - - GOUNOT 32 - - LEE 33 - - LUCCHESSI 8 - - - 73

Page 48: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

48

GROUPING SETS• On indique explicitement les groupes

– entre (..)• le groupe () est constitué de toute la table

SELECT p#, sum (qty) as tot-qty from S,SP, P

WHERE SP. P# = P.P# AND SP. S# = S.S#

GROUP BY GROUPING SETS ((P#, S.CITY, COLOR), (P#, COLOR), ())

HAVING tot-qty > 100 ;

• Problèmes avec des nuls que l’on verra plus tard

Page 49: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

49

SELECT SALES_PERSON,

MONTH(SALES_DATE) AS MONTH,

SUM(SALES) AS UNITS_SOLD

FROM SALES

GROUP BY GROUPING SETS ( (SALES_PERSON, MONTH(SALES_DATE)),

()

)

ORDER BY SALES_PERSON, MONTH

GROUPING SETS

Page 50: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

50

SALES_PERSON MONTH UNITS_SOLD --------------- ----------- ----------- GOUNOT 3 35 GOUNOT 4 14 GOUNOT 12 1 LEE 3 60 LEE 4 25 LEE 12 6 LUCCHESSI 3 9 LUCCHESSI 4 4 LUCCHESSI 12 1 - - 155

GROUPING SETS

Page 51: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

51

SELECT WEEK(SALES_DATE) AS WEEK,

DAYOFWEEK(SALES_DATE) AS DAY_WEEK,

MONTH(SALES_DATE) AS MONTH,

REGION,

SUM(SALES) AS UNITS_SOLD

FROM SALES

GROUP BY GROUPING SETS ( ROLLUP( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE) ),

ROLLUP( MONTH(SALES_DATE), REGION ) )

ORDER BY WEEK, DAY_WEEK, MONTH, REGION

GROUPING SETS & ROLLUP

Page 52: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

52

WEEK DAY_WEEK MONTH REGION UNITS_SOLD ----------- ----------- ----------- --------------- ----------- 13 6 - - 27 13 7 - - 46 13 - - - 73 14 1 - - 31 14 2 - - 43 14 - - - 74 53 1 - - 8 53 - - - 8 - - 3 Manitoba 22 - - 3 Ontario-North 8 - - 3 Ontario-South 34 - - 3 Quebec 40 - - 3 - 104 - - 4 Manitoba 17 - - 4 Ontario-North 1 - - 4 Ontario-South 14 - - 4 Quebec 11 - - 4 - 43 - - 12 Manitoba 2 - - 12 Ontario-South 4 - - 12 Quebec 2 - - 12 - 8 - - - - 155 - - - - 155

Page 53: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

53

Rollup, Cube, Grouping Setssous MsAccess

• Il y en a pas

• On peut simuler ces manipulations en utilisant – GROUP BY– UNION ALL– ORDER BY

• Peut être laborieux pour le CUBE

Page 54: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

54

ROLLUPROLLUP

Remarquez le « null as city»

Page 55: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

55

ROLLUPROLLUP

Page 56: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

56

ROLLUPROLLUP

Et le CUBE ?

Une solution pour l ambiguïté

de certains nuls ?

Page 57: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

57

Fonctions scalaires• S ’appliquent aux valeurs individuelles

– d ’attributs

– d agrégations SQL

• Il y a plusieurs catégories– mathématiques

– financières

– chaînes de caractères

– ….

• Varient entre les dialectes– MsAccess possède UCASE, pas DB2– DB2 possède LOG10, pas MsAccess

Page 58: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

58

Fonctions scalaires• Peuvent s’imbriquer

– contrairement aux agrégats SQL

SELECT log((sum([qty]^2)^(1/2))) as exempleFROM SP group by [p#]having int(log(sum([qty]))) = 5

exemple5.708757640082795.99146454710798

Page 59: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

59

Tabulations Croisées(Crosstab queries)

Tabulations Croisées(Crosstab queries)

• Présentent les résultat sous forme habituelle de feuilles de calculs– Les agrégats SUM, AVG.. de GROUP BY et les valeurs

individuelles en même temps– Impossible avec SQL standard

• Transforment les valeurs d'attributs en attributs– Par exemple

• les valeurs de P# trouvés pour un même S# deviennent les attributs P1, P2,...

• les valeurs de P1, P2.. sont les QTY (par ex.) correspondants

Page 60: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

60

TRANSFORM Sum(SP.Qty) SELECT SP.[S#], Sum(SP.Qty) AS [Row

Summary]FROM SPGROUP BY SP.[S#]PIVOT SP.[p#];

Tabulations Croisées Tabulations Croisées

Nouvellescolonnes

Page 61: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

61

S# Total Qty p1 p2 p3 p4 p5 p6

s1 1300 300 200 400 200 100 100

s2 700 300 400

s3 200 200

s4 900 200 300 400

L'intitulé Total Qty est mis par défaut par MsAccess

Tabulations Croisées Tabulations Croisées

Page 62: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

62

• La fonction agrégat dans la clause TRANSFORM est obligatoire– bien que SUM(QTY) = AVG(QTY) = QTY– mais, COUNT(QTY) = 1

• On peut générer une expression de valeur TRANSFORM SUM(0.5*QTY) AS [Q2]SELECT Sum(SP.[Q2]) AS [Qte tot. dans 1 mois], Avg(P.[Q2]) AS [Qte moy. dans 1 mois]FROM SPGROUP BY SP.[S#]PIVOT SP.[p#];

Tabulations Croisées Tabulations Croisées

Page 63: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

63

On peut utiliser la clause WHEREWHERE P# IN ('P1', 'P2')

• Alors les fonctions ne calculent les agrégats que sur P1 et P2.

• On peut aussi restreindre la tabulation seulement PIVOT SP.[p#] IN ('P1', P2')

• Mais, cette clause n'affecte pas les calculs des agrégats Peut-on appliquer la clause ORDER BY ?

Si oui, quel serait l’effet sur les valeurs pivotées ? Peut-on ordonner par rapport à une fonction agrégat ?

Comme on a fait pour les requêtes à GROUP BY ? Peut-on appliquer la clause HAVING ?

Tabulations Croisées Tabulations Croisées

Page 64: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

64

Clauses COMPUTE & FOR BROWSE(SQL Server 6.5 et 7)

• expression générale de sélection:SELECT [DISTINCT] attribut(s)

FROM table(s)[WHERE condition][GROUP BY [ROLLUP | CUBE] field(s) ][HAVING condition ][ORDER BY attribute(s)][COMPUTE row-agg (colonne), [row-agg (colonne)]... BY (colonne), [(colonne)]...][FOR BROWSE]

Clause FOR BROWSE permet une MAJ de données dynamiquement visualisées

Page 65: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

65

ComputeCompute• select title, type, price, advance

from titles where ytd_sales is not nullorder by type desc

compute AVG(price), SUM(advance) by typecompute SUM(price), SUM(advance)

go• title type price advance

fifty years... trad_cook 11.95 4000Onions.. trad_cook 20.95 7000Sushi... trad_cook 14.99 8000

avg15.96 sum

19000 title type price advanceComputer... psychology 21.95 7000... psychology .... ....

avg15.96 sum

19000 sum sum236.00 88400

• select title, type, price, advancefrom titles where ytd_sales is not nullorder by type desc

compute AVG(price), SUM(advance) by typecompute SUM(price), SUM(advance)

go• title type price advance

fifty years... trad_cook 11.95 4000Onions.. trad_cook 20.95 7000Sushi... trad_cook 14.99 8000

avg15.96 sum

19000 title type price advanceComputer... psychology 21.95 7000... psychology .... ....

avg15.96 sum

19000 sum sum236.00 88400

Page 66: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

66

Fermeture transitive (DB2)• Considère la table COURS (CNOM, PNOM, NMIN)

contenant les cours et leur pré-requis avec les notes minimales pour l ’admission en cours– ex. (BDs, Str-Ph, 15)

• Tout PNOM est une clé étrangère avec CNOM comme clé primaire ailleurs dans COURS

• Un cours peut avoir plusieurs pré-requis• Trouver les noms de tous les pré-requis de cours CNAME = ‘ BDs ’

– y compris les pré-requis de pré-requis etc.

• C ’est le calcul de la fermeture transitive• Impossible encore sous MsAccess, SQL Server =< 6.5, Oracle,

Informix…• Possible sous DB2, à partir de V 5.0

Page 67: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

67

Fermeture transitive (DB2)

WITH PRQ (CNOM, PNOM) AS((SELECT CNOM, PNOM

FROM COURS WHERE CNOM = ‘ BDs ’) UNION ALL

(SELECT C.CNOM, C.PNOM FROM PRQ AS P, COURS AS CWHERE C.CNOM = P.PNOM))

SELECT DISTINCT PNOM FROM PRQ

• La requête récursive s exécute tant que la sélection dans PRQ avec PRQ courant n’est pas vide.

Requête initiale

Requête récursive

Requête finale

Page 68: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

68

Fermeture transitive (DB2)

Requête initiale (préreq. de BDs

1ere exécution de la Requête récursive (préreq. de BDs)

COURS

2eme exécution de la Requête récursive (préreq. de préreq. de BDs)

3eme exéc. de la RR (préreq. de préreq. de préreq. de BDs) etc.

Page 69: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

69

Fermeture transitive (DB2)

CNOM PNOM

BDs StPh

Bds Lprg

CNOM PNOM NMIN

BDs StPh 15BDs LPrg 12StPh InfG 13InfG Math 11Lprg InfG 12

Page 70: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

70

Fermeture transitive (DB2)

CNOM PNOM

BDs StPh

Bds Lprg

CNOM PNOM NMIN

BDs StPh 15BDs LPrg 12StPh InfG 13InfG Math 11Lprg InfG 12

StPh InfG

Lprg InfG

Page 71: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

71

Fermeture transitive (DB2)

CNOM PNOM

BDs StPh

Bds Lprg

CNOM PNOM NMIN

BDs StPh 15BDs LPrg 12StPh InfG 13InfG Math 11Lprg InfG 12

StPh InfG

Lprg InfG

InfG Math

InfG Math

Page 72: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

72

Fermeture transitive (DB2)

CNOM PNOM

BDs StPh

Bds Lprg

CNOM PNOM NMIN

BDs StPh 15BDs LPrg 12StPh InfG 13InfG Math 11Lprg InfG 12

StPh InfG

Lprg InfG

InfG Math

InfG Math

PNOM

StPh

InfG

Math

Page 73: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

73

Fermeture Transitive Oracle

Clause Connect By

Calcul d'un arbre généalogique

"Oracle 8i The Complete Reference" Loney, K., Koch, G., Osborne, 2000

Page 74: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

74

Fermeture Transitive Oracle

Clause Connect By

Page 75: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

75

Page 76: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

76

Fermeture transitive? Trouver aussi les notes minimales nécessairesAutres applications

Tous les subordonnées d ’un chefEMPL (E#, ENOM, CHEF)

Toutes les composantes PMIN de pièces composéesCOMP (PMAJ#, PMIN#)

Tous les descendants / ascendants d ’une personne Toutes les participations d ’une entreprise

Limite d’application Pas de cycles entre les attributs sélectionnes

pour que l ’on a la fermeture transitive (point fixe)ex. pas de participations réciproques

Page 77: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

77

Fermeture transitiveMsAccess

• Il faut simuler– par CREATE TABLE PRQ

• avec la création de clé primaire

– Insert into PRQ .. select … from COURS...– Insert into PRQ .. select … from PRQ, COURS…

• exécuté interactivement ou par programme jusqu'à ce que le point fixe est atteint

– Select … FROM PRQ…

• A compléter par vos soins

Page 78: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

78

Conclusion

• Un champs nouveau– encore grandement ouvert à la recherche

• La connexion logique avec le domaine des SGBD n'est pas toujours claire

• Un point de vue sur cette connexion – On prend un champs classique d'analyse de donnés et on

s'intéresse • aux performances pour une grande quantité de données

• Aux extensions correspondantes de SQL

Page 79: 1 Les SGBDs Supports de Décisions Witold Litwin 2 Besoin Utilisation massive de fonctions agrégats –celles de SQL –autres dans le langage hôte Exemples

79

FIN