TPC-Havoc Q8_v1

TPC-Havoc benchmark · all TPC-Havoc queries

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  o_year,
  (
    SELECT
      SUM(CASE WHEN nation = 'BRAZIL' THEN volume ELSE 0 END)
    FROM (
      SELECT
        EXTRACT(YEAR FROM o2.o_orderdate) AS o_year2,
        l2.l_extendedprice * (
          1 - l2.l_discount
        ) AS volume,
        n3.n_name AS nation
      FROM part AS p2, supplier AS s2, lineitem AS l2, orders AS o2, customer AS c2, nation AS n2, nation AS n3, region AS r2
      WHERE
        p2.p_partkey = l2.l_partkey
        AND s2.s_suppkey = l2.l_suppkey
        AND l2.l_orderkey = o2.o_orderkey
        AND o2.o_custkey = c2.c_custkey
        AND c2.c_nationkey = n2.n_nationkey
        AND n2.n_regionkey = r2.r_regionkey
        AND r2.r_name = 'AMERICA'
        AND s2.s_nationkey = n3.n_nationkey
        AND o2.o_orderdate BETWEEN CAST('1995-01-01' AS DATE) AND CAST('1996-12-31' AS DATE)
        AND p2.p_type = 'ECONOMY ANODIZED STEEL'
        AND EXTRACT(YEAR FROM o2.o_orderdate) = all_nations.o_year
    ) AS subq
  ) / (
    SELECT
      SUM(volume)
    FROM (
      SELECT
        EXTRACT(YEAR FROM o3.o_orderdate) AS o_year3,
        l3.l_extendedprice * (
          1 - l3.l_discount
        ) AS volume
      FROM part AS p3, supplier AS s3, lineitem AS l3, orders AS o3, customer AS c3, nation AS n4, nation AS n5, region AS r3
      WHERE
        p3.p_partkey = l3.l_partkey
        AND s3.s_suppkey = l3.l_suppkey
        AND l3.l_orderkey = o3.o_orderkey
        AND o3.o_custkey = c3.c_custkey
        AND c3.c_nationkey = n4.n_nationkey
        AND n4.n_regionkey = r3.r_regionkey
        AND r3.r_name = 'AMERICA'
        AND s3.s_nationkey = n5.n_nationkey
        AND o3.o_orderdate BETWEEN CAST('1995-01-01' AS DATE) AND CAST('1996-12-31' AS DATE)
        AND p3.p_type = 'ECONOMY ANODIZED STEEL'
        AND EXTRACT(YEAR FROM o3.o_orderdate) = all_nations.o_year
    ) AS subq2
  ) AS mkt_share
FROM (
  SELECT DISTINCT
    EXTRACT(YEAR FROM o_orderdate) AS o_year
  FROM part, supplier, lineitem, orders, customer, nation AS n1, nation AS n2, region
  WHERE
    p_partkey = l_partkey
    AND s_suppkey = l_suppkey
    AND l_orderkey = o_orderkey
    AND o_custkey = c_custkey
    AND c_nationkey = n1.n_nationkey
    AND n1.n_regionkey = r_regionkey
    AND r_name = 'AMERICA'
    AND s_nationkey = n2.n_nationkey
    AND o_orderdate BETWEEN CAST('1995-01-01' AS DATE) AND CAST('1996-12-31' AS DATE)
    AND p_type = 'ECONOMY ANODIZED STEEL'
) AS all_nations
GROUP BY
  o_year
ORDER BY
  o_year

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 TPCHavoc

bench = TPCHavoc(scale_factor=0.01)            # your scale factor
queries = bench.get_queries(dialect="datafusion")   # your platform's dialect; drop dialect= for native SQL
print(queries['8_v1'])

Source

Query text: benchbox/core/tpchavoc/variant_sets/q08.py