TPC-DS Q28 — Store Sales Quantity Bucket Extended

TPC-DS benchmark · all TPC-DS queries

Extended price analysis across 6 quantity buckets with filters

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  *
FROM (
  SELECT
    AVG(ss_list_price) AS B1_LP,
    COUNT(ss_list_price) AS B1_CNT,
    COUNT(DISTINCT ss_list_price) AS B1_CNTD
  FROM store_sales
  WHERE
    ss_quantity BETWEEN 0 AND 5
    AND (
      ss_list_price BETWEEN 11 AND 11 + 10
      OR ss_coupon_amt BETWEEN 460 AND 460 + 1000
      OR ss_wholesale_cost BETWEEN 14 AND 14 + 20
    )
) AS B1, (
  SELECT
    AVG(ss_list_price) AS B2_LP,
    COUNT(ss_list_price) AS B2_CNT,
    COUNT(DISTINCT ss_list_price) AS B2_CNTD
  FROM store_sales
  WHERE
    ss_quantity BETWEEN 6 AND 10
    AND (
      ss_list_price BETWEEN 91 AND 91 + 10
      OR ss_coupon_amt BETWEEN 1430 AND 1430 + 1000
      OR ss_wholesale_cost BETWEEN 32 AND 32 + 20
    )
) AS B2, (
  SELECT
    AVG(ss_list_price) AS B3_LP,
    COUNT(ss_list_price) AS B3_CNT,
    COUNT(DISTINCT ss_list_price) AS B3_CNTD
  FROM store_sales
  WHERE
    ss_quantity BETWEEN 11 AND 15
    AND (
      ss_list_price BETWEEN 66 AND 66 + 10
      OR ss_coupon_amt BETWEEN 920 AND 920 + 1000
      OR ss_wholesale_cost BETWEEN 4 AND 4 + 20
    )
) AS B3, (
  SELECT
    AVG(ss_list_price) AS B4_LP,
    COUNT(ss_list_price) AS B4_CNT,
    COUNT(DISTINCT ss_list_price) AS B4_CNTD
  FROM store_sales
  WHERE
    ss_quantity BETWEEN 16 AND 20
    AND (
      ss_list_price BETWEEN 142 AND 142 + 10
      OR ss_coupon_amt BETWEEN 3054 AND 3054 + 1000
      OR ss_wholesale_cost BETWEEN 80 AND 80 + 20
    )
) AS B4, (
  SELECT
    AVG(ss_list_price) AS B5_LP,
    COUNT(ss_list_price) AS B5_CNT,
    COUNT(DISTINCT ss_list_price) AS B5_CNTD
  FROM store_sales
  WHERE
    ss_quantity BETWEEN 21 AND 25
    AND (
      ss_list_price BETWEEN 135 AND 135 + 10
      OR ss_coupon_amt BETWEEN 14180 AND 14180 + 1000
      OR ss_wholesale_cost BETWEEN 38 AND 38 + 20
    )
) AS B5, (
  SELECT
    AVG(ss_list_price) AS B6_LP,
    COUNT(ss_list_price) AS B6_CNT,
    COUNT(DISTINCT ss_list_price) AS B6_CNTD
  FROM store_sales
  WHERE
    ss_quantity BETWEEN 26 AND 30
    AND (
      ss_list_price BETWEEN 28 AND 28 + 10
      OR ss_coupon_amt BETWEEN 2513 AND 2513 + 1000
      OR ss_wholesale_cost BETWEEN 42 AND 42 + 20
    )
) AS B6
LIMIT 100

Representative DataFrame

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

def q28_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(28)
    buckets = _q28_buckets(params)

    store_sales = ctx.get_table("store_sales")
    col = ctx.col
    lit = ctx.lit

    bucket_results = []
    for i, (q_min, q_max, lp, ca, wc) in enumerate(buckets):
        bucket = store_sales.filter(
            (col("ss_quantity") >= lit(q_min))
            & (col("ss_quantity") <= lit(q_max))
            & (
                ((col("ss_list_price") >= lit(lp)) & (col("ss_list_price") <= lit(lp + 10)))
                | ((col("ss_coupon_amt") >= lit(ca)) & (col("ss_coupon_amt") <= lit(ca + 1000)))
                | ((col("ss_wholesale_cost") >= lit(wc)) & (col("ss_wholesale_cost") <= lit(wc + 20)))
            )
        )

        stats = bucket.select(
            ctx.mean("ss_list_price").alias(f"B{i + 1}_LP"),
            col("ss_list_price").count().alias(f"B{i + 1}_CNT"),
            col("ss_list_price").filter(col("ss_list_price").is_not_null()).n_unique().alias(f"B{i + 1}_CNTD"),
        )

        bucket_results.append(stats)

    result = bucket_results[0]
    for br in bucket_results[1:]:
        result = result.join(br, how="cross")

    return result

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

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

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

Source

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