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/tpch100/q17_small_quantity_order_revenue.hive b/resource/tpch100/q17_small_quantity_order_revenue.hive
new file mode 100644
index 0000000..14e87db
--- /dev/null
+++ b/resource/tpch100/q17_small_quantity_order_revenue.hive
@@ -0,0 +1,38 @@
+-- create the tables and load the data
+create external table lineitem (L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, 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) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/100/lineitem';
+create external table part (P_PARTKEY INT, P_NAME STRING, P_MFGR STRING, P_BRAND STRING, P_TYPE STRING, P_SIZE INT, P_CONTAINER STRING, P_RETAILPRICE DOUBLE, P_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/100/part';
+
+-- create the result table
+create table q17_small_quantity_order_revenue (avg_yearly double);
+create table lineitem_tmp (t_partkey int, t_avg_quantity double);
+
+-- the query
+insert overwrite table lineitem_tmp
+select
+ l_partkey as t_partkey, 0.2 * avg(l_quantity) as t_avg_quantity
+from
+ lineitem
+group by l_partkey;
+
+insert overwrite table q17_small_quantity_order_revenue
+select
+ sum(l_extendedprice) / 7.0 as avg_yearly
+from
+ (select l_quantity, l_extendedprice, t_avg_quantity from
+ lineitem_tmp t join
+ (select
+ l_quantity, l_partkey, l_extendedprice
+ from
+ part p join lineitem l
+ on
+ p.p_partkey = l.l_partkey
+ and p.p_brand = 'Brand#23'
+ and p.p_container = 'MED BOX'
+ ) l1 on l1.l_partkey = t.t_partkey
+ ) a
+where l_quantity < t_avg_quantity;
+
+DROP TABLE lineitem;
+DROP TABLE part;
+DROP TABLE q17_small_quantity_order_revenue;
+DROP TABLE lineitem_tmp;