NEAREST BY-sats

Gäller för:check markerat ja Databricks Runtime 18.3 och senare

Utökar en JOIN med top-k-rangordning på ett anpassat avstånd eller likhetsuttryck. För varje rad i frågan (vänster) table_reference hittar den upp till de översta num_results matchande raderna från måltabellen (höger) baserat på , ranking_expressionoch returnerar dem som sammanfogade rader.

ranking_expression kan vara valfritt skalära uttryck som poängsätter ett par rader från de två tabellerna, till exempel vector_cosine_similarity, vector_l2_distance, vector_inner_product eller ett sammansatt uttryck som kombinerar flera funktioner.

Syntax

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

Parameters

  • target_table_reference

    Måltabellen som ska sökas. Kan vara en tabell, underfråga eller CTE.

  • { INNER | LEFT [ OUTER ] }

    Optional. Kopplingstypen. Standardvärdet är INNER.

    • INNER släpper frågerader som inte har några matchande kandidater.
    • LEFT OUTER returnerar varje frågerad. Målkolumner är NULL när det inte finns några kandidater, till exempel när måltabellen är tom eller varje kandidat är NULL. Om det finns färre kandidater num_results för en frågerad returneras endast tillgängliga kandidater.

    Andra kopplingstyper (RIGHT, , SEMIFULL, ANTI, CROSS, NATURAL) genererar NEAREST_BY_JOIN.UNSUPPORTED_JOIN_TYPE.

  • { APPROX | EXACT }

    Styr resultatuppsättningskontraktet.

    • EXACT returnerar de exakta topp-k raderna under ranking_expression.
    • APPROX returnerar en top-k-uppsättning som approximeras den exakta rangordningen. Optimeraren kan använda snabbare, ungefärliga sökstrategier i stället för att utvärdera varje kandidat.

  • NEAREST [ num_results ]

    Valfri positiv heltalsliteral. Standardinställningen är 1. Måste finnas i intervallet [1, 100000]. Om måltabellen har färre matchande rader än num_resultsreturneras endast de tillgängliga raderna.

    Värden utanför intervallet genererar NEAREST_BY_JOIN.NUM_RESULTS_OUT_OF_RANGE.

  • EFTER AVSTÅND | LIKHET

    Anger ordningen ranking_expressionpå .

    • DISTANCE rangordnar rader efter det minsta värdet först (närmast = lägsta avståndet).
    • SIMILARITY rangordnar rader efter det största värdet först (närmaste = högsta likhet).

  • ranking_expression

    Ett skalärt uttryck som kan referera till kolumner från båda tabellerna.

    Vanliga alternativ är:

    Om det här uttrycket returnerar en datatyp som inte stöder beställning, till exempel MAP, genererar Azure Databricks DATATYPE_MISMATCH. INVALID_ORDERING_TYPE.

Notes

Asymmetri

NEAREST BY är inte kommutativt. Frågesidan fäster resultatet – varje frågerad skapar upp till num_results utdatarader:

  • När 100 rader från tabellen users sammanfogas med 1 000 rader från tabellen products med NEAREST 5returnerar kopplingen upp till 500 rader.
  • Om du växlar de två sidorna av kopplingen till koppling products med usersreturneras upp till 5 000 rader.

Om du byter de två sidorna ställs en annan fråga, så resultatet skiljer sig även för INNER JOIN.

Strömmande

NEAREST BY stöds inte på strömmande dataramar eller datauppsättningar. Frågor mot strömmande källor genererar NEAREST_BY_JOIN.STREAMING_NOT_SUPPORTED.

Inbäddningsindata

När du använder vektorbedömningsfunktioner måste båda vektorargumenten ha ARRAY<FLOAT> samma dimensionalitet. Se vector_cosine_similarity funktion för typ- och NULL hanteringsregler.

Om du vill beräkna inbäddningar från strängvärden använder du ai_query med en Databricks-värdbaserad inbäddningsmodell som databricks-gte-large-en.

Vanliga felvillkor

Exempel

I följande exempel används dessa tabeller. Inbäddningar visas som tredimensionella vektorer för korthet. I praktiken är de högre dimensionella och beräknas av en inbäddningsmodell.

> 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