TPC-H Q17 — Small-Quantity-Order Revenue

TPC-H benchmark · all TPC-H queries

Revenue from eliminating small quantity orders

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  SUM(l_extendedprice) / 7.0 AS avg_yearly
FROM lineitem, part
WHERE
  p_partkey = l_partkey
  AND p_brand = 'Brand#23'
  AND p_container = 'MED BOX'
  AND l_quantity < (
    SELECT
      0.2 * AVG(l_quantity)
    FROM lineitem
    WHERE
      l_partkey = p_partkey
  )

Representative DataFrame

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

def q17_expression_impl(ctx: DataFrameContext) -> Any:
    lineitem = ctx.get_table("lineitem")
    part = ctx.get_table("part")
    col = ctx.col
    lit = ctx.lit

    params = get_tpch_parameters(17)
    brand = params["brand"]
    container = params["container"]

    avg_qty_per_part = lineitem.group_by("l_partkey").agg((col("l_quantity").mean() * lit(0.2)).alias("avg_qty"))

    filtered = (
        part.filter((col("p_brand") == lit(brand)) & (col("p_container") == lit(container)))
        .join(lineitem, left_on="p_partkey", right_on="l_partkey")
        .join(avg_qty_per_part, left_on="p_partkey", right_on="l_partkey")
        .filter(col("l_quantity") < col("avg_qty"))
    )
    totals = filtered.select(
        col("l_extendedprice").count().alias("__q17_price_count"),
        col("l_extendedprice").sum().alias("__q17_price_sum"),
    )
    return totals.select(
        ctx.when(col("__q17_price_count") > lit(0))
        .then(col("__q17_price_sum") / lit(7.0))
        .otherwise(lit(None))
        .alias("avg_yearly")
    )

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 TPCH

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

Parameter substitution for this benchmark is seed-driven; pass a seed for a specific stream’s parameters:

print(bench.get_query(17, scale_factor=0.01, seed=42, dialect="datafusion"))

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('tpch', '17', family="expression").source)  # or family="pandas"

Source

Query text: benchbox/_binaries/tpc-h/templates/queries/17.sql