TPC-H Q20 — Potential Part Promotion

TPC-H benchmark · all TPC-H queries

Suppliers with excess inventory of parts

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  s_name,
  s_address
FROM supplier, nation
WHERE
  s_suppkey IN (
    SELECT
      ps_suppkey
    FROM partsupp
    WHERE
      ps_partkey IN (
        SELECT
          p_partkey
        FROM part
        WHERE
          p_name LIKE 'forest%'
      )
      AND ps_availqty > (
        SELECT
          0.5 * SUM(l_quantity)
        FROM lineitem
        WHERE
          l_partkey = ps_partkey
          AND l_suppkey = ps_suppkey
          AND l_shipdate >= CAST('1994-01-01' AS DATE)
          AND l_shipdate < CAST('1994-01-01' AS DATE) + INTERVAL '1 YEAR'
      )
  )
  AND s_nationkey = n_nationkey
  AND n_name = 'CANADA'
ORDER BY
  s_name

Representative DataFrame

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

def q20_expression_impl(ctx: DataFrameContext) -> Any:
    supplier = ctx.get_table("supplier")
    nation = ctx.get_table("nation")
    partsupp = ctx.get_table("partsupp")
    part = ctx.get_table("part")
    lineitem = ctx.get_table("lineitem")
    col = ctx.col
    lit = ctx.lit

    params = get_tpch_parameters(20)
    color_prefix = params["color_prefix"]
    nation_name = params["nation_name"]
    start_date = params["start_date"]
    end_date = params["end_date"]

    forest_parts = part.filter(col("p_name").str.starts_with(color_prefix)).select("p_partkey")

    shipped_qty = (
        lineitem.filter((col("l_shipdate") >= lit(start_date)) & (col("l_shipdate") < lit(end_date)))
        .group_by("l_partkey", "l_suppkey")
        .agg((col("l_quantity").sum() * lit(0.5)).alias("threshold"))
    )

    excess_partsupps = (
        partsupp.join(forest_parts, left_on="ps_partkey", right_on="p_partkey", how="semi")
        .join(shipped_qty, left_on=["ps_partkey", "ps_suppkey"], right_on=["l_partkey", "l_suppkey"])
        .filter(col("ps_availqty") > col("threshold"))
        .select("ps_suppkey")
        .unique()
    )

    return (
        supplier.join(nation, left_on="s_nationkey", right_on="n_nationkey")
        .filter(col("n_name") == lit(nation_name))
        .join(excess_partsupps, left_on="s_suppkey", right_on="ps_suppkey", how="semi")
        .select("s_name", "s_address")
        .sort("s_name")
    )

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

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

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

Source

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