pyspark.sql.DataFrame.nearestByJoin#

DataFrame.nearestByJoin(other, rankingExpression, numResults, mode, direction, *, joinType='inner')[source]#

Nearest-by top-K ranking join with another DataFrame. For each row on the left (query side), returns up to numResults rows from other (base side), ranked by rankingExpression.

The current implementation evaluates the full cross-product of left and right and bounds memory per left row by numResults. Index-backed approximate strategies (transparent to approx mode) are planned for a future release; until then, pre-filter other when it is large. Tie-breaking among rows with equal ranking values is unspecified.

New in version 4.2.0.

Parameters

otherDataFrame: Right (base side) of the join - the candidate pool searched for each row of this DataFrame.
rankingExpressionColumn: Scalar expression used to rank candidate rows on the right side.
numResultsint: Maximum number of matches per query row. Must be between 1 and 100000.
modestr: Search algorithm contract. Must be one of: approx, exact. approx allows the optimizer to use indexed or other approximate strategies when available; exact forces brute-force evaluation and requires the ranking expression to be deterministic.
directionstr: "distance" (smallest value first) or "similarity" (largest value first).
joinTypestr, keyword-only, optional: Default inner. Must be one of: inner, leftouter.

Returns

DataFrame: Joined DataFrame.

Examples

>>> from pyspark.sql import functions as sf
>>> users = spark.createDataFrame(
...     [(1, 10.0), (2, 20.0), (3, 30.0)], ["user_id", "score"])
>>> products = spark.createDataFrame(
...     [("A", 11.0), ("B", 22.0), ("C", 5.0)], ["product", "pscore"])
>>> users.nearestByJoin(
...     products, -sf.abs(users.score - products.pscore), 1, "exact", "similarity"
... ).select("user_id", "product").orderBy("user_id").show()
+-------+-------+
|user_id|product|
+-------+-------+
|      1|      A|
|      2|      B|
|      3|      B|
+-------+-------+