move hivesterix codebase into hyracks fullstack
git-svn-id: https://hyracks.googlecode.com/svn/branches/fullstack_staging@2420 123451ca-8445-de46-9d55-352943316053
diff --git a/resource/tpch/q22_global_sales_opportunity.hive b/resource/tpch/q22_global_sales_opportunity.hive
new file mode 100644
index 0000000..379cfc9
--- /dev/null
+++ b/resource/tpch/q22_global_sales_opportunity.hive
@@ -0,0 +1,70 @@
+DROP TABLE customer;
+DROP TABLE orders;
+DROP TABLE q22_customer_tmp;
+DROP TABLE q22_customer_tmp1;
+DROP TABLE q22_orders_tmp;
+DROP TABLE q22_global_sales_opportunity;
+
+-- create tables and load data
+create external table customer (C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/customer';
+create external table orders (O_ORDERKEY INT, O_CUSTKEY INT, O_ORDERSTATUS STRING, O_TOTALPRICE DOUBLE, O_ORDERDATE STRING, O_ORDERPRIORITY STRING, O_CLERK STRING, O_SHIPPRIORITY INT, O_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/orders';
+
+-- create target tables
+create table q22_customer_tmp(c_acctbal double, c_custkey int, cntrycode string);
+create table q22_customer_tmp1(avg_acctbal double);
+create table q22_orders_tmp(o_custkey int);
+create table q22_global_sales_opportunity(cntrycode string, numcust int, totacctbal double);
+
+-- the query
+insert overwrite table q22_customer_tmp
+select
+ c_acctbal, c_custkey, substr(c_phone, 1, 2) as cntrycode
+from
+ customer
+where
+ substr(c_phone, 1, 2) = '13' or
+ substr(c_phone, 1, 2) = '31' or
+ substr(c_phone, 1, 2) = '23' or
+ substr(c_phone, 1, 2) = '29' or
+ substr(c_phone, 1, 2) = '30' or
+ substr(c_phone, 1, 2) = '18' or
+ substr(c_phone, 1, 2) = '17';
+
+insert overwrite table q22_customer_tmp1
+select
+ avg(c_acctbal)
+from
+ q22_customer_tmp
+where
+ c_acctbal > 0.00;
+
+insert overwrite table q22_orders_tmp
+select
+ o_custkey
+from
+ orders
+group by
+ o_custkey;
+
+insert overwrite table q22_global_sales_opportunity
+select
+ cntrycode, count(1) as numcust, sum(c_acctbal) as totacctbal
+from
+(
+ select cntrycode, c_acctbal, avg_acctbal from
+ q22_customer_tmp1 ct1 join
+ (
+ select cntrycode, c_acctbal from
+ q22_orders_tmp ot
+ right outer join q22_customer_tmp ct
+ on
+ ct.c_custkey = ot.o_custkey
+ where
+ o_custkey is null
+ ) ct2
+) a
+where
+ c_acctbal > avg_acctbal
+group by cntrycode
+order by cntrycode;
+