asterix-app/src/test/resources/runtimets/queries/tpch/q9_product_type_profit_nt.aql

drop dataverse tpch if exists;
create dataverse tpch;
  

use dataverse tpch;

create type LineItemType as closed {
  l_orderkey: int32, 
  l_partkey: int32, 
  l_suppkey: int32, 
  l_linenumber: int32, 
  l_quantity: int32, 
  l_extendedprice: double,
  l_discount: double, 
  l_tax: double,
  l_returnflag: string, 
  l_linestatus: string, 
  l_shipdate: string,
  l_commitdate: string, 
  l_receiptdate: string, 
  l_shipinstruct: string, 
  l_shipmode: string, 
  l_comment: string
}

create type OrderType as closed {
  o_orderkey: int32, 
  o_custkey: int32, 
  o_orderstatus: string, 
  o_totalprice: double, 
  o_orderdate: string, 
  o_orderpriority: string,
  o_clerk: string, 
  o_shippriority: int32, 
  o_comment: string
}

create type CustomerType as closed {
  c_custkey: int32, 
  c_name: string, 
  c_address: string, 
  c_nationkey: int32, 
  c_phone: string, 
  c_acctbal: double, 
  c_mktsegment: string,
  c_comment: string
}

create type SupplierType as closed {
  s_suppkey: int32, 
  s_name: string,
  s_address: string,
  s_nationkey: int32,
  s_phone: string,
  s_acctbal: double,
  s_comment: string
}

create type NationType as closed {
  n_nationkey: int32,
  n_name: string,
  n_regionkey: int32,
  n_comment: string
}

create type RegionType as closed {
	r_regionkey: int32, 
	r_name: string, 
	r_comment: string
} 

create type PartType as closed {
  p_partkey: int32, 
  p_name: string, 
  p_mfgr: string,
  p_brand: string,
  p_type: string,
  p_size: int32,
  p_container: string,
  p_retailprice: double,
  p_comment: string
}

create type PartSuppType as closed {
  ps_partkey: int32, 
  ps_suppkey: int32,
  ps_availqty: int32,
  ps_supplycost: double,
  ps_comment: string 
}

create nodegroup group1  if not exists on nc1, nc2;

create dataset LineItem(LineItemType)
  partitioned by key l_orderkey, l_linenumber on group1;
create dataset Orders(OrderType)
  partitioned by key o_orderkey on group1;
create dataset Supplier(SupplierType)
  partitioned by key s_suppkey on group1;
create dataset Region(RegionType) 
  partitioned by key r_regionkey on group1;
create dataset Nation(NationType) 
  partitioned by key n_nationkey on group1;
create dataset Part(PartType)
  partitioned by key p_partkey on group1;
create dataset PartSupp(PartSuppType)
  partitioned by key ps_partkey, ps_suppkey on group1;  
create dataset Customer(CustomerType) 
  partitioned by key c_custkey on group1;

load dataset LineItem 
using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
(("path"="nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;

load dataset Orders 
using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
(("path"="nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;

load dataset Supplier 
using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
(("path"="nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;

load dataset Region 
using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
(("path"="nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;

load dataset Nation 
using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
(("path"="nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;

load dataset Part 
using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
(("path"="nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;

load dataset PartSupp 
using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
(("path"="nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;

load dataset Customer 
using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
(("path"="nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;

  
write output to nc1:"rttest/tpch_q9_product_type_profit_nt.adm";

                
for $profit in 
(  for $o in dataset('Orders')
   for $l3 in 
   (  for $p in dataset('Part')
      for $l2 in 
      (  for $ps in dataset('PartSupp')
         for $l1 in 
        (  for $s1 in
            (  for $s in dataset('Supplier')
               for $n in dataset('Nation')
               where $n.n_nationkey = $s.s_nationkey
               return 
                 { "s_suppkey": $s.s_suppkey, "n_name": $n.n_name}  )
            for $l in dataset('LineItem')
            where $s1.s_suppkey = $l.l_suppkey       
            return 
              { "l_suppkey": $l.l_suppkey, "l_extendedprice": $l.l_extendedprice, "l_discount": $l.l_discount,
                "l_quantity": $l.l_quantity, "l_partkey": $l.l_partkey, "l_orderkey": $l.l_orderkey, "n_name": $s1.n_name } )
         where $ps.ps_suppkey = $l1.l_suppkey and $ps.ps_partkey = $l1.l_partkey       
         return 
           { "l_extendedprice": $l1.l_extendedprice, "l_discount": $l1.l_discount, "l_quantity": $l1.l_quantity, 
             "l_partkey": $l1.l_partkey, "l_orderkey": $l1.l_orderkey, "n_name": $l1.n_name, "ps_supplycost": $ps.ps_supplycost }  )
      where contains($p.p_name, 'green') and $p.p_partkey = $l2.l_partkey    
      return 
        { "l_extendedprice": $l2.l_extendedprice, "l_discount": $l2.l_discount, "l_quantity": $l2.l_quantity, 
          "l_orderkey": $l2.l_orderkey, "n_name": $l2.n_name, "ps_supplycost": $l2.ps_supplycost  }  )
   where $o.o_orderkey = $l3.l_orderkey
   let $amount := $l3.l_extendedprice * (1 - $l3.l_discount) -  $l3.ps_supplycost * $l3.l_quantity 
   let $o_year := year($o.o_orderdate)
   return 
     { "nation": $l3.n_name, "o_year": $o_year, "amount": $amount }  )
 group by $nation := $profit.nation, $o_year := $profit.o_year with $profit
 order by $nation, $o_year desc
 return 
   { "nation": $nation, 
     "o_year": $o_year, 
      "sum_profit": sum( for $pr in $profit return $pr.amount )  }