TPC-DS Q86 — Web Sales ROLLUP with Rank

TPC-DS benchmark · all TPC-DS queries

Aggregates web sales with ROLLUP and hierarchical ranking

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  SUM(ws_net_paid) AS total_sum,
  i_category,
  i_class,
  GROUPING(i_category) + GROUPING(i_class) AS lochierarchy,
  RANK() OVER (
    PARTITION BY GROUPING(i_category) + GROUPING(i_class), CASE WHEN GROUPING(i_class) = 0 THEN i_category END
    ORDER BY SUM(ws_net_paid) DESC
  ) AS rank_within_parent
FROM web_sales, date_dim AS d1, item
WHERE
  d1.d_month_seq BETWEEN 1212 AND 1212 + 11
  AND d1.d_date_sk = ws_sold_date_sk
  AND i_item_sk = ws_item_sk
GROUP BY
  ROLLUP (
    i_category,
    i_class
  )
ORDER BY
  lochierarchy DESC,
  CASE WHEN lochierarchy = 0 THEN i_category END,
  rank_within_parent
LIMIT 100

Representative DataFrame

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

def q86_expression_impl(ctx: DataFrameContext) -> Any:
    from benchbox.core.tpcds.dataframe_queries.rollup_helper import (
        expand_rollup_expression,
        lochierarchy_expression,
    )

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

    col = ctx.col
    lit = ctx.lit

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

    base = (
        web_sales.join(date_dim, left_on="ws_sold_date_sk", right_on="d_date_sk")
        .join(item, left_on="ws_item_sk", right_on="i_item_sk")
        .filter(col("d_month_seq").is_between(dms, dms + 11))
    )

    agg_exprs = [col("ws_net_paid").sum().alias("total_sum")]

    rollup_result = expand_rollup_expression(
        base,
        group_cols=["i_category", "i_class"],
        agg_exprs=agg_exprs,
        ctx=ctx,
        count_sources={"total_sum": "ws_net_paid"},
    )

    lochierarchy_expr = lochierarchy_expression("grouping_id", 2, ctx=ctx)
    result = rollup_result.with_columns(lochierarchy_expr.alias("lochierarchy"))

    result = result.with_columns(
        ctx.when(col("grouping_id") & 1 == 0).then(col("i_category")).otherwise(lit(None)).alias("partition_key")
    )

    result = result.with_columns(
        _sql_rank_expression(ctx, "total_sum", ["lochierarchy", "partition_key"], descending=True).alias(
            "rank_within_parent"
        )
    )

    return _sort_rollup_hierarchy_expression(ctx, result, "total_sum")

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['86'])

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

print(bench.get_query(86, 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', '86', family="expression").source)  # or family="pandas"

Source

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