TPC-H Data Vault Q1 — Pricing Summary Report

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

Pricing summary statistics for shipped lineitems (Data Vault)

Representative SQL

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

SELECT
  sl.l_returnflag,
  sl.l_linestatus,
  SUM(sl.l_quantity) AS sum_qty,
  SUM(sl.l_extendedprice) AS sum_base_price,
  SUM(sl.l_extendedprice * (
    1 - sl.l_discount
  )) AS sum_disc_price,
  SUM(sl.l_extendedprice * (
    1 - sl.l_discount
  ) * (
    1 + sl.l_tax
  )) AS sum_charge,
  AVG(sl.l_quantity) AS avg_qty,
  AVG(sl.l_extendedprice) AS avg_price,
  AVG(sl.l_discount) AS avg_disc,
  COUNT(*) AS count_order
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
WHERE
  sl.l_shipdate <= CAST('1998-12-01' AS DATE) - INTERVAL '90' DAYS
GROUP BY
  sl.l_returnflag,
  sl.l_linestatus
ORDER BY
  sl.l_returnflag,
  sl.l_linestatus

Representative DataFrame

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

def q1_expression_impl(ctx: DataFrameContext) -> Any:

    params = get_parameters("Q1")
    col, lit = ctx.col, ctx.lit
    cutoff = date(1998, 12, 1) - timedelta(days=params.get("delta", 90))

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

    df = ll.join(sl, left_on="hk_lineitem_link", right_on="hk_lineitem_link")
    df = df.filter(col("l_shipdate") <= lit(cutoff))

    df = df.with_columns(
        (col("l_extendedprice") * (lit(1) - col("l_discount"))).alias("disc_price"),
        (col("l_extendedprice") * (lit(1) - col("l_discount")) * (lit(1) + col("l_tax"))).alias("charge"),
    )
    result = df.group_by("l_returnflag", "l_linestatus").agg(
        col("l_quantity").sum().alias("sum_qty"),
        col("l_extendedprice").sum().alias("sum_base_price"),
        col("disc_price").sum().alias("sum_disc_price"),
        col("charge").sum().alias("sum_charge"),
        col("l_quantity").mean().alias("avg_qty"),
        col("l_extendedprice").mean().alias("avg_price"),
        col("l_discount").mean().alias("avg_disc"),
        col("l_returnflag").count().alias("count_order"),
    )
    return result.sort("l_returnflag", "l_linestatus")

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

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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