Discussion :

[mister3957]

Bonjour,

Je travaille sous PostgreSQL et actuellement je sèche sur une requête qui consiste pour chaque catégorie à trouver l'article le moins cher.

Voici ce que je possède :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
table t_cat
    id,
    name
 
table t_prod
    id,
    name,
    price
 
table t_cat_prods
    id_cat,
    id_prod

Et voici ma requête qui ne fonctionne pas :

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
   c.id,
   p."name",
   p.price
FROM
   t_cat c,
   t_prod p,
   t_cat_prod cp
WHERE
   c.id = cp.id_cat AND
   p.id = cp.id_prod
ORDER BY
   p.price LIMIT 1;

Bien évidemment ça ne me renvoie qu'un résultat, et j'ai pensé à mettre des clause GROUP BY sur le "c.id", mais par la force des choses ça m'en ferait également rajouter sur p."name" et p.price.

Alors comme ça me fait bizarre de "grouper par prix", je voudrais savoir si pour vous ça avait quelque chose de choquant et si c'est le cas, m'aider à faire une requête propre..

En espérant bien avoir explicité le problème,

Merci et à bientôt

[skuatamad]

Sur les versions récentes de postgresql il est possible d'utiliser les fonctions de fenêtrage row_number ou rank/dense_rank pour gérer les ex aequo.
ROW_NUMBER, RANK, DENSE_RANK et COUNT

[mister3957]

Merci pour cette réponse, mais je ne vois pas bien comment m'en servir de ces fonctions pour mon problème..

Est-ce que tu peux m'aiguiller s'il te plait ?

Merci par avance,

A bientôt

[skuatamad]

En passant par une sous-requête :

Code :

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

1
2
3
4
5
6
7
8
select id, name, price
  from (SELECT c.id, p.name, p.price,
               rank() over(partition by c.id order by p.price) as rk
          FROM t_cat c
          join t_cat_prod cp on cp.id_cat = c.id
          join t_prod p      on p.id = cp.id_prod
       ) 
 where rk = 1

[ced]

Bonjour,

Voilà qui devrait vous aider :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT categorie, produit
FROM (
	SELECT
	   c.id AS categorie,
	   p."name" AS produit,
	   p.price,
	   DENSE_RANK() OVER(PARTITION BY c.id ORDER BY price DESC) AS rang
	FROM
	   t_cat c
	   INNER JOIN t_cat_prod cp ON c.id = cp.id_cat
	   INNER JOIN t_prod p ON cp.id_prod = p.id
) AS t
WHERE rang = 1;

Attention, je n'ai pas géré les éventuels ex-aequo, si jamais il ne fallait garder qu'un seul produit (il faut alors rajouter le critère pour les départager dans le ORDER BY du OVER)

Sinon, un conseil : prenez l'habitude d'écrire vos jointure selon la norme SQL2 INNER JOIN... ON..., ça les rendra plus lisibles et faciles à maintenir

[EDIT] Grillé en vitesse . Le moins qu'on puisse dire, c'est qu'on est d'accord . Dans le cas présent, l'utilisation de DENSE_RANK ou RANK ne change rien, puisque seul le rang 1 vous intéresse...

[mister3957]

C'est impressionnant ce truc de rank()

Merci beaucoup pour votre aide

[mister3957]

Heu.. en fait non, j'ai des résultats un peu bizarre..

Pour bien expliquer le problème, j'utilise également PostGIS et j'ai importé des rues et des bâtiments. Le but du problème est de retrouver à quelle rue appartient quelle bâtiment.

Parfois dans les données du bâtiment, j'ai le nom de la rue, mais c'est rare, alors je ne l'utilise que pour vérification post traitement.

Décomposition des tables :
- t_buildings (geometry, numero, streetname)
- t_streets (geometry, name)

La requête que j'ai fait est :

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
		t.bid, t.sid
	FROM
	(
		SELECT
			b.id as bid,
			s.id as sid,
			rank() over(partition BY s.id ORDER BY St_Distance(b.geometry, s.geometry)) AS rk
		FROM
			t_buildings b,
			t_streets s
		WHERE
			s.geometry && st_expand(b.geometry, 0.0004)
	) t
	WHERE rk = 1;

Le "s.geometry && st_expand(b.geometry, 0.0004)" permet d'utiliser l'index géographique afin de tester l'intersection avec les rues environnantes plutôt qu'avec chacune des rues de l'import.

En utilisant le t_streets.name (avec une tolérance de 10 dans les écritures selon Levenstein), je me suis aperçu que ça ne renvoyait pas la rue correspondante au bâtiment (sur 1193 adresses, 704 n'ont pas la bonne rue affectée).

Pouvez-vous me dire où est-ce que je me suis planté ?

Merci beaucoup beaucoup pour votre aide

A bientôt

[mister3957]

Non en fait c'est bon, c'est moi qui m'était chié dans différents trucs à l'import

Et j'ai rajouté du levenshtein quand on connait le nom des rues correspondantes dans le "partition by" afin que ça pèse dans la balance en plus de la distance (parfois ça aide quand les points sont proches de deux rues mais plus de la mauvaise que de la bonne)

En tout cas merci beaucoup pour m'avoir fait découvrir ces histoires de rank()

A bientôt