全部产品
云市场

创建TPC-H表

更新时间:2019-11-25 15:45:37

创建好TPC-H外表后,需要在AnalyticDB for MySQL中创建TPC-H表。

  1. CREATE TABLE nation(n_nationkey int NOT NULL COMMENT'',
  2. n_name varchar NOT NULL COMMENT'',
  3. n_regionkey int NOT NULL COMMENT'',
  4. n_comment varchar COMMENT'',
  5. dummy varchar
  6. )DISTRIBUTED BY BROADCAST;
  1. CREATE TABLE region(r_regionkey int NOT NULL COMMENT'',
  2. r_name varchar NOT NULL COMMENT'',
  3. r_comment varchar COMMENT'',
  4. dummy varchar
  5. )DISTRIBUTED BY BROADCAST;
  1. CREATE TABLE part(p_partkey int NOT NULL COMMENT'',
  2. p_name varchar NOT NULL COMMENT'',
  3. p_mfgr varchar NOT NULL COMMENT'',
  4. p_brand varchar NOT NULL COMMENT'',
  5. p_type varchar NOT NULL COMMENT'',
  6. p_size int NOT NULL COMMENT'',
  7. p_container varchar NOT NULL COMMENT'',
  8. p_retailprice decimal(15,2) NOT NULL COMMENT'',
  9. p_comment varchar NOT NULL COMMENT'',
  10. dummy varchar
  11. )DISTRIBUTED BY HASH(p_partkey);
  1. CREATE TABLE supplier(s_suppkey int NOT NULL COMMENT'',
  2. s_name varchar NOT NULL COMMENT'',
  3. s_address varchar NOT NULL COMMENT'',
  4. s_nationkey int NOT NULL COMMENT'',
  5. s_phone varchar NOT NULL COMMENT'',
  6. s_acctbal decimal(15,2) NOT NULL COMMENT'',
  7. s_comment varchar NOT NULL COMMENT'',
  8. dummy varchar
  9. )DISTRIBUTED BY HASH(s_suppkey);
  1. CREATE TABLE partsupp(ps_partkey int NOT NULL COMMENT'',
  2. ps_suppkey int NOT NULL COMMENT'',
  3. ps_availqty int NOT NULL COMMENT'',
  4. ps_supplycost decimal(15,2) NOT NULL COMMENT'',
  5. ps_comment varchar NOT NULL COMMENT'',
  6. dummy varchar
  7. )DISTRIBUTED BY HASH(ps_partkey);
  1. CREATE TABLE customer(c_custkey int NOT NULL COMMENT'',
  2. c_name varchar NOT NULL COMMENT'',
  3. c_address varchar NOT NULL COMMENT'',
  4. c_nationkey int NOT NULL COMMENT'',
  5. c_phone varchar NOT NULL COMMENT'',
  6. c_acctbal decimal(15,2) NOT NULL COMMENT'',
  7. c_mktsegment varchar NOT NULL COMMENT'',
  8. c_comment varchar NOT NULL COMMENT'',
  9. dummy varchar
  10. )DISTRIBUTED BY HASH(c_custkey);
  1. CREATE TABLE orders(o_orderkey bigint NOT NULL COMMENT'',
  2. o_custkey int NOT NULL COMMENT'',
  3. o_orderstatus varchar NOT NULL COMMENT'',
  4. o_totalprice decimal(15,2) NOT NULL COMMENT'',
  5. o_orderdate date NOT NULL COMMENT'',
  6. o_orderpriority varchar NOT NULL COMMENT'',
  7. o_clerkvar char NOT NULL COMMENT'',
  8. o_shippriority int NOT NULL COMMENT'',
  9. o_comment varchar NOT NULL COMMENT'',
  10. dummy varchar
  11. )DISTRIBUTED BY HASH(o_orderkey);
  1. CREATE TABLE lineitem(l_orderkey bigint NOT NULL COMMENT'',
  2. l_partkey int NOT NULL COMMENT'',
  3. l_suppkey int NOT NULL COMMENT'',
  4. l_linenumber int NOT NULL COMMENT'',
  5. l_quantity decimal(15,2) NOT NULL COMMENT'',
  6. l_extendedprice decimal(15,2) NOT NULL COMMENT'',
  7. l_discount decimal(15,2) NOT NULL COMMENT'',
  8. l_tax decimal(15,2) NOT NULL COMMENT'',
  9. l_returnflag varchar NOT NULL COMMENT'',
  10. l_linestatus varchar NOT NULL COMMENT'',
  11. l_shipdate date NOT NULL COMMENT'',
  12. l_commitdate date NOT NULL COMMENT'',
  13. l_receiptdate date NOT NULL COMMENT'',
  14. l_shipinstruct varchar NOT NULL COMMENT'',
  15. l_shipmode varchar NOT NULL COMMENT'',
  16. l_comment varchar NOT NULL COMMENT'',
  17. dummy varchar
  18. )DISTRIBUTED BY HASH(l_orderkey);
  1. CREATE VIEW revenue0 AS SELECT l_suppkey supplier_no
  2. ,sum((l_extendedprice*(1-l_discount)))total_revenue
  3. FROM
  4. lineitem
  5. WHERE((l_shipdate>=DATE'1995-02-01')AND(l_shipdate<(DATE'1995-02-01'
  6. +INTERVAL'3'MONTH)))
  7. GROUP BY l_suppkey;