Clause NEAREST BY

S’applique à : check marqué oui Databricks Runtime 18.3 et versions ultérieures

Étend un JOIN classement avec top-k sur une distance personnalisée ou une expression de similarité. Pour chaque ligne de la requête (à gauche) table_reference, elle trouve jusqu’aux lignes correspondantes supérieures num_results de la table cible (droite) en fonction du ranking_expression, en les retournant comme des lignes concaténées.

ranking_expression peut être n’importe quelle expression scalaire triable qui note une paire de lignes à partir des deux tables , par exemple vector_cosine_similarity, vector_l2_distance, vector_inner_product ou une expression composite qui combine plusieurs fonctions.

Syntax

{ INNER | LEFT [ OUTER ] } JOIN target_table_reference
  { APPROX | EXACT } NEAREST [ num_results ]
  BY { DISTANCE | SIMILARITY } ranking_expression

Parameters

target_table_reference

Table cible à rechercher. Il peut s’agir d’une table, d’une sous-requête ou DTE.
{ INNER | LEFT [ OUTER ] }

Optional. Type de jointure. La valeur par défaut est INNER.
- INNER supprime les lignes de requête qui n’ont aucun candidat correspondant.
- LEFT OUTER retourne chaque ligne de requête. Les colonnes côté cible sont NULL lorsqu’aucun candidat n’existe , par exemple, lorsque la table cible est vide ou que chaque candidat est NULL. Si moins de num_results candidats existent pour une ligne de requête, seuls les candidats disponibles sont renvoyés.
Autres types de jointure (RIGHT, , FULLSEMI, ANTICROSS, NATURAL) raise NEAREST_BY_JOIN.UNSUPPORTED_JOIN_TYPE.
{ APPROX | EXACT }

Contrôle le contrat du jeu de résultats.
- EXACT retourne les lignes supérieures exactes sous ranking_expression.
- APPROX retourne un jeu top-k qui correspond approximativement au classement exact. L’optimiseur peut utiliser des stratégies de recherche plus rapides et approximatives au lieu d’évaluer chaque candidat.
NEAREST [ num_results ]

Littéral entier positif facultatif. La valeur par défaut est 1. Doit être dans la plage [1, 100000]. Si la table cible a moins de lignes correspondantes que num_results, seules les lignes disponibles sont retournées.

Valeurs en dehors de la plage levée NEAREST_BY_JOIN.NUM_RESULTS_OUT_OF_RANGE.
BY DISTANCE | SIMILITUDE

Définit l’ordre de ranking_expression.
- DISTANCE classe les lignes par valeur la plus petite en premier (plus proche = distance la plus faible).
- SIMILARITY classe les lignes par valeur la plus élevée en premier (proche = similarité la plus élevée).
ranking_expression

Expression scalaire qui peut référencer des colonnes à partir des deux tables.

Les choix courants sont les suivants :
- fonctions de similarité comme vector_cosine_similarity et vector_inner_product,
- fonctions de distance comme vector_l2_distance,
- distances numériques comme la distance de Manhattan : vector_norm(zip_with(a.col, b.col, (x, y) -> x - y), 1.0f).
Si cette expression retourne un type de données qui ne prend pas en charge l’ordre, tel que MAP, Azure Databricks déclenche DATATYPE_MISMATCH. INVALID_ORDERING_TYPE.

Remarques

Asymétrie

NEAREST BY n’est pas commutatif. Le côté requête ancre le résultat : chaque ligne de requête produit jusqu’aux num_results lignes de sortie :

Lorsque 100 lignes de la jointure de table users avec 1 000 lignes de la table products avec NEAREST 5, la jointure retourne jusqu’à 500 lignes.
Si vous changez les deux côtés de la jointure à joindre productsusers, elle retourne jusqu’à 5 000 lignes.

L’échange des deux côtés pose une question différente, donc le résultat diffère même pour INNER JOIN.

Diffusion en continu

NEAREST BY n’est pas pris en charge sur les DataFrames de streaming ou les jeux de données. Les requêtes sur les sources de diffusion en continu déclenchent NEAREST_BY_JOIN.STREAMING_NOT_SUPPORTED.

Incorporation d’entrées

Lorsque vous utilisez des fonctions de scoring de vecteur, les deux arguments vectoriels doivent être ARRAY<FLOAT> avec la même dimensionnalité. Consultez vector_cosine_similarity la fonction pour connaître les règles de type et NULL de gestion.

Pour calculer des incorporations à partir de valeurs de chaîne, utilisez ai_query avec un modèle d’incorporation hébergé par Databricks, tel que databricks-gte-large-en.

Conditions d’erreur courantes

DATATYPE_MISMATCH. INVALID_ORDERING_TYPE
NEAREST_BY_JOIN.NUM_RESULTS_OUT_OF_RANGE
NEAREST_BY_JOIN.STREAMING_NOT_SUPPORTED
NEAREST_BY_JOIN.UNSUPPORTED_JOIN_TYPE

Exemples

Les exemples suivants utilisent ces tables. Les incorporations sont affichées sous forme de vecteurs 3dimensionnels pour la concision ; dans la pratique, ils sont plus dimensionnels et calculés par un modèle d’incorporation.

> CREATE TEMP VIEW users(user_id, name, embedding) AS
    VALUES
      (1, 'Alice', ARRAY(1.0f, 0.0f, 0.0f)),
      (2, 'Bob',   ARRAY(0.0f, 1.0f, 0.0f)),
      (3, 'Carol', ARRAY(0.0f, 0.0f, 0.0f));

> CREATE TEMP VIEW products(product_id, name, price, country, embedding) AS
    VALUES
      ('P1', 'Trail running shoes', 120, 'EU', ARRAY(0.9f, 0.1f, 0.1f)),
      ('P2', 'Hiking boots',        180, 'EU', ARRAY(0.8f, 0.2f, 0.0f)),
      ('P3', 'Office shoes',         95, 'US', ARRAY(0.1f, 0.9f, 0.1f)),
      ('P4', 'Sandals',              45, 'US', ARRAY(0.0f, 0.8f, 0.2f)),
      ('P5', 'Running shoes',       110, 'EU', ARRAY(0.5f, 0.5f, 0.0f));

-- Ad-hoc vector search with an explicit query vector.
> SELECT t.product_id, t.name
    FROM (SELECT ARRAY(1.0f, 0.0f, 0.0f) AS embedding) q
    INNER JOIN products t
      APPROX NEAREST 3 BY SIMILARITY vector_cosine_similarity(q.embedding, t.embedding);
 product_id  name
 ----------  -------------------
 P1          Trail running shoes
 P2          Hiking boots
 P5          Running shoes

-- Batch recommendations: for every user, return the 2 nearest products.
> SELECT q.user_id, q.name, t.product_id, t.name AS product
    FROM users q
    INNER JOIN products t
      APPROX NEAREST 2 BY SIMILARITY vector_cosine_similarity(q.embedding, t.embedding);
 user_id  name   product_id  product
 -------  -----  ----------  -------------------
 1        Alice  P1          Trail running shoes
 1        Alice  P2          Hiking boots
 2        Bob    P3          Office shoes
 2        Bob    P4          Sandals

-- Pre-filter the target table via a subquery (EU products only).
> SELECT q.user_id, q.name, t.product_id, t.name AS product, t.price
    FROM users q
    INNER JOIN (SELECT * FROM products WHERE country = 'EU') AS t
      APPROX NEAREST 2 BY SIMILARITY vector_cosine_similarity(q.embedding, t.embedding);
 user_id  name   product_id  product              price
 -------  -----  ----------  -------------------  -----
 1        Alice  P1          Trail running shoes  120
 1        Alice  P2          Hiking boots         180
 2        Bob    P5          Running shoes        110
 2        Bob    P2          Hiking boots         180

-- LEFT OUTER returns every query row. Carol's embedding has zero magnitude,
-- so vector_cosine_similarity returns NULL for all comparisons and her row
-- is preserved with NULL target columns.
> SELECT q.user_id, q.name, t.product_id, t.name AS product
    FROM users q
    LEFT OUTER JOIN products t
      APPROX NEAREST 2 BY SIMILARITY vector_cosine_similarity(q.embedding, t.embedding);
 user_id  name   product_id  product
 -------  -----  ----------  -------------------
 1        Alice  P1          Trail running shoes
 1        Alice  P2          Hiking boots
 2        Bob    P3          Office shoes
 2        Bob    P4          Sandals
 3        Carol  NULL        NULL

-- EXACT returns the exact top-k under the ranking expression.
> SELECT t.product_id, t.name
    FROM (SELECT ARRAY(1.0f, 0.0f, 0.0f) AS embedding) q
    INNER JOIN products t
      EXACT NEAREST 3 BY DISTANCE vector_l2_distance(q.embedding, t.embedding);
 product_id  name
 ----------  -------------------
 P1          Trail running shoes
 P2          Hiking boots
 P5          Running shoes

Commentaires

Cette page a-t-elle été utile ?

Last updated on 2026-06-01