Discussion :

[kitoufloux]

Bonjours à tous!

J'ai besoin d'aide sur une requête un peu compliquée pour moi....
je vais essayer de vous expliquer le plus clairement possible.

Voila, j'ai une table qui s'appelle "radar" qui contient une liste de radar mobile (sur les routes donc) avec tout son historique. C'est à dire qu'un radar peut être présent plusieurs fois au même endroit (mêmes coordonées GPS).

Mon but est de faire un "TOP 40" des apparitions dans la base de données : Trier en fonction du nombre d'apparition des radars au même endroit. De plus chaque enregistrement a un "commentaire" et il faudrait afficher le dernier commentaire enregistré pour illustré le top 40.

En espérant avoir été assez clair. N'hésitez pas à me demander plus de précisions et merci pour votre aide!

[aieeeuuuuu]

Bonjour,

L'explication est assez claire, par contre, pourrais tu donner la structure des tables en jeu ? (si tu as essayé une requête, n'hésites pas à la poster, ça peut aussi aider a mieux comprendre ce que tu veux)

Peux-tu aussi préciser ton SGBDR ?

[kitoufloux]

Bonjour,

L'explication est assez claire, par contre, pourrais tu donner la structure des tables en jeu ? (si tu as essayé une requête, n'hésites pas à la poster, ça peut aussi aider a mieux comprendre ce que tu veux)

Peux-tu aussi préciser ton SGBDR ?

Merci de ta réponse.

alors en fait cela ne concerne qu'une seule table, la table "radar" dont voici la structure :

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
CREATE TABLE radar
(
-- Hérité(e) from table evenement:  id integer NOT NULL DEFAULT nextval('evenement_id_seq'::regclass),
-- Hérité(e) from table evenement:  "position" text,
-- Hérité(e) from table evenement:  direction text,
-- Hérité(e) from table evenement:  commentaire text,
-- Hérité(e) from table evenement:  valide boolean,
-- Hérité(e) from table evenement:  "jourDeFete" text,
-- Hérité(e) from table evenement:  "zoneParticuliere" text,
-- Hérité(e) from table evenement:  "conditionMeteo" text,
-- Hérité(e) from table evenement:  debut bigint,
-- Hérité(e) from table evenement:  "dureeDeVie" bigint,
-- Hérité(e) from table evenement:  prenoms text,
-- Hérité(e) from table evenement:  departement text,
-- Hérité(e) from table evenement:  rue text,
-- Hérité(e) from table evenement:  ville text,
-- Hérité(e) from table evenement:  afficher boolean DEFAULT true,
-- Hérité(e) from table evenement:  "idGroupe" integer,
  "typeDeRadar" text,
  "typeDeVehicule" text,
  "couleurDuVehicule" text,
  vitesse integer,
  CONSTRAINT "primaireRadar" PRIMARY KEY (id)
)

Comme on peut le voir, j'hérite certaines colonnes d'une autre table, mais cela reviens à manipuler la table radar. Et j'utilise PostgreSQL comme SGBD.

les champs qui nous intéresses donc sont les champs "position", ainsi que le champs "commentaire".

[aieeeuuuuu]

les champs qui nous intéresses donc sont les champs "position", ainsi que le champs "commentaire".

... et debut que j'ai pris comme reference pour rechercher le "dernier" commentaire (est-ce un timestamp ?)
Je suppose qu'il ne peut y avoir deux lignes ayant le même debut à la même position...

hmmm, position => text...ce sont les coordonnées GPS ?
tu risque d'avoir des surprises ! (à 1 cm près, ce n'est plus la même position)...
regarde donc d'ou proviennent les données...

est-ce que cette requete te donne ce que tu veux ?

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT R1.position, R1.Nb, R2.commentaire
FROM (
	SELECT TOP 40
		position,
		COUNT(*) AS Nb,
		MAX(debut) AS LastDate
	FROM radar 
	GROUP BY position
	ORDER BY COUNT(*) DESC
	) R1 
INNER JOIN Radar R2 
	ON R1.position = R2.Position 
	AND R1.LastDate = R2.debut

[kitoufloux]

... et debut que j'ai pris comme reference pour rechercher le "dernier" commentaire (est-ce un timestamp ?)

Oui en effet, et oui c'est un timestamp.

Je suppose qu'il ne peut y avoir deux lignes ayant le même debut à la même position...

non, en effet.

hmmm, position => text...ce sont les coordonnées GPS ?
tu risque d'avoir des surprises ! (à 1 cm près, ce n'est plus la même position)...
regarde donc d'ou proviennent les données...

Oui ce sont des coordonées GPS, mais elles sont rentrées à la main et ce sont en fait des copies d'enregistrements, donc se seront exactement les mêmes, pas de soucis de ce coté là.

est-ce que cette requete te donne ce que tu veux ?

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT R1.position, R1.Nb, R2.commentaire
FROM (
	SELECT TOP 40
		position,
		COUNT(*) AS Nb,
		MAX(debut) AS LastDate
	FROM radar 
	GROUP BY position
	ORDER BY COUNT(*) DESC
	) R1 
INNER JOIN Radar R2 
	ON R1.position = R2.Position 
	AND R1.LastDate = R2.debut

Dans PgAdmin j'obtiens :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
ERROR:  syntax error at or near "40"
LINE 3:  SELECT TOP 40
                    ^
 
 
********** Erreur **********
 
ERROR: syntax error at or near "40"
État SQL :42601
Caractère : 62

[aieeeuuuuu]

pardon, sous Postgre, il faudra peut etre utiliser :

Code SQL :

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

1
2
 
LIMIT 40

en fin de sous-requete à la place du top 40

[kitoufloux]

Ca fonctionne pas mal ! l'ordre n'était pas bon, mais avec l'ajout d'un ORDER BY, tout est entré dans l'ordre!

la requête finale est donc

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
 
SELECT R1.position, R1.Nb, R2.commentaire
FROM (
	SELECT  position,
		COUNT(*) AS Nb,
		MAX(debut) AS LastDate
	FROM radar 
	GROUP BY position
	ORDER BY COUNT(*) DESC
	LIMIT 40
) R1 
INNER JOIN Radar R2 
	ON R1.position = R2.Position 
	AND R1.LastDate = R2.debut
ORDER BY nb DESC

et le résultat est parfait, merci beaucoup !

[Oishiiii]

Bonsoir,

Le ORDER BY dans la sous-requête est tout à fait inutile.

Pour simplifier la lecture, personnellement je placerais le LIMIT sur la requête principale et j'utiliserais une jointure naturelle (il faut en profiter sur PostgreSQL).

Code :

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

1
2
3
4
5
6
7
8
9
SELECT position, nb, commentaire
FROM radar
  NATURAL JOIN (
    SELECT Count(*) AS nb, Max(debut) AS debut, position
    FROM radar
    GROUP BY position
  ) AS R1
ORDER BY nb DESC
LIMIT 40

[kitoufloux]

je ne suis pas un expert en postgresql et je me pose une question :

quelle est la différence entre une jointure naturelle, et une jointure "classique" ?

[aieeeuuuuu]

Bonsoir,

Le ORDER BY dans la sous-requête est tout à fait inutile.

Voila qui est bien péremptoire !

J'ai bien dis :

Je suppose qu'il ne peut y avoir deux lignes ayant le même debut à la même position...

De là à supposer qu'il y a bien une contrainte d'unicité de déclarée... j'en doute dans le cas présent, et les deux requêtes ne sont alors sémantiquement pas identiques...

[Oishiiii]

De là à supposer qu'il y a bien une contrainte d'unicité de déclarée... j'en doute dans le cas présent, et les deux requêtes ne sont alors sémantiquement pas identiques...

Mea culpa, je ne voulais surtout pas faire allusion à un éventuel index qui effectuerai un tri implicite ou quelque chose de ce genre.
Le tri est nécessaire à cause du TOP.


Pour la jointure naturelle. Celle-ci ne nécessite pas de condition de jointure (ON ..).
La jointure est effectuée en se servant des colonnes qui ont un nom (et type normalement) commun entre les deux tables.

Avec les tables:

Code :

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

1
2
Client(Cli_id, Cli_Nom, ...)
Facture(Fact_id, Fact_date, Cli_id)

Pas besoin de faire:

Code :

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

1
2
3
4
SELECT *
FROM Facture
  JOIN Client
    ON Facture.Cli_id = Client.Cli_id

Mais seulement :

Code :

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

1
2
3
SELECT *
FROM Facture
  NATURAL JOIN Client

De plus, la jointure naturelle élimine automatiquement les colonnes en double dans le résultat de la requête.

Plus d'erreur d'ambiguité lorsque vous faites "SELECT Cli_id.." et que le SGBDR ne sait pas de quelle table vient cette colonne. Plus besoin de préfixer le nom des colonnes par le nom de la table.

[aieeeuuuuu]

Mea culpa, je ne voulais surtout pas faire allusion à un éventuel index qui effectuerai un tri implicite ou quelque chose de ce genre.
Le tri est nécessaire à cause du TOP.

Oui sans le TOP, le ORDER BY serait en effet inutile et même très mal venu (et je pense que le moteur le ferait savoir a qui de droit )

[Waldar]

Pour la jointure naturelle. Celle-ci ne nécessite pas de condition de jointure (ON ..).
La jointure est effectuée en se servant des colonnes qui ont un nom (et type normalement) commun entre les deux tables (etc)

La jointure naturelle, très belle en relationnel si je me réfère à la prose de notre seigneur en la matière fsmrel et conseillée par le visionnaire C. Date, est malheureusement complètement à proscrire en SQL.

À part introduire confusion et ambiguïté dans le code, elle n'apporte rien d'intéressant si ce n'est de réduire de quelques octets le code de celle-ci.

Je la déconseille plus que fortement.

[Oishiiii]

À part introduire confusion et ambiguïté dans le code, elle n'apporte rien d'intéressant si ce n'est de réduire de quelques octets le code de celle-ci.

Je la déconseille plus que fortement.

Merci de développer.... c'est un peu court.

[Waldar]

Très bien, vous arrivez dans une nouvelle société, on vous explique que l'application de facturation ne fonctionne plus.
Le DBA vous informe que c'est la requête suivante qui ne va plus (il vous précise aussi que chaque table possède cinquante colonnes) :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT *
  FROM GMMJKPMZIHQLQSYFVGDFCHDUDTQCAC
       NATURAL JOIN HGHNYYGOXNDHNATMHSXSLWYJYKGDLL
       NATURAL JOIN STFQUOJXGVTZNQFVTLNALCYYMGQOQJ
       NATURAL JOIN JYICEYVWXZHASKIRSRUMLSFKJEAVFL
       NATURAL JOIN XSVOYXXJNDVBMGLUTQPHTBGWTSDIYJ
       NATURAL JOIN JTOTOGYPUWVNTGSWNYBZLTXMHRKZXX
       NATURAL JOIN HGCCXJHLWFQODJOLYUVURPBWVQKZTL
       NATURAL JOIN DGWXYTWASOCENFIAFWGCLCOZTDXNCR
       NATURAL JOIN VKRSDPHAJGABYIZZPHSCTWCHLXKULA

Je vous souhaite du grand plaisir avec le dictionnaire de données !

Si la requête était écrite comme ceci, combien de temps allez-vous gagner ?

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
SELECT t1.col1, t2.col3, t4.col9, t9.col3
  from GMMJKPMZIHQLQSYFVGDFCHDUDTQCAC t1
       INNER JOIN HGHNYYGOXNDHNATMHSXSLWYJYKGDLL t2
         on t2.col1 = t1.col1
       INNER JOIN STFQUOJXGVTZNQFVTLNALCYYMGQOQJ t3
         on t3.col2 = t2.col2
       INNER JOIN JYICEYVWXZHASKIRSRUMLSFKJEAVFL t4
         on t4.col3 = t3.col3
       INNER JOIN XSVOYXXJNDVBMGLUTQPHTBGWTSDIYJ t5
         on T5.col4 = t4.col4
       INNER JOIN JTOTOGYPUWVNTGSWNYBZLTXMHRKZXX t6
         on T6.col5 = t5.col5
       INNER JOIN HGCCXJHLWFQODJOLYUVURPBWVQKZTL t7
         on T7.col6 = t2.col6
       INNER JOIN DGWXYTWASOCENFIAFWGCLCOZTDXNCR t8
         on t8.col7 = t7.col7
       INNER JOIN VKRSDPHAJGABYIZZPHSCTWCHLXKULA t9
         on t9.col8 = t8.col8

Autre cas de figure, quel est l'accès aux données dans cet exemple :

Code :

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

1
2
3
4
5
6
7
8
create table t1 (t1_id  integer);
create table t2 (t2_id  integer, t1_id  integer);
create table t3 (t3_id  integer, t2_id  integer, t1_id  integer);
 
select *
  from t1
       natural join t3 -- t3 AVANT t2
       natural join t2

Pouvez-vous honnêtement répondre sans écrire un jeu de test ?

[Oishiiii]

Je n'ai jamais affirmé qu'il fallait remplacer toutes les jointures internes par des jointures naturelles lorsqu'on écrit ses requêtes.... j'utilise les jointures internes et naturelles régulièrement en fonction des cas.

Une personne qui écrirait ce genre de requête, avec 8 jointures naturelle manque de bon sens... c'est évident...

Est-ce une raison suffisante pour "proscrire" l'utilisation de cet opérateur en SQL ?
On plaisante ?

Code :

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

1
2
3
4
5
6
7
8
create table t1 (t1_id  integer);
create table t2 (t2_id  integer, t1_id  integer);
create table t3 (t3_id  integer, t2_id  integer, t1_id  integer);
 
select *
  from t1
       natural join t3 -- t3 AVANT t2
       natural join t2

Pouvez-vous honnêtement répondre sans écrire un jeu de test ?

Je n'ai pas testé, et le résultat peut certainement varier d'une implémentation (SGBDR) à l'autre.. ça ne serait pas la première fois.

Toutefois j'ai quelques notions de théorie relationnelle, et je sait que l'opérateur de jointure (naturelle) est associatif et commutatif.
L'ordre des tables n'a aucune importance.... le résultat devrait être le même.


Entre nous, si on devait proscrire du langage SQL tout ce qui est source de "confusion" et "ambiguïté"... il ne resterait plus grand chose...

[Waldar]

Une personne qui écrirait ce genre de requête, avec 8 jointures naturelle manque de bon sens... c'est évident...

Compte-tenu que le seul gain des jointures naturelles est de gagner des octets, je ne vois pas plus de manque de bon sens qu'avec une seule jointure.
C'est la même chose.

Est-ce une raison suffisante pour "proscrire" l'utilisation de cet opérateur en SQL ?
On plaisante ?

Non "on" ne plaisante pas et oui c'est largement suffisant.
Les requêtes non maîtrisées c'est la cause numéro 1 de tous les problèmes de bases de données.

Que faites-vous quand votre modèle de données évolue ?
Vous contrôlez toutes vos requêtes avec NATURAL JOIN à cause des jointures automatique sur le nom ?

Plus d'erreur d'ambiguité lorsque vous faites "SELECT Cli_id.." et que le SGBDR ne sait pas de quelle table vient cette colonne. Plus besoin de préfixer le nom des colonnes par le nom de la table.

Ce n'est pas "plus besoin", il n'y a plus le choix puisque la colonne qui apparaît dans le select n'appartient plus à aucune table.
Vraiment, c'est magnifique !

Entre nous, si on devait proscrire du langage SQL tout ce qui est source de "confusion" et "ambiguïté"... il ne resterait plus grand chose...

Merci de développer.... c'est un peu court.

[Oishiiii]

Compte-tenu que le seul gain des jointures naturelles est de gagner des octets, je ne vois pas plus de manque de bon sens qu'avec une seule jointure.
C'est la même chose.

Ce n'est pas qu'une question d'octet..
L'opérateur de jointure naturelle est un opérateur de base (historique), il est fondamental dans la théorie relationnelle et son algèbre.
Je suis bien content que certains SGBDR me le propose et je l'utilise en ayant pleinement conscience de ses possibilités (positives ou moins positives).

D'ailleurs l'opérateur "INNER JOIN .. ON.." n'ajoutes-t'il pas trop d'octets par rapport à une jointure effectuée par la restriction d'un produit cartésien ?

Code :

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

FROM t1, t2 WHERE t1.id = t2.id

Que faites-vous quand votre modèle de données évolue ?
Vous contrôlez toutes vos requêtes avec NATURAL JOIN à cause des jointures automatique sur le nom ?

La structure de mes tables évolue rarement car elle n'a pas était conçue avec les pieds. (Je ne plaisante pas )

En appliquant ce raisonnement, est-ce que vous proscrivez aussi l'opérateur UNION ?

Ce n'est pas "plus besoin", il n'y a plus le choix puisque la colonne qui apparaît dans le select n'appartient plus à aucune table.
Vraiment, c'est magnifique !

Ont est enfin tombé d'accord

[Oishiiii]

Concernant les problèmes de confusion et incohérences du langage SQL, c'est comme une boite de chocolat, il y a tout les parfums, je ne sait pas où commencer.

Il y a aurait des centaines de pages de Chris Date (par exemple) à citer mais je n'aurai aucun ouvrage sous la main avant ce week-end.

Je vais citer les plus importants tout de même:

• Possibilité d'avoir des tables (et donc des résultats de requête) contenant des doublons
• Possibilité d'avoir plusieurs noms de colonne identique dans le résultat d'une requête.
• NULL

[Waldar]

Je ne vois pas le rapport avec UNION ?

Quelques citations en trente minutes de recherche :
MySQL :

Natural joins are not a good idea wherever you write them. They hide the meaning that more explicit code would expose and may introduce subtle bugs if column names change.

Oracle 1 :

that is the nature of the "natural join" which should be avoided like the plague.

avoid natural joins - period, don't even consider using them for anything.

Oracle 2 :

But a natural join just sees two columns with the same name.
It is a bug just WAITING to happen.

don't don't ever use a natural join in real life, forget they exist.

Discussion autour de son absence dans SQL-Server :

Because natural joins are implicit, there is no way to see what columns will be used in the join. You might not get what you think you’re getting.

En fait je n'arrive pas trouver un seul exemple qui argumente en faveur du NATURAL JOIN, si vous voulez chercher un peu...