Cláusula NEAREST BY

Se aplica a: Databricks Runtime 18.3 y versiones posteriores

Extiende un JOIN objeto con clasificación top-k en una distancia personalizada o expresión de similitud. Para cada fila de la consulta (izquierda) table_reference, busca hasta las num_results primeras filas coincidentes de la tabla de destino (derecha) en ranking_expressionfunción de , devolviéndolos como filas concatenadas.

ranking_expression puede ser cualquier expresión escalar ordenada que puntee un par de filas de las dos tablas( por ejemplo , vector_cosine_similarity, vector_l2_distance, vector_inner_product o una expresión compuesta que combina varias funciones.

Syntax

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

Parameters

• target_table_reference

  Tabla de destino que se va a buscar. Puede ser una tabla, subconsulta o CTE.

• { INNER | LEFT [ OUTER ] }

  Opcional. Tipo de combinación. El valor predeterminado es INNER.

  • INNER quita las filas de consulta que no tienen candidatos coincidentes.
  • LEFT OUTER devuelve todas las filas de consulta. Las columnas del lado de destino son NULL cuando no existen candidatos; por ejemplo, cuando la tabla de destino está vacía o cada candidato es NULL. Si existen menos num_results candidatos para una fila de consulta, solo se devuelven los candidatos disponibles.

  Otros tipos de combinación (RIGHT, FULL, SEMI, ANTI, CROSS, NATURAL) generan NEAREST_BY_JOIN.UNSUPPORTED_JOIN_TYPE.

• { APPROX | EXACT }

  Controla el contrato del conjunto de resultados.

  • EXACT devuelve las filas top-k exactas debajo ranking_expressionde .
  • APPROX devuelve un conjunto top-k que aproxima la clasificación exacta. El optimizador puede usar estrategias de búsqueda más rápidas y aproximadas en lugar de evaluar cada candidato.

• MÁS CERCANO [ num_results ]

  Literal entero positivo opcional. Tiene como valor predeterminado 1. Debe estar en el intervalo [1, 100000]. Si la tabla de destino tiene menos filas coincidentes que num_results, solo se devuelven las filas disponibles.

  Los valores fuera del intervalo generan NEAREST_BY_JOIN.NUM_RESULTS_OUT_OF_RANGE.

• BY DISTANCE | SIMILITUD

  Establece el orden de ranking_expression.

  • DISTANCE clasifica las filas por valor más pequeño primero (más cercano = distancia más baja).
  • SIMILARITY clasifica las filas por valor más grande primero (más cercano = similitud más alta).

• ranking_expression

  Expresión escalar que puede hacer referencia a columnas de ambas tablas.

  Las opciones comunes son:

  • funciones de similitud como vector_cosine_similarity y vector_inner_product,
  • funciones de distancia como vector_l2_distance,
  • distancias numéricas como la distancia de Manhattan: vector_norm(zip_with(a.col, b.col, (x, y) -> x - y), 1.0f).

  Si esta expresión devuelve un tipo de datos que no admite la ordenación, como MAP, Azure Databricks genera DATATYPE_MISMATCH. INVALID_ORDERING_TYPE.

Notas

Asimetría

NEAREST BY no es conmutante. El lado de la consulta delimita el resultado: cada fila de consulta genera hasta num_results las filas de salida:

• Cuando 100 filas de la combinación de tabla users con 1000 filas de la tabla products con NEAREST 5, la combinación devuelve hasta 500 filas.
• Si cambia los dos lados de la combinación para unirse products a users, devuelve hasta 5000 filas.

Intercambiar los dos lados formula una pregunta diferente, por lo que el resultado difiere incluso para INNER JOIN.

Streaming

NEAREST BY no se admite en secuencias dataframes o conjuntos de datos. Las consultas en orígenes de streaming generan NEAREST_BY_JOIN.STREAMING_NOT_SUPPORTED.

Inserción de entradas

Al usar funciones de puntuación de vectores, ambos argumentos vectoriales deben tener ARRAY<FLOAT> la misma dimensionalidad. Consulte vector_cosine_similarity función para conocer el tipo y NULL las reglas de control.

Para calcular las inserciones a partir de valores de cadena, use ai_query con un modelo de inserción hospedado en Databricks, como databricks-gte-large-en.

Condiciones de error comunes

• 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

En los ejemplos siguientes se usan estas tablas. Las incrustaciones se muestran como vectores 3 dimensionales para mayor brevedad; en la práctica, son dimensionales y se calculan mediante un modelo de inserción.

> 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

Artículos relacionados

• Referencia de tabla
• JOIN
• SELECT (subselección)
• vector_cosine_similarity Función
• vector_l2_distance Función
• vector_inner_product Función
• vector_search Función