TPC-Havoc Q15_v4

TPC-Havoc benchmark · all TPC-Havoc queries

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  s_suppkey,
  s_name,
  s_address,
  s_phone,
  total_revenue
FROM (
  SELECT
    s_suppkey,
    s_name,
    s_address,
    s_phone,
    COALESCE(rev_data.total_revenue, 0) AS total_revenue,
    1 AS result_group
  FROM supplier
  LEFT JOIN (
    SELECT
      l_suppkey,
      SUM(l_extendedprice * (
        1 - l_discount
      )) AS total_revenue
    FROM lineitem
    WHERE
      l_shipdate >= CAST('1996-01-01' AS DATE)
      AND l_shipdate < CAST('1996-01-01' AS DATE) + INTERVAL '3 MONTH'
    GROUP BY
      l_suppkey
  ) AS rev_data
    ON s_suppkey = rev_data.l_suppkey
  WHERE
    COALESCE(rev_data.total_revenue, 0) = (
      SELECT
        MAX(total_revenue)
      FROM (
        SELECT
          l_suppkey,
          SUM(l_extendedprice * (
            1 - l_discount
          )) AS total_revenue
        FROM lineitem
        WHERE
          l_shipdate >= CAST('1996-01-01' AS DATE)
          AND l_shipdate < CAST('1996-01-01' AS DATE) + INTERVAL '3 MONTH'
        GROUP BY
          l_suppkey
      ) AS max_calc
    )
  UNION ALL
  SELECT
    s_suppkey,
    s_name,
    s_address,
    s_phone,
    0 AS total_revenue,
    2 AS result_group
  FROM supplier
  WHERE
    FALSE
) AS combined_results
WHERE
  result_group = 1
ORDER BY
  s_suppkey

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

Source

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