TPC-H Q4 — Order Priority Checking

TPC-H benchmark · all TPC-H queries

Orders by priority with late lineitems

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  o_orderpriority,
  COUNT(*) AS order_count
FROM orders
WHERE
  o_orderdate >= CAST('1993-07-01' AS DATE)
  AND o_orderdate < CAST('1993-07-01' AS DATE) + INTERVAL '3 MONTH'
  AND EXISTS(
    SELECT
      *
    FROM lineitem
    WHERE
      l_orderkey = o_orderkey AND l_commitdate < l_receiptdate
  )
GROUP BY
  o_orderpriority
ORDER BY
  o_orderpriority

Representative DataFrame

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

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

    params = get_tpch_parameters(4)
    start_date = params["start_date"]
    end_date = params["end_date"]

    late_orders = lineitem.filter(col("l_commitdate") < col("l_receiptdate")).select("l_orderkey").unique()

    return (
        orders.filter((col("o_orderdate") >= lit(start_date)) & (col("o_orderdate") < lit(end_date)))
        .join(late_orders, left_on="o_orderkey", right_on="l_orderkey", how="semi")
        .group_by("o_orderpriority")
        .agg(col("o_orderkey").count().alias("order_count"))
        .sort("o_orderpriority")
    )

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

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

print(bench.get_query(4, 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', '4', family="expression").source)  # or family="pandas"

Source

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