Cláusula NEAREST BY

Aplica-se a: Databricks Runtime 18.3 e superior

Estende uma JOIN classificação com top-k em uma distância personalizada ou expressão de similaridade. Para cada linha na consulta (esquerda) table_reference, ela encontra até as linhas superiores num_results correspondentes da tabela de destino (direita) com base na tabela , retornando-as ranking_expressioncomo linhas concatenadas.

ranking_expression pode ser qualquer expressão escalar orderable que pontua um par de linhas das duas tabelas, por exemplo , vector_cosine_similarity, vector_l2_distance, vector_inner_product ou uma expressão composta que combina 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 destino a ser pesquisada. Pode ser uma tabela, uma subconsulta ou uma CTE.

• { INNER | LEFT [ OUTER ] }

  Optional. O tipo de junção. O padrão é INNER.

  • INNER descarta linhas de consulta que não têm candidatos correspondentes.
  • LEFT OUTER retorna todas as linhas de consulta. As colunas do lado do destino são NULL quando nenhum candidato existe , por exemplo, quando a tabela de destino está vazia ou cada candidato é NULL. Se houver menos candidatos num_results para uma linha de consulta, somente os candidatos disponíveis serão retornados.

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

• { APPROX | EXACT }

  Controla o contrato de conjunto de resultados.

  • EXACT retorna as linhas exatas top-k em ranking_expression.
  • APPROX retorna um conjunto top-k que aproxima a classificação exata. O otimizador pode usar estratégias de pesquisa mais rápidas e aproximadas em vez de avaliar cada candidato.

• NEAREST [ num_results ]

  Literal inteiro positivo opcional. Usa 1 como padrão. Deve estar no intervalo [1, 100000]. Se a tabela de destino tiver menos linhas correspondentes do que num_results, somente as linhas disponíveis serão retornadas.

  Valores fora do intervalo de aumento NEAREST_BY_JOIN.NUM_RESULTS_OUT_OF_RANGE.

• POR DISTÂNCIA | SEMELHANÇA

  Define a ordem de ranking_expression.

  • DISTANCE classifica as linhas por menor valor primeiro (mais próximo = menor distância).
  • SIMILARITY classifica as linhas por maior valor primeiro (mais próximo = maior semelhança).

• ranking_expression

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

  As opções comuns são:

  • funções de similaridade, como vector_cosine_similarity e vector_inner_product,
  • funções de distância, como vector_l2_distance,
  • distâncias numéricas como a distância de Manhattan: vector_norm(zip_with(a.col, b.col, (x, y) -> x - y), 1.0f).

  Se essa expressão retornar um tipo de dados que não dá suporte à ordenação, como MAP, Azure Databricks gerará DATATYPE_MISMATCH. INVALID_ORDERING_TYPE.

Anotações

Assimetria

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

• Quando 100 linhas da tabela users se juntam com 1.000 linhas da tabela products com NEAREST 5, a junção retorna até 500 linhas.
• Se você alternar os dois lados da junção para ingressar productsusers, ele retornará até 5.000 linhas.

Trocar os dois lados faz uma pergunta diferente, de modo que o resultado difere mesmo para INNER JOIN.

Streaming

NEAREST BY não há suporte para streaming de DataFrames ou conjuntos de dados. As consultas em relação às fontes de streaming são geradas NEAREST_BY_JOIN.STREAMING_NOT_SUPPORTED.

Inserindo entradas

Ao usar funções de pontuação de vetor, ambos os argumentos de vetor devem estar ARRAY<FLOAT> com a mesma dimensionalidade. Consulte vector_cosine_similarity a função para regras de tipo e NULL tratamento.

Para calcular inserções de valores de cadeia de caracteres, use ai_query com um modelo de inserção hospedado pelo Databricks, como databricks-gte-large-en.

Condições de erro comuns

• 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

Exemplos

Os exemplos a seguir usam essas tabelas. As inserções são mostradas como vetores tridimensionais para brevidade; na prática, elas são de maior dimensão e computadas por um modelo de inserçã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

Artigos relacionados

• referência de tabela
• JOIN
• SELECT (subseleção)
• vector_cosine_similarity função
• vector_l2_distance função
• vector_inner_product função
• vector_search função