asterix-app/src/test/resources/optimizerts/queries/q1_pricing_summary_report_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: double, 
  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 dataset LineItem(LineItemType)
  partitioned by key l_orderkey, l_linenumber;

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;

write output to nc1:"rttest/tpch_q1_pricing_summary_report_nt.adm";
 
for $l in dataset('LineItem')
where $l.l_shipdate <= '1998-09-02'
/*+ hash*/
group by $l_returnflag := $l.l_returnflag, $l_linestatus := $l.l_linestatus  
  with $l
order by $l_returnflag, $l_linestatus
return {
  "l_returnflag": $l_returnflag,
  "l_linestatus": $l_linestatus,
  "sum_qty": sum(for $i in $l return $i.l_quantity),
  "sum_base_price": sum(for $i in $l return $i.l_extendedprice),
  "sum_disc_price": sum(for $i in $l return $i.l_extendedprice * (1 - $i.l_discount)),
  "sum_charge": sum(for $i in $l return $i.l_extendedprice * (1 - $i.l_discount) * (1 + $i.l_tax)),
  "ave_qty": avg(for $i in $l return $i.l_quantity),  
  "ave_price": avg(for $i in $l return $i.l_extendedprice),
  "ave_disc": avg(for $i in $l return $i.l_discount),
  "count_order": count($l)
}