如何使用AnalyticDB for PostgreSQL 6.0 進行TPC-H 1TB資料的測試

TPC-H 簡介

TPC-H是事務處理性能委員會（ Transaction ProcessingPerformance Council ）制定的基準程式之一，TPC-H 主要目的是評價特定查詢的決策支援能力，該基準模拟了決策支援系統中的資料庫操作，測試資料庫系統複雜查詢的響應時間。

TPC-H 裡的表是模拟一個配件銷售管理系統進行模組化。詳情參考

TPCH Specification

8張表邏輯關系

測試資料量的說明

資料量的大小對查詢速度有直接的影響，TPC-H 中使用SF描述資料量，1SF 對應1GB 機關。1000SF，即1TB。1SF對應的資料量隻是8個表的總資料量不包括索引等空間占用，準備資料時需預留更多空間。

AnalyticDB for PostgreSQL 6.0 規格選擇

選擇成本效益适中的兩種規格：

• ADB PG 6.0 SSD存儲+單節點1核+執行個體節點數64

• ADB PG 6.0 SSD存儲+單節點4核+執行個體節點數32

• 後續的測試過程均使用預設配置，未針對TPC-H的查詢SQL做特别的修改

測試步驟

開通一個ECS執行個體

準備一台ECS（建議規格：ecs.g6.4xlarge規格、CentOS系統、ESSD 2T資料盤，建議與AnalyticDB for PostgreSQL 6.0 執行個體用相同region和VPC網絡），用于1T資料生成、資料上傳/入庫、用戶端測試。

開通一個AnalyticDB for PostgreSQL 6.0 執行個體

參考配置如下圖。建議與ECS執行個體用相同區域和VPC網絡。

開通外網，修改白名單，建立資料庫賬号

進入阿裡雲分析型資料庫PostgreSQL産品頁，進入分析型資料庫PostgreSQL版控制台，找到已開通的AnalyticDB for PostgreSQL 6.0 執行個體，點選“執行個體名連結”進入詳情頁，參考下圖位置，修改配置項。

生成TPC-H 1T資料

• SSH進入到ECS執行個體，下載下傳TPC-H dbgen代碼，編譯後dbgen目錄生成 dbgen/qgen 執行程式。

git clone https://github.com/gregrahn/tpch-kit.git
cd tpch-kit/dbgen
make           

• 生成1T資料，運作 ./dbgen --help 檢視如何生成，指令參考：

  ./dbgen -vf -s 1000

• 也可以并行生成分片檔案，如下shell腳本參考（10個分片檔案）：

for((i=1;i<=10;i++));
do
./dbgen -s 1000 -S $i -C 10 -f &
done           

• 處理生成的 tbl 檔案，tbl檔案每行最後會多1個'|'，可以用seed指令将每行後面的'|'去掉，shell腳本參考：

sed -i 's/.$//' ./region.tbl &
sed -i 's/.$//' ./nation.tbl &
for((i=1;i<=10;i++));
do
sed -i 's/.$//' ./lineitem.tbl.$i &
sed -i 's/.$//' ./orders.tbl.$i &
sed -i 's/.$//' ./customer.tbl.$i &
sed -i 's/.$//' ./partsupp.tbl.$i &
sed -i 's/.$//' ./part.tbl.$i &
sed -i 's/.$//' ./supplier.tbl.$i &
done           

向資料庫中建表

在ECS機器上檢查是否存在PSQL指令，如果沒有，安裝PSQL用戶端：

sudo yum install postgresql

準備TPC-H涉及到的8張表建立SQL，建表語句參考如下。

列存表适合向量計算、JIT架構。對大批量資料的通路和統計，效率更高。是以建表語句中使用了

• AO列存表

• 不開壓縮

• 設定複制表

create table nation (
    n_nationkey  integer not null,
    n_name       char(25) not null,
    n_regionkey  integer not null,
    n_comment    varchar(152)
)
with (appendonly=true, orientation=column)
distributed REPLICATED;
create table region (
    r_regionkey  integer not null,
    r_name       char(25) not null,
    r_comment    varchar(152)
)
with (appendonly=true, orientation=column)
distributed REPLICATED;
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 DECIMAL(15,2) not null,
    p_comment     varchar(23) not null
)
with (appendonly=true, orientation=column)
distributed by (p_partkey);
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     DECIMAL(15,2) not null,
    s_comment     varchar(101) not null
)
with (appendonly=true, orientation=column)
distributed by (s_suppkey);
create table partsupp (
    ps_partkey     integer not null,
    ps_suppkey     integer not null,
    ps_availqty    integer not null,
    ps_supplycost  DECIMAL(15,2)  not null,
    ps_comment     varchar(199) not null
)
with (appendonly=true, orientation=column)
distributed by (ps_partkey);
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     DECIMAL(15,2)  not null,
    c_mktsegment  char(10) not null,
    c_comment     varchar(117) not null
)
with (appendonly=true, orientation=column)
distributed by (c_custkey);
create table orders (
    o_orderkey       bigint not null,
    o_custkey        integer not null,
    o_orderstatus    char(1) not null,
    o_totalprice     DECIMAL(15,2) 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
)
with (appendonly=true, orientation=column)
distributed by (o_orderkey);
create table lineitem (
    l_orderkey    bigint not null,
    l_partkey     integer not null,
    l_suppkey     integer not null,
    l_linenumber  integer not null,
    l_quantity    DECIMAL(15,2) not null,
    l_extendedprice  DECIMAL(15,2) not null,
    l_discount    DECIMAL(15,2) not null,
    l_tax         DECIMAL(15,2) 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
)
with (appendonly=true, orientation=column)
distributed by (l_orderkey);           

運作SQL腳本檔案的shell腳本參考：

export PGPASSWORD=<資料庫賬号密碼>
psql -h <ADB PG執行個體内網或外網位址> -p 3432 -U <資料庫賬号> -f <建立表的SQL腳本檔案路徑>            

導入資料

準備工作就緒，可以開始導入資料了，導入資料有兩種方式：

1. 通過 copy from導入
2. 通過OSS外表方式導入

下面分别介紹兩種導入方法

COPY方式導入

SQL腳本參考：

\copy nation from '/data/tpch_1t/nation.tbl' DELIMITER '|';
\copy region from '/data/tpch_1t/region.tbl' DELIMITER '|';
\copy supplier from '/data/tpch_1t/supplier.tbl' DELIMITER '|';           

tbl路徑以實際路徑為準，導入shell腳本參考建立表的shell腳本（或psql進入資料庫執行）。為提高導入效率（ECS網絡帶寬保障），可以把SQL拆開并發導入。

使用OSS外表方式導入資料

将生成的資料檔案上傳到oss

./ossutil64 cp -r <tbl檔案目錄> oss://<oss bucket>/<目錄>/ 
               -i <AccessKey ID> -k <Access Key Secret> 
               -e <EndPoint>           

使用AnalyticDB for PostgreSQL 6.0的OSS外表進行TPCH測試資料導入，已在北京可用區的OSS上準備好了可以使用OSS外表的資料，且為公共可讀權限。使用者無需再進行資料生成，然後再導入資料到OSS。

OSS外表文檔參考:

OSS資料導入AnalyticDB for PostgreSQL

建立OSS外部表的建表語句示例(含OSS位址)

create readable external table ext_nation ( n_nationkey int, n_name varchar(25), n_regionkey integer, 
  n_comment varchar(152)) 
    location('oss://oss-cn-beijing.aliyuncs.com 
    filepath=data/tpch_data_1000x/nation.tbl 
    id=$AccessKey key=$AccessKeySecret 
    bucket=oss-y') FORMAT 'TEXT' (DELIMITER '|' ) ;


create readable external table ext_region ( R_REGIONKEY int, R_NAME CHAR(25),R_COMMENT VARCHAR(152)) 
    location('oss://oss-cn-beijing.aliyuncs.com 
    filepath=data/tpch_data_1000x/region.tbl 
    id=$AccessKey key=$AccessKeySecret 
    bucket=oss-y') FORMAT 'TEXT' (DELIMITER '|' ) ;

CREATE readable external TABLE ext_lineitem ( l_orderkey bigint, l_partkey bigint, l_suppkey bigint, 
  l_linenumber bigint, l_quantity double precision, l_extendedprice double precision, 
  l_discount double precision, l_tax double precision, l_returnflag CHAR(1), 
  l_linestatus CHAR(1), l_shipdate DATE, l_commitdate DATE, l_receiptdate DATE, 
  l_shipinstruct CHAR(25), l_shipmode CHAR(10), l_comment VARCHAR(44)) 
    location('oss://oss-cn-beijing.aliyuncs.com 
    filepath=data/tpch_data_1000x/lineitem.tbl 
    id= $AccessKey key= $AccessKeySecret 
    bucket=oss-y ') FORMAT 'TEXT' (DELIMITER '|' ) ;

CREATE readable external TABLE ext_orders ( o_orderkey bigint , o_custkey bigint , o_orderstatus CHAR(1) , 
  o_totalprice double precision, o_orderdate DATE , o_orderpriority CHAR(15) , o_clerk CHAR(15) , 
  o_shippriority bigint , o_comment VARCHAR(79) ) 
    location('oss://oss-cn-beijing.aliyuncs.com 
    filepath=data/tpch_data_1000x/orders.tbl 
    id=$AccessKey key=$AccessKeySecret  
    bucket=oss-y') FORMAT 'TEXT' (DELIMITER '|' ) ;

CREATE readable external TABLE ext_part ( p_partkey bigint , p_name VARCHAR(55) , p_mfgr CHAR(25) , 
  p_brand CHAR(10) , p_type VARCHAR(25) , p_size bigint , p_container CHAR(10) , 
  p_retailprice double precision , p_comment VARCHAR(23) ) 
    location('oss://oss-cn-beijing.aliyuncs.com 
    filepath=data/tpch_data_1000x/part.tbl 
    id= $AccessKey key= $AccessKeySecret 
    bucket=oss-y') FORMAT 'TEXT' (DELIMITER '|' ) ;

CREATE readable external TABLE ext_partsupp ( ps_partkey bigint , ps_suppkey bigint , 
  ps_availqty bigint , ps_supplycost double precision , ps_comment VARCHAR(199) ) 
    location('oss://oss-cn-beijing.aliyuncs.com 
    filepath=data/tpch_data_1000x/partsupp.tbl 
    id= $AccessKey key= $AccessKeySecret 
    bucket=oss-y') FORMAT 'TEXT' (DELIMITER '|' ) ;

CREATE readable external TABLE ext_supplier ( s_suppkey bigint , s_name CHAR(25) , 
  s_address VARCHAR(40) , s_nationkey bigint , s_phone CHAR(15) , s_acctbal DECIMAL(15,2) , 
  s_comment VARCHAR(101) ) 
    location('oss://oss-cn-beijing.aliyuncs.com 
    filepath=data/tpch_data_1000x/supplier.tbl 
    id= $AccessKey key= $AccessKeySecret
    bucket=oss-y') FORMAT 'TEXT' (DELIMITER '|' ) ;

CREATE readable external TABLE ext_customer ( c_custkey bigint , c_name VARCHAR(25) , 
  c_address VARCHAR(40) , c_nationkey bigint , c_phone CHAR(15) , c_acctbal double precision , 
  c_mktsegment CHAR(10) , c_comment VARCHAR(117) ) 
    location('oss://oss-cn-beijing.aliyuncs.com 
    filepath=data/tpch_data_1000x/customer.tbl 
    id= $AccessKey key= $AccessKeySecret
    bucket=oss-y') FORMAT 'TEXT' (DELIMITER '|' ) ;           

從OSS外表寫入TPC-H資料到AnalyticDB for PostgreSQL

insert into nation select * from ext_nation;
insert into region select * from ext_region;
insert into lineitem select * from ext_lineitem;
insert into orders select * from ext_orders;
insert into customer select * from ext_customer;
insert into part select * from ext_part;
insert into partsupp select * from ext_partsupp;
insert into supplier select * from ext_supplier;           

至此資料導入完畢，進入查詢執行階段

收集統計資訊

analyze nation;
analyze region;
analyze lineitem;
analyze orders;
analyze customer;
analyze part;
analyze partsupp;
analyze supplier;           

執行查詢

使用如下shell腳本測試，也可以通過psql等其他用戶端逐條執行查詢SQL。具體的22條SQL語句見本文最後。

查詢加速

特别的，AnalyticDB for PostgreSQL 6.0 的向量加速引擎，可以提升查詢性能1倍左右。

在session級别， 修改GUC變量enable_odyssey 為on ，可開啟加速引擎。

set enable_odyssey = on;

如需關閉加速引擎，設定該參數為off。

set enable_odyssey = off;

如果使用如下腳本執行22條TPCH SQL，需要在每個Query檔案開始出增加一行，

set enable_odyssey = on;

total_cost=0

for i in {1..22}
do
        echo "begin run Q${i}, query/q$i.sql , `date`"
        begin_time=`date +%s.%N`
        #psql -h ${執行個體連接配接位址} -p ${端口号} -U ${資料庫使用者} -f query/q${i}.sql > ./log/log_q${i}.out
        rc=$?
        end_time=`date +%s.%N`
        cost=`echo "$end_time-$begin_time"|bc`
        total_cost=`echo "$total_cost+$cost"|bc`
        if [ $rc -ne 0 ] ; then
              printf "run Q%s fail, cost: %.2f, totalCost: %.2f, `date`\n" $i $cost $total_cost
         else
              printf "run Q%s succ, cost: %.2f, totalCost: %.2f, `date`\n" $i $cost $total_cost
         fi
done           

測試結果

各表資料量說明，1TB資料(不含索引)

表名	資料條目數
customer	15000w
lineitem	600000w
nation	25
orders	150000w
part	20000w
partsupp	80000w
region	5
supplier	1000w

執行時間統計

SQL	1core * 64節點 SSD型(RT:秒）	4core * 32節點 SSD型(RT:秒）	4core * 32節點 SSD型 - 開啟向量加速引擎(RT:秒）
Total	3703.56	2534.29	1258.24

22個SQL

-- Q1
-- 開啟向量加速引擎，并設定開關變量為on
set enable_odyssey = on;
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 '93 day'
group by
    l_returnflag,
    l_linestatus
order by
    l_returnflag,
    l_linestatus;

-- Q2
-- 開啟向量加速引擎，并設定開關變量為on
set enable_odyssey = on;
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 = 23
    and p_type like '%STEEL'
    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 r_name = 'EUROPE'
    )
order by
    s_acctbal desc,
    n_name,
    s_name,
    p_partkey
limit 100;

-- Q3
-- 開啟向量加速引擎，并設定開關變量為on
set enable_odyssey = on;
select
    l_orderkey,
    sum(l_extendedprice * (1 - l_discount)) as revenue,
    o_orderdate,
    o_shippriority
from
    customer,
    orders,
    lineitem
where
    c_mktsegment = 'MACHINERY'
    and c_custkey = o_custkey
    and l_orderkey = o_orderkey
    and o_orderdate < date '1995-03-24'
    and l_shipdate > date '1995-03-24'
group by
    l_orderkey,
    o_orderdate,
    o_shippriority
order by
    revenue desc,
    o_orderdate
limit 10;

-- Q4
-- 開啟向量加速引擎，并設定開關變量為on
set enable_odyssey = on;
select
    o_orderpriority,
    count(*) as order_count
from
    orders
where
    o_orderdate >= date '1996-08-01'
    and o_orderdate < date '1996-08-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;

-- Q5
-- 開啟向量加速引擎，并設定開關變量為on
set enable_odyssey = on;
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 = 'MIDDLE EAST'
    and o_orderdate >= date '1994-01-01'
    and o_orderdate < date '1994-01-01' + interval '1' year
group by
    n_name
order by
    revenue desc;

-- Q6
-- 開啟向量加速引擎，并設定開關變量為on
set enable_odyssey = on;
select
    sum(l_extendedprice * l_discount) as revenue
from
    lineitem
where
    l_shipdate >= date '1994-01-01'
    and l_shipdate < date '1994-01-01' + interval '1' year
    and l_discount between 0.06 - 0.01 and 0.06 + 0.01
    and l_quantity < 24;

-- Q7
-- 開啟向量加速引擎，并設定開關變量為on
set enable_odyssey = on;
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 = 'JORDAN' and n2.n_name = 'INDONESIA')
                or (n1.n_name = 'INDONESIA' and n2.n_name = 'JORDAN')
            )
            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;

-- Q8
-- 開啟向量加速引擎，并設定開關變量為on
set enable_odyssey = on;
select
    o_year,
    sum(case
        when nation = 'INDONESIA' 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 = 'ASIA'
            and s_nationkey = n2.n_nationkey
            and o_orderdate between date '1995-01-01' and date '1996-12-31'
            and p_type = 'STANDARD BRUSHED BRASS'
    ) as all_nations
group by
    o_year
order by
    o_year;

-- Q9
-- 開啟向量加速引擎，并設定開關變量為on
set enable_odyssey = on;
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 '%chartreuse%'
    ) as profit
group by
    nation,
    o_year
order by
    nation,
    o_year desc;

-- Q10
-- 開啟向量加速引擎，并設定開關變量為on
set enable_odyssey = on;
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 '1994-08-01'
    and o_orderdate < date '1994-08-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
-- 開啟向量加速引擎，并設定開關變量為on
set enable_odyssey = on;
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 = 'INDONESIA'
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 = 'INDONESIA'
        )
order by
    value desc;

-- Q12
-- 開啟向量加速引擎，并設定開關變量為on
set enable_odyssey = on;
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 in ('REG AIR', 'TRUCK')
    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;

-- Q13
-- 開啟向量加速引擎，并設定開關變量為on
set enable_odyssey = on;
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 '%pending%requests%'
        group by
            c_custkey
    ) as c_orders (c_custkey, c_count)
group by
    c_count
order by
    custdist desc,
    c_count desc;

-- Q14
-- 開啟向量加速引擎，并設定開關變量為on
set enable_odyssey = on;
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-11-01'
    and l_shipdate < date '1994-11-01' + interval '1' month;

-- Q15
-- 開啟向量加速引擎，并設定開關變量為on
set enable_odyssey = on;
create view revenue0 (supplier_no, total_revenue) as
    select
        l_suppkey,
        sum(l_extendedprice * (1 - l_discount))
    from
        lineitem
    where
        l_shipdate >= date '1997-10-01'
        and l_shipdate < date '1997-10-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;
drop view revenue0;

-- Q16
-- 開啟向量加速引擎，并設定開關變量為on
set enable_odyssey = on;
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#44'
    and p_type not like 'SMALL BURNISHED%'
    and p_size in (36, 27, 34, 45, 11, 6, 25, 16)
    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;

-- Q17
-- 開啟向量加速引擎，并設定開關變量為on
set enable_odyssey = on;
select
    sum(l_extendedprice) / 7.0 as avg_yearly
from
    lineitem,
    part
where
    p_partkey = l_partkey
    and p_brand = 'Brand#42'
    and p_container = 'JUMBO PACK'
    and l_quantity < (
        select
            0.2 * avg(l_quantity)
        from
            lineitem
        where
            l_partkey = p_partkey
    );

-- Q18
-- 開啟向量加速引擎，并設定開關變量為on
set enable_odyssey = on;
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
-- 開啟向量加速引擎，并設定開關變量為on
set enable_odyssey = on;
select
    sum(l_extendedprice* (1 - l_discount)) as revenue
from
    lineitem,
    part
where
    (
        p_partkey = l_partkey
        and p_brand = 'Brand#43'
        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#45'
        and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
        and l_quantity >= 12 and l_quantity <= 12 + 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#11'
        and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
        and l_quantity >= 24 and l_quantity <= 24 + 10
        and p_size between 1 and 15
        and l_shipmode in ('AIR', 'AIR REG')
        and l_shipinstruct = 'DELIVER IN PERSON'
    );

-- Q20
-- 開啟向量加速引擎，并設定開關變量為on
set enable_odyssey = on;
select
    s_name,
    s_address
from
    supplier,
    nation
where
    s_suppkey in (
        select
            ps_suppkey
        from
            partsupp
        where
            ps_partkey in (
                select
                    p_partkey
                from
                    part
                where
                    p_name like 'magenta%'
            )
            and ps_availqty > (
                select
                    0.5 * sum(l_quantity)
                from
                    lineitem
                where
                    l_partkey = ps_partkey
                    and l_suppkey = ps_suppkey
                    and l_shipdate >= date '1996-01-01'
                    and l_shipdate < date '1996-01-01' + interval '1' year
            )
    )
    and s_nationkey = n_nationkey
    and n_name = 'RUSSIA'
order by
    s_name;

-- Q21
-- 開啟向量加速引擎，并設定開關變量為on
set enable_odyssey = on;
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 = 'MOZAMBIQUE'
group by
    s_name
order by
    numwait desc,
    s_name
limit 100;

-- Q22
-- 開啟向量加速引擎，并設定開關變量為on
set enable_odyssey = on;
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
                                ('13', '31', '23', '29', '30', '18', '17')
                        and c_acctbal > (
                                select
                                        avg(c_acctbal)
                                from
                                        customer
                                where
                                        c_acctbal > 0.00
                                        and substring(c_phone from 1 for 2) in
                                                ('13', '31', '23', '29', '30', '18', '17')
                        )
                        and not exists (
                                select
                                        *
                                from
                                        orders
                                where
                                        o_custkey = c_custkey
                        )
        ) as custsale
group by
        cntrycode
order by
        cntrycode;