pc.org/tpch/ .This implementation is tested with the DB2 data format." This PACT program implements the query 9 of the TPC-H benchmark:
select nation, o_year, sum(amount) as sum_profit from ( select n_name as nation, extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount from part, supplier, lineitem, partsupp, orders, 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 '%[COLOR]%' ) as profit group by nation, o_year order by nation, o_year desc;
Plan:
Match "part" and "partsupp" on "partkey" -> "parts" with (partkey, suppkey) as key Match "orders" and "lineitem" on "orderkey" -> "ordered_parts" with (partkey, suppkey) as key Match "parts" and "ordered_parts" on (partkey, suppkey) -> "filtered_parts" with "suppkey" as key Match "supplier" and "nation" on "nationkey" -> "suppliers" with "suppkey" as key Match "filtered_parts" and "suppliers" on" suppkey" -> "partlist" with (nation, o_year) as key Group "partlist" by (nation, o_year), calculate sum(amount)
Attention: The "order by" part is not implemented!