| drop dataverse loj-super-key_02 if exists; |
| |
| create dataverse loj-super-key_02; |
| |
| use dataverse loj-super-key_02; |
| |
| 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 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 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; |
| |
| write output to nc1:"/tmp/loj-super-key_01.adm"; |
| |
| create dataset LineItems(LineItemType) |
| partitioned by key l_partkey, l_linenumber on group1; |
| create dataset PartSupp(PartSuppType) |
| partitioned by key ps_partkey, ps_suppkey on group1; |
| |
| |
| for $ps in dataset('PartSupp') |
| let $items := |
| for $li in dataset('LineItems') |
| where $li.l_partkey = $ps.ps_partkey |
| and $li.l_suppkey = $ps.ps_suppkey |
| and $li.l_extendedprice = $ps.ps_supplycost |
| return $li |
| return { |
| "partsupp": $ps, |
| "li": $items |
| } |
| |
| |