Discussion :

[chduq]

Débutant en langage SQL, je souhaite résoudre le problème suivant :

Soit une table "compte" avec 6 colonnes (date + valeurs numériques entières) :

Date Val1 Val2 Val3 Val4 Val5
---------- ---- ---- ---- ---- ----
19/01/2008 10 23 30 35 48
20/01/2008 20 22 29 18 9
21/01/2008 12 13 14 9 41
22/01/2008 15 23 24 33 40
23/01/2008 7 8 9 28 19

Soit une table "reference" avec 10 colonnes (valeurs numériques entières) :

Val1 Val2 Val3 Val4 Val5 Val6 Val7 Val8 Val9 Val10
---- ---- ---- ---- ---- ---- ---- ---- ---- ----
9 10 17 18 30 34 35 41 42 48

Comment compter le nombre de valeurs vraies pour chaque enregistrement de la table "compte", par rapport à ma table de "reference" ?

Dans l'exemple ci-dessus la requête renverrait le résultat suivant :

19/01/2008 4
20/01/2008 2
21/01/2008 2
22/01/2008 0
23/01/2008 1

Merci par avance de votre aide.

PS : Je travaille sous MS SQL Server 2005 Express selon la norme SQL 92

[al1_24]

Comment compter le nombre de valeurs vraies pour chaque enregistrement de la table "compte", par rapport à ma table de "reference" ?

C'est quoi une valeur vraie ?

[chduq]

Explication par l'exemple :

Pour le 19/01/2008, dans ma table "compte" j'ai 4 valeurs vraies (10, 30, 35, 48), c'est à dire 4 valeurs qui correspondent à l'une des 10 valeurs de ma table "reference".

[al1_24]

Avec un tel modèle de données, cela va être fort difficile...
As-tu entendu parler des formes normales ?

[SQLpro]

Voici un extraordinaire exemple de non maitrise de ce qu'est une base de données relationnelle. Comme vous l'a dit al1_24 :

Avec un tel modèle de données, cela va être fort difficile...

En effet il ne s'agit même pas d'un modèle de données, mais d'un système de fichiers plats tel COBOL.

Si vous voulez traiter des données dans des bases, l'apprentissage de SQL ne suffit pas du tout. Il faut commencer par savoir modéliser les données et notamment comme le dit à nouveau al1_24 :

As-tu entendu parler des formes normales ?

En fait vous ne respectez même pas la première des formes normale : toute données doit être atomique. Or votre série de Val1, val2.... Valn, montre que vous en êtes resté aux structure linéaires de type tableau... Or un modèle relationnel n'a rien à voir avec cela.

Essayon sde vous le démontrer :

voici la requête qu'il va falloir faire dans votre cas pour traiter votre problème :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
SELECT CPT_DATE, CASE WHEN CPT_VAL1 = REF_VAL1 
                        OR CPT_VAL1 = REF_VAL2 
                        OR CPT_VAL1 = REF_VAL3 
                        OR CPT_VAL1 = REF_VAL4 
                        OR CPT_VAL1 = REF_VAL5 
                        OR CPT_VAL1 = REF_VAL6 
                        OR CPT_VAL1 = REF_VAL7 
                        OR CPT_VAL1 = REF_VAL8 
                        OR CPT_VAL1 = REF_VAL9 
                        OR CPT_VAL1 = REF_VAL10 THEN 1 ELSE 0 END
               + CASE WHEN CPT_VAL2 = REF_VAL1 
                        OR CPT_VAL2 = REF_VAL2 
                        OR CPT_VAL2 = REF_VAL3
                        OR CPT_VAL2 = REF_VAL4 
                        OR CPT_VAL2 = REF_VAL5 
                        OR CPT_VAL2 = REF_VAL6 
                        OR CPT_VAL2 = REF_VAL7 
                        OR CPT_VAL2 = REF_VAL8 
                        OR CPT_VAL2 = REF_VAL9
                        OR CPT_VAL2 = REF_VAL10 THEN 1 ELSE 0 END
               + CASE WHEN CPT_VAL3 = REF_VAL1 
                        OR CPT_VAL3 = REF_VAL2 
                        OR CPT_VAL3 = REF_VAL3 
                        OR CPT_VAL3 = REF_VAL4 
                        OR CPT_VAL3 = REF_VAL5 
                        OR CPT_VAL3 = REF_VAL6 
                        OR CPT_VAL3 = REF_VAL7 
                        OR CPT_VAL3 = REF_VAL8 
                        OR CPT_VAL3 = REF_VAL9 
                        OR CPT_VAL3 = REF_VAL10 THEN 1 ELSE 0 END
               + CASE WHEN CPT_VAL4 = REF_VAL1 
                        OR CPT_VAL4 = REF_VAL2 
                        OR CPT_VAL4 = REF_VAL3 
                        OR CPT_VAL4 = REF_VAL4 
                        OR CPT_VAL4 = REF_VAL5 
                        OR CPT_VAL4 = REF_VAL6 
                        OR CPT_VAL4 = REF_VAL7 
                        OR CPT_VAL4 = REF_VAL8 
                        OR CPT_VAL4 = REF_VAL9 
                        OR CPT_VAL4 = REF_VAL10 THEN 1 ELSE 0 END
               + CASE WHEN CPT_VAL5 = REF_VAL1 
                        OR CPT_VAL5 = REF_VAL2 
                        OR CPT_VAL5 = REF_VAL3 
                        OR CPT_VAL5 = REF_VAL4 
                        OR CPT_VAL5 = REF_VAL5 
                        OR CPT_VAL5 = REF_VAL6 
                        OR CPT_VAL5 = REF_VAL7 
                        OR CPT_VAL5 = REF_VAL8 
                        OR CPT_VAL5 = REF_VAL9 OR CPT_VAL5 = REF_VAL10 THEN 1 ELSE 0 END
              AS NOMBRE
FROM   CPT
       CROSS JOIN REF

Vous pouvez la tester à l'aide du script de création des tables et insert suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE TABLE CPT 
(CPT_DATE     DATETIME,
 CPT_VAL1     INT,
 CPT_VAL2     INT,
 CPT_VAL3     INT,
 CPT_VAL4     INT,
 CPT_VAL5     INT)
 
INSERT INTO CPT VALUES ('19/01/2008', 10, 23, 30, 35, 48)
INSERT INTO CPT VALUES ('20/01/2008', 20, 22, 29, 18, 9)
INSERT INTO CPT VALUES ('21/01/2008', 12, 13, 14, 9, 41)
INSERT INTO CPT VALUES ('22/01/2008', 15, 23, 24, 33, 40)
INSERT INTO CPT VALUES ('23/01/2008', 7, 8, 9, 28, 19)
 
CREATE TABLE REF
(REF_VAL1     INT,
 REF_VAL2     INT,
 REF_VAL3     INT,
 REF_VAL4     INT,
 REF_VAL5     INT,
 REF_VAL6     INT,
 REF_VAL7     INT,
 REF_VAL8     INT,
 REF_VAL9     INT,
 REF_VAL10    INT)
 
INSERT INTO REF VALUES (9, 10, 17, 18, 30, 34, 35, 41, 42, 48)

Voici maintenant le même résultat par une requête SQL sur une base bien modélisée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT T1.CPT_DATE, COUNT(T3.VAL) AS NOMBRE
FROM   CPT AS T1
       INNER JOIN CPT_VAL AS T2
             ON T1.CPT_DATE = T2.CPT_DATE
       LEFT OUTER JOIN REF AS T3
             ON T2.VAL = T3.VAL
GROUP  BY T1.CPT_DATE

Que vous pouvez aussi tester avec le script CREATE / INSERT suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
CREATE TABLE CPT 
(CPT_DATE     DATETIME)
 
INSERT INTO CPT VALUES ('19/01/2008')
INSERT INTO CPT VALUES ('20/01/2008')
INSERT INTO CPT VALUES ('21/01/2008')
INSERT INTO CPT VALUES ('22/01/2008')
INSERT INTO CPT VALUES ('23/01/2008')
 
CREATE TABLE CPT_VAL 
(CPT_DATE     DATETIME,
 VAL          INT)
 
INSERT INTO CPT_VAL VALUES ('19/01/2008', 10)
INSERT INTO CPT_VAL VALUES ('19/01/2008', 23)
INSERT INTO CPT_VAL VALUES ('19/01/2008', 30)
INSERT INTO CPT_VAL VALUES ('19/01/2008', 35)
INSERT INTO CPT_VAL VALUES ('19/01/2008', 48)
INSERT INTO CPT_VAL VALUES ('20/01/2008', 20)
INSERT INTO CPT_VAL VALUES ('20/01/2008', 22)
INSERT INTO CPT_VAL VALUES ('20/01/2008', 29)
INSERT INTO CPT_VAL VALUES ('20/01/2008', 18)
INSERT INTO CPT_VAL VALUES ('20/01/2008', 9)
INSERT INTO CPT_VAL VALUES ('21/01/2008', 12)
INSERT INTO CPT_VAL VALUES ('21/01/2008', 13)
INSERT INTO CPT_VAL VALUES ('21/01/2008', 14)
INSERT INTO CPT_VAL VALUES ('21/01/2008', 9)
INSERT INTO CPT_VAL VALUES ('21/01/2008', 41)
INSERT INTO CPT_VAL VALUES ('22/01/2008', 15)
INSERT INTO CPT_VAL VALUES ('22/01/2008', 23)
INSERT INTO CPT_VAL VALUES ('22/01/2008', 24)
INSERT INTO CPT_VAL VALUES ('22/01/2008', 33)
INSERT INTO CPT_VAL VALUES ('22/01/2008', 40)
INSERT INTO CPT_VAL VALUES ('23/01/2008', 7)
INSERT INTO CPT_VAL VALUES ('23/01/2008', 8)
INSERT INTO CPT_VAL VALUES ('23/01/2008', 9)
INSERT INTO CPT_VAL VALUES ('23/01/2008', 28)
INSERT INTO CPT_VAL VALUES ('23/01/2008', 19)
 
CREATE TABLE REF
(VAL     INT)
 
INSERT INTO REF VALUES (9)
INSERT INTO REF VALUES (10)
INSERT INTO REF VALUES (17)
INSERT INTO REF VALUES (18)
INSERT INTO REF VALUES (30)
INSERT INTO REF VALUES (34)
INSERT INTO REF VALUES (35)
INSERT INTO REF VALUES (41)
INSERT INTO REF VALUES (42)
INSERT INTO REF VALUES (48)

Non seulement la première requête est très difficile à écrire (Al1_24, malgré sa bonne volonté a du abandonnée à mon avis... mais moi comme j'ai choppé la grippe j'en profite pour me détendre avec du SQL !)
Mais elle possède deux inconvénient majeurs :
1) si vous voulez rajouter un 6eme valeur dans les comptes ou une 11e valeur dans les references, alors il vous faut restructurer les deux tables et récrie la requête...
2 ) les performances d'une telle requête sont catastrophique car aucune utilisation d'index n'est possible

Autrement dit, commencez par apprendre à modéliser une base de données avant de tenter de faire des requêtes SQL !
http://sqlpro.developpez.com/cours/modelisation/merise/
http://merise.developpez.com/faq/?page=MCD

A +

[fsmrel]

il ne s'agit même pas d'un modèle de données, mais d'un système de fichiers plats tel COBOL.

COBOL n'est pas un système de fichiers plats (ou replets), mais un langage de 3e génération, qui a vu le jour il y a cinquante ans grâce à la ténacité de l'amiral Grace Murray Hopper.

Voir par exemple :
http://cs-www.cs.yale.edu/homes/tap/...per-story.html

Cela dit, il est évident que COBOL permet de manipuler tous types de fichiers.

[SQLpro]

Mon cher fmsrel je fais là une élipse destinée à me faire comprendre le plus rapidement possible !

A +

[fsmrel]

Cerres SQLpro, mais si vous écriviez :

"un système de fichiers plats tel Java"

je suppose que ça réagirait...

[SQLpro]

Oh que oui.... mais à l'époque de Cobol on a inventé subséquemment les techniques de lecture par bloc des fichiers, qui restent encore le moyen le plus rapide de lecture de fichiers et aussi les fichiers ISAM...
Cobol était donc très imbriqué avec la gestion de fichiers, comme Java est très imbriqué avec l'Internet et les accès TCP/IP.

Cela choquerait moins, si je disais un système NOS comme Java...

A +

[fsmrel]

à l'époque de Cobol on a inventé subséquemment les techniques de lecture par bloc des fichiers, qui restent encore le moyen le plus rapide de lecture de fichiers et aussi les fichiers ISAM...

Pour avoir eu à développer des méthodes de lecture de type QSAM sur ordinausaures IBM de 2e génération, en assembleur, et en échange direct avec les interrupts hard des bandes magnétiques (on n’avait pas encore de disques, mais des bandes et des cartes, voire du ruban papier passant son temps à se déchirer...), je n’ai pas attendu Cobol pour regrouper les enregistrements en blocs physiques et faire travailler les canaux en parallèle pour aller encore plus vite (il a fallu attendre 30 ans pour que certains de mes propres programmes en assembleur soient remplacés et je ne suis pas seul dans ce cas : Sabre a même été implanté avec son système de bandes magnétiques, en France, au début des années quatre-vingt-dix, mais je ne peux en dire plus...). En tout cas, je confirme qu’aujourd’hui, pour aller vite pour des traitements de masse, il n’y a pas mieux que le regroupements des enregistrements en blocs et les LDS (Linear Data sets) utilisés par DB2 ne sont qu’une reprise à la fois simple et sophistiquée des vielles marmites (qui ont toujours permis de faire les meilleures soupes). Un petit coup de rotational positioning sensing (RPS) et autres trucs permettent encore de booster tout ça.

Quant à ISAM, je me souviens avec émotion de la gestion des overflows, c'était au début des années soixante-dix, c’était l’bon temps, on était les rois... Et avec IMS, on avait même droit à HISAM, SHISAM, HDAM, GSAM (et HIDAM pour les feignants, donc le plus utilisé...) Et puis sont arrivés Ted Codd, Ray Boyce, Don Chamberlin, Jim Gray, mon copain Chris Date, j’en passe et des meilleurs, qui ont bâti un monde nouveau (ce dont certains opportunistes ayant su flairer le bon coup, comme Lawrence Ellison, ont su tirer "profit"...) but this is another story....

Cela choquerait moins, si je disais un système NOS comme Java...

Certes...