TPC-DS-OBT Q28

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

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  *
FROM (
  SELECT
    AVG(obt.list_price) AS B1_LP,
    COUNT(obt.list_price) AS B1_CNT,
    COUNT(DISTINCT obt.list_price) AS B1_CNTD
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      obt.quantity BETWEEN 0 AND 5
      AND (
        obt.list_price BETWEEN 0 AND 0 + 10
        OR obt.coupon_amt BETWEEN 0 AND 0 + 1000
        OR obt.wholesale_cost BETWEEN 0 AND 0 + 20
      )
    )
    AND channel = 'store'
) AS B1, (
  SELECT
    AVG(obt.list_price) AS B2_LP,
    COUNT(obt.list_price) AS B2_CNT,
    COUNT(DISTINCT obt.list_price) AS B2_CNTD
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      obt.quantity BETWEEN 6 AND 10
      AND (
        obt.list_price BETWEEN 0 AND 0 + 10
        OR obt.coupon_amt BETWEEN 0 AND 0 + 1000
        OR obt.wholesale_cost BETWEEN 0 AND 0 + 20
      )
    )
    AND channel = 'store'
) AS B2, (
  SELECT
    AVG(obt.list_price) AS B3_LP,
    COUNT(obt.list_price) AS B3_CNT,
    COUNT(DISTINCT obt.list_price) AS B3_CNTD
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      obt.quantity BETWEEN 11 AND 15
      AND (
        obt.list_price BETWEEN 0 AND 0 + 10
        OR obt.coupon_amt BETWEEN 0 AND 0 + 1000
        OR obt.wholesale_cost BETWEEN 0 AND 0 + 20
      )
    )
    AND channel = 'store'
) AS B3, (
  SELECT
    AVG(obt.list_price) AS B4_LP,
    COUNT(obt.list_price) AS B4_CNT,
    COUNT(DISTINCT obt.list_price) AS B4_CNTD
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      obt.quantity BETWEEN 16 AND 20
      AND (
        obt.list_price BETWEEN 0 AND 0 + 10
        OR obt.coupon_amt BETWEEN 0 AND 0 + 1000
        OR obt.wholesale_cost BETWEEN 0 AND 0 + 20
      )
    )
    AND channel = 'store'
) AS B4, (
  SELECT
    AVG(obt.list_price) AS B5_LP,
    COUNT(obt.list_price) AS B5_CNT,
    COUNT(DISTINCT obt.list_price) AS B5_CNTD
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      obt.quantity BETWEEN 21 AND 25
      AND (
        obt.list_price BETWEEN 0 AND 0 + 10
        OR obt.coupon_amt BETWEEN 0 AND 0 + 1000
        OR obt.wholesale_cost BETWEEN 0 AND 0 + 20
      )
    )
    AND channel = 'store'
) AS B5, (
  SELECT
    AVG(obt.list_price) AS B6_LP,
    COUNT(obt.list_price) AS B6_CNT,
    COUNT(DISTINCT obt.list_price) AS B6_CNTD
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      obt.quantity BETWEEN 26 AND 30
      AND (
        obt.list_price BETWEEN 0 AND 0 + 10
        OR obt.coupon_amt BETWEEN 0 AND 0 + 1000
        OR obt.wholesale_cost BETWEEN 0 AND 0 + 20
      )
    )
    AND channel = 'store'
) AS B6
LIMIT 100

Representative DataFrame

No DataFrame implementation is registered for this query.

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

Source

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