Discussion :

[Patrice Henrio]

* Bonjour, *

J'ai peut-être mal cherché et je m'en excuse. Je débute en SQL.
Une table contient deux clés étrangères sur une deuxième table (id1_TabB, id2_TabB).

Je voudrai ajouter une colonne dans la table A qui contiendrait la moyenne des valeurs de la table B entre id1 et id2.

Est-ce possible et surtout comment. Ce que j'ai vu jusque là permettait d ela faire mais avec des colonnes de la même table.

* Merci *

[CinePhil]

Vu que ce serait une colonne calculable, tu ne devrais pas la créer.
Cependant, le calcul ne me semble pas simple en SQL a priori.

Quel est ton SGBD ?

[sweetasnz]

bonjour,
pensez vous que ceci pourrait aider?

je pars du principe où la colonne qui servira pour la moyenne existe (ici tab1.c)

sous ORACLE 11g :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table tab1 (a number, b number, c number default null);
insert into tab1(a,b) values (1, 10);
insert into tab1(a,b) values (2, 20);
insert into tab1(a,b) values (3, 30);
insert into tab1(a,b) values (4, 40);
 
create table tab2 (d number, e number, f number);
insert into tab2 values (1, 11,101);
insert into tab2 values (2, 22,202);
insert into tab2 values (3, 33,303);
insert into tab2 values (4, 44,404);
 
update tab1 set c=(select (d+e+f)/3.  from tab2,tab1 where tab2.d=tab1.a and tab2.d=4)
where tab1.a=(select d  from tab2,tab1 where tab2.d=tab1.a and tab1.a=4);

il faut ensuite boucler sur une colonne qui renvoie la bonne information, ici dans cet exemple c'est tab1.a et tab2.d. avec la valeur 4 (qui serait récupérée à chaque itération et mise à jour dans la requête)
bref, si cela ne répond pas à votre attente, 1000 excuses par avance.


résultat de l'exemple (select * from tab1)
tab1 final
A B C
1 10
2 20
3 30
4 40 150,666666666666666666666666

[Patrice Henrio]

Vu que ce serait une colonne calculable, tu ne devrais pas la créer.
Cependant, le calcul ne me semble pas simple en SQL a priori.

Quel est ton SGBD ?

J'utilise H2.
En fait cette base est à 99% en consultation et à 1% en création. Le temps de traitement en création n'est pas un problème. Par contre il l'est en consultation. J'ai besoin d'un calcul relativement long (j'ai simplifié pour présenter mon problème) mais qui est fait une fois pour toute d'où mon idée de le faire comme cela (ou alors dans une vue peut-être ?).

[Patrice Henrio]

bonjour,
pensez vous que ceci pourrait aider?

je pars du principe où la colonne qui servira pour la moyenne existe (ici tab1.c)

sous ORACLE 11g :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table tab1 (a number, b number, c number default null);
insert into tab1(a,b) values (1, 10);
insert into tab1(a,b) values (2, 20);
insert into tab1(a,b) values (3, 30);
insert into tab1(a,b) values (4, 40);
 
create table tab2 (d number, e number, f number);
insert into tab2 values (1, 11,101);
insert into tab2 values (2, 22,202);
insert into tab2 values (3, 33,303);
insert into tab2 values (4, 44,404);
 
update tab1 set c=(select (d+e+f)/3.  from tab2,tab1 where tab2.d=tab1.a and tab2.d=4)
where tab1.a=(select d  from tab2,tab1 where tab2.d=tab1.a and tab1.a=4);

il faut ensuite boucler sur une colonne qui renvoie la bonne information, ici dans cet exemple c'est tab1.a et tab2.d. avec la valeur 4 (qui serait récupérée à chaque itération et mise à jour dans la requête)
bref, si cela ne répond pas à votre attente, 1000 excuses par avance.


résultat de l'exemple (select * from tab1)
tab1 final
A B C
1 10
2 20
3 30
4 40 150,666666666666666666666666

C'est un peu plus compliqué que cela.

Code :

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

1
2
3
4
5
6
7
 
Create Tab_1 a nombre, b nombre, c nombre, d nombre;
Insert into tab_1(a,b,c) Values (1,12,17);
Insert into tab_1(a,b,c) Values (2,7,13);
insert into tab_1(a,b,c) Values (3, 4,9);
 
Create Tab_2 id nombre, v nombre

On suppose que la table Tab_2 contient au moins les lignes d'indice 4 à 17

Pour chaque ligne de Tab_1 je veux placer dans la colonne d la moyenne des valeurs de Tab_2 d'indice compris entre la valeur d eb et la valeur de c

Code :

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

update Tab_1 set d=AVG (select v from tab_2 where id between Tab_1.b and Tab_1.c);

Est-ce que c'est possible ?
Le cas présenté dans la réponse doit être particulièrement gourmand en temps du fait d'une boucle en SQL.

[Arkhena]

Salut Papa,

Ben finalement, tu vois, je trouve le temps de te répondre

Pourquoi ne pas calculer les valeurs dans ton programme qui insère les données ?

Etant donné que tu n'as pas vraiment de souci de performances au niveau du remplissage des données, tu pourrais faire ce calcul en amont dans ton tableur qui comporte toutes tes données, non ?

(Je suis consciente que ça fait pas avancer le schmilblick au niveau SQL)...

Bonne continuation!

Arkhena

[Patrice Henrio]

Salut Papa,

Ben finalement, tu vois, je trouve le temps de te répondre

Pourquoi ne pas calculer les valeurs dans ton programme qui insère les données ?

Etant donné que tu n'as pas vraiment de souci de performances au niveau du remplissage des données, tu pourrais faire ce calcul en amont dans ton tableur qui comporte toutes tes données, non ?

(Je suis consciente que ça fait pas avancer le schmilblick au niveau SQL)...

Bonne continuation!

Arkhena

C'était surtout pour progresser en SQL que je posais la question car effectivement j'ai la solution de calculer tout cela en Visual Basic, y compris créer le script d'injection SQL. Mais je me demandais si cela n'était pas faisable directement ...

[Arkhena]

Peut-être un truc de ce genre (Attention, pas testé) :

Code :

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

1
2
3
4
UPDATE  Tab_1 tb1
SET     tb1.col1 =  (   SELECT  AVG(v) 
                        FROM    tab_2 tb2
                        WHERE   tb2.id BETWEEN tb1.b AND tb1.c )

[sweetasnz]

@Patrice : Achtung à la syntaxe si tu veux progresser...

Peut-être un truc de ce genre (Attention, pas testé) :

Code :

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

1
2
3
4
UPDATE  Tab_1 tb1
SET     tb1.col1 =  (   SELECT  AVG(v) 
                        FROM    tab_2 tb2
                        WHERE   tb2.id BETWEEN tb1.b AND tb1.c )

a priori c'est bon
testé sous oracle :

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
 
CREATE TABLE tab1 (a number, b number, c number, d number);
INSERT INTO tab1(a,b,c) VALUES (1,12,17);
INSERT INTO tab1(a,b,c) VALUES (2,7,13);
INSERT INTO tab1(a,b,c) VALUES (3, 4,9);
 
CREATE TABLE tab2 (id number, v number);
INSERT INTO tab2(id,v) VALUES (1,2);
INSERT INTO tab2(id,v) VALUES (2,3);
INSERT INTO tab2(id,v) VALUES (3,4);
INSERT INTO tab2(id,v) VALUES (4,6);
INSERT INTO tab2(id,v) VALUES (5,4);
INSERT INTO tab2(id,v) VALUES (6,6);
INSERT INTO tab2(id,v) VALUES (7,4);
INSERT INTO tab2(id,v) VALUES (8,6);
INSERT INTO tab2(id,v) VALUES (9,4);
INSERT INTO tab2(id,v) VALUES (10,6);
INSERT INTO tab2(id,v) VALUES (11,8);
INSERT INTO tab2(id,v) VALUES (12,6);
INSERT INTO tab2(id,v) VALUES (13,8);
INSERT INTO tab2(id,v) VALUES (14,6);
INSERT INTO tab2(id,v) VALUES (15,8);
INSERT INTO tab2(id,v) VALUES (16,6);
INSERT INTO tab2(id,v) VALUES (17,8);
 
UPDATE tab1 SET d=(SELECT AVG(v) FROM tab2 WHERE id BETWEEN tab1.b AND tab1.c);
 
select * from tab1;
select * from tab2;

[Patrice Henrio]

@Patrice : Achtung à la synthaxe si tu veux progresser...



a priori c'est bon
testé sous oracle :

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
 
CREATE TABLE tab1 (a number, b number, c number, d number);
INSERT INTO tab1(a,b,c) VALUES (1,12,17);
INSERT INTO tab1(a,b,c) VALUES (2,7,13);
INSERT INTO tab1(a,b,c) VALUES (3, 4,9);
 
CREATE TABLE tab2 (id number, v number);
INSERT INTO tab2(id,v) VALUES (1,2);
INSERT INTO tab2(id,v) VALUES (2,3);
INSERT INTO tab2(id,v) VALUES (3,4);
INSERT INTO tab2(id,v) VALUES (4,6);
INSERT INTO tab2(id,v) VALUES (5,4);
INSERT INTO tab2(id,v) VALUES (6,6);
INSERT INTO tab2(id,v) VALUES (7,4);
INSERT INTO tab2(id,v) VALUES (8,6);
INSERT INTO tab2(id,v) VALUES (9,4);
INSERT INTO tab2(id,v) VALUES (10,6);
INSERT INTO tab2(id,v) VALUES (11,8);
INSERT INTO tab2(id,v) VALUES (12,6);
INSERT INTO tab2(id,v) VALUES (13,8);
INSERT INTO tab2(id,v) VALUES (14,6);
INSERT INTO tab2(id,v) VALUES (15,8);
INSERT INTO tab2(id,v) VALUES (16,6);
INSERT INTO tab2(id,v) VALUES (17,8);
 
UPDATE tab1 SET d=(SELECT AVG(v) FROM tab2 WHERE id BETWEEN tab1.b AND tab1.c);
 
select * from tab1;
select * from tab2;

Merci de la réponse.

Pour la syntaxe j'étais bien conscient que cela pêchait mais j'essayais d'expliquer au mieux mon problème sachant qu'il est un tantinet plus compliqué : mais la réponse apportée y répond. Je vais la tester avec H2.

[Patrice Henrio]

Quand je disais que le problème était plus compliqué que prévu :
Une table pourtours avec les colonnes id, lat_centre
Une table pourtours_limes avec les colonnes id_pourtour, id_limes
Une table limes avec les colonnes id, id_premier_point, id_dernier_point
Une table points avec les colonnes id, lat

Voici ce que je veux obtenir :
A partir de pourtours_limes je récupère tous les limes en relation avec un pourtour particulier.
A partir de tous ces limes je récupère tous les points les composant (ceux dont l'identifiant est entre id_premier_point et id_dernier_point)
A partir de tous ces points je calcule la moyenne de la colonne lat et je mets cette valeur dans la colonne lat_centre de la table pourtour à la ligne correspondant au pourtour choisi.

Et il faut faire cela pour toutes les lignes de la table pourtour.

[Patrice Henrio]

Bon je sais le calculer pour un pourtour.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT AVG(latitude)
           FROM points 
           JOIN (SELECT id, id_premier_point, id_dernier_point
                              FROM limes
                              JOIN (SELECT id_limes  
                                                 FROM pourtours_limes AS PL
                                                 JOIN POURTOURS AS P 
                                                 ON (P.ID = 1001)
                                                       AND  (PL.id_pourtour  = P.id)
                                     )
                               ON limes.id = id_limes
                   )
            ON points.id BETWEEN id_premier_point AND id_dernier_point;

Mais maintenant pour remplir la colonne lat-centre de la table pourtours avec ce calcul pour chaque ligne ?

Je précise que je suis débutant en SQL.

Bien sûr la partie

Code :

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

1
2
3
4
5
6
JOIN (SELECT id_limes  
                                                 FROM pourtours_limes AS PL
                                                 JOIN POURTOURS AS P 
                                                 ON (P.ID = 1001)
                                                       AND  (PL.id_pourtour  = P.id)
                                     )

doit s'écrire

Code :

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

1
2
3
4
JOIN (SELECT id_limes  
                                                 FROM pourtours_limes AS PL
                                                 WHERE (PL.id_pourtour  = 1001)
                                     )

[Patrice Henrio]

Quand je disais que le problème était plus compliqué que prévu :
Une table pourtours avec les colonnes id, lat_centre
Une table pourtours_limes avec les colonnes id_pourtour, id_limes
Une table limes avec les colonnes id, id_premier_point, id_dernier_point
Une table points avec les colonnes id, lat

Voici ce que je veux obtenir :
A partir de pourtours_limes je récupère tous les limes en relation avec un pourtour particulier.
A partir de tous ces limes je récupère tous les points les composant (ceux dont l'identifiant est entre id_premier_point et id_dernier_point)
A partir de tous ces points je calcule la moyenne de la colonne lat et je mets cette valeur dans la colonne lat_centre de la table pourtour à la ligne correspondant au pourtour choisi.

Et il faut faire cela pour toutes les lignes de la table pourtour.

Je sais créer une table (via select) qui donne pour chaque pourtour la moyenne des latitudes des points composant le pourtour.

Code :

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

1
2
3
4
5
6
7
SELECT  PL.ID_POURTOUR, AVG(PT.LATITUDE) 
                    FROM POURTOURS_LIMES AS PL
                    JOIN LIMES AS L  
                    ON (PL.ID_LIMES = L.ID)
                    JOIN POINTS AS PT
                    ON (PT.ID BETWEEN L.ID_PREMIER_POINT AND L.ID_DERNIER_POINT)
                    GROUP BY PL.ID_POURTOUR

[Patrice Henrio]

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
UPDATE POURTOURS 
       SET LAT_CENTRE = 
           (SELECT Centre
                   FROM (SELECT  PL.ID_POURTOUR AS N, AVG(PT.LATITUDE)  as Centre
                                 FROM POURTOURS_LIMES AS PL
                                 JOIN LIMES AS L  
                                 ON (PL.ID_LIMES = L.ID)
                                 JOIN POINTS AS PT
                                 ON (PT.ID BETWEEN L.ID_PREMIER_POINT AND L.ID_DERNIER_POINT)
                                 GROUP BY N
					    )
                   WHERE N=POURTOURS.ID
		    );