Read Primitives — MIN_BY Complex

Read Primitives benchmark · all Read Primitives queries

Find the cheapest part for each brand

Representative SQL

Rendered for DataFusion with default parameters.

WITH ranked AS (
  SELECT
    p_brand,
    p_name,
    p_type,
    p_retailprice,
    ROW_NUMBER() OVER (PARTITION BY p_brand ORDER BY p_retailprice ASC, p_name) AS rn
  FROM part
)
SELECT
  p_brand,
  p_name AS cheapest_part_name,
  p_type AS cheapest_part_type,
  p_retailprice AS min_price
FROM ranked
WHERE
  rn = 1
ORDER BY
  min_price ASC,
  p_brand

Representative DataFrame

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

def min_by_complex_expression_impl(ctx: DataFrameContext) -> Any:
    col = ctx.col
    return (
        ctx.get_table("part")
        .sort(["p_brand", "p_retailprice", "p_name", "p_type"])
        .group_by("p_brand")
        .agg(
            col("p_name").first().alias("cheapest_part_name"),
            col("p_type").first().alias("cheapest_part_type"),
            col("p_retailprice").min().alias("min_price"),
        )
        .sort(["min_price", "p_brand"])
        .select("p_brand", "cheapest_part_name", "cheapest_part_type", "min_price")
    )

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['min_by_complex'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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