测试对比 | TPC-H性能对比：泽拓昆仑Klustron VS Oracle

alter system set statement_timeout=6000000;
alter system set mysql_read_timeout=1200;
alter system set mysql_write_timeout=1200;
alter system set lock_timeout=1200000;
alter system set log_min_duration_statement=1200000;
alter system set effective_cache_size = '48GB';
alter system set work_mem  = '5GB';
alter system set wal_buffers='64MB';
alter system set extension.aggregate_ignore_work_mem=false;
alter system set enable_nestloop=false;
alter system set extension.max_custom_indexscan_parallelism=16;
alter system set extension.custom_hashjoin_parallelism=32;

mysql -h 192.168.0.17 -P 33501 -upgx -ppgx_pwd
mysql -h 192.168.0.18 -P 33503 -upgx -ppgx_pwd
mysql -h 192.168.0.19 -P 33505 -upgx -ppgx_pwd

set persist innodb_buffer_pool_size=48*1024*1024*1024;
set persist lock_wait_timeout=1200;
set persist innodb_lock_wait_timeout=1200;    
set persist fullsync_timeout=1200000; 
set persist enable_fullsync=false;
set persist innodb_flush_log_at_trx_commit=2;
set persist sync_binlog=0;
set persist max_binlog_size=1*1024*1024*1024;

alter system set sga_max_size=98304M scope=spfile;
alter system set sga_target=98304M scope=spfile;

alter table LINEITEM parallel(degree 24);
alter table PART parallel(degree 8);
alter table CUSTOMER parallel(degree 8);
alter table ORDERS parallel(degree 8);
alter table PARTSUPP parallel(degree 8);

create TABLEGROUP tpch10 partitions 3 (
        partition tpch10_auto_p0 with(hash=0,shard=1),
        partition tpch10_auto_p1 with(hash=1,shard=2),
        partition tpch10_auto_p2 with(hash=2,shard=3)
);


CREATE TABLE NATION  ( N_NATIONKEY  INTEGER NOT NULL,
                            N_NAME       CHAR(25) NOT NULL,
                            N_REGIONKEY  INTEGER NOT NULL,
                            N_COMMENT    VARCHAR(152),
          PRIMARY KEY (n_nationkey)) WITH(SHARD=all);




CREATE TABLE REGION  ( R_REGIONKEY  INTEGER NOT NULL,
                            R_NAME       CHAR(25) NOT NULL,
                            R_COMMENT    VARCHAR(152),
          PRIMARY KEY (r_regionkey)) WITH (SHARD=all);


CREATE TABLE PART  ( P_PARTKEY     INTEGER NOT NULL,
                          P_NAME        VARCHAR(55) NOT NULL,
                          P_MFGR        CHAR(25) NOT NULL,
                          P_BRAND       CHAR(10) NOT NULL,
                          P_TYPE        VARCHAR(25) NOT NULL,
                          P_SIZE        INTEGER NOT NULL,
                          P_CONTAINER   CHAR(10) NOT NULL,
                          P_RETAILPRICE double NOT NULL,
                          P_COMMENT     VARCHAR(23) NOT NULL,
        PRIMARY KEY (p_partkey)) PARTITION BY HASH (p_partkey) partitions 3 TABLEGROUP tpch10;






CREATE TABLE SUPPLIER ( S_SUPPKEY     INTEGER NOT NULL,
                             S_NAME        CHAR(25) NOT NULL,
                             S_ADDRESS     VARCHAR(40) NOT NULL,
                             S_NATIONKEY   INTEGER NOT NULL,
                             S_PHONE       CHAR(15) NOT NULL,
                             S_ACCTBAL     double NOT NULL,
                             S_COMMENT     VARCHAR(101) NOT NULL,
           PRIMARY KEY (s_suppkey)) PARTITION BY HASH (s_suppkey) partitions 3 TABLEGROUP tpch10;








CREATE TABLE PARTSUPP ( PS_PARTKEY     INTEGER NOT NULL,
                             PS_SUPPKEY     INTEGER NOT NULL,
                             PS_AVAILQTY    INTEGER NOT NULL,
                             PS_SUPPLYCOST  double  NOT NULL,
                             PS_COMMENT     VARCHAR(199) NOT NULL,
           PRIMARY KEY (ps_partkey, ps_suppkey)) PARTITION BY HASH (ps_partkey) partitions 3 TABLEGROUP tpch10;








CREATE TABLE CUSTOMER ( C_CUSTKEY     INTEGER NOT NULL,
                             C_NAME        VARCHAR(25) NOT NULL,
                             C_ADDRESS     VARCHAR(40) NOT NULL,
                             C_NATIONKEY   INTEGER NOT NULL,
                             C_PHONE       CHAR(15) NOT NULL,
                             C_ACCTBAL     double NOT NULL,
                             C_MKTSEGMENT  CHAR(10) NOT NULL,
                             C_COMMENT     VARCHAR(117) NOT NULL,
           PRIMARY KEY(c_custkey)) PARTITION BY HASH (C_CUSTKEY) partitions 3 TABLEGROUP tpch10;




CREATE TABLE ORDERS  ( O_ORDERKEY       INTEGER NOT NULL,
                           O_CUSTKEY        INTEGER NOT NULL,
                           O_ORDERSTATUS    CHAR(1) NOT NULL,
                           O_TOTALPRICE     double NOT NULL,
                           O_ORDERDATE      DATE NOT NULL,
                           O_ORDERPRIORITY  CHAR(15) NOT NULL,  
                           O_CLERK          CHAR(15) NOT NULL, 
                           O_SHIPPRIORITY   INTEGER NOT NULL,
                           O_COMMENT        VARCHAR(79) NOT NULL,
         PRIMARY KEY(o_orderkey)) PARTITION BY HASH (o_orderkey) partitions 3 TABLEGROUP tpch10;






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    double NOT NULL,
                             L_EXTENDEDPRICE  double NOT NULL,
                             L_DISCOUNT    double NOT NULL,
                             L_TAX         double NOT NULL,
                             L_RETURNFLAG  CHAR(1) NOT NULL,
                             L_LINESTATUS  CHAR(1) NOT NULL,
                             L_SHIPDATE    DATE NOT NULL,
                             L_COMMITDATE  DATE NOT NULL,
                             L_RECEIPTDATE DATE NOT NULL,
                             L_SHIPINSTRUCT CHAR(25) NOT NULL,
                             L_SHIPMODE     CHAR(10) NOT NULL,
                             L_COMMENT      VARCHAR(44) NOT NULL,
           PRIMARY KEY(l_orderkey, l_linenumber)) PARTITION BY HASH (l_orderkey) partitions 3 TABLEGROUP tpch10;

CREATE TABLE NATION  ( N_NATIONKEY  number NOT NULL,
                            N_NAME       CHAR(25) NOT NULL,
                            N_REGIONKEY  number NOT NULL,
                            N_COMMENT    VARCHAR2(152),
          PRIMARY KEY (n_nationkey));




CREATE TABLE REGION  ( R_REGIONKEY  number NOT NULL,
                            R_NAME       CHAR(25) NOT NULL,
                            R_COMMENT    VARCHAR2(152),
          PRIMARY KEY (r_regionkey));


CREATE TABLE PART  ( P_PARTKEY     number NOT NULL,
                          P_NAME        VARCHAR2(55) NOT NULL,
                          P_MFGR        CHAR(25) NOT NULL,
                          P_BRAND       CHAR(10) NOT NULL,
                          P_TYPE        VARCHAR2(25) NOT NULL,
                          P_SIZE        number NOT NULL,
                          P_CONTAINER   CHAR(10) NOT NULL,
                          P_RETAILPRICE binary_double NOT NULL,
                          P_COMMENT     VARCHAR2(23) NOT NULL,
        PRIMARY KEY (p_partkey)) PARTITION BY HASH (p_partkey) partitions 3;






CREATE TABLE SUPPLIER ( S_SUPPKEY     number NOT NULL,
                             S_NAME        CHAR(25) NOT NULL,
                             S_ADDRESS     VARCHAR2(40) NOT NULL,
                             S_NATIONKEY   number NOT NULL,
                             S_PHONE       CHAR(15) NOT NULL,
                             S_ACCTBAL     binary_double NOT NULL,
                             S_COMMENT     VARCHAR2(101) NOT NULL,
           PRIMARY KEY (s_suppkey)) PARTITION BY HASH (s_suppkey) partitions 3;








CREATE TABLE PARTSUPP ( PS_PARTKEY     number NOT NULL,
                             PS_SUPPKEY     number NOT NULL,
                             PS_AVAILQTY    number NOT NULL,
                             PS_SUPPLYCOST  binary_double  NOT NULL,
                             PS_COMMENT     VARCHAR2(199) NOT NULL,
           PRIMARY KEY (ps_partkey, ps_suppkey)) PARTITION BY HASH (ps_partkey) partitions 3;








CREATE TABLE CUSTOMER ( C_CUSTKEY     number NOT NULL,
                             C_NAME        VARCHAR2(25) NOT NULL,
                             C_ADDRESS     VARCHAR2(40) NOT NULL,
                             C_NATIONKEY   number NOT NULL,
                             C_PHONE       CHAR(15) NOT NULL,
                             C_ACCTBAL     binary_double NOT NULL,
                             C_MKTSEGMENT  CHAR(10) NOT NULL,
                             C_COMMENT     VARCHAR2(117) NOT NULL,
           PRIMARY KEY(c_custkey)) PARTITION BY HASH (C_CUSTKEY) partitions 3;




CREATE TABLE ORDERS  ( O_ORDERKEY       number NOT NULL,
                           O_CUSTKEY        number NOT NULL,
                           O_ORDERSTATUS    CHAR(1) NOT NULL,
                           O_TOTALPRICE     binary_double NOT NULL,
                           O_ORDERDATE      DATE NOT NULL,
                           O_ORDERPRIORITY  CHAR(15) NOT NULL,  
                           O_CLERK          CHAR(15) NOT NULL, 
                           O_SHIPPRIORITY   number NOT NULL,
                           O_COMMENT        VARCHAR2(79) NOT NULL,
         PRIMARY KEY(o_orderkey)) PARTITION BY HASH (o_orderkey) partitions 3;






CREATE TABLE LINEITEM ( L_ORDERKEY    number NOT NULL,
                             L_PARTKEY     number NOT NULL,
                             L_SUPPKEY     number NOT NULL,
                             L_LINENUMBER  number NOT NULL,
                             L_QUANTITY    binary_double NOT NULL,
                             L_EXTENDEDPRICE  binary_double NOT NULL,
                             L_DISCOUNT    binary_double NOT NULL,
                             L_TAX         binary_double NOT NULL,
                             L_RETURNFLAG  CHAR(1) NOT NULL,
                             L_LINESTATUS  CHAR(1) NOT NULL,
                             L_SHIPDATE    DATE NOT NULL,
                             L_COMMITDATE  DATE NOT NULL,
                             L_RECEIPTDATE DATE NOT NULL,
                             L_SHIPINSTRUCT CHAR(25) NOT NULL,
                             L_SHIPMODE     CHAR(10) NOT NULL,
                             L_COMMENT      VARCHAR2(44) NOT NULL,
           PRIMARY KEY(l_orderkey, l_linenumber)) PARTITION BY HASH (l_orderkey) partitions 3;

analyze nation;
analyze region;
analyze part;
analyze supplier;
analyze partsupp;
analyze customer;
analyze orders;
analyze lineitem;
analyze lineitem(l_shipmode,l_receiptdate,l_returnflag,l_linestatus,l_orderkey,l_suppkey,l_discount,l_quantity,l_partkey);
analyze supplier(s_suppkey,s_nationkey);
analyze part(p_size,p_type,p_partkey);
analyze partsupp(ps_suppkey,ps_supplycost);
analyze nation(n_regionkey,n_nationkey,n_name);
analyze region(r_name,r_regionkey);
analyze customer(c_mktsegment,c_custkey,c_nationkey,c_name);
analyze orders(o_custkey,o_orderkey,o_orderdate,o_orderpriority,o_totalprice);

EXEC DBMS_STATS.GATHER_TABLE_STATS('ASHAN','REGION',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>16,cascade=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('ASHAN','NATION',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>16,cascade=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('ASHAN','LINEITEM',estimate_percent=>30,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>16,cascade=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('ASHAN','CUSTOMER',estimate_percent=>50,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>16,cascade=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('ASHAN','PART',estimate_percent=>50,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>16,cascade=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('ASHAN','PARTSUPP',estimate_percent=>30,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>16,cascade=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('ASHAN','ORDERS',estimate_percent=>30,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>16,cascade=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('ASHAN','SUPPLIER',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>16,cascade=>TRUE);

#Q1:
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)) as sum_charge,
  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 '91' day
group by
  l_returnflag,
  l_linestatus
order by
  l_returnflag,
  l_linestatus
LIMIT 1;
 
 
 
#Q2:
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 = 6
  and p_type like '%BRASS'
  and s_nationkey = n_nationkey
  and n_regionkey = r_regionkey
  and r_name = 'ASIA'
  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 r_name = 'ASIA'
  )
order by
  s_acctbal desc,
  n_name,
  s_name,
  p_partkey
LIMIT 100;
 
 
 
#Q3:
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 < date '1995-03-17'
  and l_shipdate > date '1995-03-17'
group by
  l_orderkey,
  o_orderdate,
  o_shippriority
order by
  revenue desc,
  o_orderdate
LIMIT 10; 
 
 
#Q4:
select
  o_orderpriority,
  count(*) as order_count
from
  orders
where
  o_orderdate >= date '1995-07-01'
  and o_orderdate < date '1995-07-01' + 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
LIMIT 1;
 
 
 
#Q5:
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 = 'AFRICA'
  and o_orderdate >= date '1997-01-01'
  and o_orderdate < date '1997-01-01' + interval '1' year
group by
  n_name
order by
  revenue desc
LIMIT 1;
 
 
 
#Q6:
select
  sum(l_extendedprice * l_discount) as revenue
from
  lineitem
where
  l_shipdate >= date '1997-01-01'
  and l_shipdate < date '1997-01-01' + interval '1' year
  and l_discount between 0.05 - 0.01 and 0.05 + 0.01
  and l_quantity < 25
LIMIT 1;
 
 
 
#Q7:
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 = 'IRAN' and n2.n_name = 'PERU')
        or (n1.n_name = 'PERU' and n2.n_name = 'IRAN')
      )
      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
LIMIT 1; 
 
 
#Q8:
select
  o_year,
  sum(case
    when nation = 'PERU' 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 COPPER'
  ) as all_nations
group by
  o_year
order by
  o_year
LIMIT 1; 
 
 
#Q9:
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 '%pink%'
  ) as profit
group by
  nation,
  o_year
order by
  nation,
  o_year desc
LIMIT 1;
 
 
 
#Q10:
select
  c_custkey,
  c_name,
  sum(l_extendedprice * (1 - l_discount)) as revenue,
  c_acctbal,
  n_name,
  c_address,
  c_phone,
  c_comment
from
  customer,
  orders,
  lineitem,
  nation
where
  c_custkey = o_custkey
  and l_orderkey = o_orderkey
  and o_orderdate >= date '1993-05-01'
  and o_orderdate < date '1993-05-01' + interval '3' month
  and l_returnflag = 'R'
  and c_nationkey = n_nationkey
group by
  c_custkey,
  c_name,
  c_acctbal,
  c_phone,
  n_name,
  c_address,
  c_comment
order by
  revenue desc
LIMIT 20; 
 
 
#Q11:
select
  ps_partkey,
  sum(ps_supplycost * ps_availqty) as value
from
  partsupp,
  supplier,
  nation
where
  ps_suppkey = s_suppkey
  and s_nationkey = n_nationkey
  and n_name = 'MOZAMBIQUE'
group by
  ps_partkey having
    sum(ps_supplycost * ps_availqty) > (
      select
        sum(ps_supplycost * ps_availqty) * 0.0001000000
      from
        partsupp,
        supplier,
        nation
      where
        ps_suppkey = s_suppkey
        and s_nationkey = n_nationkey
        and n_name = 'MOZAMBIQUE'
    )
order by
  value desc
LIMIT 1; 
 
 
#Q12:
select
  l_shipmode,
  sum(case
    when o_orderpriority = '1-URGENT'
      or o_orderpriority = '2-HIGH'
      then 1
    else 0
  end) as high_line_count,
  sum(case
    when o_orderpriority <> '1-URGENT'
      and o_orderpriority <> '2-HIGH'
      then 1
    else 0
  end) as low_line_count
from
  orders,
  lineitem
where
  o_orderkey = l_orderkey
  and (l_shipmode = 'MAIL' or l_shipmode = 'FOB')
  and l_commitdate < l_receiptdate
  and l_shipdate < l_commitdate
  and l_receiptdate >= date '1994-01-01'
  and l_receiptdate < date '1994-01-01' + interval '1' year
group by
  l_shipmode
order by
  l_shipmode
LIMIT 1;
 
 
 
#Q13:
select
  c_count,
  count(*) as custdist
from
  (
    select
      c_custkey,
      count(o_orderkey)
    from
      customer left outer join orders on
        c_custkey = o_custkey
        and o_comment not like '%special%requests%'
    group by
      c_custkey
  ) as c_orders (c_custkey, c_count)
group by
  c_count
order by
  custdist desc,
  c_count desc
LIMIT 1; 
 
 
#Q14:
select
  100.00 * sum(case
    when p_type like 'PROMO%'
      then l_extendedprice * (1 - l_discount)
    else 0
  end)  sum(l_extendedprice * (1 - l_discount)) as promo_revenue
from
  lineitem,
  part
where
  l_partkey = p_partkey
  and l_shipdate >= date '1994-10-01'
  and l_shipdate < date '1994-10-01' + interval '1' month
LIMIT 1; 
 
 
#Q15:
create view revenue0 (supplier_no, total_revenue) as
  select
    l_suppkey,
    sum(l_extendedprice * (1 - l_discount))
  from
    lineitem
  where
    l_shipdate >= date '1994-04-01'
    and l_shipdate < date '1994-04-01' + interval '3' month
  group by
    l_suppkey;




select
  s_suppkey,
  s_name,
  s_address,
  s_phone,
  total_revenue
from
  supplier,
  revenue0
where
  s_suppkey = supplier_no
  and total_revenue = (
    select
      max(total_revenue)
    from
      revenue0
  )
order by
  s_suppkey
LIMIT 1;


drop view revenue0; 
 
 
#Q16:
select
  p_brand,
  p_type,
  p_size,
  count(distinct ps_suppkey) as supplier_cnt
from
  partsupp,
  part
where
  p_partkey = ps_partkey
  and p_brand <> 'Brand#11'
  and p_type not like 'STANDARD BRUSHED%'
  and p_size in (9, 21, 24, 30, 36, 50, 27, 39)
  and ps_suppkey not in (
    select
      s_suppkey
    from
      supplier
    where
      s_comment like '%Customer%Complaints%'
  )
group by
  p_brand,
  p_type,
  p_size
order by
  supplier_cnt desc,
  p_brand,
  p_type,
  p_size
LIMIT 1; 
 
 
#Q17:
select
  sum(l_extendedprice)  7.0 as avg_yearly
from
  lineitem,
  part,
  (SELECT l_partkey AS agg_partkey, 0.2 * avg(l_quantity) AS avg_quantity FROM lineitem GROUP BY l_partkey) part_agg
where
  p_partkey = l_partkey
  and agg_partkey = l_partkey
  and p_brand = 'Brand#11'
  and p_container = 'SM CAN'
  and l_quantity < avg_quantity
LIMIT 1;
 
 
 
#Q18:
select
  c_name,
  c_custkey,
  o_orderkey,
  o_orderdate,
  o_totalprice,
  sum(l_quantity)
from
  customer,
  orders,
  lineitem
where
  o_orderkey in (
    select
      l_orderkey
    from
      lineitem
    group by
      l_orderkey having
        sum(l_quantity) > 312
  )
  and c_custkey = o_custkey
  and o_orderkey = l_orderkey
group by
  c_name,
  c_custkey,
  o_orderkey,
  o_orderdate,
  o_totalprice
order by
  o_totalprice desc,
  o_orderdate
LIMIT 100; 
 
 
#Q19:
select
  sum(l_extendedprice* (1 - l_discount)) as revenue
from
  lineitem,
  part
where
  (
    p_partkey = l_partkey
    and p_brand = 'Brand#12'
    and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
    and l_quantity >= 5 and l_quantity <= 5 + 10
    and p_size between 1 and 5
    and l_shipmode in ('AIR', 'AIR REG')
    and l_shipinstruct = 'DELIVER IN PERSON'
  )
  or
  (
    p_partkey = l_partkey
    and p_brand = 'Brand#33'
    and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
    and l_quantity >= 18 and l_quantity <= 18 + 10
    and p_size between 1 and 10
    and l_shipmode in ('AIR', 'AIR REG')
    and l_shipinstruct = 'DELIVER IN PERSON'
  )
  or
  (
    p_partkey = l_partkey
    and p_brand = 'Brand#45'
    and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
    and l_quantity >= 20 and l_quantity <= 20 + 10
    and p_size between 1 and 15
    and l_shipmode in ('AIR', 'AIR REG')
    and l_shipinstruct = 'DELIVER IN PERSON'
  )
LIMIT 1; 
 
 
#Q20:
select
  s_name,
  s_address
from
  supplier,
  nation
where
  s_suppkey in (
    select
      ps_suppkey
    from
      partsupp,
      (
        select
          l_partkey agg_partkey,
          l_suppkey agg_suppkey,
          0.5 * sum(l_quantity) AS agg_quantity
        from
          lineitem
        where
          l_shipdate >= date '1995-01-01'
          and l_shipdate < date '1995-01-01' + interval '1' year
        group by
          l_partkey,
          l_suppkey
      ) agg_lineitem
    where
      agg_partkey = ps_partkey
      and agg_suppkey = ps_suppkey
      and ps_partkey in (
        select
          p_partkey
        from
          part
        where
          p_name like 'hot%'
      )
      and ps_availqty > agg_quantity
  )
  and s_nationkey = n_nationkey
  and n_name = 'KENYA'
order by
  s_name
LIMIT 1;
 
 
 
#Q21:
select
  s_name,
  count(*) as numwait
from
  supplier,
  lineitem l1,
  orders,
  nation
where
  s_suppkey = l1.l_suppkey
  and o_orderkey = l1.l_orderkey
  and o_orderstatus = 'F'
  and l1.l_receiptdate > l1.l_commitdate
  and exists (
    select
      *
    from
      lineitem l2
    where
      l2.l_orderkey = l1.l_orderkey
      and l2.l_suppkey <> l1.l_suppkey
  )
  and not exists (
    select
      *
    from
      lineitem l3
    where
      l3.l_orderkey = l1.l_orderkey
      and l3.l_suppkey <> l1.l_suppkey
      and l3.l_receiptdate > l3.l_commitdate
  )
  and s_nationkey = n_nationkey
  and n_name = 'IRAQ'
group by
  s_name
order by
  numwait desc,
  s_name
LIMIT 100; 
 
 
#Q22:
select
  cntrycode,
  count(*) as numcust,
  sum(c_acctbal) as totacctbal
from
  (
    select
      substring(c_phone from 1 for 2) as cntrycode,
      c_acctbal
    from
      customer
    where
      substring(c_phone from 1 for 2) in
        ('24', '27', '34', '23', '29', '15', '13')
      and c_acctbal > (
        select
          avg(c_acctbal)
        from
          customer
        where
          c_acctbal > 0.00
          and substring(c_phone from 1 for 2) in
            ('24', '27', '34', '23', '29', '15', '13')
      )
      and not exists (
        select
          *
        from
          orders
        where
          o_custkey = c_custkey
      )
  ) as custsale
group by
  cntrycode
order by
  cntrycode
LIMIT 1;

#Q1:
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)) as sum_charge,
  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 '91' day
group by
  l_returnflag,
  l_linestatus
order by
  l_returnflag,
  l_linestatus
offset 0 rows fetch next 1 rows only;




#Q2:
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 = 6
  and p_type like '%BRASS'
  and s_nationkey = n_nationkey
  and n_regionkey = r_regionkey
  and r_name = 'ASIA'
  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 r_name = 'ASIA'
  )
order by
  s_acctbal desc,
  n_name,
  s_name,
  p_partkey
offset 0 rows fetch next 100 rows only;




#Q3:
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 < date '1995-03-17'
  and l_shipdate > date '1995-03-17'
group by
  l_orderkey,
  o_orderdate,
  o_shippriority
order by
  revenue desc,
  o_orderdate
offset 0 rows fetch next 10 rows only;


#Q4:
select
  o_orderpriority,
  count(*) as order_count
from
  orders
where
  o_orderdate >= date '1995-07-01'
  and o_orderdate < date '1995-07-01' + 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
offset 0 rows fetch next 1 rows only;




#Q5:
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 = 'AFRICA'
  and o_orderdate >= date '1997-01-01'
  and o_orderdate < date '1997-01-01' + interval '1' year
group by
  n_name
order by
  revenue desc
offset 0 rows fetch next 1 rows only;




#Q6:
select
  sum(l_extendedprice * l_discount) as revenue
from
  lineitem
where
  l_shipdate >= date '1997-01-01'
  and l_shipdate < date '1997-01-01' + interval '1' year
  and l_discount between 0.05 - 0.01 and 0.05 + 0.01
  and l_quantity < 25
offset 0 rows fetch next 1 rows only;




#Q7:
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 = 'IRAN' and n2.n_name = 'PERU')
        or (n1.n_name = 'PERU' and n2.n_name = 'IRAN')
      )
      and l_shipdate between date '1995-01-01' and date '1996-12-31'
  ) shipping
group by
  supp_nation,
  cust_nation,
  l_year
order by
  supp_nation,
  cust_nation,
  l_year
offset 0 rows fetch next 1 rows only;


#Q8:
select
  o_year,
  sum(case
    when nation = 'PERU' 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 COPPER'
  ) all_nations
group by
  o_year
order by
  o_year
offset 0 rows fetch next 1 rows only;


#Q9:
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 '%pink%'
  ) profit
group by
  nation,
  o_year
order by
  nation,
  o_year desc
offset 0 rows fetch next 1 rows only;


#Q10:
select
  c_custkey,
  c_name,
  sum(l_extendedprice * (1 - l_discount)) as revenue,
  c_acctbal,
  n_name,
  c_address,
  c_phone,
  c_comment
from
  customer,
  orders,
  lineitem,
  nation
where
  c_custkey = o_custkey
  and l_orderkey = o_orderkey
  and o_orderdate >= date '1993-05-01'
  and o_orderdate < date '1993-05-01' + interval '3' month
  and l_returnflag = 'R'
  and c_nationkey = n_nationkey
group by
  c_custkey,
  c_name,
  c_acctbal,
  c_phone,
  n_name,
  c_address,
  c_comment
order by
  revenue desc
offset 0 rows fetch next 20 rows only;


#Q11:
select
  ps_partkey,
  sum(ps_supplycost * ps_availqty) as value
from
  partsupp,
  supplier,
  nation
where
  ps_suppkey = s_suppkey
  and s_nationkey = n_nationkey
  and n_name = 'MOZAMBIQUE'
group by
  ps_partkey having
    sum(ps_supplycost * ps_availqty) > (
      select
        sum(ps_supplycost * ps_availqty) * 0.0001000000
      from
        partsupp,
        supplier,
        nation
      where
        ps_suppkey = s_suppkey
        and s_nationkey = n_nationkey
        and n_name = 'MOZAMBIQUE'
    )
order by
  value desc
offset 0 rows fetch next 1 rows only;


#Q12:
select
  l_shipmode,
  sum(case
    when o_orderpriority = '1-URGENT'
      or o_orderpriority = '2-HIGH'
      then 1
    else 0
  end) as high_line_count,
  sum(case
    when o_orderpriority <> '1-URGENT'
      and o_orderpriority <> '2-HIGH'
      then 1
    else 0
  end) as low_line_count
from
  orders,
  lineitem
where
  o_orderkey = l_orderkey
  and (l_shipmode = 'MAIL' or l_shipmode = 'FOB')
  and l_commitdate < l_receiptdate
  and l_shipdate < l_commitdate
  and l_receiptdate >= date '1994-01-01'
  and l_receiptdate < date '1994-01-01' + interval '1' year
group by
  l_shipmode
order by
  l_shipmode
offset 0 rows fetch next 1 rows only;


#Q13:
select
  c_count,
  count(*) as custdist
from
  (
    select
      c_custkey,
      count(o_orderkey) as c_count
    from
      customer left outer join orders on
        c_custkey = o_custkey
        and o_comment not like '%special%requests%'
    group by
      c_custkey
  ) c_orders
group by
  c_count
order by
  custdist desc,
  c_count desc
offset 0 rows fetch next 1 rows only;


#Q14:
select
  100.00 * sum(case
    when p_type like 'PROMO%'
      then l_extendedprice * (1 - l_discount)
    else 0
  end)  sum(l_extendedprice * (1 - l_discount)) as promo_revenue
from
  lineitem,
  part
where
  l_partkey = p_partkey
  and l_shipdate >= date '1994-10-01'
  and l_shipdate < date '1994-10-01' + interval '1' month
offset 0 rows fetch next 1 rows only;


#Q15:
create view revenue0 (supplier_no, total_revenue) as
  select
    l_suppkey,
    sum(l_extendedprice * (1 - l_discount))
  from
    lineitem
  where
    l_shipdate >= date '1994-04-01'
    and l_shipdate < date '1994-04-01' + interval '3' month
  group by
    l_suppkey;


select
  s_suppkey,
  s_name,
  s_address,
  s_phone,
  total_revenue
from
  supplier,
  revenue0
where
  s_suppkey = supplier_no
  and total_revenue = (
    select
      max(total_revenue)
    from
      revenue0
  )
order by
  s_suppkey
offset 0 rows fetch next 1 rows only;




drop view revenue0;


#Q16:
select
  p_brand,
  p_type,
  p_size,
  count(distinct ps_suppkey) as supplier_cnt
from
  partsupp,
  part
where
  p_partkey = ps_partkey
  and p_brand <> 'Brand#11'
  and p_type not like 'STANDARD BRUSHED%'
  and p_size in (9, 21, 24, 30, 36, 50, 27, 39)
  and ps_suppkey not in (
    select
      s_suppkey
    from
      supplier
    where
      s_comment like '%Customer%Complaints%'
  )
group by
  p_brand,
  p_type,
  p_size
order by
  supplier_cnt desc,
  p_brand,
  p_type,
  p_size
offset 0 rows fetch next 1 rows only;


#Q17:
select
  sum(l_extendedprice)  7.0 as avg_yearly
from
  lineitem,
  part,
  (SELECT l_partkey AS agg_partkey, 0.2 * avg(l_quantity) AS avg_quantity FROM lineitem GROUP BY l_partkey) part_agg
where
  p_partkey = l_partkey
  and agg_partkey = l_partkey
  and p_brand = 'Brand#11'
  and p_container = 'SM CAN'
  and l_quantity < avg_quantity
offset 0 rows fetch next 1 rows only;


#Q18:
select
  c_name,
  c_custkey,
  o_orderkey,
  o_orderdate,
  o_totalprice,
  sum(l_quantity)
from
  customer,
  orders,
  lineitem
where
  o_orderkey in (
    select
      l_orderkey
    from
      lineitem
    group by
      l_orderkey having
        sum(l_quantity) > 312
  )
  and c_custkey = o_custkey
  and o_orderkey = l_orderkey
group by
  c_name,
  c_custkey,
  o_orderkey,
  o_orderdate,
  o_totalprice
order by
  o_totalprice desc,
  o_orderdate
offset 0 rows fetch next 100 rows only;


#Q19:
select
  sum(l_extendedprice* (1 - l_discount)) as revenue
from
  lineitem,
  part
where
  (
    p_partkey = l_partkey
    and p_brand = 'Brand#12'
    and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
    and l_quantity >= 5 and l_quantity <= 5 + 10
    and p_size between 1 and 5
    and l_shipmode in ('AIR', 'AIR REG')
    and l_shipinstruct = 'DELIVER IN PERSON'
  )
  or
  (
    p_partkey = l_partkey
    and p_brand = 'Brand#33'
    and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
    and l_quantity >= 18 and l_quantity <= 18 + 10
    and p_size between 1 and 10
    and l_shipmode in ('AIR', 'AIR REG')
    and l_shipinstruct = 'DELIVER IN PERSON'
  )
  or
  (
    p_partkey = l_partkey
    and p_brand = 'Brand#45'
    and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
    and l_quantity >= 20 and l_quantity <= 20 + 10
    and p_size between 1 and 15
    and l_shipmode in ('AIR', 'AIR REG')
    and l_shipinstruct = 'DELIVER IN PERSON'
  )
offset 0 rows fetch next 1 rows only;


#Q20:
select
  s_name,
  s_address
from
  supplier,
  nation
where
  s_suppkey in (
    select
      ps_suppkey
    from
      partsupp,
      (
        select
          l_partkey agg_partkey,
          l_suppkey agg_suppkey,
          0.5 * sum(l_quantity) AS agg_quantity
        from
          lineitem
        where
          l_shipdate >= date '1995-01-01'
          and l_shipdate < date '1995-01-01' + interval '1' year
        group by
          l_partkey,
          l_suppkey
      ) agg_lineitem
    where
      agg_partkey = ps_partkey
      and agg_suppkey = ps_suppkey
      and ps_partkey in (
        select
          p_partkey
        from
          part
        where
          p_name like 'hot%'
      )
      and ps_availqty > agg_quantity
  )
  and s_nationkey = n_nationkey
  and n_name = 'KENYA'
order by
  s_name
offset 0 rows fetch next 1 rows only;


#Q21:
select
  s_name,
  count(*) as numwait
from
  supplier,
  lineitem l1,
  orders,
  nation
where
  s_suppkey = l1.l_suppkey
  and o_orderkey = l1.l_orderkey
  and o_orderstatus = 'F'
  and l1.l_receiptdate > l1.l_commitdate
  and exists (
    select
      *
    from
      lineitem l2
    where
      l2.l_orderkey = l1.l_orderkey
      and l2.l_suppkey <> l1.l_suppkey
  )
  and not exists (
    select
      *
    from
      lineitem l3
    where
      l3.l_orderkey = l1.l_orderkey
      and l3.l_suppkey <> l1.l_suppkey
      and l3.l_receiptdate > l3.l_commitdate
  )
  and s_nationkey = n_nationkey
  and n_name = 'IRAQ'
group by
  s_name
order by
  numwait desc,
  s_name
offset 0 rows fetch next 100 rows only;


#Q22:
select
  cntrycode,
  count(*) as numcust,
  sum(c_acctbal) as totacctbal
from
  (select substr(c_phone,1,2) as cntrycode,
    c_acctbal
    from
      customer
    where
      substr(c_phone,1,2) in
        ('24', '27', '34', '23', '29', '15', '13')
      and c_acctbal > (
        select
          avg(c_acctbal)
        from
          customer
        where
          c_acctbal > 0.00
          and substr(c_phone,1,2) in
            ('24', '27', '34', '23', '29', '15', '13')
      )
      and not exists (
        select
          *
        from
          orders
        where
          o_custkey = c_custkey)
  ) custsale
group by
  cntrycode
order by
  cntrycode
offset 0 rows fetch next 1 rows only;