Discussion :

[Invité]

Bonjour tout le monde

Je cherche a faire un peu d'analyse de donnees sur mes tables, et je commence a tomber sur des cas assez recurrents qui me posent probleme.

Admettons que j'ai 3 tables.
Une table "community" avec 1 colonne "id" et d'autres attributs
Une table "user", de meme.
Une table "community_user" qui me modelise la relation ManyToMany entre les deux.

Si je veux par exemple le nombre de moins de 30 ans par communaute, pas de soucis

Code :

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

select community_id, count(user_id) from community_user, user where id=user_id and age<30 group by community_id

Par contre, comment les choses se passent si je veux calculer plusieurs aggregats avec des filtres differents. Disons renvoyer le nombre de moins de 18 ans et de plus de 60 ans dans la meme requete.

J'avais commence quelque chose avec des clauses select imbriquees dans mon from, mais je me suis vite rendu compte que mon extraction commencait apres que ces select aient ete faits en integralite. Si je rajoute un limit 10 a la fin par exemple, je dois quand meme patienter jusqu'a la fin des select.

Ainsi,

Code :

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

1
2
3
4
select community_id, c1, c2 from
(select community_id, count(user_id) as "c1" from community_user group by community_id) as "q1",
(select community_id, count(user_id) as "c2" from community_user, user where id=user_id and age<30 group by community_id) as "q2"
where q1.community_id=q2.community_id

semble vraiment inefficace !

Y a-t'il une meilleure methode pour faire cela ?
Le SELECT CASE n'est pas satisfaisant, parce que les filtres que je veux appliquer ne sont pas forcement disjoints.
Les Window Functions (je suis sous Postgresql) m'ont ete recommandees, mais je ne vois pas du tout comment elles peuvent m'aider...
Les UNION ne me semblent pas tres "propres" dans ce cas, compare aux jointures (mais vu ce que j'y connais...)

[Invité]

Je viens de penser a ca

Code :

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

1
2
3
4
5
 
SELECT id,
(SELECT COUNT(id) FROM user, community_user WHERE id=user_id AND community_id=community.id),
(SELECT COUNT(id) FROM user, community_user WHERE id=user_id AND age<30 AND community_id=community.id)
FROM community

Mais je soupconne que c'est aussi lent que mon autre solution... Qu'en pensez-vous ?

[CinePhil]

Il faut utiliser CASE et additionner ceux qui répondent à la condition au lieu de les compter.

Au passage, les jointures s'écrivent depuis 20 ans avec l'opérateur JOIN ; il serait temps de s'y mettre !

Et indente et aère ton code, il sera plus facile à lire et à débugguer.

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
SELECT cu.community_id, 
	SUM (
		CASE 
			WHEN u.age < 30 THEN 1
			ELSE 0
		END
	) AS moins_de_30_ans,
	SUM (
		CASE 
			WHEN u.age > 60 THEN 1
			ELSE 0
		END
	) AS plus_de_60_ans
FROM community_user cu
INNER JOIN user u ON u.id = cu.user_id 
GROUP BY cu.community_id

[Invité]

Merci pour la piste. Je vais lancer un test en taille reelle sur mes tables pour voir comment les differentes approches se comportent. A ce rythme la, ca prendra tout le week-end ^^

En ce qui concerne le WHERE, c'est une mauvaise habitude que j'ai prise de regarder les requetes genererees par mon ORM (SQLAlchemy) qui au lieu de INNER JOIN utilise des WHERE partout.
Mais effectivement, c'est plus lisible de separer les clauses de filtrage de la jointure, j'en conviens

[CinePhil]

Ah ces ORM ! Quelle plaie !

[Invité]

Etant plutot debutant en SQL, j'etais parti du principe que l'ORM savait un peu mieux que moi ce qu'il faisait.
Ensuite je me suis rendu compte que le WHERE et INNER JOIN etaient effectivement interpretes de la meme facon par le moteur, donc je croyais que les formulations etaient juste equivalentes. Je ne savais pas qu'une ecriture etait plus recommandee que l'autre.