Requête SQL possible?

Version imprimable

Bonjour,
j'aurais aimé votre avis sur la faisabilité d'une requête (quasi sûr que c'est possible, mais je n'y arrive pas).

J'ai une table d'observations avec comme identifiant unique id_poi. Chaque observation est liée à une commune.
id_poi, commune_id_commune
J'ai une table qui permet d'enregistrer des votes sur ces observations associant l'adresse e-mail d'une personne
vote_id, id_poi

J'aimerais pour toutes les communes ne ressortir que le top 5 des observations les plus votées....j'en suis là :
Code:

1 2 3 4 5 6 SELECT count(*) as nb_votes, poi_poi_id, lib_commune FROM `votes` INNER JOIN poi ON poi.id_poi = votes.id_poi INNER JOIN commune ON poi.commune_id_commune = commune.id_commune GROUP BY poi_poi_id ORDER BY commune.lib_commune ASC, nb_votes DESC, poi.id_poi ASC
mais bien entendu, ça me ressort toutes les observations avec le nombre de votes associés et je dois filtrer côté langage de programmation....j'aimerais le faire côté SQL.
je ne trouve pas de clause having to qui ferait ce que je veux...

Merci d'avance pour vos lumières

Gérald

PS : je suis sur MariaDB 10

Bonjour,

As-tu d'essayer d'ajouter une clause LIMIT après la clause ORDER BY?

Code:

1
2
3
4
5
6
7
8
SELECT count(*) as nb_votes, poi_poi_id, lib_commune
FROM `votes`
INNER JOIN poi ON poi.id_poi = votes.id_poi
INNER JOIN commune ON poi.commune_id_commune = commune.id_commune
GROUP BY poi_poi_id
ORDER BY commune.lib_commune ASC, nb_votes DESC, poi.id_poi ASC
LIMIT 5

22/10/2020, 09h08
escartefigue

bonjour

autre solution : utiliser une fonction OLAP et ne conserver que les 5 premiers

Citation:
Envoyé par vanagreg
Bonjour,

As-tu d'essayer d'ajouter une clause LIMIT après la clause ORDER BY?
Code:

1 2 3 4 5 6 7 8 SELECT count(*) as nb_votes, poi_poi_id, lib_commune FROM `votes` INNER JOIN poi ON poi.id_poi = votes.id_poi INNER JOIN commune ON poi.commune_id_commune = commune.id_commune GROUP BY poi_poi_id ORDER BY commune.lib_commune ASC, nb_votes DESC, poi.id_poi ASC LIMIT 5
Encore une fois LIMIT ne fait pas partie du SQL et possède de nombreux effets de bord !

A +

En SQL :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
WITH 
T AS
(
SELECT COUNT(*) AS nb_votes, 
       DENSE_RANK() OVER(ORDER BY COUNT(*) DESC)  AS RANG,
       poi_poi_id, 
       lib_commune
FROM   votes
       INNER JOIN poi 
          ON poi.id_poi = votes.id_poi
       INNER JOIN commune 
          ON poi.commune_id_commune = commune.id_commune
GROUP  BY poi_poi_id
)
SELECT *
FROM   T_EMPLOYEE_EMP
WHERE  RANG <= 5;

A +

22/10/2020, 14h42
escartefigue

Citation:

Envoyé par SQLpro

En SQL

C'est in extenso ce que je proposai plus haut ;)
Autre avantage comparativement à l'utilisation de LIMIT, en cas d'ex aeoquo, LIMIT choisira arbitrairement l'un des ex-aequo, contrairement à la solution basée sur DENSE_RANK() :
22/10/2020, 15h17
vanagreg

Citation:

Envoyé par escartefigue

Autre avantage comparativement à l'utilisation de LIMIT, en cas d'ex aeoquo, LIMIT choisira arbitrairement l'un des ex-aequo, contrairement à la solution basée sur DENSE_RANK() :

Cela dépend si on veut strictement 5 lignes ou pas. D'après la doc MariaDB, je vois que la clause LIMIT n'a pas d'options "WITH TIES"...
22/10/2020, 15h28
SQLpro

2 pièce(s) jointe(s)

Citation:

Envoyé par vanagreg

Cela dépend si on veut strictement 5 lignes ou pas. D'après la doc MariaDB, je vois que la clause LIMIT n'a pas d'options "WITH TIES"...

Non, ça ne dépend pas.... La demande d'information ne répond pas à des données retrouvées arbitrairement !
Lorsque l'on demande les 5 premiers, il peut y avoir 1, 2, 3, 4, 5, 6, 7.... lignes en réponse !

Ce n'est pas la même chose que de demander 5 lignes au hasard s'il y a des ex aequo.....

je rappelle que sur un podium de compétition sportive, il peut y avoir plus de 3 personnes, ou moins de 3 personnes...
Quelques exemples :

4 ex æquo en première place, pas de second ni de 3e

Pièce jointe 581996

et celui là plus classique :

Pièce jointe 581997

A +
22/10/2020, 15h51
vanagreg

Je parlais de la demande initiale. Ce n'est pas précisé s'il faut les ex æquo ou pas. C'est bien d'ailleurs pour cela que la clause TOP propose l'option WITH TIES non (qui n'est pas l'option par défaut d'ailleurs si je ne me trompe pas)?
23/10/2020, 08h48
SQLpro

Citation:

Envoyé par vanagreg

Je parlais de la demande initiale. Ce n'est pas précisé s'il faut les ex æquo ou pas. C'est bien d'ailleurs pour cela que la clause TOP propose l'option WITH TIES non (qui n'est pas l'option par défaut d'ailleurs si je ne me trompe pas)?

Oui, mais elle ne répond toujours pas à la spécification des n premiers... Puisqu'elle ne concerne que les lignes identique à partir de la dernière ligne et non une gestion complète des ex æquo

A +
08/11/2020, 16h04
gerald2545

Merci pour vos retours.
Je teste ça dès ce soir!
Il me faut en effet un nombre identique de résultats pour toutes les catégories