TPC-H Data Vault Q19 — Discounted Revenue

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

Revenue for parts with specific conditions (Data Vault)

Representative SQL

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

SELECT
  SUM(sl.l_extendedprice * (
    1 - sl.l_discount
  )) AS revenue
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#12'
    AND sp.p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
    AND sl.l_quantity >= 1
    AND sl.l_quantity <= 1 + 10
    AND sp.p_size BETWEEN 1 AND 5
    AND sl.l_shipmode IN ('AIR', 'AIR REG')
    AND sl.l_shipinstruct = 'DELIVER IN PERSON'
  )
  OR (
    sp.p_brand = 'Brand#23'
    AND sp.p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
    AND sl.l_quantity >= 10
    AND sl.l_quantity <= 10 + 10
    AND sp.p_size BETWEEN 1 AND 10
    AND sl.l_shipmode IN ('AIR', 'AIR REG')
    AND sl.l_shipinstruct = 'DELIVER IN PERSON'
  )
  OR (
    sp.p_brand = 'Brand#34'
    AND sp.p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
    AND sl.l_quantity >= 20
    AND sl.l_quantity <= 20 + 10
    AND sp.p_size BETWEEN 1 AND 15
    AND sl.l_shipmode IN ('AIR', 'AIR REG')
    AND sl.l_shipinstruct = 'DELIVER IN PERSON'
  )

Representative DataFrame

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

def q19_expression_impl(ctx: DataFrameContext) -> Any:

    params = get_parameters("Q19")
    col, lit = ctx.col, ctx.lit

    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"
    )

    air_modes = col("l_shipmode").is_in(["AIR", "AIR REG"])
    deliver = col("l_shipinstruct") == lit("DELIVER IN PERSON")

    cond1 = (
        (col("p_brand") == lit(params.get("brand1")))
        & col("p_container").is_in(["SM CASE", "SM BOX", "SM PACK", "SM PKG"])
        & (col("l_quantity") >= lit(params.get("quantity1")))
        & (col("l_quantity") <= lit(params.get("quantity1") + 10))
        & (col("p_size") >= lit(1))
        & (col("p_size") <= lit(5))
        & air_modes
        & deliver
    )
    cond2 = (
        (col("p_brand") == lit(params.get("brand2")))
        & col("p_container").is_in(["MED BAG", "MED BOX", "MED PKG", "MED PACK"])
        & (col("l_quantity") >= lit(params.get("quantity2")))
        & (col("l_quantity") <= lit(params.get("quantity2") + 10))
        & (col("p_size") >= lit(1))
        & (col("p_size") <= lit(10))
        & air_modes
        & deliver
    )
    cond3 = (
        (col("p_brand") == lit(params.get("brand3")))
        & col("p_container").is_in(["LG CASE", "LG BOX", "LG PACK", "LG PKG"])
        & (col("l_quantity") >= lit(params.get("quantity3")))
        & (col("l_quantity") <= lit(params.get("quantity3") + 10))
        & (col("p_size") >= lit(1))
        & (col("p_size") <= lit(15))
        & air_modes
        & deliver
    )

    return (
        df.filter(cond1 | cond2 | cond3)
        .with_columns((col("l_extendedprice") * (lit(1) - col("l_discount"))).alias("revenue"))
        .agg(
            col("revenue").sum().alias("revenue"),
            col("revenue").count().alias("n"),
        )
        .select(ctx.when(col("n") > lit(0)).then(col("revenue")).otherwise(lit(None)).alias("revenue"))
    )

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

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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