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/q8_national_market_share.hive b/resource/tpch100/q8_national_market_share.hive
new file mode 100644
index 0000000..72d8b69
--- /dev/null
+++ b/resource/tpch100/q8_national_market_share.hive
@@ -0,0 +1,55 @@
+-- create the tables and load the data
+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 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/100/customer';
+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 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/100/orders';
+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/100/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/100/nation';
+create external table region (R_REGIONKEY INT, R_NAME STRING, R_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/100/region';
+
+-- create the result table
+create table q8_national_market_share(o_year string, mkt_share double);
+
+-- the query
+insert overwrite table q8_national_market_share
+select
+ o_year, sum(case when nation = 'BRAZIL' then volume else 0.0 end) / sum(volume) as mkt_share
+from
+ (
+select
+ year(o_orderdate) as o_year, l_extendedprice * (1-l_discount) as volume,
+ n2.n_name as nation
+ from
+ nation n2 join
+ (select o_orderdate, l_discount, l_extendedprice, s_nationkey
+ from supplier s join
+ (select o_orderdate, l_discount, l_extendedprice, l_suppkey
+ from part p join
+ (select o_orderdate, l_partkey, l_discount, l_extendedprice, l_suppkey
+ from lineitem l join
+ (select o_orderdate, o_orderkey
+ from orders o join
+ (select c.c_custkey
+ from customer c join
+ (select n1.n_nationkey
+ from nation n1 join region r
+ on n1.n_regionkey = r.r_regionkey and r.r_name = 'AMERICA'
+ ) n11 on c.c_nationkey = n11.n_nationkey
+ ) c1 on c1.c_custkey = o.o_custkey
+ ) o1 on l.l_orderkey = o1.o_orderkey and o1.o_orderdate >= '1995-01-01'
+ and o1.o_orderdate < '1996-12-31'
+ ) l1 on p.p_partkey = l1.l_partkey and p.p_type = 'ECONOMY ANODIZED STEEL'
+ ) p1 on s.s_suppkey = p1.l_suppkey
+ ) s1 on s1.s_nationkey = n2.n_nationkey
+ ) all_nation
+group by o_year
+order by o_year;
+
+DROP TABLE customer;
+DROP TABLE orders;
+DROP TABLE lineitem;
+DROP TABLE supplier;
+DROP TABLE nation;
+DROP TABLE region;
+DROP TABLE part;
+DROP TABLE q8_national_market_share;