0
votes

La jointure gauche sera nulle dans MySQL

Je recherche un moyen de créer une fonction de pertinence relative lorsque je recherche des valeurs dans deux (ou plus) tables différentes. J'ai donc des tableaux comme celui-ci

id relevance
 1 0.1
 2 0.15
 3 2.1
 6 4.1
 8 0.1
11 0.13

J'ai besoin d'obtenir une fonction de pertinence à partir de ces deux tableaux en les fusionnant. Les mêmes identifiants de ligne auront une pertinence 10x et les lignes avec les identifiants d'une seule table auront une pertinence de "poids".

Voici un tableau intermédiaire que j'ai besoin d'obtenir (et ma question est COMMENT créer un tel tableau ):

id1  weight1  id2  weight2
1    0.1      null null
2    0.15     null null
3    0.12     3    0.09
6    0.21     6    0.2
null null     8    0.1
null null     11   0.13

En utilisant cette table, je peux calculer la pertinence tout ce dont j'ai besoin, mais le problème est de créer une telle table à partir de ces deux. Pourriez-vous m'aider?

J'ai essayé avec LEFT JOIN, STRAIGHT_JOIN, LEFT OUTER JOIN, mais ils donnent des résultats très différents.

Edit: Si ça compte, j'envisage actuellement la table finale pour ressembler à quelque chose comme ceci:

table1:

id weight
1  0.1
2  0.15
3  0.12
6  0.21

table2:

id weight
3  0.09
6  0.2
8  0.1
11 0.13

mysql left-join relevance

14 commentaires

Étant donné que LEFT JOIN et LEFT OUTER JOIN sont exactement la même chose, cette assertion semble hautement invraisemblable

Vous avez besoin d'une FULL OUTER JOIN, que MySQL ne prend pas en charge, mais qui peut être émulée - bien qu'il faille s'interroger sur la sagesse d'avoir deux tables qui stockent apparemment le même genre de chose.

@Strawberry Ces tables sont en fait des sous-requêtes, oui, elles sont tirées de la même table, mais avec une partie "where" très différente. Comment puis-je émuler FULL OUTER JOIN?

Par curiosité, à quoi ressemblerait le résultat final?

Je pense comme ça e-wm.org/i/E20190222-150905-001. png cependant la fonction de pertinence finale n'a pas encore été créée.

Dans ce cas, je soupçonne que cela peut être massivement simplifié

@Strawberry Pourriez-vous me donner votre idée de comment simplifier cela?

Vous devez vraiment repenser votre schéma. Plusieurs tables stockant le même genre de chose est une mauvaise idée.

@Strowberry J'ai des mots différents stockés dans une table et une autre table stocke des "vecteurs" - relativité entre les mots et les documents. Tableau1 (et Tableau 2) est en fait un ensemble de documents où un mot spécifique existe. Ainsi, la table1 et la table2 sont différentes pièces de la même table (documents), mais sont liées à différents mots. Par exemple, mot "chien" peut être dans les documents 1,2,3,6 et mot "bon" dans les docs 3,6,8,11. J'ai besoin d'un ensemble de docs où j'ai les deux mots "bon chien" et qu'un seul mot apparaît (seulement "bon" ou seulement "chien").

Je peux utiliser des requêtes plus longues "Le renard rapide saute par-dessus le chien paresseux" et dans ce cas j'aurai beaucoup plus de morceaux de table de documents.

Cela ressemble à une approche terrible - mais bon, c'est votre enterrement.

Si le problème est littéralement tel que vous le décrivez, vous devriez probablement effectuer des recherches FULLTEXT sur les données. Sinon, vous devez au moins stocker tous les mots et références de document dans un seul tableau.

@ Fraise oui, j'ai une table de mots et une autre table de vecteurs - deux tables sont les mêmes pour tous les documents.

Est-ce que cela répond à votre question? Comment faire une FULL OUTER JOIN dans MySQL?

4 Réponses :

2
votes

Vous pouvez utiliser FULL OUTER JOIN pour cela, par exemple :

SELECT t1.id AS id1, t1.weight AS weight1, t2.id AS id2, t2.weight AS weight2
FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id
UNION
SELECT t1.id AS id1, t1.weight AS weight1, t2.id AS id2, t2.weight AS weight2
FROM table1 t1 RIGHT JOIN table2 t2 ON t1.id = t2.id;

4 commentaires

FWIW, je préférerais écrire cela de cette façon ... SELECT t1.id id1, t1.weight weight1, t2.id id2, t2.weight weight2 FROM table1 t1 LEFT JOIN table2 t2 ON t2.id = t1 .id UNION SELECT t1.id id1, t1.weight weight1, t2.id id2, t2.weight weight2 FROM table2 t2 LEFT JOIN table1 t1 ON t1.id = t2.id;

@ Fraise ah d'accord. S'agit-il uniquement de la lisibilité ou des performances?

Simplement de la lisibilité - conceptuellement, je trouve un JOINT GAUCHE plus intuitif

Ok j'ai essayé ça, ça marche, mais je pense que j'aurai besoin de beaucoup de telles sous-requêtes pour fusionner par union, car je peux avoir 3 ou même plus de tables (jusqu'à 8) et ensuite je dois les combiner toutes les unes avec les autres, droit? C'est 2 ^ 8 = 64 sous-requêtes différentes ... wow.

1
votes

SELECT id
     , SUM(weight) * CASE WHEN COUNT(*)=1 THEN 1 ELSE 10 END relevance 
  FROM
     ( SELECT id
            , weight 
         FROM table1 
        UNION 
          ALL 
       SELECT id
            , weight 
         FROM table2
     ) x
 GROUP 
    BY id;
+----+-----------+
| id | relevance |
+----+-----------+
|  1 |      0.10 |
|  2 |      0.15 |
|  3 |      2.10 |
|  6 |      4.10 |
|  8 |      0.10 |
| 11 |      0.13 |
+----+-----------+

0 commentaires

0
votes

Nous pouvons utiliser des procédures stockées et des tables temporaires pour obtenir la solution

CALL GetReleavance()

Dans la procédure de création d'une table temporaire avec tous les identifiants et poids de différentes tables et obtenir la somme (poids) en fonction de l'identifiant.

Appelez la procédure stockée en utilisant

CREATE PROCEDURE GetReleavance()
BEGIN
Create TEMPORARY TABLE tmpList ENGINE=MEMORY
SELECT id, weight from t1
union all 
SELECT id, weight from t2
union all 
SELECT id, weight from t3;

select id, sum(weight)* POW(10,COUNT(1)-1) as relevance 
from tmpList 
group by id;

DROP TEMPORARY TABLE IF EXISTS tmpList;

END

Vous pouvez faire Union tout pour le nombre de table que vous voulez et cela n'aura pas d'impact majeur sur les performances. p>

4 commentaires

D'ACCORD. Mais pourquoi voudrions-nous faire ça!?!

Les tables temporaires peuvent avoir un index optimisé. Créer un index tmpId sur tmpList (id) sur la procédure après la création de la table temporaire réduira le temps de groupe par classe

Le lien stackoverflow. com / questions / 116423 /… en explique plus sur une table temporaire

D'ACCORD. Et cela nous aiderait-il du tout? J'ai du mal à voir comment :-(

1
votes

Voici quelques exemples:

id | relevance
-: | --------:
 1 |      0.10
 2 |      0.15
 3 |      0.21
 6 |      0.41
 8 |      0.10
11 |      0.13

select id, sum(weight) as relevance
from
(
 select id, weight from Table1
 union all 
 select id, weight from Table2
) q
group by id
order by id;

id | relevance
-: | --------:
 1 |      0.10
 2 |      0.15
 3 |      0.21
 6 |      0.41
 8 |      0.10
11 |      0.13

select 
id12.id as id,
coalesce(t1.weight,0) + coalesce(t2.weight,0) as relevance
from (select id from Table1 union select id from Table2) id12
left join Table1 t1 on t1.id = id12.id
left join Table2 t2 on t2.id = id12.id
order by id12.id;

id |  id1 | weight1 |  id2 | weight2
-: | ---: | ------: | ---: | ------:
 1 |    1 |    0.10 | null |    null
 2 |    2 |    0.15 | null |    null
 3 |    3 |    0.12 |    3 |    0.09
 6 |    6 |    0.21 |    6 |    0.20
 8 | null |    null |    8 |    0.10
11 | null |    null |   11 |    0.13

select 
id12.id as id,
t1.id as id1,
t1.weight as weight1,
t2.id as id2,
t2.weight as weight2
from (select id from Table1 union select id from Table2) id12
left join Table1 t1 on t1.id = id12.id
left join Table2 t2 on t2.id = id12.id
;

insert into Table2 (id, weight) values
 (3,  0.09)
,(6,  0.20)
,(8,  0.10)
,(11, 0.13)
;

insert into Table1 (id, weight) values
 (1, 0.10)
,(2, 0.15)
,(3, 0.12)
,(6, 0.21)
;

create table Table2 (
 id int primary key not null,
 weight decimal(10,2) not null default 0
);

create table Table1 (
 id int primary key not null,
 weight decimal(10,2) not null default 0
);

db fiddle ici em>

Les deuxième et troisième requêtes renvoient le même résultat.
Quel est le meilleur?
Cela dépendra du nombre de champs supplémentaires et / ou de calculs supplémentaires nécessaires.

3 commentaires

Merci. Btw, notez qu'une fois UNION est utilisé et une autre fois UNION ALL. Il est important de comprendre la différence. UNION ne renverra que des enregistrements uniques et supprimera les doublons, tandis que UNION ALL collera simplement les résultats des requêtes ensemble.

Je n'ai pas du tout besoin d'UNION. La table initiale pour table1 et table2 est la même table contient TOUS les documents, tandis que table1 et table2 ne sont que des sous-requêtes contenant une ligne sélectionnée de la même table. Donc, mes solutions ici sont de remplacer une sous-requête par UNION avec une sous-requête (sélectionner l'ID dans la documentation). Cela fonctionne bien.

Oh comme ça. Ok, on dirait que vous savez ce que vous faites. :)