HeatWave User Guide  /  ...  /  Additional tpch Queries

7.1.6 Additional tpch Queries

This topic provides additional tpch queries that you can run to test the HeatWave Cluster.

  • TPCH-Q1: Pricing Summary Report Query

    As described in the TPC Benchmark™ H (TPC-H) specification: "The Pricing Summary Report Query provides a summary pricing report for all lineitems shipped as of a given date. The date is within 60 - 120 days of the greatest ship date contained in the database. The query lists totals for extended price, discounted extended price, discounted extended price plus tax, average quantity, average extended price, and average discount. These aggregates are grouped by RETURNFLAG and LINESTATUS, and listed in ascending order of RETURNFLAG and LINESTATUS. A count of the number of lineitems in each group is included."

    mysql> SELECT
              l_returnflag,
              l_linestatus,
              SUM(l_quantity) AS sum_qty,
              SUM(l_extendedprice) AS sum_base_price,
              SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
              SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
              AVG(l_quantity) AS avg_qty,
              AVG(l_extendedprice) AS avg_price,
              AVG(l_discount) AS avg_disc,
              COUNT(*) AS count_order
          FROM
              lineitem
          WHERE
              l_shipdate <= DATE '1998-12-01' - INTERVAL '90' DAY
          GROUP BY l_returnflag , l_linestatus
          ORDER BY l_returnflag , l_linestatus;
  • TPCH-Q3: Shipping Priority Query

    As described in the TPC Benchmark™ H (TPC-H) specification: "The Shipping Priority Query retrieves the shipping priority and potential revenue, defined as the sum of l_extendedprice * (1-l_discount), of the orders having the largest revenue among those that had not been shipped as of a given date. Orders are listed in decreasing order of revenue. If more than 10 unshipped orders exist, only the 10 orders with the largest revenue are listed."

    mysql> SELECT
              l_orderkey,
              SUM(l_extendedprice * (1 - l_discount)) AS revenue,
              o_orderdate,
              o_shippriority
          FROM
              customer,
              orders,
              lineitem
          WHERE
              c_mktsegment = 'BUILDING'
                  AND c_custkey = o_custkey
                  AND l_orderkey = o_orderkey
                  AND o_orderdate < DATE '1995-03-15'
                  AND l_shipdate > DATE '1995-03-15'
          GROUP BY l_orderkey , o_orderdate , o_shippriority
          ORDER BY revenue DESC , o_orderdate
          LIMIT 10;
  • TPCH-Q9: Product Type Profit Measure Query

    As described in the TPC Benchmark™ H (TPC-H) specification: "The Product Type Profit Measure Query finds, for each nation and each year, the profit for all parts ordered in that year that contain a specified substring in their names and that were filled by a supplier in that nation. The profit is defined as the sum of [(l_extendedprice*(1-l_discount)) - (ps_supplycost * l_quantity)] for all lineitems describing parts in the specified line. The query lists the nations in ascending alphabetical order and, for each nation, the year and profit in descending order by year (most recent first). "

    mysql> SELECT
              nation, o_year, SUM(amount) AS sum_profit
          FROM
              (SELECT
                  n_name AS nation,
                      YEAR(o_ORDERdate) AS o_year,
                      l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
              FROM
                  part
              STRAIGHT_JOIN partsupp
              STRAIGHT_JOIN lineitem
              STRAIGHT_JOIN supplier
              STRAIGHT_JOIN orders
              STRAIGHT_JOIN nation
              WHERE
                  s_suppkey = l_suppkey
                      AND ps_suppkey = l_suppkey
                      AND ps_partkey = l_partkey
                      AND p_partkey = l_partkey
                      AND o_ORDERkey = l_ORDERkey
                      AND s_nationkey = n_nationkey
                      AND p_name LIKE '%green%') AS profit
          GROUP BY nation , o_year
          ORDER BY nation , o_year DESC;