TPC-H Data Vault Q11 — Important Stock Identification

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

Find most important stock in a nation (Data Vault)

Representative SQL

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

SELECT
  hp.p_partkey,
  SUM(sps.ps_supplycost * sps.ps_availqty) AS value
FROM link_part_supplier AS lps
JOIN hub_part AS hp
  ON lps.hk_part = hp.hk_part
JOIN sat_partsupp AS sps
  ON lps.hk_part_supplier = sps.hk_part_supplier AND sps.load_end_dts IS NULL
JOIN hub_supplier AS hs
  ON lps.hk_supplier = hs.hk_supplier
JOIN link_supplier_nation AS lsn
  ON hs.hk_supplier = lsn.hk_supplier
JOIN hub_nation AS hn
  ON lsn.hk_nation = hn.hk_nation
JOIN sat_nation AS sn
  ON hn.hk_nation = sn.hk_nation AND sn.load_end_dts IS NULL
WHERE
  sn.n_name = 'GERMANY'
GROUP BY
  hp.p_partkey
HAVING
  SUM(sps.ps_supplycost * sps.ps_availqty) > (
    SELECT
      SUM(sps2.ps_supplycost * sps2.ps_availqty) * 0.0001
    FROM link_part_supplier AS lps2
    JOIN sat_partsupp AS sps2
      ON lps2.hk_part_supplier = sps2.hk_part_supplier AND sps2.load_end_dts IS NULL
    JOIN hub_supplier AS hs2
      ON lps2.hk_supplier = hs2.hk_supplier
    JOIN link_supplier_nation AS lsn2
      ON hs2.hk_supplier = lsn2.hk_supplier
    JOIN hub_nation AS hn2
      ON lsn2.hk_nation = hn2.hk_nation
    JOIN sat_nation AS sn2
      ON hn2.hk_nation = sn2.hk_nation AND sn2.load_end_dts IS NULL
    WHERE
      sn2.n_name = 'GERMANY'
  )
ORDER BY
  value DESC

Representative DataFrame

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

def q11_expression_impl(ctx: DataFrameContext) -> Any:

    params = get_parameters("Q11")
    col, lit = ctx.col, ctx.lit
    nation = params.get("nation", "GERMANY")
    fraction = params.get("fraction", 0.0001)

    lps = _strip_audit_columns(ctx.get_table("link_part_supplier"))
    sps = _strip_audit_columns(ctx.get_table("sat_partsupp").filter(col("load_end_dts").is_null()))
    lsn = _strip_audit_columns(ctx.get_table("link_supplier_nation"))
    sn = _strip_audit_columns(ctx.get_table("sat_nation").filter(col("load_end_dts").is_null()))
    hp = _strip_audit_columns(ctx.get_table("hub_part"))

    df = (
        lps.join(sps, left_on="hk_part_supplier", right_on="hk_part_supplier")
        .join(lsn, left_on="hk_supplier", right_on="hk_supplier")
        .join(sn, left_on="hk_nation", right_on="hk_nation")
        .filter(col("n_name") == lit(nation))
        .join(hp, left_on="hk_part", right_on="hk_part")
    )

    df = df.with_columns((col("ps_supplycost") * col("ps_availqty")).alias("stock_value"))

    total = df.agg(col("stock_value").sum().alias("total_value"))
    threshold = ctx.scalar(total, "total_value") * fraction

    by_part = df.group_by("p_partkey").agg(
        col("stock_value").sum().alias("value"),
    )
    result = by_part.filter(col("value") > lit(threshold))
    return result.sort([("value", "desc")])

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

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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