| DROP TABLE IF EXISTS orders; |
| DROP TABLE IF EXISTS lineitem; |
| DROP TABLE IF EXISTS supplier; |
| DROP TABLE IF EXISTS nation; |
| DROP TABLE IF EXISTS q21_tmp1; |
| DROP TABLE IF EXISTS q21_tmp2; |
| DROP TABLE IF EXISTS q21_suppliers_who_kept_orders_waiting; |
| |
| -- create tables and load 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/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/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/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/nation'; |
| |
| -- create target tables |
| create table q21_tmp1(l_orderkey int, count_suppkey int, max_suppkey int); |
| create table q21_tmp2(l_orderkey int, count_suppkey int, max_suppkey int); |
| create table q21_suppliers_who_kept_orders_waiting(s_name string, numwait int); |
| |
| -- the query |
| insert overwrite table q21_tmp1 |
| select |
| l_orderkey, count(distinct l_suppkey), max(l_suppkey) as max_suppkey |
| from |
| lineitem |
| group by l_orderkey; |
| |
| insert overwrite table q21_tmp2 |
| select |
| l_orderkey, count(distinct l_suppkey), max(l_suppkey) as max_suppkey |
| from |
| lineitem |
| where |
| l_receiptdate > l_commitdate |
| group by l_orderkey; |
| |
| insert overwrite table q21_suppliers_who_kept_orders_waiting |
| select |
| s_name, count(1) as numwait |
| from |
| (select s_name from |
| (select s_name, t2.l_orderkey, l_suppkey, count_suppkey, max_suppkey |
| from q21_tmp2 t2 right outer join |
| (select s_name, l_orderkey, l_suppkey from |
| (select s_name, t1.l_orderkey, l_suppkey, count_suppkey, max_suppkey |
| from |
| q21_tmp1 t1 join |
| (select s_name, l_orderkey, l_suppkey |
| from |
| orders o join |
| (select s_name, l_orderkey, l_suppkey |
| from |
| nation n join supplier s |
| on |
| s.s_nationkey = n.n_nationkey |
| join lineitem l |
| on |
| s.s_suppkey = l.l_suppkey |
| where |
| l.l_receiptdate > l.l_commitdate |
| ) l1 on o.o_orderkey = l1.l_orderkey |
| ) l2 on l2.l_orderkey = t1.l_orderkey |
| ) a |
| where |
| (count_suppkey >= 0) |
| ) l3 on l3.l_orderkey = t2.l_orderkey |
| ) b |
| )c |
| group by s_name |
| order by numwait desc, s_name |
| limit 100; |