TPC-DS-OBT Q8 — obt_profit_by_channel
TPC-DS-OBT benchmark · all TPC-DS-OBT queries
Total net profit by channel
Representative SQL
Rendered for DataFusion with default parameters.
SELECT store_s_store_name, SUM(net_profit)FROM tpcds_sales_returns_obt AS obtCROSS JOIN ( SELECT ca_zip FROM ( SELECT SUBSTRING(bill_addr_ca_zip FROM 1 FOR 5) AS ca_zip FROM tpcds_sales_returns_obt AS obt WHERE SUBSTRING(obt.bill_addr_ca_zip FROM 1 FOR 5) IN ( '17629', '67418', '43964', '84483', '57375', '32536', '43532', '15594', '32255', '94244', '71793', '91564', '17315', '12493', '64595', '29724', '34483', '52572', '44253', '58624', '68118', '72746', '88543', '89432', '12246', '78692', '72332', '75669', '16355', '42774', '30893', '45653', '91260', '58034', '56217', '46852', '47098', '74060', '42238', '35726', '80916', '44008', '13102', '36053', '18186', '16356', '34396', '95948', '34928', '94133', '22889', '97767', '23363', '28331', '90331', '89736', '43414', '51715', '79408', '94468', '65044', '65709', '49290', '79492', '16041', '27811', '84014', '25863', '37781', '46959', '56336', '47759', '25329', '52291', '19270', '91285', '51469', '63600', '88904', '44692', '82689', '60624', '16741', '32708', '10330', '96407', '31273', '31049', '24266', '17808', '27373', '64423', '28909', '43016', '79081', '62540', '24819', '61182', '47173', '42796', '95772', '34305', '54576', '29931', '98513', '53976', '81788', '65312', '11297', '36269', '60814', '57058', '16584', '69584', '71171', '33347', '41863', '42053', '32959', '50932', '65305', '89925', '31159', '31674', '75551', '30909', '86142', '82233', '61420', '27968', '40527', '89823', '34184', '67226', '58121', '74547', '52120', '41227', '25352', '26842', '56259', '48808', '49513', '90424', '45175', '48096', '39818', '46123', '97717', '52032', '19918', '29449', '97068', '31525', '30870', '27879', '94379', '66272', '11323', '85121', '16605', '93706', '10352', '19785', '38153', '82735', '51334', '85745', '96037', '58363', '56361', '21456', '72678', '44550', '22469', '20926', '15218', '59594', '73515', '36546', '35527', '39435', '34163', '28768', '56434', '39774', '37802', '39049', '86472', '81970', '88144', '81806', '95688', '75046', '28620', '99951', '21644', '24565', '55058', '83660', '66568', '32309', '38209', '29575', '10368', '44869', '48620', '45917', '65364', '81226', '57711', '20658', '94925', '49027', '82090', '31432', '85684', '31957', '27569', '20548', '62323', '41693', '95210', '45368', '47401', '61242', '23009', '14623', '93659', '37527', '43437', '87815', '53695', '33103', '10275', '66102', '10474', '69052', '19750', '82239', '82398', '13327', '92443', '91718', '29341', '21148', '88767', '37845', '71503', '73746', '51186', '92847', '76434', '45532', '83567', '36587', '36871', '68343', '22361', '15889', '44500', '57321', '67538', '27349', '92265', '18201', '44895', '46304', '19525', '39262', '76025', '63967', '65876', '68401', '56731', '35298', '71016', '80850', '70677', '63844', '83538', '69391', '75915', '31359', '79854', '33311', '49234', '96937', '63382', '41517', '41869', '24502', '11632', '37356', '79611', '66806', '66759', '97130', '20466', '78988', '52496', '17683', '97622', '62559', '30549', '84660', '19989', '62492', '40707', '74725', '39817', '49985', '66794', '89516', '42610', '75790', '69944', '60133', '41210', '49817', '71453', '85182', '19057', '50233', '97569', '14079', '37311', '18200', '67370', '91890', '22600', '84393', '10745', '99998', '45235', '99083', '70557', '24595', '21450', '26360', '25733', '56096', '69265', '49299', '46486', '55238', '88375', '87114', '14521', '10064', '80051', '34214', '69212', '37480', '19180', '16894', '89639', '74592', '25813', '89781', '78026', '56162', '32374', '37815', '40242', '51243', '22328', '60574', '94960', '54236', '86036', '19877', '64137', '92675', '99663', '53233', '28599', '15474', '39283', '75953', '91784', '86565', '61837', '40274', '24820', '11216', '13099', '50860', '94541', '52320', '48625', '56051', '66108', '20669', '34569', '78953', '90917', '15732', '36937', '45971' ) AND channel = 'store' INTERSECT SELECT ca_zip FROM ( SELECT SUBSTRING(bill_addr_ca_zip FROM 1 FOR 5) AS ca_zip, COUNT(*) AS cnt FROM tpcds_sales_returns_obt AS obt WHERE ( obt.bill_addr_ca_address_sk = obt.bill_customer_c_current_addr_sk AND obt.bill_customer_c_preferred_cust_flag = 'Y' ) AND channel = 'store' GROUP BY obt.bill_addr_ca_zip HAVING COUNT(*) > 10 ) AS A1 ) AS A2) AS V1WHERE ( store_sk = store_s_store_sk AND sold_date_sk = sold_date_d_date_sk AND sold_date_d_qoy = 1 AND sold_date_d_year = 1998 AND ( SUBSTRING(store_s_zip FROM 1 FOR 2) = SUBSTRING(V1.ca_zip FROM 1 FOR 2) ) ) AND channel = 'store'GROUP BY store_s_store_nameORDER BY store_s_store_nameLIMIT 100Representative 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 resultGet 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 factorqueries = bench.get_queries(dialect="datafusion") # your platform's dialect; drop dialect= for native SQLprint(queries['8'])For the DataFrame form:
from benchbox.core.query_catalog import get_dataframe_render
print(get_dataframe_render('tpcds_obt', '8', family="expression").source) # or family="pandas"Source
Query text: benchbox/core/tpcds_obt/queries.py