TPC-H Data Vault Q9 — Product Type Profit Measure

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

Profit on a given line of parts (Data Vault)

Representative SQL

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

SELECT
  nation,
  o_year,
  SUM(amount) AS sum_profit
FROM (
  SELECT
    sn.n_name AS nation,
    EXTRACT(YEAR FROM so.o_orderdate) AS o_year,
    sl.l_extendedprice * (
      1 - sl.l_discount
    ) - sps.ps_supplycost * sl.l_quantity AS amount
  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
  JOIN hub_supplier AS hs
    ON ll.hk_supplier = hs.hk_supplier
  JOIN link_part_supplier AS lps
    ON hp.hk_part = lps.hk_part AND hs.hk_supplier = lps.hk_supplier
  JOIN sat_partsupp AS sps
    ON lps.hk_part_supplier = sps.hk_part_supplier AND sps.load_end_dts IS NULL
  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
  JOIN hub_order AS ho
    ON ll.hk_order = ho.hk_order
  JOIN sat_order AS so
    ON ho.hk_order = so.hk_order AND so.load_end_dts IS NULL
  WHERE
    sp.p_name LIKE '%green%'
) AS profit
GROUP BY
  nation,
  o_year
ORDER BY
  nation,
  o_year DESC

Representative DataFrame

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

def q9_expression_impl(ctx: DataFrameContext) -> Any:

    params = get_parameters("Q9")
    col, lit = ctx.col, ctx.lit
    color = params.get("color", "green")

    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()))
    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()))
    so = _strip_audit_columns(ctx.get_table("sat_order").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_name").str.contains(color))
        .join(lps, left_on=["hk_part", "hk_supplier"], right_on=["hk_part", "hk_supplier"])
        .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")
        .join(so, left_on="hk_order", right_on="hk_order")
    )

    df = df.with_columns(col("o_orderdate").dt.year().alias("o_year")).with_columns(
        (col("l_extendedprice") * (lit(1) - col("l_discount")) - col("ps_supplycost") * col("l_quantity")).alias(
            "amount"
        )
    )

    result = df.group_by("n_name", "o_year").agg(col("amount").sum().alias("sum_profit"))
    return result.sort([("n_name", "asc"), ("o_year", "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[9])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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