2
votes

Jointure SQL sur colonne générique / jointure sur col1 et col2 si col1 dans la table sinon jointure sur col2

Imaginez que je sois une entreprise qui vend des horoscopes, sur la base des noms de clients. J'ai un tableau avec le nom, le nom de famille et le texte de l'horoscope. Comme je ne peux pas couvrir toutes les combinaisons de noms, je stocke souvent le nom de famille comme NULL, comme valeur fourre-tout.

Customer horoscope DB

sur | fam | horoscope
----------------------
John| Doe  | text1
Jack| Doe  | text3
Lisa| Smith| text5
Carl| Smith| text5

Et une liste de clients

customer DB

sur | fam
---------
John| Doe
Jack| Doe
Lisa| Smith
Carl| Smith

Nous devons maintenant faire correspondre un horoscope à chaque client. Si nous avons une correspondance complète sur le nom et le nom de famille, nous faisons correspondre les deux, mais si nous n'avons pas de correspondance complète, nous faisons simplement correspondre le nom de famille, donc le résultat sera:

Horoscope DB

sur | fam | horoscope
----------------------
John| Doe  | text1
Jane| Doe  | text2
NULL| Doe  | text3
Ike | Smith| text4
NULL| Smith| text5

sql google-bigquery join

0 commentaires

5 Réponses :

1
votes

Voici une méthode:

select . . .
from (select c.*,
             h.* except (sur, fam), -- whatever columns you want
             row_number() over (partition by c.fam
                                order by (case when c.sur = h.sur then 1 else 2 end)
                               ) as seqnum
      from horoscope h join
           customer c
           on c.fam = h.fam
     ) ch
where seqnum = 1;

Fondamentalement, cela rejoint la famille et choisit la "meilleure correspondance" - qui est une correspondance exacte sur le nom de famille.

Vous devez cependant faire attention, car différentes familles peuvent avoir le même nom de famille.

1 commentaires

BigQuery ne produit que 2 lignes.

1
votes

Une autre solution serait d'utiliser l'agrégation conditionnelle. Vous pouvez JOIN sur le nom de famille, puis vérifier si un horoscope existe pour le nom de famille donné; sinon, utilisez le nom de famille NULL.

SELECT
    c.sur,
    c.fam,
    COALESCE(
        MAX(CASE WHEN c.sur = h.sur THEN h.text END),
        MAX(CASE WHEN h.sur IS NULL THEN h.text END) 
    ) horoscope_text
FROM
    customer c
    INNER JOIN horoscope h ON c.fam = h.fam
GROUP BY 
    c.sur,
    c.fam

0 commentaires

1
votes

D'après ce que j'ai compris, voici une façon de le faire

select c.id customer_id, c.sur, c.fam, h.id horoscope_id, h.sur h_sur, 
h.fam h_fam, h.horoscope
FROM customer c join horoscope h
on (c.sur = h.sur and c.fam = h.fam)
or (h.sur is null and c.fam = h.fam and not exists 
      (select 1 from horoscope h1 where h1.sur = c.sur and h1.fam = c.fam)
   )

et le résultat

0 commentaires

1
votes

Vous pouvez adhérer à plusieurs conditions pour couvrir chaque cas:

select c.sur, c.fam, h.horoscope from customer c 
inner join horoscope h
on (c.fam = h.fam and c.sur = h.sur) or 
  (c.fam = h.fam and h.sur is null and not exists(
    select 1 from horoscope 
    where fam = c.fam and sur = c.sur
  )
)

Voir le démo

1 commentaires

J'aime beaucoup votre solution, car elle s'étend facilement aux jointures plus grandes. Bien que cela fonctionne dans le violon, il n'est malheureusement pas pris en charge par BigQuery. La sous-requête EXISTS n'est pas prise en charge dans le prédicat de jointure.

1
votes

Voici pour BigQuery Standard SQL

Row sur     fam     horoscope    
1   John    Doe     text1    
2   Jack    Doe     text3    
3   Lisa    Smith   text5    
4   Carl    Smith   text5

Vous pouvez tester, jouer avec ci-dessus en utilisant des exemples de données de votre exemple comme dans l'exemple ci-dessous

#standardSQL
WITH `project.dataset.horoscope` AS (
  SELECT 'John' sur,'Doe' fam, 'text1' horoscope UNION ALL
  SELECT 'Jane', 'Doe', 'text2' UNION ALL
  SELECT NULL, 'Doe', 'text3' UNION ALL
  SELECT 'Ike', 'Smith', 'text4' UNION ALL
  SELECT NULL, 'Smith', 'text5' 
), `project.dataset.customer` AS (
  SELECT 'John' sur, 'Doe' fam UNION ALL
  SELECT 'Jack', 'Doe' UNION ALL
  SELECT 'Lisa', 'Smith' UNION ALL
  SELECT 'Carl', 'Smith' 
)
SELECT c.sur, c.fam,
  ARRAY_AGG(horoscope ORDER BY h.sur DESC LIMIT 1)[OFFSET(0)] horoscope
FROM `project.dataset.customer` c
JOIN `project.dataset.horoscope` h
ON c.fam = h.fam
AND c.sur = IFNULL(h.sur, c.sur)
GROUP BY c.sur, c.fam

avec résultat

#standardSQL
SELECT c.sur, c.fam,
  ARRAY_AGG(horoscope ORDER BY h.sur DESC LIMIT 1)[OFFSET(0)] horoscope
FROM `project.dataset.customer` c
JOIN `project.dataset.horoscope` h
ON c.fam = h.fam
AND c.sur = IFNULL(h.sur, c.sur)
GROUP BY c.sur, c.fam

0 commentaires