TPC-DS-OBT Q66

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

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  warehouse_w_warehouse_name,
  warehouse_w_warehouse_sq_ft,
  warehouse_w_city,
  warehouse_w_county,
  warehouse_w_state,
  warehouse_w_country,
  ship_carriers,
  year,
  SUM(jan_sales) AS jan_sales,
  SUM(feb_sales) AS feb_sales,
  SUM(mar_sales) AS mar_sales,
  SUM(apr_sales) AS apr_sales,
  SUM(may_sales) AS may_sales,
  SUM(jun_sales) AS jun_sales,
  SUM(jul_sales) AS jul_sales,
  SUM(aug_sales) AS aug_sales,
  SUM(sep_sales) AS sep_sales,
  SUM(oct_sales) AS oct_sales,
  SUM(nov_sales) AS nov_sales,
  SUM(dec_sales) AS dec_sales,
  SUM(CAST(jan_sales AS DOUBLE PRECISION) / warehouse_w_warehouse_sq_ft) AS jan_sales_per_sq_foot,
  SUM(CAST(feb_sales AS DOUBLE PRECISION) / warehouse_w_warehouse_sq_ft) AS feb_sales_per_sq_foot,
  SUM(CAST(mar_sales AS DOUBLE PRECISION) / warehouse_w_warehouse_sq_ft) AS mar_sales_per_sq_foot,
  SUM(CAST(apr_sales AS DOUBLE PRECISION) / warehouse_w_warehouse_sq_ft) AS apr_sales_per_sq_foot,
  SUM(CAST(may_sales AS DOUBLE PRECISION) / warehouse_w_warehouse_sq_ft) AS may_sales_per_sq_foot,
  SUM(CAST(jun_sales AS DOUBLE PRECISION) / warehouse_w_warehouse_sq_ft) AS jun_sales_per_sq_foot,
  SUM(CAST(jul_sales AS DOUBLE PRECISION) / warehouse_w_warehouse_sq_ft) AS jul_sales_per_sq_foot,
  SUM(CAST(aug_sales AS DOUBLE PRECISION) / warehouse_w_warehouse_sq_ft) AS aug_sales_per_sq_foot,
  SUM(CAST(sep_sales AS DOUBLE PRECISION) / warehouse_w_warehouse_sq_ft) AS sep_sales_per_sq_foot,
  SUM(CAST(oct_sales AS DOUBLE PRECISION) / warehouse_w_warehouse_sq_ft) AS oct_sales_per_sq_foot,
  SUM(CAST(nov_sales AS DOUBLE PRECISION) / warehouse_w_warehouse_sq_ft) AS nov_sales_per_sq_foot,
  SUM(CAST(dec_sales AS DOUBLE PRECISION) / warehouse_w_warehouse_sq_ft) AS dec_sales_per_sq_foot,
  SUM(jan_net) AS jan_net,
  SUM(feb_net) AS feb_net,
  SUM(mar_net) AS mar_net,
  SUM(apr_net) AS apr_net,
  SUM(may_net) AS may_net,
  SUM(jun_net) AS jun_net,
  SUM(jul_net) AS jul_net,
  SUM(aug_net) AS aug_net,
  SUM(sep_net) AS sep_net,
  SUM(oct_net) AS oct_net,
  SUM(nov_net) AS nov_net,
  SUM(dec_net) AS dec_net
FROM (
  SELECT
    obt.warehouse_w_warehouse_name,
    obt.warehouse_w_warehouse_sq_ft,
    obt.warehouse_w_city,
    obt.warehouse_w_county,
    obt.warehouse_w_state,
    obt.warehouse_w_country,
    '1' || ',' || '1' AS ship_carriers,
    obt.sold_date_d_year AS year,
    SUM(CASE WHEN obt.sold_date_d_moy = 1 THEN sales_price * obt.quantity ELSE 0 END) AS jan_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 2 THEN sales_price * obt.quantity ELSE 0 END) AS feb_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 3 THEN sales_price * obt.quantity ELSE 0 END) AS mar_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 4 THEN sales_price * obt.quantity ELSE 0 END) AS apr_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 5 THEN sales_price * obt.quantity ELSE 0 END) AS may_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 6 THEN sales_price * obt.quantity ELSE 0 END) AS jun_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 7 THEN sales_price * obt.quantity ELSE 0 END) AS jul_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 8 THEN sales_price * obt.quantity ELSE 0 END) AS aug_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 9 THEN sales_price * obt.quantity ELSE 0 END) AS sep_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 10 THEN sales_price * obt.quantity ELSE 0 END) AS oct_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 11 THEN sales_price * obt.quantity ELSE 0 END) AS nov_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 12 THEN sales_price * obt.quantity ELSE 0 END) AS dec_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 1 THEN net_paid * obt.quantity ELSE 0 END) AS jan_net,
    SUM(CASE WHEN obt.sold_date_d_moy = 2 THEN net_paid * obt.quantity ELSE 0 END) AS feb_net,
    SUM(CASE WHEN obt.sold_date_d_moy = 3 THEN net_paid * obt.quantity ELSE 0 END) AS mar_net,
    SUM(CASE WHEN obt.sold_date_d_moy = 4 THEN net_paid * obt.quantity ELSE 0 END) AS apr_net,
    SUM(CASE WHEN obt.sold_date_d_moy = 5 THEN net_paid * obt.quantity ELSE 0 END) AS may_net,
    SUM(CASE WHEN obt.sold_date_d_moy = 6 THEN net_paid * obt.quantity ELSE 0 END) AS jun_net,
    SUM(CASE WHEN obt.sold_date_d_moy = 7 THEN net_paid * obt.quantity ELSE 0 END) AS jul_net,
    SUM(CASE WHEN obt.sold_date_d_moy = 8 THEN net_paid * obt.quantity ELSE 0 END) AS aug_net,
    SUM(CASE WHEN obt.sold_date_d_moy = 9 THEN net_paid * obt.quantity ELSE 0 END) AS sep_net,
    SUM(CASE WHEN obt.sold_date_d_moy = 10 THEN net_paid * obt.quantity ELSE 0 END) AS oct_net,
    SUM(CASE WHEN obt.sold_date_d_moy = 11 THEN net_paid * obt.quantity ELSE 0 END) AS nov_net,
    SUM(CASE WHEN obt.sold_date_d_moy = 12 THEN net_paid * obt.quantity ELSE 0 END) AS dec_net
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      obt.warehouse_sk = obt.warehouse_w_warehouse_sk
      AND obt.sold_date_sk = obt.sold_date_d_date_sk
      AND obt.sold_time_sk = sold_time_t_time_sk
      AND obt.ship_mode_sk = obt.ship_mode_sm_ship_mode_sk
      AND obt.sold_date_d_year = 1998
      AND sold_time_t_time BETWEEN 1 AND 1 + 28800
      AND obt.ship_mode_sm_carrier IN ('1', '1')
    )
    AND channel = 'web'
  GROUP BY
    obt.warehouse_w_warehouse_name,
    obt.warehouse_w_warehouse_sq_ft,
    obt.warehouse_w_city,
    obt.warehouse_w_county,
    obt.warehouse_w_state,
    obt.warehouse_w_country,
    obt.sold_date_d_year
  UNION ALL
  SELECT
    obt.warehouse_w_warehouse_name,
    obt.warehouse_w_warehouse_sq_ft,
    obt.warehouse_w_city,
    obt.warehouse_w_county,
    obt.warehouse_w_state,
    obt.warehouse_w_country,
    '1' || ',' || '1' AS ship_carriers,
    obt.sold_date_d_year AS year,
    SUM(CASE WHEN obt.sold_date_d_moy = 1 THEN sales_price * obt.quantity ELSE 0 END) AS jan_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 2 THEN sales_price * obt.quantity ELSE 0 END) AS feb_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 3 THEN sales_price * obt.quantity ELSE 0 END) AS mar_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 4 THEN sales_price * obt.quantity ELSE 0 END) AS apr_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 5 THEN sales_price * obt.quantity ELSE 0 END) AS may_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 6 THEN sales_price * obt.quantity ELSE 0 END) AS jun_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 7 THEN sales_price * obt.quantity ELSE 0 END) AS jul_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 8 THEN sales_price * obt.quantity ELSE 0 END) AS aug_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 9 THEN sales_price * obt.quantity ELSE 0 END) AS sep_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 10 THEN sales_price * obt.quantity ELSE 0 END) AS oct_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 11 THEN sales_price * obt.quantity ELSE 0 END) AS nov_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 12 THEN sales_price * obt.quantity ELSE 0 END) AS dec_sales,
    SUM(CASE WHEN obt.sold_date_d_moy = 1 THEN net_paid * obt.quantity ELSE 0 END) AS jan_net,
    SUM(CASE WHEN obt.sold_date_d_moy = 2 THEN net_paid * obt.quantity ELSE 0 END) AS feb_net,
    SUM(CASE WHEN obt.sold_date_d_moy = 3 THEN net_paid * obt.quantity ELSE 0 END) AS mar_net,
    SUM(CASE WHEN obt.sold_date_d_moy = 4 THEN net_paid * obt.quantity ELSE 0 END) AS apr_net,
    SUM(CASE WHEN obt.sold_date_d_moy = 5 THEN net_paid * obt.quantity ELSE 0 END) AS may_net,
    SUM(CASE WHEN obt.sold_date_d_moy = 6 THEN net_paid * obt.quantity ELSE 0 END) AS jun_net,
    SUM(CASE WHEN obt.sold_date_d_moy = 7 THEN net_paid * obt.quantity ELSE 0 END) AS jul_net,
    SUM(CASE WHEN obt.sold_date_d_moy = 8 THEN net_paid * obt.quantity ELSE 0 END) AS aug_net,
    SUM(CASE WHEN obt.sold_date_d_moy = 9 THEN net_paid * obt.quantity ELSE 0 END) AS sep_net,
    SUM(CASE WHEN obt.sold_date_d_moy = 10 THEN net_paid * obt.quantity ELSE 0 END) AS oct_net,
    SUM(CASE WHEN obt.sold_date_d_moy = 11 THEN net_paid * obt.quantity ELSE 0 END) AS nov_net,
    SUM(CASE WHEN obt.sold_date_d_moy = 12 THEN net_paid * obt.quantity ELSE 0 END) AS dec_net
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      obt.warehouse_sk = obt.warehouse_w_warehouse_sk
      AND obt.sold_date_sk = obt.sold_date_d_date_sk
      AND obt.sold_time_sk = sold_time_t_time_sk
      AND obt.ship_mode_sk = obt.ship_mode_sm_ship_mode_sk
      AND obt.sold_date_d_year = 1998
      AND sold_time_t_time BETWEEN 1 AND 1 + 28800
      AND obt.ship_mode_sm_carrier IN ('1', '1')
    )
    AND channel = 'catalog'
  GROUP BY
    obt.warehouse_w_warehouse_name,
    obt.warehouse_w_warehouse_sq_ft,
    obt.warehouse_w_city,
    obt.warehouse_w_county,
    obt.warehouse_w_state,
    obt.warehouse_w_country,
    obt.sold_date_d_year
) AS x
GROUP BY
  warehouse_w_warehouse_name,
  warehouse_w_warehouse_sq_ft,
  warehouse_w_city,
  warehouse_w_county,
  warehouse_w_state,
  warehouse_w_country,
  ship_carriers,
  year
ORDER BY
  warehouse_w_warehouse_name
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['66'])

Source

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