TPC-H Data Vault Q18 — Large Volume Customer

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

Customers with large orders (Data Vault)

Representative SQL

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

SELECT
  sc.c_name,
  hc.c_custkey,
  ho.o_orderkey,
  so.o_orderdate,
  so.o_totalprice,
  large_orders.total_qty
FROM hub_customer AS hc
JOIN sat_customer AS sc
  ON hc.hk_customer = sc.hk_customer AND sc.load_end_dts IS NULL
JOIN link_order_customer AS loc
  ON hc.hk_customer = loc.hk_customer
JOIN hub_order AS ho
  ON loc.hk_order = ho.hk_order
JOIN sat_order AS so
  ON ho.hk_order = so.hk_order AND so.load_end_dts IS NULL
JOIN (
  SELECT
    ll2.hk_order,
    SUM(sl2.l_quantity) AS total_qty
  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
  GROUP BY
    ll2.hk_order
  HAVING
    SUM(sl2.l_quantity) > 300
) AS large_orders
  ON ho.hk_order = large_orders.hk_order
ORDER BY
  so.o_totalprice DESC,
  so.o_orderdate
LIMIT 100

Representative DataFrame

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

def q18_expression_impl(ctx: DataFrameContext) -> Any:

    params = get_parameters("Q18")
    col, lit = ctx.col, ctx.lit
    quantity = params.get("quantity", 300)

    hc = _strip_audit_columns(ctx.get_table("hub_customer"))
    sc = _strip_audit_columns(ctx.get_table("sat_customer").filter(col("load_end_dts").is_null()))
    loc = _strip_audit_columns(ctx.get_table("link_order_customer"))
    ho = _strip_audit_columns(ctx.get_table("hub_order"))
    so = _strip_audit_columns(ctx.get_table("sat_order").filter(col("load_end_dts").is_null()))
    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()))

    order_qty = (
        ll.join(sl, left_on="hk_lineitem_link", right_on="hk_lineitem_link")
        .group_by("hk_order")
        .agg(col("l_quantity").sum().alias("total_qty"))
        .filter(col("total_qty") > lit(quantity))
    )

    df = (
        hc.join(sc, left_on="hk_customer", right_on="hk_customer")
        .join(loc, left_on="hk_customer", right_on="hk_customer")
        .join(ho, left_on="hk_order", right_on="hk_order")
        .join(so, left_on="hk_order", right_on="hk_order")
        .join(order_qty, left_on="hk_order", right_on="hk_order")
    )

    result = df.select("c_name", "c_custkey", "o_orderkey", "o_orderdate", "o_totalprice", "total_qty")
    return result.sort([("o_totalprice", "desc"), ("o_orderdate", "asc")]).limit(100)

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[18])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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