IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

Solutions pour le petit jeu des requêtes SQL


précédentsommairesuivant

II. Solutions - 2° partie

II-A. Solution au problème n° 11 - premiers

Il suffit de prendre le problème à l'envers et de récupérer facilement tous les nombres non premiers, c'est-à-dire ceux pour lesquels le reste de le division entière par au moins un des nombres qui lui est inférieur vaut 0 :

 
Sélectionnez
SELECT EN1.ENT_N
FROM   T_ENTIER_ENT EN1
       CROSS JOIN T_ENTIER_ENT EN2
WHERE  MOD(EN1.ENT_N, EN2.ENT_N) = 0
  AND  EN2.ENT_N BETWEEN 2 AND EN1.ENT_N -1 ;

Pour ce faire, on réalise le produit cartésien du nombre avec tous les nombres compris entre 2 et ce nombre moins un et l'on teste si le reste de la division entière vaut zéro. Si ce reste vaut zéro, alors preuve est faite que ce nombre n'est pas premier.

Dès que nous avons tous les nombres non premiers, il ne s'agit plus que de trouver le complément :

 
Sélectionnez
SELECT * 
FROM   T_ENTIER_ENT
WHERE  ENT_N NOT IN (SELECT EN1.ENT_N
                     FROM   T_ENTIER_ENT EN1
                            CROSS JOIN T_ENTIER_ENT EN2
                     WHERE  MOD(EN1.ENT_N, EN2.ENT_N) = 0
                       AND  EN2.ENT_N BETWEEN 2 AND EN1.ENT_N -1)
ORDER BY 1 ;

Ceci peut aussi être fait à l'aide de l'opérateur ensembliste EXCEPT

 
Sélectionnez
SELECT * 
FROM   T_ENTIER_ENT
EXCEPT
SELECT EN1.ENT_N
FROM   T_ENTIER_ENT EN1
       CROSS JOIN T_ENTIER_ENT EN2
WHERE  MOD(EN1.ENT_N, EN2.ENT_N) = 0
  AND  EN2.ENT_N BETWEEN 2 AND EN1.ENT_N -1
ORDER BY 1 ;

La requête pour MS SQL Server :

 
Sélectionnez
SELECT * 
FROM   T_ENTIER_ENT
WHERE  ENT_N NOT IN (SELECT EN1.ENT_N
                     FROM   T_ENTIER_ENT EN1
                            CROSS JOIN T_ENTIER_ENT EN2
                     WHERE  EN1.ENT_N % EN2.ENT_N = 0
                       AND  EN2.ENT_N BETWEEN 2 AND EN1.ENT_N -1)
ORDER BY 1 ;

II-B. Solution au problème n° 12 - traduction

Facile avec la fonction COALESCE et des sous requêtes. Voici un premier exemple de solution.
Solution 1 :

 
Sélectionnez
SELECT TDR_LIBELLE, TDR_ID, TDR_LANGUE
FROM   T_TRADUCTION_TDR TDR
WHERE  TDR_LANGUE = COALESCE((SELECT DISTINCT TDR_LANGUE 
                              FROM   T_TRADUCTION_TDR 
                              WHERE  TDR_LANGUE = 'Français' 
                                AND  TDR_ID = TDR.TDR_ID), (SELECT DISTINCT TDR_LANGUE 
                                                            FROM   T_TRADUCTION_TDR 
                                                            WHERE  TDR_LANGUE = 'Anglais'  
                                                              AND  TDR_ID = TDR.TDR_ID)) ;

Une solution élégante nous a été donnée par Sonia.

Solution 2 :
Sélectionnez
SELECT TDR_LIBELLE, TDR_ID, TDR_LANGUE
FROM   T_TRADUCTION_TDR
WHERE  TDR_LANGUE = 'Français'
UNION
SELECT TDR_LIBELLE, TDR_ID, TDR_LANGUE
FROM   T_TRADUCTION_TDR
WHERE  TDR_LANGUE='Anglais'
  AND  TDR_ID NOT IN (SELECT TDR_ID
                      FROM   T_TRADUCTION_TDR
                      WHERE  TDR_LANGUE='Français')

Cette solution n'utilise pas la fonction COALESCE.
Enfin, une solution sans utiliser de sous requêtes.

Solution 3 :
Sélectionnez
SELECT COALESCE(fr.TDR_LIBELLE, en.TDR_LIBELLE) AS TDR_LIBELLE,
       COALESCE(fr.TDR_LANGUE, en.TDR_LANGUE) AS TDR_LANGUE,
       COALESCE(fr.TDR_ID, en.TDR_ID) AS TDR_ID
FROM   T_TRADUCTION_TDR t
       LEFT OUTER JOIN T_TRADUCTION_TDR as en
            ON en.TDR_ID = t.TDR_ID
               AND en.TDR_LANGUE='Anglais'
       LEFT OUTER JOIN T_TRADUCTION_TDR as fr
            ON fr.TDR_ID = t.TDR_ID
               AND fr.TDR_LANGUE='Français'
GROUP BY t.TDR_ID, fr.TDR_LIBELLE, en.TDR_LIBELLE, fr.TDR_LANGUE, en.TDR_LANGUE, fr.TDR_ID, en.TDR_ID

Si l'on analyse les performances des trois requêtes à l'aide d'un outil qui visualise le plan d'exécution (MS SQL Server), on est frappé par la grande différence qui existe entre la première version et les deux suivantes...

Solution 1 (24 phases de traitement) :

Image non disponible

Solution 2 (7 phases de traitement) :

Image non disponible

Solution 3 (8 phases de traitement) :

Image non disponible

Mais la manière de faire n'indique pas toujours le coût exact. Mieux vaut s'interroger sur le nombre de lectures effectuées pour chacune des requêtes :

Solution 1 :
Sélectionnez
Table 'T_TRADUCTION_TDR'. Compte d'analyses 7, lectures logiques 7, lectures physiques 0, lectures anticipées 0.
Solution 2 :
Sélectionnez
Table 'T_TRADUCTION_TDR'. Compte d'analyses 4, lectures logiques 4, lectures physiques 0, lectures anticipées 0.
Solution 3 :
Sélectionnez
Table 'T_TRADUCTION_TDR'. Compte d'analyses 9, lectures logiques 9, lectures physiques 0, lectures anticipées 0.

Autrement dit, la solution 3, pourtant assez économe en phases de traitement, est pire en terme de lecture que la première.

II-C. Solution au problème n° 13 - les bons joeurs

En fait, la difficulté réside dans la jointure... Mais un simple CROSS JOIN suffit à résoudre le problème.

Pour compter les bonne réponses il suffit d'utiliser le CASE et d'attribuer la valeur 1 si la réponse est bonne et la valeur 0 si elle est fausse. La somme de ces valeurs donne le score.

Voici donc une solution :

 
Sélectionnez
SELECT PNL_NOM,
       CASE WHEN J.PNL_REPONSE1 = R.RPS_REPONSE1 THEN 1 ELSE 0 END +
       CASE WHEN J.PNL_REPONSE2 = R.RPS_REPONSE2 THEN 1 ELSE 0 END +
       CASE WHEN J.PNL_REPONSE3 = R.RPS_REPONSE3 THEN 1 ELSE 0 END +
       CASE WHEN J.PNL_REPONSE4 = R.RPS_REPONSE4 THEN 1 ELSE 0 END +
       CASE WHEN J.PNL_REPONSE5 = R.RPS_REPONSE5 THEN 1 ELSE 0 END AS BONNE_REPONSE
FROM  T_PANEL_PNL J
      CROSS JOIN T_REPONSES_RPS R
ORDER BY BONNE_REPONSE DESC ;


Une solution dans le même genre nous a été donnée par Fabien C. :

 
Sélectionnez
SELECT PNL_NOM, 
       ((SELECT COUNT(*) FROM T_REPONSES_RPS R WHERE R.RPS_REPONSE1 = J.PNL_REPONSE1) +
        (SELECT COUNT(*) FROM T_REPONSES_RPS R WHERE R.RPS_REPONSE2 = J.PNL_REPONSE2) +
        (SELECT COUNT(*) FROM T_REPONSES_RPS R WHERE R.RPS_REPONSE3 = J.PNL_REPONSE3) +
        (SELECT COUNT(*) FROM T_REPONSES_RPS R WHERE R.RPS_REPONSE4 = J.PNL_REPONSE4) +
        (SELECT COUNT(*) FROM T_REPONSES_RPS R WHERE R.RPS_REPONSE5 = J.PNL_REPONSE5)) AS T_REPONSES_RPS
FROM T_PANE id="XV"L_PNL J ;

II-D. Solution au problème n° 14 - tranches d'âge

Plusieurs petites difficultés :

  • le calcul de l'âge ;
  • la réalisation des tranches ;
  • les effets de bords...

Pour le calcul de l'âge, je me sert de la formule suivante :

 
Sélectionnez
CAST(CURRENT_DATE - CLI_DATE_NAISSANCE AS INTERVAL DAY) / 365.2425


qui me donne un age décimal.

Pour la réalisation des tranches, il faut ajouter l'information puisqu'elle n'y est pas. Construisons donc une nouvelle table :

 
Sélectionnez
CREATE TABLE T_TRANCHE_AGE_TAG
(TAG_LIMITE INTEGER) ;
 
Sélectionnez
INSERT INTO T_TRANCHE_AGE_TAG VALUES (18) ;
INSERT INTO T_TRANCHE_AGE_TAG VALUES (40) ;


Ces informations nous suffisent, mais il serait plus pratique d'avoir la représentation des tranches sous la forme :

 
Sélectionnez
TRANCHE_MIN   TRANCHE_MAX
------------- -------------
0             18
18            40
40            NULL

Car nous pourrons alors joindre la table des clients par une fourchette de valeur à cette représentation. Bien entendu nous ferrons en sorte de représenter le NULL comme étant une valeur loin dans le futur.

Cette requête nous donne presque la solution :

 
Sélectionnez
SELECT *
FROM   T_TRANCHE_AGE_TAG TA1
       LEFT OUTER JOIN  T_TRANCHE_AGE_TAG TA2
            ON TA1.TAG_LIMITE < TA2.TAG_LIMITE ;
 
Sélectionnez
TAG_LIMITE  TAG_LIMITE  
----------- ----------- 
18          40
40          NULL

Il faut juste rajouter la tranche commençant par zéro. Cela peut être fait à l'aide d'une opération d'UNION. Profitons-en pour mettre tout cela dans une vue :

 
Sélectionnez
CREATE VIEW V_TRANCHE_AGE_TAG
AS
SELECT TA1.TAG_LIMITE AS TRANCHE_MIN, TA2.TAG_LIMITE AS TRANCHE_MAX
FROM   T_TRANCHE_AGE_TAG TA1
       LEFT OUTER JOIN  T_TRANCHE_AGE_TAG TA2
            ON TA1.TAG_LIMITE < TA2.TAG_LIMITE
UNION  
SELECT 0, MIN(TAG_LIMITE)
FROM   T_TRANCHE_AGE_TAG ;

Assemblons maintenant tout ceci dans la requête finale :

 
Sélectionnez
SELECT TRANCHE_MIN, COALESCE(TRANCHE_MAX, 9999) AS TRANCHE_MAX, COUNT(*) AS NOMBRE 
FROM   V_TRANCHE_AGE_TAG TAG
       INNER JOIN T_CLIENT_CLI
             ON  CAST(CURRENT_DATE - CLI_DATE_NAISSANCE AS INTERVAL DAY) / 365.2425 
                    BETWEEN TRANCHE_MIN AND COALESCE(TRANCHE_MAX, 9999)
GROUP BY TRANCHE_MIN, TRANCHE_MAX
ORDER BY TRANCHE_MIN ;
 
Sélectionnez
TRANCHE_MIN TRANCHE_MAX NOMBRE      
----------- ----------- ----------- 
0           18          6
18          40          4
40          9999        13

Néanmoins cette solution n'est pas correcte. En effet ceux qui ont exactement 18 ans ou 40 ans sont comptabilisé 2 fois. Voici ce qui serait beaucoup plus correct :

 
Sélectionnez
SELECT TRANCHE_MIN, COALESCE(TRANCHE_MAX, 9999) AS TRANCHE_MAX, COUNT(*) AS NOMBRE 
FROM   V_TRANCHE_AGE_TAG TAG
       INNER JOIN T_CLIENT_CLI
             ON  CAST(CURRENT_DATE - CLI_DATE_NAISSANCE AS INTERVAL DAY) / 365.2425 > TRANCHE_MIN AND
                 CAST(CURRENT_DATE - CLI_DATE_NAISSANCE AS INTERVAL DAY) / 365.2425 <= COALESCE(TRANCHE_MAX, 999)
GROUP BY TRANCHE_MIN, TRANCHE_MAX
ORDER BY 1 ;

La requête pour MS SQL Server :

 
Sélectionnez
SELECT TRANCHE_MIN, COALESCE(TRANCHE_MAX, 9999) AS TRANCHE_MAX, COUNT(*) AS NOMBRE 
FROM   V_TRANCHE_AGE_TAG TAG
       INNER JOIN T_CLIENT_CLI
             ON  DATEDIFF(DAY, CLI_DATE_NAISSANCE, CURRENT_TIMESTAMP)  / 365.2425 > TRANCHE_MIN AND
                 DATEDIFF(DAY, CLI_DATE_NAISSANCE, CURRENT_TIMESTAMP)  / 365.2425 <= COALESCE(TRANCHE_MAX, 999)
GROUP BY TRANCHE_MIN, TRANCHE_ id="XVI"MAX
ORDER BY 1 ;

II-E. Solution au problème n° 15 - Les articles

La encore la technique consiste à rajouter de l'information à notre base de données. Notamment en créant une tables des articles parasites. Pour cela nous créons une table de nom T_ARTICLE_ATC avec l'article considéré et sa longueur incluant soit le blanc soit l'apostrophe avec le type SQL CHAR et non VARCHAR qui élimine les blancs finaux, et nous stockons la longueur considérée de cet article avec le blanc ou l'apostrophe, comme ceci :

 
Sélectionnez
CREATE TABLE T_ARTICLE_ATC 
(ATC_ARTICLE  VARCHAR(8), 
 ATC_LONGUEUR INTEGER);

On y insère les principaux articles de la langue française :

 
Sélectionnez
INSERT INTO T_ARTICLE_ATC VALUES ('L''', 2) ;
INSERT INTO T_ARTICLE_ATC VALUES ('LE',  3) ;
INSERT INTO T_ARTICLE_ATC VALUES ('LA',  3) ;
INSERT INTO T_ARTICLE_ATC VALUES ('LES', 4) ;
INSERT INTO T_ARTICLE_ATC VALUES ('UN',  3) ;
INSERT INTO T_ARTICLE_ATC VALUES ('UNE', 4) ;
INSERT INTO T_ARTICLE_ATC VALUES ('DE',  3) ;
INSERT INTO T_ARTICLE_ATC VALUES ('DES', 4) ;
INSERT INTO T_ARTICLE_ATC VALUES ('D''', 2) ;

Ensuite, la requête consiste à lier les deux tables par jointure externe afin de conserver tous les titres en utilisant comme critère de jointure le début du titre par rapport au contenu de la table des articles. Si jointure il y a (donc colonne ATC_ARTICLE non vide), alors on transforme le titre en supprimant l'article en tête du titre et en le repoussant en fin entre parenthèses. La requête suivante répond exactement à la demande du sieur Bouchon :

 
Sélectionnez
SELECT CASE 
          WHEN ATC.ATC_ARTICLE IS NULL THEN OVG.OVG_TITRE 
          ELSE SUBSTRING(OVG.OVG_TITRE FROM ATC.ATC_LONGUEUR + 1 FOR 64 - ATC.ATC_LONGUEUR) 
               + ' (' + TRIM(RIGHT, SUBSTRING(OVG.OVG_TITRE FROM 1 FOR ATC.ATC_LONGUEUR)) + ')' 
       END AS TITRE 
FROM   T_OUVRAGES_OVG OVG 
       LEFT OUTER JOIN T_ARTICLE_ATC ATC 
            ON UPPER(SUBSTRING(OVG.OVG_TITRE FROM 1 FOR ATC.ATC_LONGUEUR)) 
               = SUBSTRING(ATC.ATC_ARTICLE FROM 1 FOR ATC.ATC_LONGUEUR)  ;

La requête pour MS SQL Server :

 
Sélectionnez
SELECT CASE
          WHEN ATC.ATC_ARTICLE IS NULL THEN OVG.OVG_TITRE
          ELSE SUBSTRING(OVG.OVG_TITRE, ATC.ATC_LONGUEUR + 1, 64 - ATC.ATC_LONGUEUR)
               + ' (' + RTRIM(SUBSTRING(OVG.OVG_TITRE, 1, ATC.ATC_LONGUEUR)) + ')'
       END AS TITRE
FROM   T_OUVRAGES_OVG OVG
       LEFT OUTER JOIN T_ARTICLE_ATC ATC
            ON UPPER(SUBSTRING(OVG.OVG_TITRE,   1, ATC.ATC_LONGUEUR))
                   = SUBSTRING(ATC.ATC_ARTIC id="XVII"LE, 1, ATC.ATC_LONGUEUR) ;

II-F. Solution au problème n° 16 - tri alphabétique

La solution consiste, comme souvent, à rajouter des informations afin de piloter l'ordre...

Ajoutons une table avec les nombres et leur correspondance littérale :

 
Sélectionnez
CREATE TABLE T_NOMBRE_LETTRE_NBL 
(NBL_NOMBRE CHAR(1), 
 NBL_LETTRES VARCHAR(16));

Et insérons-y les valeurs suivantes :

 
Sélectionnez
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('1', 'Un');
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('2', 'Deux');
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('3', 'Trois');
INSERT INTO T_NOMBRE_LETTRE_NBLVALUES ('4', 'Quatre');
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('5', 'Cinq');
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('6', 'Six');
INSERT INTO T_NOMBRE_LETTRE_NBLVALUES ('7', 'Sept');
INSERT INTO T_NOMBRE_LETTRE_NBLVALUES ('8', 'Huit');
INSERT INTO T_NOMBRE_LETTRE_NBLVALUES ('9', 'Neuf');
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('0', 'Zéro');


Si votre SGBDR dispose des spécifications de collations conforme à la norme SQL, alors cette table suffit.

Dès lors, la requête s'exprime de la sorte :

 
Sélectionnez
SELECT LVR_TITRE,
       CASE
          WHEN NBL.NBL_NOMBRE IS NULL THEN LVR_TITRE
          ELSE NBL_LETTRES || ' ' || SUBSTRING(LVR.LVR_TITRE FROM 2 FOR CHARACTER_LENGTH(LVR.LVR_TITRE)-1) 
       END COLLATE SQL_latin_DictionnaryUsage
FROM   T_LIVRE_LVR LVR
       LEFT OUTER JOIN T_NOMBRE_LETTRE_NBL NBL
            ON SUBSTRING(LVR.LVR_TITRE FROM 1 FOR 1) = NBL.NBL_NOMBRE
ORDER  BY 2 ;

NOTA : Le nom de la collation est donné à titre indicatif, les éditeurs ayant toute latitude à ce sujet.

La requête pour MS SQL Server :

 
Sélectionnez
SELECT LVR_TITRE 
FROM   T_LIVRE_LVR LVR
       LEFT OUTER JOIN T_NOMBRE_LETTRE_NBL NBL
            ON SUBSTRING(LVR.LVR_TITRE, 1, 1) = NBL.NBL_NOMBRE
ORDER  BY CASE 
             WHEN NBL.NBL_NOMBRE IS NULL THEN LVR_TITRE
             ELSE NBL_LETTRES + ' ' + SUBSTRING(LVR.LVR_TITRE, 2, LEN(LVR.LVR_TITRE)-1)  
          END  COLLATE French_CI_AI ;

SQL Server possède l'avantage de pouvoir effectuer un tri externe, c'est à dire sans que la colonne ne figure dans la clause SELECT. Ici la collation choisie permet de faire confusion entre les caractères accentuées ou non (et plus généralement les caractères diacritiques ou non : cédille, tilde, ligature...) et de confondre aussi la casse (majuscule/minuscule).

Si telle n'était pas le cas; il faudrait alors allonger la table T_NOMBRE_LETTRE_NBL avec de nouvelles entrées telles que :

 
Sélectionnez
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('À', 'a'); 
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('Â', 'a'); 
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('â', 'a');

Etc.


Un seul problème subsisterait alors, celui de la gestion de l'espace pour le remplacement des nombres par les littéraux alors que cela n'est pas nécessaire pour id="XVIII" les caractères.

II-G. Solution au problème n° 17 - appariement

En fait une double condition doit être simultanément vraie : que tous les PER_2 de la valeur PER_1 scrutée soit dans les valeur de PER_2 en regard ET que tous les PER_2 en regard soit dans les valeurs des PER_2 de la valeur PER_1 scrutée. En négativant cette double condition, on obtient :

On joint la table sur elle-même avec équivalence pour PER_1 et différence pour PER_2. Pour plus de clarté, appelons les deux occurrences de notre table ORIGINE pour la première et DESTINATION pour la seconde :

 
Sélectionnez
SELECT DISTINCT ORIGINE.PER_1, DESTINATION.PER_1
FROM   T_PAIRE_PER ORIGINE
       INNER JOIN T_PAIRE_PER DESTINATION
             ON ORIGINE.PER_2 = DESTINATION.PER_2
                AND ORIGINE.PER_1 <> DESTINATION.PER_1
-- Le premier filtre considère que toutes les valeurs PER_2 de ORIGINE doivent être présentent dans PER_2 de DESTINATION.
-- Avec cette clause A1 et A9 sont éliminées.
WHERE NOT EXISTS (SELECT *
                   FROM   T_PAIRE_PER
                   WHERE  PER_1 = ORIGINE.PER_1
                      AND PER_2 NOT IN (SELECT PER_2
                                        FROM   T_PAIRE_PER
                                        WHERE  PER_1 = DESTINATION.PER_1))
-- Le second filtre considère que toutes les valeurs PER_2 de DESTINATION doivent être présentent dans PER_2 de ORIGINE.
-- Avec cette clause A5 est éliminée.
  AND  NOT EXISTS (SELECT *
                   FROM   T_PAIRE_PER
                   WHERE  PER_1 = DESTINATION.PER_1
                      AND PER_2 NOT IN (SELECT PER_2
                                        FROM   T_PAIRE_PER
                                        WHERE  PER_1 = ORIGINE.PER_1))
ORDER BY 1, 2 ;
Le premier filtre
conduit au jeu de
résultat suivant :
Le second filtre
conduit au jeu de
résultat suivant :
Solution
 
Sélectionnez
PER_1 PER_1 
----- ----- 

A2    A3
A2    A4
A2    A6
A2    A8
A2    A9


A3    A6
A3    A8

A4    A2
A4    A3
A4    A6
A4    A8
A4    A9

A5    A1

A6    A3

A6    A8


A8    A3

A8    A6
 
Sélectionnez
PER_1 PER_1 
----- ----- 
A1    A5

A2    A4



A3    A2
A3    A4
A3    A6
A3    A8

A4    A2






A6    A2
A6    A3
A6    A4
A6    A8

A8    A2
A8    A3
A8    A4
A8    A6

A9    A2
A9    A4
 
Sélectionnez
PER_1 PER_1
----- ----- 


A2    A4





A3    A6
A3    A8

A4    A2







A6    A3

A6    A8


A8    A3

A8    A6

Les données communes ont été mise en évidence par une couleur blanche et en gras.

On peut lire ces filtres de la manière suivante : il ne faut pas qu'il existe une valeur de PER_2 de la table origine qui ne soit pas dans la table de destination. Et vice versa pour le second filtre.

En fait le problème s'apparente à la division relationnelle et plus particulièrement à la division de Todd que Joe Celko évoque dans son ouvrage SQL for smarties (SQ id="XIX"L Avancé, Vuibert éditeur).

II-H. Solution au problème n° 18 - meilleure correspondance partielle

Pour trouver la réponse à ce problème, il faut décomposer la chaîne de caractère composant le code en autant de chaînes de caractères de longueur allant de 1 à la valeur maximum permet le format du type SQL, ici 16. Puis de comptabiliser pour chacune de ces combinaisons, celle qui sont communes à l'aide d'un GROUP BY. Enfin de retenir de ce calcul celles ayant le nombre le plus élevé d'occurrences avec la longueur la plus grande.

Pour décomposer la chaîne représentant le code, il faut une table des nombres allant de 1 à la longueur maximale de la colonnes RTE_CODE soit 16. Pour cela reprenons la table T_ENTIER_ENT de l'exercice 6 :

 
Sélectionnez
CREATE TABLE T_ENTIER_ENT
(ENT_N INTEGER);
 
Sélectionnez
INSERT INTO T_ENTIER_ENT VALUES (0);
INSERT INTO T_ENTIER_ENT VALUES (1);
INSERT INTO T_ENTIER_ENT VALUES (2);
INSERT INTO T_ENTIER_ENT VALUES (3);
INSERT INTO T_ENTIER_ENT VALUES (4);
INSERT INTO T_ENTIER_ENT VALUES (5);
INSERT INTO T_ENTIER_ENT VALUES (6);
INSERT INTO T_ENTIER_ENT VALUES (7);
INSERT INTO T_ENTIER_ENT VALUES (8);
INSERT INTO T_ENTIER_ENT VALUES (9) ;
 
Sélectionnez
INSERT INTO T_ENTIER_ENT 
SELECT TEU.ENT_N                      -- les unités
       + 10   * TED.ENT_N             -- les dizaines
       + 100  * TEC.ENT_N             -- les centaines
       + 1000 * TEM.ENT_N             -- les milliers
FROM   T_ENTIER_ENT TEU               -- table des entiers pour les unités
       CROSS JOIN T_ENTIER_ENT TED    -- table des entiers pour les dizaines
       CROSS JOIN T_ENTIER_ENT TEC    -- table des entiers pour les centaines
       CROSS JOIN T_ENTIER_ENT TEM    -- table des entiers pour les milliers
WHERE  TEU.ENT_N           
       + 10   * TED.ENT_N  
       + 100  * TEC.ENT_N  
       + 1000 * TEM.ENT_N  > 9        -- empêche d'insérer les doublons des unités

Volontairement, je la peuple avec des valeurs nettement supérieures à la taille maximale de la colonne concernée...

La requête est alors la suivante :

 
Sélectionnez
SELECT DISTINCT R.RTE_DESTINATION, SUBSTRING(RTE_CODE FROM 1 FOR LNG) as RTE_CODE
FROM   T_ROUTE_RTE R
       INNER JOIN (SELECT RTE_DESTINATION, MAX(LNG) AS LNG
                   FROM   (SELECT RTE_DESTINATION, RTE_CODE2, LNG, COUNT(*) AS NBR
                           FROM   (SELECT RTE_DESTINATION,
                                          SUBSTRING(RTE_CODE FROM 1 FOR ENT_N) as RTE_CODE2,
                                          ENT_N AS LNG
                                   FROM   T_ROUTE_RTE
                                          CROSS JOIN T_ENTIER_ENT
                                   WHERE  ENT_N BETWEEN 1 AND CHARACTER_LENGTH(RTE_CODE)) T
                           GROUP  BY RTE_DESTINATION, RTE_CODE2, LNG
                           HAVING COUNT(*) = (SELECT COUNT(*)
                                              FROM   T_ROUTE_RTE
                                              WHERE  RTE_DESTINATION = T.RTE_DESTINATION
                                              GROUP  BY RTE_DESTINATION)) TT
                   GROUP BY RTE_DESTINATION) TTT
            ON R.RTE_DESTINATION = TTT.RTE_DESTINATION

La même requête pour MS SQL Server :

 
Sélectionnez
SELECT DISTINCT R.RTE_DESTINATION, SUBSTRING(RTE_CODE, 1, LNG) as RTE_CODE
FROM   T_ROUTE_RTE R
       INNER JOIN (SELECT RTE_DESTINATION, MAX(LNG) AS LNG
                   FROM   (SELECT RTE_DESTINATION, RTE_CODE2, LNG, COUNT(*) AS NBR
                           FROM   (SELECT RTE_DESTINATION,
                                          SUBSTRING(RTE_CODE, 1, ENT_N) as RTE_CODE2,
                                          ENT_N AS LNG
                                   FROM   T_ROUTE_RTE
                                          CROSS JOIN T_ENTIER_ENT
                                   WHERE  ENT_N BETWEEN 1 AND LEN(RTE_CODE)) T
                           GROUP  BY RTE_DESTINATION, RTE_CODE2, LNG
                           HAVING COUNT(*) = (SELECT COUNT(*)
                                              FROM   T_ROUTE_RTE
                                              WHERE  RTE_DESTINATION = T.RTE_DESTINATION
                                              GROUP  BY RTE_DESTINATION)) TT
                   GROUP BY RTE_DESTINATION) TTT
            ON R.RTE_DESTINATION = TTT.RTE_DESTINATION

Décomposition :

Le calcul de toutes les sous chaines se fait à l'aide d'un produit cartésien avec la table des nombres.
Pour un maximum d'efficacité on ne découpe pas les sous chaînes dont la longueur est supérieur à la taille de la données.

On spécifie aussi la longueur dans la colonne LNG

 
Sélectionnez
SELECT RTE_DESTINATION,
       SUBSTRING(RTE_CODE FROM 1 FOR ENT_N) as RTE_CODE2,
       ENT_N AS LNG
FROM   T_ROUTE_RTE
       CROSS JOIN T_ENTIER_ENT
WHERE  ENT_N BETWEEN 1 AND CHARACTER_LENGTH(RTE_CODE) ;

La même requête pour MS SQL Server :

 
Sélectionnez
SELECT RTE_DESTINATION,
       SUBSTRING(RTE_CODE, 1, ENT_N) as RTE_CODE2,
       ENT_N AS LNG
FROM   T_ROUTE_RTE
       CROSS JOIN T_ENTIER_ENT
WHERE  ENT_N BETWEEN 1 AND LEN(RTE_CODE) ;

Ce qui donne :

 
Sélectionnez
RTE_DESTINATION                  RTE_CODE2        LNG 
-------------------------------- ---------------- ----------- 
albania-Mobile                   0                1 
albania-Mobile                   00               2 
albania-Mobile                   003              3 
albania-Mobile                   0035             4 
albania-Mobile                   00355            5 
albania-Mobile                   003553           6 
albania-Mobile                   0035538          7 
albania-Mobile                   0                1 
albania-Mobile                   00               2 
albania-Mobile                   003              3 
albania-Mobile                   0035             4 
albania-Mobile                   00355            5 
albania-Mobile                   003556           6 
albania-Mobile                   0035569          7 
albania-Mobile                   0                1 
albania-Mobile                   00               2 
albania-Mobile                   003              3 
albania-Mobile                   0035             4 
albania-Mobile                   00355            5 
albania-Mobile                   003556           6 
albania-Mobile                   0035560          7 
SFR-Mobile                       0                1 
SFR-Mobile                       00               2 
SFR-Mobile                       007              3 
SFR-Mobile                       0077             4 
SFR-Mobile                       00772            5 
SFR-Mobile                       007728           6 
SFR-Mobile                       0077280          7 
SFR-Mobile                       0                1 
SFR-Mobile                       00               2 
SFR-Mobile                       007              3 
SFR-Mobile                       0077             4 
SFR-Mobile                       00773            5 
SFR-Mobile                       007739           6 
SFR-Mobile                       0077390          7 
BOUYGE_TEL                       0                1 
BOUYGE_TEL                       00               2 
BOUYGE_TEL                       007              3 
BOUYGE_TEL                       0078             4 
BOUYGE_TEL                       00784            5 
BOUYGE_TEL                       007845           6 
BOUYGE_TEL                       0078452          7

De ce résultat passé en sous requête, on compte le nombre de lignes identiques groupées par RTE_DESTINATION, RTE_CODE et longueur de la chaîne :

 
Sélectionnez
SELECT RTE_DESTINATION, RTE_CODE2, LNG, COUNT(*) AS NBR
FROM   (SELECT RTE_DESTINATION,
               SUBSTRING(RTE_CODE FROM 1 FOR ENT_N) as RTE_CODE2,
               ENT_N AS LNG
        FROM   T_ROUTE_RTE
               CROSS JOIN T_ENTIER_ENT
        WHERE  ENT_N BETWEEN 1 AND CHARACTER_LENGTH(RTE_CODE)) T
GROUP  BY RTE_DESTINATION, RTE_CODE2, LNG ;

La même requête pour MS SQL Server :

 
Sélectionnez
SELECT RTE_DESTINATION, RTE_CODE2, LNG, COUNT(*) AS NBR
FROM   (SELECT RTE_DESTINATION,
               SUBSTRING(RTE_CODE, 1, ENT_N) as RTE_CODE2,
               ENT_N AS LNG
        FROM   T_ROUTE_RTE
               CROSS JOIN T_ENTIER_ENT
        WHERE  ENT_N BETWEEN 1 AND LEN(RTE_CODE)) T
GROUP  BY RTE_DESTINATION, RTE_CODE2, LNG ;
 
Sélectionnez
RTE_DESTINATION                  RTE_CODE2        LNG         NBR 
-------------------------------- ---------------- ----------- ----------- 
BOUYGE_TEL                       0                1           1
BOUYGE_TEL                       00               2           1
BOUYGE_TEL                       007              3           1
BOUYGE_TEL                       0078             4           1
BOUYGE_TEL                       00784            5           1
BOUYGE_TEL                       007845           6           1
BOUYGE_TEL                       0078452          7           1

SFR-Mobile                       0                1           2
SFR-Mobile                       00               2           2
SFR-Mobile                       007              3           2
SFR-Mobile                       0077             4           2

SFR-Mobile                       00772            5           1
SFR-Mobile                       007728           6           1
SFR-Mobile                       0077280          7           1
SFR-Mobile                       00773            5           1
SFR-Mobile                       007739           6           1
SFR-Mobile                       0077390          7           1

albania-Mobile                   0                1           3
albania-Mobile                   00               2           3
albania-Mobile                   003              3           3
albania-Mobile                   0035             4           3
albania-Mobile                   00355            5           3

albania-Mobile                   003553           6           1
albania-Mobile                   0035538          7           1
albania-Mobile                   003556           6           2
albania-Mobile                   0035560          7           1
albania-Mobile                   0035569          7           1

Le résultat nous apparaît comme étant celui pour lequel pour chaque RTE_DESTINATION, NBR est maximum et en second LNG est maximum. C'est ce que les lignes mise en exergue en blanc gras indique.

Filtrons d'abord sur nombre au moyen d'une clause HAVING :

 
Sélectionnez
SELECT RTE_DESTINATION, RTE_CODE2, LNG, COUNT(*) AS NBR
FROM   (SELECT RTE_DESTINATION,
               SUBSTRING(RTE_CODE FROM 1 FOR ENT_N) as RTE_CODE2,
               ENT_N AS LNG
        FROM   T_ROUTE_RTE
               CROSS JOIN T_ENTIER_ENT
        WHERE  ENT_N BETWEEN 1 AND CHARACTER_LENGTH(RTE_CODE)) T
GROUP  BY RTE_DESTINATION, RTE_CODE2, LNG
HAVING COUNT(*) = (SELECT COUNT(*)
                   FROM   T_ROUTE_RTE
                   WHERE  RTE_DESTINATION = T.RTE_DESTINATION
                   GROUP  BY RTE_DESTINATION)

La même requête pour MS SQL Server :

 
Sélectionnez
SELECT RTE_DESTINATION, RTE_CODE2, LNG, COUNT(*) AS NBR
FROM   (SELECT RTE_DESTINATION,
               SUBSTRING(RTE_CODE, 1, ENT_N) as RTE_CODE2,
               ENT_N AS LNG
        FROM   T_ROUTE_RTE
               CROSS JOIN T_ENTIER_ENT
        WHERE  ENT_N BETWEEN 1 AND LEN(RTE_CODE)) T
GROUP  BY RTE_DESTINATION, RTE_CODE2, LNG
HAVING COUNT(*) = (SELECT COUNT(*)
                   FROM   T_ROUTE_RTE
                   WHERE  RTE_DESTINATION = T.RTE_DESTINATION
                   GROUP  BY RTE_DESTINATION) ;


Qui donne :

 
Sélectionnez
RTE_DESTINATION                  RTE_CODE2        LNG         NBR
-------------------------------- ---------------- ----------- ----------- 
BOUYGE_TEL                       0                1           1
BOUYGE_TEL                       00               2           1
BOUYGE_TEL                       007              3           1
BOUYGE_TEL                       0078             4           1
BOUYGE_TEL                       00784            5           1
BOUYGE_TEL                       007845           6           1
BOUYGE_TEL                       0078452          7           1
SFR-Mobile                       0                1           2
SFR-Mobile                       00               2           2
SFR-Mobile                       007              3           2
SFR-Mobile                       0077             4           2
albania-Mobile                   0                1           3
albania-Mobile                   00               2           3
albania-Mobile                   003              3           3
albania-Mobile                   0035             4           3
albania-Mobile                   00355            5           3

Dans ce résultat passé en sous requête, on recherche ensuite la longueur la plus grande pour chaque RTE_DESTINATION :

 
Sélectionnez
SELECT RTE_DESTINATION, MAX(LNG) AS LNG
FROM   (SELECT RTE_DESTINATION, RTE_CODE2, LNG, COUNT(*) AS NBR
        FROM   (SELECT RTE_DESTINATION,
                       SUBSTRING(RTE_CODE FROM 1 FOR ENT_N) as RTE_CODE2,
                       ENT_N AS LNG
                FROM   T_ROUTE_RTE
                       CROSS JOIN T_ENTIER_ENT
                WHERE  ENT_N BETWEEN 1 AND CHARACTER_LENGTH(RTE_CODE)) T
                GROUP  BY RTE_DESTINATION, RTE_CODE2, LNG
                HAVING COUNT(*) = (SELECT COUNT(*)
                                   FROM   T_ROUTE_RTE
                                   WHERE  RTE_DESTINATION = T.RTE_DESTINATION
                                   GROUP  BY RTE_DESTINATION)) TT
GROUP BY RTE_DESTINATION ;

La même requête pour MS SQL Server :

 
Sélectionnez
SELECT RTE_DESTINATION, MAX(LNG) AS LNG
FROM   (SELECT RTE_DESTINATION, RTE_CODE2, LNG, COUNT(*) AS NBR
        FROM   (SELECT RTE_DESTINATION,
                       SUBSTRING(RTE_CODE, 1, ENT_N) as RTE_CODE2,
                       ENT_N AS LNG
                FROM   T_ROUTE_RTE
                       CROSS JOIN T_ENTIER_ENT
                WHERE  ENT_N BETWEEN 1 AND LEN(RTE_CODE)) T
                GROUP  BY RTE_DESTINATION, RTE_CODE2, LNG
                HAVING COUNT(*) = (SELECT COUNT(*)
                                   FROM   T_ROUTE_RTE
                                   WHERE  RTE_DESTINATION = T.RTE_DESTINATION
                                   GROUP  BY RTE_DESTINATION)) TT
GROUP BY RTE_DESTINATION ;

Qui donne :

 
Sélectionnez
RTE_DESTINATION                  LNG 
-------------------------------- ----------- 
BOUYGE_TEL                       7
albania-Mobile                   5
SFR-Mobile                       4

Il ne suffit plus que de recoller les données avec la table d'origine et c'est ce qui donne la requête vue au début.

Il est important que le type de la colonne RTE_CODE soit en fixe (CH id="XX"AR) et non en alfa variable (VARCHAR).

II-I. Solution au problème n° 19 - la médiane

La définition stricte de la médiane est la suivante : une valeur pour laquelle, lorsque toutes les valeurs sont classées, il y a autant de valeurs ordonnées avant qu'après. Pour réaliser ce traitement, on peut considérer que la médiane consiste à classer un ensemble en deux sous ensembles :

  • l'un contenant les valeurs en dessous de la valeur cherchée,
  • l'autre contenant les valeurs au dessus des valeurs cherchées,

et à condition que ces deux ensembles comportent le même nombre de lignes.

II-I-1. Solution question 1 : les données à calculées sont toutes différentes (il n'y a pas de doublon) et en nombre impaires :

 
Sélectionnez
SSTT_ID      STT_VALEUR
----------- ----------------------------------------------------- 
1           22.0
3           22.5
5           23.0 <-- la médiane est ici : 23
4           24.0
2           27.5

Pour calculer le nombre de valeurs en dessous, on peut faire :

 
Sélectionnez
SELECT COUNT(*) 
FROM   T_STATISTIQUES_STT SOU 
WHERE  STT_VALEUR < [la valeur cherchée]  ;

Idem pour les valeurs au dessus :

 
Sélectionnez
SELECT COUNT(*) 
FROM   T_STATISTIQUES_STT SUR
WHERE  STT_VALEUR > [la valeur cherchée]  ;

Lorsque les deux sous ensemble compte le même nombre de lignes à une ligne près, alors la valeur cherchée est la bonne. Une autre manière de formuler cela est de dire que le nombre de ligne de l'ensemble SUR moins le nombre de lignes de l'ensemble SOU doit être 0, 1 ou -1, suivant que le nombre de lignes total est pair ou impair. On peut exprime cela avec l'expression suivante :

 
Sélectionnez
ABS((SELECT COUNT(*) 
     FROM   T_STATISTIQUES_STT SOU 
     WHERE  STT_VALEUR < [la valeur cherchée]) - 
    (SELECT COUNT(*) 
     FROM   T_STATISTIQUES_STT SUR
     WHERE  STT_VALEUR > [la valeur cherchée])) <= 1

Pour obtenir la valeur cherchée, il suffit d'encapsuler cette expression en tant que sous requête corrélée :

 
Sélectionnez
SELECT *
FROM   T_STATISTIQUES_STT STT
WHERE  ABS((SELECT COUNT(*)
            FROM   T_STATISTIQUES_STT SOU
            WHERE  SOU.STT_VALEUR < STT.STT_VALEUR) -
           (SELECT COUNT(*)
            FROM   T_STATISTIQUES_STT SUR
            WHERE  SUR.STT_VALEUR > STT.STT_VALEUR)) <= 1 ;

II-I-2. Solution question 2 : les données à calculées sont toutes différentes (il n'y a pas de doublon) et en nombre paires :

Seulement cette solution ne permet de trouver la bonne valeur que dans le cas ou les occurrences sont en nombre impaires. Dans le cas ou les lignes sont en nombre pair, par exemple en rajoutant la ligne (INSERT INTO T_STATISTIQUES1_ST1 VALUES (8, 23.5)), la médiane doit maintenant être calculée comme suit :

 
Sélectionnez
STT_ID      STT_VALEUR
----------- ----------------------------------------------------- 
1           22.0
3           22.5
5           23.0
                  <-- la médiane est ici : moyenne de 23.0 et 23.5, soit 23.25
6           23.5
4           24.0
2           27.5

la requête précédente fournit le résultat suivant :

 
Sélectionnez
STT_ID      STT_VALEUR
----------- ----------------------------------------------------- 
5           23.0
6           23.5


Il convient donc de réaliser la moyenne de ce résultat pour exprimer la médiane. Cela s'effectue à l'aide de l'expression :

 
Sélectionnez
SELECT AVG(STT_VALEUR) AS MEDIANE
FROM   (SELECT STT_VALEUR
        FROM   T_STATISTIQUES_STT STT
        WHERE  ABS((SELECT COUNT(*)
                    FROM   T_STATISTIQUES_STT SOU
                    WHERE  SOU.STT_VALEUR < STT.STT_VALEUR) -
                   (SELECT COUNT(*)
                    FROM   T_STATISTIQUES_STT SUR
                    WHERE  SUR.STT_VALEUR > STT.STT_VALEUR)) <= 1 ) T
 
Sélectionnez
MEDIANE
--------------
23.75

II-I-3. Solution question 3 : les données à calculer admettent des doublons et un nombre de lignes impairs

On a ajouté des lignes à notre jeu d'essai :

 
Sélectionnez
INSERT INTO T_STATISTIQUES_STT VALUES (7, 22.0);
INSERT INTO T_STATISTIQUES_STT VALUES (8, 22.0);
INSERT INTO T_STATISTIQUES_STT VALUES (9, 22.0);
INSERT INTO T_STATISTIQUES_STT VALUES (10, 22.0);
INSERT INTO T_STATISTIQUES_STT VALUES (11, 22.0);

Dès lors, la médiane à calculer se trouve ici :

 
Sélectionnez
STT_ID      STT_VALEUR
----------- ----------------------------------------------------- 
1           22.0
10          22.0
11          22.0
8           22.0
7           22.0

9           22.0 <-- la médiane est ici : 22.0

3           22.5
5           23.0
6           23.5
4           24.0
2           27.5


L'application de notre requête précédemment mise au point nous donne :

 
Sélectionnez
MEDIANE
--------------
NULL

Ce qui n'est pas tout à fait la bonne réponse.... ! Que s'est-il passé ?

Constatons tout d'abord que l'ensemble inférieur (SOU) est formé d'une seule valeur. Dès lors les opérations ensemblistes ne sont pas capable de faire la distinction entre les différentes valeurs. La limite entre les deux ensembles se positionne donc en dehors (ici dessous) de l'équilibre des lignes, et rien n'est retourné par la requête principale.

Une solution pour contourner le problème des lignes paires et impaires est de dédoubler le nombre des lignes. Cela ne change rien à la médiane de multiplier par deux les occurrences si ces nouvelles occurrences sont des lignes dupliquées, mais cela permet d'avoir l'assurance que l'on travaille sur un nombre le lignes paires. Faisons cela à l'aide d'une vue :

 
Sélectionnez
CREATE VIEW V_STATISTIQUES_STT
AS
   SELECT *
   FROM   T_STATISTIQUES_STT
   UNION ALL
   SELECT *
   FROM   T_STATISTIQUES_STT

Dès lors, on peut exprimer notre précédente requête par :

 
Sélectionnez
SELECT STT_VALEUR
FROM   V_STATISTIQUES_STT STT
WHERE  (SELECT COUNT(*)
        FROM   T_STATISTIQUES_STT) <= (SELECT COUNT(*)
                                       FROM   V_STATISTIQUES_STT AS SOU
                                       WHERE  SOU.STT_VALEUR >= STT.STT_VALEUR)
   AND (SELECT COUNT(*)
        FROM   T_STATISTIQUES_STT) <= (SELECT COUNT(*)
                                       FROM   V_STATISTIQUES_STT AS SOU
                                       WHERE  SOU.STT_VALEUR <= STT.STT_VALEUR)


Remarquez comment nous avons changé notre requête pour nous adapté au nouveau cas en exigeant que le nombre de lignes de la vue en dessous (ou au dessus) de la valeur soit égal au nombre de ligne original de la table... Bien entendu cette requête renvoie systématiquement un multiple de deux lignes puisqu'elle opère sur un dédoublement des valeurs de la table. Il faut alors faire une moyenne des valeurs retournées :

 
Sélectionnez
SELECT AVG(STT_VALEUR) AS MEDIANE
FROM   (SELECT STT_VALEUR
        FROM   V_STATISTIQUES_STT STT
        WHERE  (SELECT COUNT(*)
                FROM   T_STATISTIQUES_STT) <= (SELECT COUNT(*)
                                               FROM   V_STATISTIQUES_STT AS SOU
                                               WHERE  SOU.STT_VALEUR <= STT.STT_VALEUR)
           AND (SELECT COUNT(*)
                FROM   T_STATISTIQUES_STT) <= (SELECT COUNT(*)
                                               FROM   V_STATISTIQUES_STT AS SUR
                                               WHERE  SUR.STT_VALEUR >= STT.STT_VALEUR) ) T

Mais cette requête n'est pas encore parfaite... En effet, si nous ajoutons la ligne suivante :

 
Sélectionnez
INSERT INTO T_STATISTIQUES_STT VALUES (12, 22.5)

La médiane devrait être :

 
Sélectionnez
STT_ID      STT_VALEUR
----------- ----------------------------------------------------- 
1           22.0
7           22.0
8           22.0
9           22.0
10          22.0
11          22.0
                  <-- la médiane est ici : moyenne de 22.0 et 22.5, soit 22.25
3           22.5
12          22.5
5           23.0
6           23.5
4           24.0
2           27.5

Or la requête précédente nous donne pour valeur :

 
Sélectionnez
MEDIANE
-----------
22.125

Simplement parce que le dédoublement des valeurs peut conduire à des occurrences quadruples ou plus encore des valeurs. Mais par définition la médiane dans ce cas étant la moyenne des deux valeurs, il suffit d'ajouter un DISTINCT dans le calcul de la fonction SQL AVG...

Finalement notre requête pour exprimer la médiane est en définitive :

 
Sélectionnez
SELECT AVG(DISTINCT STT_VALEUR) AS MEDIANE
FROM   (SELECT STT_VALEUR
        FROM   V_STATISTIQUES_STT STT
        WHERE  (SELECT COUNT(*)
                FROM   T_STATISTIQUES_STT) <= (SELECT COUNT(*)
                                               FROM   V_STATISTIQUES_STT AS SOU
                                               WHERE  SOU.STT_VALEUR <= STT.STT_VALEUR)
           AND (SELECT COUNT(*)
                FROM   T_STATISTIQUES_STT) <= (SELECT COUNT(*)
                                               FROM   V_STATISTIQUES_STT AS SUR
                                               WHERE  SUR.STT_VALEUR >= STT.STT_VALEUR) ) T ;

Et si nous ne voulons pas de vue, cette requête peut finalement s'écrire :

 
Sélectionnez
SELECT AVG(DISTINCT STT_VALEUR) AS MEDIANE
FROM   (SELECT STT_VALEUR
        FROM   (SELECT * 
                FROM   T_STATISTIQUES_STT 
                UNION  ALL 
                SELECT * 
                FROM   T_STATISTIQUES_STT) STT
        WHERE  (SELECT COUNT(*)
                FROM   T_STATISTIQUES_STT) <= (SELECT COUNT(*)
                                               FROM   (SELECT * 
                                                       FROM   T_STATISTIQUES_STT 
                                                       UNION  ALL 
                                                       SELECT * 
                                                       FROM   T_STATISTIQUES_STT) AS SOU
                                               WHERE  SOU.STT_VALEUR <= STT.STT_VALEUR)
           AND (SELECT COUNT(*)
                FROM   T_STATISTIQUES_STT) <= (SELECT COUNT(*)
                                               FROM   (SELECT * 
                                                       FROM   T_STATISTIQUES_STT 
                                                       UNION  ALL 
                                                       SELECT * 
                                                       FROM   T_STATISTIQUES_STT) AS SUR
                                               WHERE  SUR.STT_VALEUR >= STT.STT_VALEUR) ) T ;

Chris Date et Joe Celko se sont bien battus par papiers interposés pour trouver les failles de ces différentes problématiques de l'expression de la médiane. Ils en ont tirés différentes expressions, dont celle que je viens de vous donner est la première version corrigés de la médiane de Date.

Joe Celko propose de calculer le nombre de lignes à retourner par les deux sous ensembles à l'aide de la demi valuation du nombre total des lignes. La, requête est intéressante à décomposer :

1. Les valeurs au dessus sont exprimées par :
Sélectionnez
SELECT ST1.STT_ID, ST1.STT_VALEUR
FROM   T_STATISTIQUES_STT AS ST1
       INNER JOIN T_STATISTIQUES_STT AS ST2
             ON ST1.STT_VALEUR <= ST2.STT_VALEUR
GROUP  BY ST1.STT_ID, ST1.STT_VALEUR
HAVING COUNT(*) <= (SELECT COUNT(*) / 2 + 0.5 
                    FROM T_STATISTIQUES_STT);
 
Sélectionnez
STT_ID      STT_VALEUR
----------- ----------------------------------------------------- 
3           22.5
12          22.5
5           23.0
6           23.5
4           24.0
2           27.5
2. Les valeurs en dessous sont exprimées par :
Sélectionnez
SELECT ST1.STT_ID, ST1.STT_VALEUR
FROM   T_STATISTIQUES_STT AS ST1
       INNER JOIN T_STATISTIQUES_STT AS ST2
             ON ST1.STT_VALEUR >= ST2.STT_VALEUR
GROUP  BY ST1.STT_ID, ST1.STT_VALEUR
HAVING COUNT(*) <= (SELECT COUNT(*) / 2 + 0.5 
                    FROM T_STATISTIQUES_STT)
 
Sélectionnez
STT_ID      STT_VALEUR
----------- ----------------------------------------------------- 
1           22.0
7           22.0
8           22.0
9           22.0
10          22.0
11          22.0

Il ne suffit plus que de prendre le minimum des valeurs au dessus et le maximum des valeurs en dessous pour obtenir notre solution en une ou deux lignes :

 
Sélectionnez
SELECT MIN(STT_VALEUR) AS STT_VALEUR
-- valeurs au dessus
FROM   (SELECT ST1.STT_ID, ST1.STT_VALEUR
        FROM   T_STATISTIQUES_STT AS ST1
               INNER JOIN T_STATISTIQUES_STT AS ST2
                     ON ST1.STT_VALEUR <= ST2.STT_VALEUR
        GROUP  BY ST1.STT_ID, ST1.STT_VALEUR
        HAVING COUNT(*) <= (SELECT COUNT(*) / 2 + 0.5 
                            FROM T_STATISTIQUES_STT) ) SUR
UNION  ALL
SELECT MAX(STT_VALEUR) AS STT_VALEUR
-- valeurs en dessous
FROM   (SELECT ST1.STT_ID, ST1.STT_VALEUR
        FROM   T_STATISTIQUES_STT AS ST1
               INNER JOIN T_STATISTIQUES_STT AS ST2
                     ON ST1.STT_VALEUR >= ST2.STT_VALEUR
        GROUP  BY ST1.STT_ID, ST1.STT_VALEUR
        HAVING COUNT(*) <= (SELECT COUNT(*) / 2 + 0.5 
                            FROM T_STATISTIQUES_STT) ) SOU ;

Qui donne :

 
Sélectionnez
STT_VALEUR
-----------------------------------------------------
22.5
22.0

Nous savons désormais quoi faire, et la requête globale s'exprime de la sorte :

 
Sélectionnez
SELECT AVG(STT_VALEUR) AS MEDIANE
FROM (SELECT MIN(STT_VALEUR) AS STT_VALEUR
      -- valeurs au dessus
      FROM   (SELECT ST1.STT_ID, ST1.STT_VALEUR
              FROM   T_STATISTIQUES_STT AS ST1
                     INNER JOIN T_STATISTIQUES_STT AS ST2
                           ON ST1.STT_VALEUR <= ST2.STT_VALEUR
              GROUP  BY ST1.STT_ID, ST1.STT_VALEUR
              HAVING COUNT(*) <= (SELECT COUNT(*) / 2 + 0.5 
                                  FROM T_STATISTIQUES_STT) ) SUR
      UNION 
      SELECT MAX(STT_VALEUR) AS STT_VALEUR
      -- valeurs en dessous
      FROM   (SELECT ST1.STT_ID, ST1.STT_VALEUR
              FROM   T_STATISTIQUES_STT AS ST1
                     INNER JOIN T_STATISTIQUES_STT AS ST2
                           ON ST1.STT_VALEUR >= ST2.STT_VALEUR
              GROUP  BY ST1.STT_ID, ST1.STT_VALEUR
              HAVING COUNT(*) <= (SELECT COUNT(*) / 2 + 0.5 
                                  FROM T_STATISTIQUES_STT) ) SOU ) T ;

Une façon plus concise d'exprimer la médiane est celle-ci :

 
Sélectionnez
SELECT AVG(STT_VALEUR)
FROM (SELECT E.STT_VALEUR
      FROM   T_STATISTIQUES_STT E
             CROSS JOIN T_STATISTIQUES_STT D
      GROUP BY E.STT_VALEUR
      HAVING SUM(CASE
                    WHEN E.STT_VALEUR = D.STT_VALEUR THEN 1  
                    ELSE 0
                 END) >= ABS(SUM(SIGN(E.STT_VALEUR - D.STT_VALEUR)))
     ) AS T

D'autres formulations sont possibles, notamment en faisant usage d'une fonction de fenêtrage (norme SQL:2003) :

 
Sélectionnez
SELECT AVG(STT_VALEUR) AS MEDIANE
FROM   (SELECT *
        FROM   (SELECT *, ROW_NUMBER() OVER(ORDER BY STT_VALEUR) AS RANG,
                          COUNT(*) OVER() AS NOMBRE
                FROM   T_STATISTIQUES_STT) AS T
        WHERE  (    NOMBRE % 2 = 0
                AND RANG IN (NOMBRE / 2, (NOMBRE / 2) + 1 ))
          OR   (    NOMBRE % 2 = 1
                AND RANG = NOMBRE / 2) ) AS T ;

Enfin, si votre SGBDR supporte une fonction limitant le nombre de lignes retournées par une requête telle que TOP (MS SQL Server) ou LIMIT (MySQL), alors vous pouvez construire votre requête ainsi :

 
Sélectionnez
SELECT AVG(VAL)
FROM   (SELECT MAX(STT_VALEUR) AS VAL
        FROM  (SELECT TOP(SELECT  CASE
                                     WHEN N % 1 = 0 THEN (N /2) + 1
                                     ELSE N / 2
                                  END AS TOPN
                          FROM (SELECT COUNT(*) AS N
                                FROM   dbo.T_STATISTIQUES_STT) AS T) 
                                                                 STT_VALEUR
               FROM   dbo.T_STATISTIQUES_STT
               ORDER  BY STT_VALEUR) AS T1
        UNION
        SELECT MIN(STT_VALEUR) AS VAL
        FROM  (SELECT TOP(SELECT  CASE
                                     WHEN N % 1 = 0 THEN (N /2) + 1
                                     ELSE N / 2
                                  END AS TOPN
                          FROM (SELECT COUNT(*) AS N
                                FROM   dbo.T_STATISTIQUES_STT) AS T) 
                                                                 STT_VALEUR
               FROM   dbo.T_STATISTIQUES_STT
               ORDER  BY STT_VALEUR DESC) AS T2
        ) AS T ;

Un bon exercice complémentaire serait de voir quel est le coût de chacune de ces formulations et d'en déduire la meilleure. Cela risque de vous réserver que id=""lques surprises... !

II-J. Solution au problème n° 20 - insertion en bloc

Une première manière de faire est d'utiliser le produit cartésien des deux tables restreinte à l'univers visé :

 
Sélectionnez
INSERT INTO T_OBJET_COULEUR_OBC (OBJ_OBJET, CLR_COULEUR)
SELECT OBJ_OBJET, CLR_COULEUR
FROM   T_OBJET_OBJ
       CROSS JOIN T_COULEUR_CLR
WHERE  OBJ_OBJET = 'feu tricolore'    AND CLR_COULEUR IN ('rouge', 'vert', 'orange')
  OR   OBJ_OBJET = 'drapeau français' AND CLR_COULEUR IN ('bleu', 'blanc', 'rouge') ;

Une façon plus basique est de réaliser une union des lignes à insérer :

 
Sélectionnez
INSERT INTO T_OBJET_COULEUR_OBC (OBJ_OBJET, CLR_COULEUR)
SELECT OBJ_OBJET, 'rouge' FROM T_OBJET_OBJ WHERE OBJ_OBJET = 'feu tricolore'
UNION
SELECT OBJ_OBJET, 'vert' FROM T_OBJET_OBJ WHERE OBJ_OBJET = 'feu tricolore'
UNION
SELECT OBJ_OBJET, 'orange' FROM T_OBJET_OBJ WHERE OBJ_OBJET = 'feu tricolore'
UNION
SELECT OBJ_OBJET, 'bleu' FROM T_OBJET_OBJ WHERE OBJ_OBJET = 'drapeau français'
UNION
SELECT OBJ_OBJET, 'blanc' FROM T_OBJET_OBJ WHERE OBJ_OBJET = 'drapeau français'
UNION
SELECT OBJ_OBJET, 'rouge' FROM T_OBJET_OBJ WHERE OBJ_OBJET = 'drapeau français' ;

C'est très verbeux, mais certains SGBDR acceptent de s'affranchir de la clause FROM. Dans ce cas c'est plus rapide.

Exemple pour MS SQL Server :

 
Sélectionnez
INSERT INTO T_OBJET_COULEUR_OBC (OBJ_OBJET, CLR_COULEUR)
SELECT 'feu tricolore', 'rouge'
UNION
SELECT 'feu tricolore', 'vert'
UNION
SELECT 'feu tricolore', 'orange'
UNION
SELECT 'drapeau français', 'bleu'
UNION
SELECT 'drapeau français', 'blanc'
UNION
SELECT 'drapeau français', 'rouge' ;

Oracle, quant à lui, exige la clause FROM, mais autorise de préciser une table virtuelle de nom DUAL :

 
Sélectionnez
INSERT INTO T_OBJET_COULEUR_OBC (OBJ_OBJET, CLR_COULEUR)
SELECT 'feu tricolore', 'rouge' FROM DUAL
UNION
SELECT 'feu tricolore', 'vert' FROM DUAL
UNION
SELECT 'feu tricolore', 'orange' FROM DUAL
UNION
SELECT 'drapeau français', 'bleu' FROM DUAL
UNION
SELECT 'drapeau français', 'b id=""lanc' FROM DUAL
UNION
SELECT 'drapeau français', 'rouge' FROM DUAL ;

précédentsommairesuivant

Copyright © SQLPro. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.