AnalyticDB PostgreSQL 6.0版在支援ACID和分散式交易的同時，提供了優秀的巨量資料MPP分析效能。本文講述如何運行TPC-H進行測試。
TPC-H簡介
以下文字描述引用自TPC Benchmark™ H (TPC-H)規範：
“TPC-H是一個決策支援基準，由一套面向業務的臨時查詢和並發資料修改組成。選擇的查詢和填充資料庫的資料具有廣泛的行業相關性。該基準測試說明了決策支援系統可以檢查大量資料，執行高度複雜的查詢，並解答關鍵的業務問題。”
詳情請參見TPCH Specification。
說明
本文的TPC-H的實現基於TPC-H的基準測試，並不能與發行的TPC-H基準測試結果相比較，本文中的測試並不符合TPC-H基準測試的所有要求。
前提條件
登入阿里雲帳號。
已建立用於測試的AnalyticDB for PostgreSQL執行個體。建立執行個體具體操作，請參見建立執行個體。
本文測試的AnalyticDB for PostgreSQL執行個體規格如下：
引擎版本：6.0標準版
節點規格（segment）：2C16G
節點數量（segment）：32
儲存磁碟類型：ESSD雲端硬碟
節點儲存容量（segment）：200 GB
已建立用於測試的ECS執行個體。建立執行個體具體操作，請參見建立執行個體。
本文測試的ECS執行個體規格如下：
執行個體規格：ecs.g6e.4xlarge
作業系統：CentOS 7.x
系統硬碟：磁碟類型為ESSD雲端硬碟、容量為40 GiB、效能層級為PL1。
資料盤：磁碟類型為ESSD雲端硬碟、容量為2048 GiB、效能層級為PL3。
說明ECS執行個體建立完成後需要掛載資料庫，具體操作，請參見分區格式化資料盤（Linux）。
已開通OSS並建立儲存空間。
已將ECS執行個體的IP地址添加到AnalyticDB for PostgreSQL執行個體的白名單中，操作方式，請參見設定白名單。
已在ECS上安裝psql，安裝方式，請參見用戶端串連。
產生測試資料
登入ECS執行個體，登入方式，請參見串連ECS執行個體。
在ECS上執行以下命令，下載TPC-H dbgen代碼到資料盤並編譯。
本文中資料盤的路徑為
/mnt。wget https://github.com/electrum/tpch-dbgen/archive/refs/heads/master.zip yum install -y unzip zip unzip master.zip cd tpch-dbgen-master/ echo "#define EOL_HANDLING 1" >> config.h # 消除產生資料末尾的'|' make ./dbgen --help在ECS上執行如下命令，產生1 TB測試資料集。建議使用分區檔案，分區數量與AnalyticDB for PostgreSQL執行個體節點數量一致，例如本文中樣本AnalyticDB for PostgreSQL執行個體有32個計算節點，以下樣本中將建立32個分區檔案。
for((i=1;i<=32;i++)); do ./dbgen -s 1000 -S $i -C 32 -f & done說明資料量的大小對查詢速度有直接的影響，TPC-H中使用SF描述資料量，1SF對應1 GB單位。1000SF，即1 TB。1SF對應的資料量只是8個表的總資料量不包括索引等空間佔用，準備資料時需預留更多空間。
後台運行dbgen程式時間較長，您可以通過
ps -fHU $USER | grep dbgen命令查看進度，確保dbgen程式運行完成。
建立測試表
使用psql串連AnalyticDB for PostgreSQL，串連方式，請參見用戶端串連。
執行如下語句建立用於測試的8張表。
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, COMPRESSTYPE=LZ4, COMPRESSLEVEL=9) 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, COMPRESSTYPE=LZ4, COMPRESSLEVEL=9) 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, COMPRESSTYPE=LZ4, COMPRESSLEVEL=9) 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, COMPRESSTYPE=LZ4, COMPRESSLEVEL=9) 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, COMPRESSTYPE=LZ4, COMPRESSLEVEL=9) 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, COMPRESSTYPE=LZ4, COMPRESSLEVEL=9) DISTRIBUTED BY (C_CUSTKEY) ; CREATE TABLE ORDERS ( O_ORDERKEY BIGINT NOT NULL, O_CUSTKEY INTEGER NOT NULL, O_ORDERSTATUS "char" 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, COMPRESSTYPE=LZ4, COMPRESSLEVEL=9) DISTRIBUTED BY (O_ORDERKEY) ORDER BY(O_ORDERDATE) ; 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" NOT NULL, L_LINESTATUS "char" 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, COMPRESSTYPE=LZ4, COMPRESSLEVEL=9) DISTRIBUTED BY (L_ORDERKEY) ORDER BY(L_SHIPDATE) ;
匯入資料
以下內容為您介紹如何匯入資料到AnalyticDB for PostgreSQL資料庫中。
在AnalyticDB for PostgreSQL資料庫中使用COPY命令匯入nation和region兩張小表，命令如下：
\copy nation from '/mnt/tpch-dbgen-master/nation.tbl' DELIMITER '|'; \copy region from '/mnt/tpch-dbgen-master/region.tbl' DELIMITER '|';說明上述樣本中的
/mnt/tpch-dbgen-master為樣本路徑，測試過程中請替換成nation.tbl和region.tbl檔案的實際路徑。將其餘的6張大表通過ossutil上傳到OSS。更多關於ossutil的使用方法，請參見概述。
說明由於\COPY命令需要通過Master節點進行串列資料寫入處理，無法實現並行寫入大批量資料，因此需要通過OSS進行資料匯入。
在ECS上下載ossutil，命令如下：
wget http://gosspublic.alicdn.com/ossutil/1.7.3/ossutil64修改檔案的執行許可權，命令如下：
chmod 755 ossutil64執行如下語句，依次將其餘6張表的
.tbl檔案通過ossutil上傳到OSS。ls <table_name>.tbl* | while read line; do ~/ossutil64 -e <EndPoint> -i <AccessKey ID> -k <Access Key Secret> cp $line oss://<oss bucket>/<目錄>/ & done
待所有表上傳到OSS後，在AnalyticDB for PostgreSQL資料庫中執行如下語句將資料從OSS匯入表。具體資訊，請參見使用COPY/UNLOAD匯入/匯出資料到OSS。
COPY customer FROM 'oss://<oss bucket>/<目錄>/customer.tbl' ACCESS_KEY_ID '<AccessKey ID>' SECRET_ACCESS_KEY '<Access Key Secret>' FORMAT AS text "delimiter" '|' "null" '' ENDPOINT '<EndPoint>' FDW 'oss_fdw' ; COPY lineitem FROM 'oss://<oss bucket>/<目錄>/lineitem.tbl' ACCESS_KEY_ID '<AccessKey ID>' SECRET_ACCESS_KEY '<Access Key Secret>' FORMAT AS text "delimiter" '|' "null" '' ENDPOINT '<EndPoint>' FDW 'oss_fdw' ; -- lineitem表定義了排序列，資料匯入完成後可對資料進行聚簇排序 sort lineitem; COPY orders FROM 'oss://<oss bucket>/<目錄>/orders.tbl' ACCESS_KEY_ID '<AccessKey ID>' SECRET_ACCESS_KEY '<Access Key Secret>' FORMAT AS text "delimiter" '|' "null" '' ENDPOINT '<EndPoint>' FDW 'oss_fdw' ; -- orders表定義了排序列，資料匯入完成後可對資料進行聚簇排序 sort orders; COPY part FROM 'oss://<oss bucket>/<目錄>/part.tbl' ACCESS_KEY_ID '<AccessKey ID>' SECRET_ACCESS_KEY '<Access Key Secret>' FORMAT AS text "delimiter" '|' "null" '' ENDPOINT '<EndPoint>' FDW 'oss_fdw' ; COPY supplier FROM 'oss://<oss bucket>/<目錄>/supplier.tbl' ACCESS_KEY_ID '<AccessKey ID>' SECRET_ACCESS_KEY '<Access Key Secret>' FORMAT AS text "delimiter" '|' "null" '' ENDPOINT '<EndPoint>' FDW 'oss_fdw' ; COPY partsupp FROM 'oss://<oss bucket>/<目錄>/partsupp.tbl' ACCESS_KEY_ID '<AccessKey ID>' SECRET_ACCESS_KEY '<Access Key Secret>' FORMAT AS text "delimiter" '|' ENDPOINT '<EndPoint>' FDW 'oss_fdw' ;
執行查詢
您可以通過Shell指令碼進行測試，也可以通過psql等用戶端工具逐條執行查詢SQL，以下內容將分別介紹兩種執行查詢的方法。
Shell指令碼
下載tpch_query .tar.gz檔案並解壓到
~/tpch_query目錄。建立一個名為
query.sh的Shell指令碼，用於執行全部查詢，並記錄每條耗時和總耗時。Shell指令碼內容如下：
#!/bin/bash total_cost=0 for i in {1..22} do echo "begin run Q${i}, tpch_query/q$i.sql , `date`" begin_time=`date +%s.%N` ./psql ${執行個體串連地址} -p ${連接埠號碼} -U ${資料庫使用者} -f ~/tpch_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在ECS上運行
query.sh指令碼。nohup bash ~/query.sh > /tmp/tpch.log &執行如下命令查看結果。
cat /tmp/tpch.log
用戶端工具
串連AnalyticDB for PostgreSQL資料庫後，逐條執行以下語句進行查詢，並對比查詢結果。
--建立向量化計算引擎Laser外掛程式
create extension if not exists laser;
-- Q1
-- 開啟向量加速引擎，並設定開關變數為on
set laser.enable = 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 laser.enable = 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 laser.enable = 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 laser.enable = 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
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 laser.enable = 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 laser.enable = 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 laser.enable = 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 laser.enable = 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 laser.enable = 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 laser.enable = 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 laser.enable = 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 laser.enable = 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 laser.enable = 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 laser.enable = 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 laser.enable = 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 laser.enable = 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 laser.enable = 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 laser.enable = 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 laser.enable = 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 laser.enable = 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 laser.enable = 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;