Clausola NEAREST BY

Si applica a: Databricks Runtime 18.3 e versioni successive

Estende un JOIN oggetto con classificazione top-k su una distanza personalizzata o un'espressione di somiglianza. Per ogni riga della query (a sinistra) table_reference, trova fino alle num_results prime righe corrispondenti della tabella di destinazione (a destra) in base a ranking_expression, restituendole come righe concatenate.

ranking_expression può essere qualsiasi espressione scalare ordinabile che assegna punteggi a una coppia di righe dalle due tabelle, ad esempio vector_cosine_similarity, vector_l2_distance, vector_inner_product o un'espressione composita che combina diverse funzioni.

Syntax

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

Parameters

• target_table_reference

  Tabella di destinazione da cercare. Può essere una tabella, una sottoquery o un CTE.

• { INNER | LEFT [ OUTER ] }

  Optional. Tipo di join. Il valore predefinito è INNER.

  • INNER elimina le righe di query che non hanno candidati corrispondenti.
  • LEFT OUTER restituisce ogni riga di query. Le colonne lato destinazione sono NULL quando non esistono candidati, ad esempio quando la tabella di destinazione è vuota o ogni candidato è NULL. Se esistono meno candidati num_results per una riga di query, vengono restituiti solo i candidati disponibili.

  Altri tipi di join (RIGHT, FULL, ANTISEMI, CROSS, , ) NATURALgenerano NEAREST_BY_JOIN.UNSUPPORTED_JOIN_TYPE.

• { APPROX | EXACT }

  Controlla il contratto del set di risultati.

  • EXACT restituisce le righe top-k esatte in ranking_expression.
  • APPROX restituisce un set top-k che approssima la classificazione esatta. L'ottimizzatore può usare strategie di ricerca più veloci e approssimative anziché valutare ogni candidato.

• NEAREST [ num_results ]

  Valore letterale integer positivo facoltativo. Di default è 1. Deve essere compreso nell'intervallo [1, 100000]. Se la tabella di destinazione contiene meno righe corrispondenti a num_results, vengono restituite solo le righe disponibili.

  I valori esterni all'intervallo generano NEAREST_BY_JOIN.NUM_RESULTS_OUT_OF_RANGE.

• BY DISTANCE | SOMIGLIANZA

  Imposta l'ordinamento di ranking_expression.

  • DISTANCE classifica le righe in base al valore più piccolo (più vicino = distanza più bassa).
  • SIMILARITY classifica prima le righe in base al valore più grande (più vicino = somiglianza più alta).

• ranking_expression

  Espressione scalare che può fare riferimento a colonne di entrambe le tabelle.

  Le scelte comuni sono:

  • funzioni di somiglianza come vector_cosine_similarity e vector_inner_product,
  • funzioni di distanza come vector_l2_distance,
  • distanze numeriche come la distanza di Manhattan: vector_norm(zip_with(a.col, b.col, (x, y) -> x - y), 1.0f).

  Se questa espressione restituisce un tipo di dati che non supporta l'ordinamento, ad esempio MAP, Azure Databricks genera DATATYPE_MISMATCH. INVALID_ORDERING_TYPE.

Note

Asimmetria

NEAREST BY non è commutativo. Il lato query ancora il risultato: ogni riga di query produce fino alle num_results righe di output:

• Quando 100 righe della tabella users vengono unite a 1.000 righe dalla tabella products con NEAREST 5, il join restituisce fino a 500 righe.
• Se si passano i due lati del join a join products con users, vengono restituite fino a 5.000 righe.

Lo scambio dei due lati pone una domanda diversa, quindi il risultato differisce anche per INNER JOIN.

Streaming

NEAREST BY non è supportato nei dataframe di streaming o nei set di dati. Le query sulle origini di streaming generano NEAREST_BY_JOIN.STREAMING_NOT_SUPPORTED.

Incorporamento di input

Quando si usano funzioni di assegnazione dei punteggi vettoriali, entrambi gli argomenti vettoriali devono avere ARRAY<FLOAT> la stessa dimensionalità. Vedere vector_cosine_similarity la funzione per le regole di gestione e NULL tipo.

Per calcolare incorporamenti da valori stringa, usare ai_query con un modello di incorporamento ospitato da Databricks, ad esempio databricks-gte-large-en.

Condizioni di errore comuni

• 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

Examples

Negli esempi seguenti vengono usate queste tabelle. Gli incorporamenti vengono visualizzati come vettori tridimensionali per brevità; in pratica sono dimensionali superiori e calcolati da un modello di incorporamento.

> 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

Articoli correlati

• riferimento alla tabella
• JOIN
• SELECT (selezione secondaria)
• vector_cosine_similarity Funzione
• vector_l2_distance Funzione
• vector_inner_product Funzione
• vector_search Funzione