Read Primitives — ASOF Join Basic

Read Primitives benchmark · all Read Primitives queries

ASOF join to find closest prior order for shipments

Representative SQL

Rendered in the benchmark’s native SQL with default parameters (no dialect translation available).

SELECT
  l.l_orderkey,
  l.l_shipdate,
  o.o_orderdate,
  o.o_totalprice,
  l.l_shipdate - o.o_orderdate AS days_to_ship
FROM lineitem AS l
ASOF JOIN orders AS o
  ON l.l_orderkey = o.o_orderkey AND l.l_shipdate >= o.o_orderdate
WHERE
  l.l_shipdate >= CAST('1995-01-01' AS DATE)
  AND l.l_shipdate < CAST('1995-02-01' AS DATE)
ORDER BY
  l.l_orderkey,
  l.l_shipdate
LIMIT 100

Representative DataFrame

Expression family (Polars, PySpark, DataFusion), default parameters.

def asof_join_basic_expression_impl(ctx: DataFrameContext) -> Any:
    lineitem, orders = _tables(ctx, "lineitem", "orders")
    col = ctx.col
    lit = ctx.lit

    filtered_lineitem = lineitem.filter(
        (col("l_shipdate") >= lit(date(1995, 1, 1))) & (col("l_shipdate") < lit(date(1995, 2, 1)))
    )

    return (
        filtered_lineitem.join(orders, left_on="l_orderkey", right_on="o_orderkey")
        .filter(col("l_shipdate") >= col("o_orderdate"))
        .with_columns((col("l_shipdate") - col("o_orderdate")).dt.total_days().alias("days_to_ship"))
        .select("l_orderkey", "l_shipdate", "o_orderdate", "o_totalprice", "days_to_ship")
        .sort(["l_orderkey", "l_shipdate"])
        .limit(100)
    )

Get the exact query for your run

The blocks above are representative. To get the precise statement a specific platform runs at a specific scale, substitute your own values:

from benchbox import ReadPrimitives

bench = ReadPrimitives(scale_factor=0.01)            # your scale factor
queries = bench.get_queries(dialect="datafusion")   # your platform's dialect; drop dialect= for native SQL
print(queries['asof_join_basic'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('read_primitives', 'asof_join_basic', family="expression").source)  # or family="pandas"

Source

Query text: benchbox/core/read_primitives/queries.py