NEAREST BY-Klausel

Gilt für:check marked yes Databricks Runtime 18.3 und höher

Erweitert eine JOIN Top-K-Rangfolge auf einem benutzerdefinierten Abstand oder Ähnlichkeitsausdruck. Für jede Zeile in der Abfrage (links) table_reference findet sie basierend auf der Zieltabelle (rechts) bis zu den obersten num_results übereinstimmenden Zeilen aus der ranking_expressionZieltabelle (rechts), die sie als verkettete Zeilen zurückgibt.

ranking_expression kann ein beliebiger geordneter Skalarausdruck sein, der ein Zeilenpaar aus den beiden Tabellen bewertet, z. B. vector_cosine_similarity, vector_l2_distance, vector_inner_product oder einen zusammengesetzten Ausdruck, der mehrere Funktionen kombiniert.

Syntax

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

Parameters

  • target_table_reference

    Die zu durchsuchende Zieltabelle. Kann eine Tabelle, Unterabfrage oder CTE sein.

  • { INNER | LEFT [ OUTER ] }

    Dies ist optional. Der Verknüpfungstyp. Der Standardwert lautet INNER.

    • INNER löscht Abfragezeilen, die keine übereinstimmenden Kandidaten haben.
    • LEFT OUTER gibt jede Abfragezeile zurück. Zielseitige Spalten sind, wenn keine Kandidaten vorhanden sind NULL , z. B. wenn die Zieltabelle leer ist oder jeder Kandidat ist NULL. Wenn weniger als num_results Kandidaten für eine Abfragezeile vorhanden sind, werden nur die verfügbaren Kandidaten zurückgegeben.

    Andere Verknüpfungstypen (RIGHT, FULL, SEMI, ANTI, CROSS, NATURAL) auslösen NEAREST_BY_JOIN.UNSUPPORTED_JOIN_TYPE.

  • { APPROX | EXACT }

    Steuert den Vertrag für das Resultset.

    • EXACT gibt die genauen obersten K-Zeilen unter ranking_expression.
    • APPROX gibt einen Top-K-Satz zurück, der die genaue Rangfolge annähert. Der Optimierer kann schnellere, ungefähre Suchstrategien verwenden, anstatt jeden Kandidaten auszuwerten.

  • NEAREST [ num_results ]

    Optionales positives ganzzahliges Literal. Wird standardmäßig auf 1 festgelegt. Muss sich im Bereich [1, 100000]befinden. Wenn die Zieltabelle weniger übereinstimmende Zeilen enthält als num_results, werden nur die verfügbaren Zeilen zurückgegeben.

    Werte außerhalb des Bereichs werden erhöht NEAREST_BY_JOIN.NUM_RESULTS_OUT_OF_RANGE.

  • ABSTAND | ÄHNLICHKEIT

    Legt die Sortierung von ranking_expression.

    • DISTANCE sortiert Zeilen nach dem kleinsten Wert zuerst (nächster = niedrigster Abstand).
    • SIMILARITY sortiert Zeilen nach dem größten Wert zuerst (am nächsten = höchste Ähnlichkeit).

  • ranking_expression

    Ein skalarer Ausdruck, der auf Spalten aus beiden Tabellen verweisen kann.

    Häufige Auswahlmöglichkeiten sind:

    Wenn dieser Ausdruck einen Datentyp zurückgibt, der keine Sortierung unterstützt, z. B. MAP, löst Azure Databricks DATATYPE_MISMATCH aus. INVALID_ORDERING_TYPE.

Hinweise

Asymmetrie

NEAREST BY ist nicht kommutativ. Die Abfrageseite verankert das Ergebnis – jede Abfragezeile erzeugt bis zu num_results Ausgabezeilen:

  • Wenn 100 Zeilen aus der Tabellenbeitrittszeile users mit 1.000 Zeilen aus tabelle products mit NEAREST 5, gibt die Verknüpfung bis zu 500 Zeilen zurück.
  • Wenn Sie die beiden Seiten der Verknüpfung auf die Verknüpfung productsusersumstellen, werden bis zu 5.000 Zeilen zurückgegeben.

Das Austauschen der beiden Seiten stellt eine andere Frage, sodass das Ergebnis sogar für INNER JOIN.

Streaming

NEAREST BY wird beim Streamen von DataFrames oder Datasets nicht unterstützt. Abfragen gegen Streamingquellen lösen aus NEAREST_BY_JOIN.STREAMING_NOT_SUPPORTED.

Einbetten von Eingaben

Bei Verwendung von Vektorbewertungsfunktionen müssen beide Vektorargumente mit der gleichen Dimensionalität übereinstimmen ARRAY<FLOAT> . Informationen zu Typ- und NULL Behandlungsregeln finden Sie unter vector_cosine_similarity "Funktion".

Um Einbettungen aus Zeichenfolgenwerten zu berechnen, verwenden Sie ai_query mit einem in Databricks gehosteten Einbettungsmodell wie z databricks-gte-large-en. B. .

Häufige Fehlerbedingungen

Beispiele

In den folgenden Beispielen werden diese Tabellen verwendet. Einbettungen werden als 3dimensionale Vektoren aus Platzgründen dargestellt; in der Praxis werden sie durch ein Einbettungsmodell höherdimensional und berechnet.

> 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