TPC-DS Q41 — Item Dimension Analysis

TPC-DS benchmark · all TPC-DS queries

Finds product names from items matching manufacturer and attribute criteria

Representative SQL

Rendered for DataFusion with default parameters.

SELECT DISTINCT
  (
    i_product_name
  )
FROM item AS i1
WHERE
  i_manufact_id BETWEEN 742 AND 742 + 40
  AND (
    SELECT
      COUNT(*) AS item_cnt
    FROM item
    WHERE
      (
        i_manufact = i1.i_manufact
        AND (
          (
            i_category = 'Women'
            AND (
              i_color = 'orchid' OR i_color = 'papaya'
            )
            AND (
              i_units = 'Pound' OR i_units = 'Lb'
            )
            AND (
              i_size = 'petite' OR i_size = 'medium'
            )
          )
          OR (
            i_category = 'Women'
            AND (
              i_color = 'burlywood' OR i_color = 'navy'
            )
            AND (
              i_units = 'Bundle' OR i_units = 'Each'
            )
            AND (
              i_size = 'N/A' OR i_size = 'extra large'
            )
          )
          OR (
            i_category = 'Men'
            AND (
              i_color = 'bisque' OR i_color = 'azure'
            )
            AND (
              i_units = 'N/A' OR i_units = 'Tsp'
            )
            AND (
              i_size = 'small' OR i_size = 'large'
            )
          )
          OR (
            i_category = 'Men'
            AND (
              i_color = 'chocolate' OR i_color = 'cornflower'
            )
            AND (
              i_units = 'Bunch' OR i_units = 'Gross'
            )
            AND (
              i_size = 'petite' OR i_size = 'medium'
            )
          )
        )
      )
      OR (
        i_manufact = i1.i_manufact
        AND (
          (
            i_category = 'Women'
            AND (
              i_color = 'salmon' OR i_color = 'midnight'
            )
            AND (
              i_units = 'Oz' OR i_units = 'Box'
            )
            AND (
              i_size = 'petite' OR i_size = 'medium'
            )
          )
          OR (
            i_category = 'Women'
            AND (
              i_color = 'snow' OR i_color = 'steel'
            )
            AND (
              i_units = 'Carton' OR i_units = 'Tbl'
            )
            AND (
              i_size = 'N/A' OR i_size = 'extra large'
            )
          )
          OR (
            i_category = 'Men'
            AND (
              i_color = 'purple' OR i_color = 'gainsboro'
            )
            AND (
              i_units = 'Dram' OR i_units = 'Unknown'
            )
            AND (
              i_size = 'small' OR i_size = 'large'
            )
          )
          OR (
            i_category = 'Men'
            AND (
              i_color = 'metallic' OR i_color = 'forest'
            )
            AND (
              i_units = 'Gram' OR i_units = 'Ounce'
            )
            AND (
              i_size = 'petite' OR i_size = 'medium'
            )
          )
        )
      )
  ) > 0
ORDER BY
  i_product_name
LIMIT 100

Representative DataFrame

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

def q41_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(41)
    manufact, colors, units, sizes = _q41_parameters(params)

    col = ctx.col
    lit = ctx.lit

    item = ctx.get_table("item")

    i1 = item.filter((col("i_manufact_id") >= lit(manufact)) & (col("i_manufact_id") <= lit(manufact + 40)))

    condition = None
    for category, color, unit, size in _Q41_GROUPS:
        group = (
            (col("i_category") == lit(category))
            & col("i_color").is_in(colors[color : color + 2])
            & col("i_units").is_in(units[unit : unit + 2])
            & col("i_size").is_in(sizes[size : size + 2])
        )
        condition = group if condition is None else condition | group

    matching_manufacts = item.filter(condition & col("i_manufact").is_not_null()).select("i_manufact").unique()

    return (
        i1.join(matching_manufacts, on="i_manufact")
        .select(col("i_product_name"))
        .unique()
        .sort("i_product_name", 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['41'])

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

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

Source

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