TPC-DS Q22 — Inventory ROLLUP by Product

TPC-DS benchmark · all TPC-DS queries

Analyzes inventory with ROLLUP by product name, brand, class, category

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  i_product_name,
  i_brand,
  i_class,
  i_category,
  AVG(inv_quantity_on_hand) AS qoh
FROM inventory, date_dim, item
WHERE
  inv_date_sk = d_date_sk
  AND inv_item_sk = i_item_sk
  AND d_month_seq BETWEEN 1212 AND 1212 + 11
GROUP BY
  ROLLUP (
    i_product_name,
    i_brand,
    i_class,
    i_category
  )
ORDER BY
  qoh,
  i_product_name,
  i_brand,
  i_class,
  i_category
LIMIT 100

Representative DataFrame

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

def q22_expression_impl(ctx: DataFrameContext) -> Any:
    from .rollup_helper import expand_rollup_expression

    params = get_parameters(22)
    dms = params.get("dms", 1212)

    inventory, item, date_dim = _tables(ctx, "inventory", "item", "date_dim")

    col = ctx.col

    date_filtered = date_dim.filter(col("d_month_seq").is_between(dms, dms + 11))

    inv_data = inventory.join(date_filtered, left_on="inv_date_sk", right_on="d_date_sk").join(
        item, left_on="inv_item_sk", right_on="i_item_sk"
    )

    group_cols = ["i_product_name", "i_brand", "i_class", "i_category"]
    agg_exprs = [ctx.mean("inv_quantity_on_hand").alias("qoh")]

    result = expand_rollup_expression(inv_data, group_cols, agg_exprs, ctx)
    return (
        result.select([*group_cols, "qoh"])
        .sort(["qoh", "i_product_name", "i_brand", "i_class", "i_category"], nulls_last=True)
        .head(100)
    )

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 TPCDS

bench = TPCDS(scale_factor=1.0)            # your scale factor
queries = bench.get_queries(dialect="datafusion")   # your platform's dialect; drop dialect= for native SQL
print(queries['22'])

Parameter substitution for this benchmark is seed-driven; pass a seed for a specific stream’s parameters:

print(bench.get_query(22, scale_factor=1.0, seed=42, dialect="datafusion"))

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('tpcds', '22', family="expression").source)  # or family="pandas"

Source

Query text: _sources/tpc-ds/query_templates/query22.tpl