Cláusula NEAREST BY

Aplica-se a:sim Databricks Runtime 18.3 e superiores

Estende a JOIN com a classificação top-k numa expressão personalizada de distância ou similaridade. Para cada linha na consulta (esquerda) table_reference, encontra-se até linhas correspondentes no topo num_results da tabela de destino (direita) com base no ranking_expression, devolvendo-as como linhas concatenadas.

ranking_expression pode ser qualquer expressão escalar ordenável que marque um par de linhas das duas tabelas — por exemplo , vector_cosine_similarity, vector_l2_distance, vector_inner_product, ou uma expressão composta que combine várias funções.

Syntax

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

Parameters

  • target_table_reference

    A tabela de alvos para procurar. Pode ser uma tabela, subconsulta ou CTE.

  • { INNER | LEFT [ OUTER ] }

    Optional. O tipo de junção. A predefinição é INNER.

    • INNER elimina linhas de consulta que não têm candidatos correspondentes.
    • LEFT OUTER devolve todas as linhas de consulta. Colunas do lado alvo são NULL quando não existem candidatos — por exemplo, quando a tabela alvo está vazia ou todos os candidatos são NULL. Se existirem menos candidatos num_results para uma linha de consulta, apenas os candidatos disponíveis são devolvidos.

    Outros tipos de junção (RIGHT, FULL, SEMI, ANTI, CROSS, ) NATURALelevam NEAREST_BY_JOIN.UNSUPPORTED_JOIN_TYPE.

  • { APPROX | EXACT }

    Controla o contrato do conjunto de resultados.

    • EXACT devolve as linhas exatas do top-k sob ranking_expression.
    • APPROX devolve um conjunto top-k que aproxima a classificação exata. O otimizador pode usar estratégias de pesquisa aproximadas mais rápidas em vez de avaliar todos os candidatos.

  • MAIS PRÓXIMO [ num_results ]

    Literal inteiro positivo opcional. O valor padrão é 1. Deve estar dentro do intervalo [1, 100000]. Se a tabela alvo tiver menos linhas correspondentes do que num_results, apenas as linhas disponíveis são devolvidas.

    Valores fora do intervalo aumentam NEAREST_BY_JOIN.NUM_RESULTS_OUT_OF_RANGE.

  • POR DISTÂNCIA | SEMELHANÇA

    Define a ordem de ranking_expression.

    • DISTANCE ordena as linhas pelo valor mais pequeno primeiro (mais próximo = distância mais baixa).
    • SIMILARITY classifica as linhas pelo maior valor em primeiro (mais próximo = maior similaridade).

  • ranking_expression

    Uma expressão escalar que pode referenciar colunas de ambas as tabelas.

    As escolhas mais comuns são:

    Se esta expressão devolver um tipo de dado que não suporta ordenação, como MAP, Azure Databricks eleva DATATYPE_MISMATCH. INVALID_ORDERING_TYPE.

Notes

Assimetria

NEAREST BY não é comutativo. O lado da consulta ancora o resultado — cada linha da consulta produz até linhas num_results de saída:

  • Quando 100 linhas da mesa users se juntam com 1.000 linhas da tabela products com NEAREST 5, a junção retorna até 500 linhas.
  • Se trocares os dois lados da junção para unir products com users, devolve até 5.000 linhas.

Trocar os dois lados coloca uma questão diferente, pelo que o resultado difere mesmo para INNER JOIN.

Serviço de streaming

NEAREST BY não é suportado em DataFrames ou Conjuntos de Dados em streaming. Consultas contra fontes de streaming levantam NEAREST_BY_JOIN.STREAMING_NOT_SUPPORTED.

Embedding de entradas

Ao usar funções de pontuação vetorial, ambos os argumentos vetoriais devem ter ARRAY<FLOAT> a mesma dimensionalidade. Ver vector_cosine_similarity função para regras de tipo e NULL manuseamento.

Para calcular embeddings a partir de valores de cadeia, use ai_query com um modelo de embedding hospedado em Databricks, como databricks-gte-large-en.

Condições de erro comuns

Examples

Os exemplos seguintes utilizam estas tabelas. Os embeddings são apresentados como vetores tridimensionais para brevidade; na prática, são de dimensão superior e calculadas por um modelo de imersão.

> 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
  • Last updated on