KLAUSA TERDEKAT BY

Berlaku untuk:check ditandai ya Databricks Runtime 18.3 ke atas

Memperluas dengan peringkat k atas pada jarak kustom atau ekspresi kesamaan JOIN . Untuk setiap baris dalam kueri (kiri) table_reference, baris tersebut menemukan baris yang cocok hingga atas num_results dari tabel target (kanan) berdasarkan ranking_expression, mengembalikannya sebagai baris yang digabungkan.

ranking_expression bisa berupa ekspresi skalar yang dapat diurutkan yang menilai sepasang baris dari dua tabel — misalnya vector_cosine_similarity, vector_l2_distance, vector_inner_product, atau ekspresi komposit yang menggabungkan beberapa fungsi.

Syntax

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

Parameters

  • target_table_reference

    Tabel target untuk dicari. Bisa berupa tabel, subkueri, atau CTE.

  • { INNER | LEFT [ OUTER ] }

    Optional. Jenis gabungan. Defaultnya adalah INNER.

    • INNER menghilangkan baris kueri yang tidak memiliki kandidat yang cocok.
    • LEFT OUTER mengembalikan setiap baris kueri. Kolom sisi target adalah NULL ketika tidak ada kandidat — misalnya, ketika tabel target kosong atau setiap kandidat adalah NULL. Jika kurang dari num_results kandidat yang ada untuk baris kueri, hanya kandidat yang tersedia yang dikembalikan.

    Jenis gabungan lainnya (RIGHT, , SEMIFULL, ANTI, CROSS, NATURAL) naikkan NEAREST_BY_JOIN.UNSUPPORTED_JOIN_TYPE.

  • { APPROX | EXACT }

    Mengontrol kontrak tataan hasil.

    • EXACT mengembalikan baris top-k yang tepat di bawah ranking_expression.
    • APPROX mengembalikan set top-k yang mempertaruhkan peringkat yang tepat. Pengoptimal dapat menggunakan strategi pencarian yang lebih cepat, bukan mengevaluasi setiap kandidat.

  • TERDEKAT [ num_results ]

    Bilangan bulat positif opsional literal. Secara default menjadi 1. Harus dalam rentang [1, 100000]. Jika tabel target memiliki lebih sedikit baris yang cocok daripada num_results, hanya baris yang tersedia yang dikembalikan.

    Nilai di luar rentang naikkan NEAREST_BY_JOIN.NUM_RESULTS_OUT_OF_RANGE.

  • BERDASARKAN JARAK | KESAMAAN

    Mengatur urutan ranking_expression.

    • DISTANCE memberi peringkat baris menurut nilai terkecil terlebih dahulu (terdekat = jarak terendah).
    • SIMILARITY peringkat baris berdasarkan nilai terbesar terlebih dahulu (terdekat = kesamaan tertinggi).

  • ranking_expression

    Ekspresi skalar yang dapat mereferensikan kolom dari kedua tabel.

    Pilihan umumnya adalah:

    Jika ekspresi ini mengembalikan tipe data yang tidak mendukung pengurutan, seperti MAP, Azure Databricks menaikkan DATATYPE_MISMATCH. INVALID_ORDERING_TYPE.

Notes

Asimetri

NEAREST BY tidak komuttif. Sisi kueri menjangkar hasil — setiap baris kueri menghasilkan hingga num_results baris output:

  • Saat 100 baris dari gabungan tabel users dengan 1.000 baris dari tabel products dengan NEAREST 5, gabungan mengembalikan hingga 500 baris.
  • Jika Anda mengalihkan dua sisi gabungan untuk bergabung products dengan users, itu mengembalikan hingga 5.000 baris.

Bertukar kedua belah pihak mengajukan pertanyaan yang berbeda, sehingga hasilnya berbeda bahkan untuk INNER JOIN.

Streaming

NEAREST BY tidak didukung pada streaming DataFrame atau Himpunan Data. Kueri terhadap sumber streaming menaikkan NEAREST_BY_JOIN.STREAMING_NOT_SUPPORTED.

Menyematkan input

Saat menggunakan fungsi penilaian vektor, kedua argumen vektor harus ARRAY<FLOAT> dengan dimensi yang sama. Lihat vector_cosine_similarity fungsi untuk jenis dan NULL aturan penanganan.

Untuk menghitung penyematan dari nilai string, gunakan ai_query dengan model penyematan yang dihosting Databricks seperti databricks-gte-large-en.

Kondisi kesalahan umum

Examples

Contoh berikut menggunakan tabel ini. Penyematan ditampilkan sebagai vektor 3 dimensi untuk brevity; dalam praktiknya mereka berdimensi lebih tinggi dan dihitung oleh model penyematan.

> 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