Skip to content

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 11 AND 11 + 10
OR obt.coupon_amt BETWEEN 460 AND 460 + 1000
OR obt.wholesale_cost BETWEEN 14 AND 14 + 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 91 AND 91 + 10
OR obt.coupon_amt BETWEEN 1430 AND 1430 + 1000
OR obt.wholesale_cost BETWEEN 32 AND 32 + 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 66 AND 66 + 10
OR obt.coupon_amt BETWEEN 920 AND 920 + 1000
OR obt.wholesale_cost BETWEEN 4 AND 4 + 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 142 AND 142 + 10
OR obt.coupon_amt BETWEEN 3054 AND 3054 + 1000
OR obt.wholesale_cost BETWEEN 80 AND 80 + 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 135 AND 135 + 10
OR obt.coupon_amt BETWEEN 14180 AND 14180 + 1000
OR obt.wholesale_cost BETWEEN 38 AND 38 + 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 28 AND 28 + 10
OR obt.coupon_amt BETWEEN 2513 AND 2513 + 1000
OR obt.wholesale_cost BETWEEN 42 AND 42 + 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