TPC-H Data Vault Q17 — Small-Quantity-Order Revenue

TPC-H Data Vault benchmark · all TPC-H Data Vault queries

Revenue from eliminating small quantity orders (Data Vault)

Representative SQL

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

SELECT
  SUM(sl.l_extendedprice) / 7.0 AS avg_yearly
FROM link_lineitem AS ll
JOIN sat_lineitem AS sl
  ON ll.hk_lineitem_link = sl.hk_lineitem_link AND sl.load_end_dts IS NULL
JOIN hub_part AS hp
  ON ll.hk_part = hp.hk_part
JOIN sat_part AS sp
  ON hp.hk_part = sp.hk_part AND sp.load_end_dts IS NULL
WHERE
  sp.p_brand = 'Brand#23'
  AND sp.p_container = 'MED BOX'
  AND sl.l_quantity < (
    SELECT
      0.2 * AVG(sl2.l_quantity)
    FROM link_lineitem AS ll2
    JOIN sat_lineitem AS sl2
      ON ll2.hk_lineitem_link = sl2.hk_lineitem_link AND sl2.load_end_dts IS NULL
    WHERE
      ll2.hk_part = hp.hk_part
  )

Representative DataFrame

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

def q17_expression_impl(ctx: DataFrameContext) -> Any:

    params = get_parameters("Q17")
    col, lit = ctx.col, ctx.lit
    brand = params.get("brand", "Brand#23")
    container = params.get("container", "MED BOX")

    ll = _strip_audit_columns(ctx.get_table("link_lineitem"))
    sl = _strip_audit_columns(ctx.get_table("sat_lineitem").filter(col("load_end_dts").is_null()))
    sp = _strip_audit_columns(ctx.get_table("sat_part").filter(col("load_end_dts").is_null()))

    df = (
        ll.join(sl, left_on="hk_lineitem_link", right_on="hk_lineitem_link")
        .join(sp, left_on="hk_part", right_on="hk_part")
        .filter((col("p_brand") == lit(brand)) & (col("p_container") == lit(container)))
    )

    avg_qty = (
        df.group_by("hk_part")
        .agg(col("l_quantity").mean().alias("mean_qty"))
        .with_columns((col("mean_qty") * lit(0.2)).alias("avg_qty"))
        .drop("mean_qty")
    )

    filtered = df.join(avg_qty, left_on="hk_part", right_on="hk_part").filter(col("l_quantity") < col("avg_qty"))
    return filtered.agg(
        col("l_extendedprice").sum().alias("total"),
        col("l_extendedprice").count().alias("n"),
    ).select(ctx.when(col("n") > lit(0)).then(col("total") / 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 DataVault

bench = DataVault(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])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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