TPC-H Data Vault Q12 — Shipping Modes and Order Priority

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

Effect of shipping modes on order priority (Data Vault)

Representative SQL

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

SELECT
  sl.l_shipmode,
  SUM(
    CASE
      WHEN so.o_orderpriority = '1-URGENT' OR so.o_orderpriority = '2-HIGH'
      THEN 1
      ELSE 0
    END
  ) AS high_line_count,
  SUM(
    CASE
      WHEN so.o_orderpriority <> '1-URGENT' AND so.o_orderpriority <> '2-HIGH'
      THEN 1
      ELSE 0
    END
  ) AS low_line_count
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 sat_order AS so
  ON ll.hk_order = so.hk_order AND so.load_end_dts IS NULL
WHERE
  sl.l_shipmode IN ('MAIL', 'SHIP')
  AND sl.l_commitdate < sl.l_receiptdate
  AND sl.l_shipdate < sl.l_commitdate
  AND sl.l_receiptdate >= CAST('1994-01-01' AS DATE)
  AND sl.l_receiptdate < CAST('1994-01-01' AS DATE) + INTERVAL '1' YEAR
GROUP BY
  sl.l_shipmode
ORDER BY
  sl.l_shipmode

Representative DataFrame

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

def q12_expression_impl(ctx: DataFrameContext) -> Any:

    params = get_parameters("Q12")
    col, lit = ctx.col, ctx.lit
    shipmode1 = params.get("shipmode1", "MAIL")
    shipmode2 = params.get("shipmode2", "SHIP")
    start_date = params.get("start_date", date(1994, 1, 1))
    end_date = params.get("end_date", date(1995, 1, 1))

    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()))
    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(so, left_on="hk_order", right_on="hk_order")
        .filter(
            col("l_shipmode").is_in([shipmode1, shipmode2])
            & (col("l_commitdate") < col("l_receiptdate"))
            & (col("l_shipdate") < col("l_commitdate"))
            & (col("l_receiptdate") >= lit(start_date))
            & (col("l_receiptdate") < lit(end_date))
        )
    )

    result = df.group_by("l_shipmode").agg(
        ctx.when((col("o_orderpriority") == lit("1-URGENT")) | (col("o_orderpriority") == lit("2-HIGH")))
        .then(lit(1))
        .otherwise(lit(0))
        .sum()
        .alias("high_line_count"),
        ctx.when((col("o_orderpriority") != lit("1-URGENT")) & (col("o_orderpriority") != lit("2-HIGH")))
        .then(lit(1))
        .otherwise(lit(0))
        .sum()
        .alias("low_line_count"),
    )
    return result.sort("l_shipmode")

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

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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