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/hivesterix/resource/tpch/q11_important_stock.hive b/hivesterix/resource/tpch/q11_important_stock.hive
new file mode 100644
index 0000000..bfa3743
--- /dev/null
+++ b/hivesterix/resource/tpch/q11_important_stock.hive
@@ -0,0 +1,47 @@
+DROP TABLE partsupp;
+DROP TABLE supplier;
+DROP TABLE nation;
+DROP TABLE q11_important_stock;
+DROP TABLE q11_part_tmp;
+DROP TABLE q11_sum_tmp;
+
+-- create tables and load data
+create external table supplier (S_SUPPKEY INT, S_NAME STRING, S_ADDRESS STRING, S_NATIONKEY INT, S_PHONE STRING, S_ACCTBAL DOUBLE, S_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/supplier';
+create external table nation (N_NATIONKEY INT, N_NAME STRING, N_REGIONKEY INT, N_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/nation';
+create external table partsupp (PS_PARTKEY INT, PS_SUPPKEY INT, PS_AVAILQTY INT, PS_SUPPLYCOST DOUBLE, PS_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION'/tpch/10/partsupp';
+
+-- create the target table
+create table q11_important_stock(ps_partkey INT, value DOUBLE);
+create table q11_part_tmp(ps_partkey int, part_value double);
+create table q11_sum_tmp(total_value double);
+
+-- the query
+insert overwrite table q11_part_tmp
+select
+ ps_partkey, sum(ps_supplycost * ps_availqty) as part_value
+from
+ nation n join supplier s
+ on
+ s.s_nationkey = n.n_nationkey and n.n_name = 'GERMANY'
+ join partsupp ps
+ on
+ ps.ps_suppkey = s.s_suppkey
+group by ps_partkey;
+
+insert overwrite table q11_sum_tmp
+select
+ sum(part_value) as total_value
+from
+ q11_part_tmp;
+
+insert overwrite table q11_important_stock
+select
+ ps_partkey, part_value as value
+from
+ (
+ select ps_partkey, part_value, total_value
+ from q11_part_tmp join q11_sum_tmp
+ ) a
+where part_value > total_value * 0.0001
+order by value desc;
+