Probleme syntaxe pour intersection [Résolu]

[Are-no]

Voilà le problème :

Soit une table avec des id_user et des id_lieu

id_user | id_lieu
1 | 1
1 | 2
2 | 1
2 | 2
2 | 3
3 | 1
4 | 1
4 | 3

Je veut tout les Id_user qui fréquente a la fois les Id_lieu '1' et '2'

Je me doute qu'il faut faire un intersection chose que je sais faire en SQL avec INTERSECT mais malheureusement c'est pas dispo en MySQL donc je me suis rabattu sur une syntaxe avec IN mais impossible de trouvé la bonne syntaxe.

Une idée ?

[Antoun]

Code :

1
2
3
4
5
6
SELECT id_user
FROM ta_table
WHERE id_lieu = 1 AND id_user IN (
  SELECT id_user
  FROM ta_table
  WHERE id_lieu = 2);

autre possibilité, avec une jointure :

Code :

1
2
3
4
5
6
7
8
SELECT T1.id_user
FROM ta_table T1
  INNER JOIN (  
	  SELECT id_user
    FROM ta_table
    WHERE id_lieu = 2) AS T2
		ON T1.id_user = T2.id_user
WHERE id_lieu = 1 ;

Dans les deux cas, il faut des sous-requêtes, donc au moins la version 4.1.

[Cybher]

salut,

je ne sais pas au niveau performance par rapport aux requetes d'Antoun, mais je ferais quelque chose dans ce genre :

Code :

1
2
3
SELECT id_user FROM tatable 
WHERE id_lieu IN (1,2) GROUP BY id_user 
HAVING count(DISTINCT(id_lieu))=2

[Antoun]

Envoyé par Cybher

salut,

je ne sais pas au niveau performance par rapport aux requetes d'Antoun, mais je ferais quelque chose dans ce genre : (...)

Pour les perfs, je ne sais pas trop, mais ça a l'avantage d'être compatible avec les versions 4.0 et antérieures !

[sibawe]

La solution proposée marche bien, mais sa réalisation peut prendre des plombes .

Mettons que tu aies 2 listes de personnes: list_A and list_B. Tu veux savoir quelles sont les personnes qui sont dans A et pas dans B. D'un point de vue ensembliste, tu cherches à faire "A moins B".

Solution facile, mais qui prend du temps:

select person from list_A where person not in (select person from list_B);

Dans mon cas, 2 listes de quelques milliers de lignes suffisent à mettre mon serveur 1GB à genoux pendant qqes minutes.


Le temps de calcul est divisé par 1000 en utilisant l'algorithme suivant:

1/ crée une table temporaire où tu dumpes les 2 listes
2/ utilise les fonctions "group" de mysql pour séparer les éléments de l'intersection des éléments hors-intersection

Exemple:

Code :

1
2
3
4
 
CREATE TEMPORARY TABLE dump (person varchar(30), list_source char); /* crée la table temporaire*/
INSERT INTO dump SELECT person, 'A' FROM list_A; /* dump les éléments de list_A dans la table et marque les par la lettre "A"*/
INSERT INTO dump SELECT person, 'B' FROM list_B; /* dump les éléments de list_B dans la table et marque les par la lettre "B"*/

-> "A moins B":

Code :

1
2
3
SELECT person 
FROM (SELECT * FROM dump GROUP BY person HAVING count(*)='1') AS T  /* Prends les éléments qui ne sont PAS dans l'intersection ( count='1' ) */
WHERE list_source='A'; /* Prends uniquement les éléments marqués "A" */

-> "B moins A":

Code :

1
2
3
SELECT person 
FROM (SELECT * FROM dump GROUP BY person HAVING count(*)='1') AS T  /* Prends les éléments qui ne sont PAS dans l'intersection ( count='1' ) */
WHERE list_source='B'; /* Prends uniquement les éléments marqués "B" */

-> "intersection de A et B"

Code :

SELECT person FROM dump GROUP BY person HAVING count(*)='2';

Voilà, ... en attendant que MySQL intégre les opérateurs ensemblistes.

Bon courage!

Sibawe

[Antoun]

En fait, le problème vient du IN qui est super mal optimisé... Il doit être possible de faire qqch avec EXISTS qui soit efficace.

[urbansurfer]

Je vais devoir réaliser des intersection multiples. pour ce faire j'hésite entre 2 approches :

1/ tout mettre dans une seule table et je lance ce genre de requête :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT id_user
FROM ta_table
WHERE id_lieu = 1 AND id_user IN (
  SELECT id_user
  FROM ta_table
  WHERE id_lieu = 2)
AND id_user IN (
  SELECT id_user
  FROM ta_table
  WHERE id_lieu = 3)
AND id_user IN (
  SELECT id_user
  FROM ta_table
  WHERE id_lieu = 4);

2/ je créé des tables pour chaque lieux et je met les user id et je fais une intersection entre les tables pour déterminer les user_id qui sont communs à ces tables.

Sachant qu'Il y aura plusieurs dizinaines de millier de "users" et des centaines de lieux.

- Y a t-il un intérêt en terme de perf a utiliser la solution 2 ?
- si oui, quelle genre de requête dois-je réaliser ?
- si non, comment optimiser la solution 1 ?

[ced]

Bonjour,

S'il y a plusieurs centaines de lieux, il ne faut surtout pas créer une table par lieu... A chaque fois qu'il faudra ajouter un lieu, il faudra une nouvelle table, ce qui est ingérable !
Pour "optimiser" la solution 1, il faut prendre la dernière requête proposée :

Code :

SELECT id_user FROM ta_table WHERE id_lieu IN (1, 2, 3, 4) GROUP BY id_user HAVING count(*)= 4;

ced

[urbansurfer]

Merci pour cette réponse