TPC-DS-OBT Q5 — obt_top_items_by_quantity

TPC-DS-OBT benchmark · all TPC-DS-OBT queries

Top items by units sold

Representative SQL

Rendered for DataFusion with default parameters.

WITH ssr AS (
  SELECT
    obt.store_s_store_id,
    SUM(obt.sales_price) AS sales,
    SUM(profit) AS profit,
    SUM(return_amt) AS returns,
    SUM(net_loss) AS profit_loss
  FROM tpcds_sales_returns_obt AS obt
  CROSS JOIN (
    SELECT
      obt.store_sk AS store_sk,
      obt.sold_date_sk AS date_sk,
      obt.ext_sales_price AS sales_price,
      obt.net_profit AS profit,
      CAST(0 AS DECIMAL(7, 2)) AS return_amt,
      CAST(0 AS DECIMAL(7, 2)) AS net_loss
    FROM tpcds_sales_returns_obt AS obt
    WHERE
      channel = 'store'
    UNION ALL
    SELECT
      obt.store_sk AS store_sk,
      obt.returned_date_sk AS date_sk,
      CAST(0 AS DECIMAL(7, 2)) AS sales_price,
      CAST(0 AS DECIMAL(7, 2)) AS profit,
      obt.return_amount AS return_amt,
      obt.return_net_loss AS net_loss
    FROM tpcds_sales_returns_obt AS obt
    WHERE
      channel = 'store'
  ) AS salesreturns
  WHERE
    (
      date_sk = obt.sold_date_d_date_sk
      AND obt.sold_date_d_date BETWEEN CAST('1998-01-01' AS DATE) AND (
        CAST('1998-01-01' AS DATE) + INTERVAL '14 DAY'
      )
      AND obt.store_sk = obt.store_s_store_sk
    )
    AND channel IN ('catalog', 'store', 'web')
  GROUP BY
    obt.store_s_store_id
), csr AS (
  SELECT
    obt.catalog_page_cp_catalog_page_id,
    SUM(obt.sales_price) AS sales,
    SUM(profit) AS profit,
    SUM(return_amt) AS returns,
    SUM(net_loss) AS profit_loss
  FROM tpcds_sales_returns_obt AS obt
  CROSS JOIN (
    SELECT
      obt.catalog_page_sk AS page_sk,
      obt.sold_date_sk AS date_sk,
      obt.ext_sales_price AS sales_price,
      obt.net_profit AS profit,
      CAST(0 AS DECIMAL(7, 2)) AS return_amt,
      CAST(0 AS DECIMAL(7, 2)) AS net_loss
    FROM tpcds_sales_returns_obt AS obt
    WHERE
      channel = 'catalog'
    UNION ALL
    SELECT
      obt.catalog_page_sk AS page_sk,
      obt.returned_date_sk AS date_sk,
      CAST(0 AS DECIMAL(7, 2)) AS sales_price,
      CAST(0 AS DECIMAL(7, 2)) AS profit,
      obt.return_amount AS return_amt,
      obt.return_net_loss AS net_loss
    FROM tpcds_sales_returns_obt AS obt
    WHERE
      channel = 'catalog'
  ) AS salesreturns
  WHERE
    (
      date_sk = obt.sold_date_d_date_sk
      AND obt.sold_date_d_date BETWEEN CAST('1998-01-01' AS DATE) AND (
        CAST('1998-01-01' AS DATE) + INTERVAL '14 DAY'
      )
      AND page_sk = obt.catalog_page_cp_catalog_page_sk
    )
    AND channel IN ('catalog', 'store', 'web')
  GROUP BY
    obt.catalog_page_cp_catalog_page_id
), wsr AS (
  SELECT
    obt.web_site_web_site_id,
    SUM(obt.sales_price) AS sales,
    SUM(profit) AS profit,
    SUM(return_amt) AS returns,
    SUM(net_loss) AS profit_loss
  FROM tpcds_sales_returns_obt AS obt
  CROSS JOIN (
    SELECT
      obt.web_site_web_site_sk AS wsr_web_site_sk,
      obt.sold_date_sk AS date_sk,
      obt.ext_sales_price AS sales_price,
      obt.net_profit AS profit,
      CAST(0 AS DECIMAL(7, 2)) AS return_amt,
      CAST(0 AS DECIMAL(7, 2)) AS net_loss
    FROM tpcds_sales_returns_obt AS obt
    WHERE
      channel = 'web'
    UNION ALL
    SELECT
      obt.web_site_web_site_sk AS wsr_web_site_sk,
      obt.returned_date_sk AS date_sk,
      CAST(0 AS DECIMAL(7, 2)) AS sales_price,
      CAST(0 AS DECIMAL(7, 2)) AS profit,
      obt.return_amount AS return_amt,
      obt.return_net_loss AS net_loss
    FROM tpcds_sales_returns_obt AS obt
    WHERE
      (
        obt.item_sk = obt.item_sk AND obt.sale_id = obt.sale_id
      ) AND channel = 'web'
  ) AS salesreturns
  WHERE
    (
      date_sk = obt.sold_date_d_date_sk
      AND obt.sold_date_d_date BETWEEN CAST('1998-01-01' AS DATE) AND (
        CAST('1998-01-01' AS DATE) + INTERVAL '14 DAY'
      )
      AND wsr_web_site_sk = obt.web_site_web_site_sk
    )
    AND channel IN ('catalog', 'store', 'web')
  GROUP BY
    obt.web_site_web_site_id
)
SELECT
  channel,
  id,
  SUM(sales) AS sales,
  SUM(returns) AS returns,
  SUM(profit) AS profit
FROM (
  SELECT
    'store channel' AS channel,
    'store' || store_s_store_id AS id,
    sales,
    returns,
    (
      profit - profit_loss
    ) AS profit
  FROM ssr
  UNION ALL
  SELECT
    'catalog channel' AS channel,
    'catalog_page' || catalog_page_cp_catalog_page_id AS id,
    sales,
    returns,
    (
      profit - profit_loss
    ) AS profit
  FROM csr
  UNION ALL
  SELECT
    'web channel' AS channel,
    'web_site' || web_site_web_site_id AS id,
    sales,
    returns,
    (
      profit - profit_loss
    ) AS profit
  FROM wsr
) AS x
GROUP BY
  ROLLUP (
    channel,
    id
  )
ORDER BY
  channel,
  id
LIMIT 100

Representative DataFrame

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

def impl(ctx: DataFrameContext) -> Any:
        table = ctx.get_table(OBT_TABLE)
        for column, op, value in spec.filters:
            table = table.filter(_FILTER_OPS[op](ctx.col(column), ctx.lit(value)))
        aggregates = [getattr(ctx.col(column), func)().alias(alias) for alias, column, func in spec.aggregations]
        if spec.group_keys:
            result = table.group_by(*spec.group_keys).agg(*aggregates)
        else:
            result = table.select(*aggregates)
        if spec.sort_keys:
            descending = list(spec.descending) if spec.descending else [False] * len(spec.sort_keys)
            result = result.sort(list(spec.sort_keys), descending=descending)
        if spec.limit:
            result = result.limit(spec.limit)
        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 TPCDSOBT

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

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('tpcds_obt', '5', family="expression").source)  # or family="pandas"

Source

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