text: /*
  ################################################################################
  # TESTCASE NAME : plan_table
  # COMPONENT(S)  : plan_table:复杂查询 tpch测试
  ################################################################################
  */
  
  --I1. 建表
  create schema tpch;
  set current_schema=tpch;
  drop table if exists customer;
  drop table if exists lineitem;
  drop table if exists nation;
  drop table if exists orders;
  drop table if exists part;
  drop table if exists partsupp;
  drop table if exists region;
  drop table if exists supplier;
  
  CREATE TABLE customer (
      c_custkey integer NOT NULL,
      c_name character varying(25) NOT NULL,
      c_address character varying(40) NOT NULL,
      c_nationkey integer NOT NULL,
      c_phone character(15) NOT NULL,
      c_acctbal numeric(15,2) NOT NULL,
      c_mktsegment character(10) NOT NULL,
      c_comment character varying(117) NOT NULL
  )
  with (orientation = column)
  DISTRIBUTE BY HASH (c_custkey);
  
  CREATE TABLE lineitem (
      l_orderkey integer NOT NULL,
      l_partkey integer NOT NULL,
      l_suppkey integer NOT NULL,
      l_linenumber integer NOT NULL,
      l_quantity numeric(15,2) NOT NULL,
      l_extendedprice numeric(15,2) NOT NULL,
      l_discount numeric(15,2) NOT NULL,
      l_tax numeric(15,2) NOT NULL,
      l_returnflag character(1) NOT NULL,
      l_linestatus character(1) NOT NULL,
      l_shipdate date NOT NULL,
      l_commitdate date NOT NULL,
      l_receiptdate date NOT NULL,
      l_shipinstruct character(25) NOT NULL,
      l_shipmode character(10) NOT NULL,
      l_comment character varying(44) NOT NULL
  )
  with (orientation = column)
  DISTRIBUTE BY HASH (l_orderkey);
  
  CREATE TABLE nation (
      n_nationkey integer NOT NULL,
      n_name character(25) NOT NULL,
      n_regionkey integer NOT NULL,
      n_comment character varying(152)
  )
  with (orientation = column)
  DISTRIBUTE BY REPLICATION;
  
  CREATE TABLE orders (
      o_orderkey integer NOT NULL,
      o_custkey integer NOT NULL,
      o_orderstatus character(1) NOT NULL,
      o_totalprice numeric(15,2) NOT NULL,
      o_orderdate date NOT NULL,
      o_orderpriority character(15) NOT NULL,
      o_clerk character(15) NOT NULL,
      o_shippriority integer NOT NULL,
      o_comment character varying(79) NOT NULL
  )
  with (orientation = column)
  DISTRIBUTE BY HASH (o_orderkey);
  
  CREATE TABLE part (
      p_partkey integer NOT NULL,
      p_name character varying(55) NOT NULL,
      p_mfgr character(25) NOT NULL,
      p_brand character(10) NOT NULL,
      p_type character varying(25) NOT NULL,
      p_size integer NOT NULL,
      p_container character(10) NOT NULL,
      p_retailprice numeric(15,2) NOT NULL,
      p_comment character varying(23) NOT NULL
  )
  with (orientation = column)
  DISTRIBUTE BY HASH (p_partkey);
  
  CREATE TABLE partsupp (
      ps_partkey integer NOT NULL,
      ps_suppkey integer NOT NULL,
      ps_availqty integer NOT NULL,
      ps_supplycost numeric(15,2) NOT NULL,
      ps_comment character varying(199) NOT NULL
  )
  with (orientation = column)
  DISTRIBUTE BY HASH (ps_partkey);
  
  CREATE TABLE region (
      r_regionkey integer NOT NULL,
      r_name character(25) NOT NULL,
      r_comment character varying(152)
  )
  with (orientation = column)
  DISTRIBUTE BY REPLICATION;
  
  CREATE TABLE supplier (
      s_suppkey integer NOT NULL,
      s_name character(25) NOT NULL,
      s_address character varying(40) NOT NULL,
      s_nationkey integer NOT NULL,
      s_phone character(15) NOT NULL,
      s_acctbal numeric(15,2) NOT NULL,
      s_comment character varying(101) NOT NULL
  )
  with (orientation = column)
  DISTRIBUTE BY HASH (s_suppkey);
  
  SET explain_perf_mode=pretty;
  
  --I1.q1
  --S1.
  explain plan set statement_id='q1' for
  select
          l_returnflag,
          l_linestatus,
          sum(l_quantity) as sum_qty,
          sum(l_extendedprice) as sum_base_price,
          sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
          sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)/1000) as sum_charge, --add /1000
          avg(l_quantity) as avg_qty,
          avg(l_extendedprice) as avg_price,
          avg(l_discount) as avg_disc,
          count(*) as count_order
  from
          lineitem
  where
          l_shipdate <= date '1998-12-01' - interval '3 day'
  group by
          l_returnflag,
          l_linestatus
  order by
          l_returnflag,
          l_linestatus;
  --S2
  select id,operation,options,object_name,object_type,object_owner,projection from plan_table_data where statement_id='q1' order by id;
  --S3
  explain(costs off)
  select
          l_returnflag,
          l_linestatus,
          sum(l_quantity) as sum_qty,
          sum(l_extendedprice) as sum_base_price,
          sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
          sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)/1000) as sum_charge, --add /1000
          avg(l_quantity) as avg_qty,
          avg(l_extendedprice) as avg_price,
          avg(l_discount) as avg_disc,
          count(*) as count_order
  from
          lineitem
  where
          l_shipdate <= date '1998-12-01' - interval '3 day'
  group by
          l_returnflag,
          l_linestatus
  order by
          l_returnflag,
          l_linestatus;
  
  
  --I3.q2
  --S2.
  explain plan set statement_id='q2' for
  select
          s_acctbal,
          s_name,
          n_name,
          p_partkey,
          p_mfgr,
          s_address,
          s_phone,
          s_comment
  from
          part,
          supplier,
          partsupp,
          nation,
          region
  where
          p_partkey = ps_partkey
          and s_suppkey = ps_suppkey
          and p_size = 15
          and p_type like 'SMALL%'
          and s_nationkey = n_nationkey
          and n_regionkey = r_regionkey
          and r_name = 'EUROPE '
          and ps_supplycost = (
                  select
                          min(ps_supplycost)
                  from
                          partsupp,
                          supplier,
                          nation,
                          region
                  where
                          p_partkey = ps_partkey
                          and s_suppkey = ps_suppkey
                          and s_nationkey = n_nationkey
                          and n_regionkey = r_regionkey
                          and n_regionkey = r_regionkey
                          and r_name = 'EUROPE '
          )
  order by
          s_acctbal desc,
          n_name,
          s_name,
          p_partkey
  limit 100
  ;
  --S2.
  select id,operation,options,object_name,object_type,object_owner,projection from plan_table_data where statement_id='q2' order by id;	
  --S3.
  explain(costs off)
  select
          s_acctbal,
          s_name,
          n_name,
          p_partkey,
          p_mfgr,
          s_address,
          s_phone,
          s_comment
  from
          part,
          supplier,
          partsupp,
          nation,
          region
  where
          p_partkey = ps_partkey
          and s_suppkey = ps_suppkey
          and p_size = 15
          and p_type like 'SMALL%'
          and s_nationkey = n_nationkey
          and n_regionkey = r_regionkey
          and r_name = 'EUROPE '
          and ps_supplycost = (
                  select
                          min(ps_supplycost)
                  from
                          partsupp,
                          supplier,
                          nation,
                          region
                  where
                          p_partkey = ps_partkey
                          and s_suppkey = ps_suppkey
                          and s_nationkey = n_nationkey                                                                                                                                                      1,1           Top
                          and s_nationkey = n_nationkey
                          and n_regionkey = r_regionkey
                          and r_name = 'EUROPE '
          )
  order by
          s_acctbal desc,
          n_name,
          s_name,
          p_partkey
  limit 100
  ;
  
  
  --I3.q3
  --S1.
  explain plan set statement_id='q3' for
  select
          l_orderkey,
          sum(l_extendedprice * (1 - l_discount)) as revenue,
          o_orderdate,
          o_shippriority
  from
          customer,
          orders,
          lineitem
  where
          c_mktsegment = 'BUILDING'
          and c_custkey = o_custkey
          and l_orderkey = o_orderkey
          and o_orderdate < '1995-03-15'::date
          and l_shipdate > '1995-03-15'::date
  group by
          l_orderkey,
          o_orderdate,
          o_shippriority
  order by
          revenue desc,
          o_orderdate
  limit 10
  ;
  --S2.
  select id,operation,options,object_name,object_type,object_owner,projection from plan_table_data where statement_id='q3' order by id;	
  --S3.
  explain(costs off)
  select
          l_orderkey,
          sum(l_extendedprice * (1 - l_discount)) as revenue,
          o_orderdate,
          o_shippriority
  from
          customer,
          orders,
          lineitem
  where
          c_mktsegment = 'BUILDING'
          and c_custkey = o_custkey
          and l_orderkey = o_orderkey
          and o_orderdate < '1995-03-15'::date
          and l_shipdate > '1995-03-15'::date
  group by
          l_orderkey,
          o_orderdate,
          o_shippriority
  order by
          revenue desc,
          o_orderdate
  limit 10
  ;
  
  
  --I4.q4
  explain plan set statement_id='q4' for
  select
          o_orderpriority,
          count(*) as order_count
  from
          orders
  where
          o_orderdate >= '1993-07-01'::date
          and o_orderdate < '1993-07-01'::date + interval '3 month'
          and exists (
                  select
                          *
                  from
                          lineitem
                  where
                          l_orderkey = o_orderkey
                          and l_commitdate < l_receiptdate
          )
  group by
          o_orderpriority
  order by
          o_orderpriority;
  --S2.
  select id,operation,options,object_name,object_type,object_owner,projection from plan_table_data where statement_id='q4' order by id;	
  --S3.
  explain(costs off)
  select
          o_orderpriority,
          count(*) as order_count
  from
          orders
  where
          o_orderdate >= '1993-07-01'::date
          and o_orderdate < '1993-07-01'::date + interval '3 month'
          and exists (
                  select
                          *
                  from
                          lineitem
                  where
                          l_orderkey = o_orderkey
                          and l_commitdate < l_receiptdate
          )
  group by
          o_orderpriority
  order by
          o_orderpriority;
  
  --I5.q5
  explain plan set statement_id='q5' for
  select
          n_name,
          sum(l_extendedprice * (1 - l_discount)) as revenue
  from
          customer,
          orders,
          lineitem,
          supplier,
          nation,
          region
  where
          c_custkey = o_custkey
          and l_orderkey = o_orderkey
          and l_suppkey = s_suppkey
          and c_nationkey = s_nationkey
          and s_nationkey = n_nationkey
          and n_regionkey = r_regionkey
          and r_name = 'ASIA'
          and o_orderdate >= '1994-01-01'::date
          and o_orderdate < '1994-01-01'::date + interval '1 year'
  group by
          n_name
  order by
          revenue desc;
  --S2.
  select id,operation,options,object_name,object_type,object_owner,projection from plan_table_data where statement_id='q5' order by id;	
  --S3.
  explain(costs off)
  select
          n_name,
          sum(l_extendedprice * (1 - l_discount)) as revenue
  from
          customer,
          orders,
          lineitem,
          supplier,
          nation,
          region
  where
          c_custkey = o_custkey
          and l_orderkey = o_orderkey
          and l_suppkey = s_suppkey
          and c_nationkey = s_nationkey
          and s_nationkey = n_nationkey
          and n_regionkey = r_regionkey
          and r_name = 'ASIA'
          and o_orderdate >= '1994-01-01'::date
          and o_orderdate < '1994-01-01'::date + interval '1 year'
  group by
          n_name
  order by
          revenue desc;
  
  
  --I6.q6
  explain plan set statement_id='q6' for
  select
          sum(l_extendedprice * l_discount) as revenue
  from
          lineitem
  where
          l_shipdate >= '1994-01-01'::date
          and l_shipdate < '1994-01-01'::date + interval '1 year'
          and l_discount between 0.06 - 0.01 and 0.06 + 0.01
          and l_quantity < 24;
  --S2.
  select id,operation,options,object_name,object_type,object_owner,projection from plan_table_data where statement_id='q6' order by id;	
  --S3.
  explain(costs off)
  select
          sum(l_extendedprice * l_discount) as revenue
  from
          lineitem
  where
          l_shipdate >= '1994-01-01'::date
          and l_shipdate < '1994-01-01'::date + interval '1 year'
          and l_discount between 0.06 - 0.01 and 0.06 + 0.01
          and l_quantity < 24;
  
  
  --I7.q7
  explain plan set statement_id='q7' for
  select
          supp_nation,
          cust_nation,
          l_year,
          sum(volume) as revenue
  from
          (
                  select
                          n1.n_name as supp_nation,
                          n2.n_name as cust_nation,
                          extract(year from l_shipdate) as l_year,
                          l_extendedprice * (1 - l_discount) as volume
                  from
                          supplier,
                          lineitem,
                          orders,
                          customer,
                          nation n1,
                          nation n2
                  where
                          s_suppkey = l_suppkey
                          and o_orderkey = l_orderkey
                          and c_custkey = o_custkey
                          and s_nationkey = n1.n_nationkey
                          and c_nationkey = n2.n_nationkey
                          and (
                                  (n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
                                  or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
                          )
                          and l_shipdate between date '1995-01-01' and date '1996-12-31'
          ) as shipping
  group by
          supp_nation,
          cust_nation,
          l_year
  order by
          supp_nation,
          cust_nation,
          l_year;
  --S2.
  select id,operation,options,object_name,object_type,object_owner,projection from plan_table_data where statement_id='q7' order by id;	
  --S3.
  explain(costs off)
  select
          supp_nation,
          cust_nation,
          l_year,
          sum(volume) as revenue
  from
          (
                  select
                          n1.n_name as supp_nation,
                          n2.n_name as cust_nation,
                          extract(year from l_shipdate) as l_year,
                          l_extendedprice * (1 - l_discount) as volume
                  from
                          supplier,
                          lineitem,
                          orders,
                          customer,
                          nation n1,
                          nation n2
                  where
                          s_suppkey = l_suppkey
                          and o_orderkey = l_orderkey
                          and c_custkey = o_custkey
                          and s_nationkey = n1.n_nationkey
                          and c_nationkey = n2.n_nationkey
                          and (
                                  (n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
                                  or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
                          )
                          and l_shipdate between date '1995-01-01' and date '1996-12-31'
          ) as shipping
  group by
          supp_nation,
          cust_nation,
          l_year
  order by
          supp_nation,
          cust_nation,
          l_year;
  
  
  --I8.q8
  explain plan set statement_id='q8' for
  select
          o_year,
          sum(case
                  when nation = 'BRAZIL' then volume
                  else 0
          end) / sum(volume) as mkt_share
  from
          (
                  select
                          extract(year from o_orderdate) as o_year,
                          l_extendedprice * (1 - l_discount) as volume,
                          n2.n_name as nation
                  from
                          part,
                          supplier,
                          lineitem,
                          orders,
                          customer,
                          nation n1,
                          nation n2,
                          region
                  where
                          p_partkey = l_partkey
                          and s_suppkey = l_suppkey
                          and l_orderkey = o_orderkey
                          and o_custkey = c_custkey
                          and c_nationkey = n1.n_nationkey
                          and n1.n_regionkey = r_regionkey
                          and r_name = 'AMERICA'
                          and s_nationkey = n2.n_nationkey
                          and o_orderdate between date '1995-01-01' and date '1996-12-31'
                          and p_type = 'ECONOMY ANODIZED STEEL'
          ) as all_nations
  group by
          o_year
  order by
          o_year;
  --S2.
  select id,operation,options,object_name,object_type,object_owner,projection from plan_table_data where statement_id='q8' order by id;	
  --S3.
  explain(costs off)
  select
          o_year,
          sum(case
                  when nation = 'BRAZIL' then volume
                  else 0
          end) / sum(volume) as mkt_share
  from
          (
                  select
                          extract(year from o_orderdate) as o_year,
                          l_extendedprice * (1 - l_discount) as volume,
                          n2.n_name as nation
                  from
                          part,
                          supplier,
                          lineitem,
                          orders,
                          customer,
                          nation n1,
                          nation n2,
                          region
                  where
                          p_partkey = l_partkey
                          and s_suppkey = l_suppkey
                          and l_orderkey = o_orderkey
                          and o_custkey = c_custkey
                          and c_nationkey = n1.n_nationkey
                          and n1.n_regionkey = r_regionkey
                          and r_name = 'AMERICA'
                          and s_nationkey = n2.n_nationkey
                          and o_orderdate between date '1995-01-01' and date '1996-12-31'
                          and p_type = 'ECONOMY ANODIZED STEEL'
          ) as all_nations
  group by
          o_year
  order by
          o_year;
  
  
  --I9.q9
  explain plan set statement_id='q9' for
  select
          nation,
          o_year,
          sum(amount) as sum_profit
  from
          (
                  select
                          n_name as nation,
                          extract(year from o_orderdate) as o_year,
                          l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
                  from
                          part,
                          supplier,
                          lineitem,
                          partsupp,
                          orders,
                          nation
                  where
                          s_suppkey = l_suppkey
                          and ps_suppkey = l_suppkey
                          and ps_partkey = l_partkey
                          and p_partkey = l_partkey
                          and o_orderkey = l_orderkey
                          and s_nationkey = n_nationkey
                          and p_name like '%green%'
          ) as profit
  group by
          nation,
          o_year
  order by
          nation,
          o_year desc;
  --S2.
  select id,operation,options,object_name,object_type,object_owner,projection from plan_table_data where statement_id='q9' order by id;	
  --S3.
  explain(costs off)
  select
          nation,
          o_year,
          sum(amount) as sum_profit
  from
          (
                  select
                          n_name as nation,
                          extract(year from o_orderdate) as o_year,
                          l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
                  from
                          part,
                          supplier,
                          lineitem,
                          partsupp,
                          orders,
                          nation
                  where
                          s_suppkey = l_suppkey
                          and ps_suppkey = l_suppkey
                          and ps_partkey = l_partkey
                          and p_partkey = l_partkey
                          and o_orderkey = l_orderkey
                          and s_nationkey = n_nationkey
                          and p_name like '%green%'
          ) as profit
  group by
          nation,
          o_year
  order by
          nation,
          o_year desc;
  
  --清理
  delete from plan_table_data;
  drop schema tpch cascade;
  