本文介紹如何通過TPC-H測試AnalyticDB for PostgreSQL7.0版的向量分析效能。
TPC-H簡介
以下文字描述引用自TPC Benchmark™ H (TPC-H)規範：
“TPC-H是一個決策支援基準，由一套面向業務的臨時查詢和並發資料修改組成。選擇的查詢和填充資料庫的資料具有廣泛的行業相關性。該基準測試說明了決策支援系統可以檢查大量資料，執行高度複雜的查詢，並解答關鍵的業務問題。”
詳情請參見TPCH Specification。
說明
本文的TPC-H的實現基於TPC-H的基準測試，並不能與發行的TPC-H基準測試結果相比較，本文中的測試並不符合TPC-H基準測試的所有要求。
效能測試結果
本次測試基於TPC-H 100GB的資料集，運行該Benchmark下的22條標準測試SQL。本文在開啟自研計算引擎Laser和Greenplum原生計算引擎兩種情境下進行了測試，兩種情境的測試效能結合下圖。通過測試結果可以看到，Laser計算引擎對比原生計算引擎帶來了顯著的效能提升，總執行時間僅原生計算引擎的五分之一左右。
Query | AnalyticDB PostgreSQL 7.0 （單位為s） | Greenplum （單位為s） |
Q1 | 11.59 | 26.34 |
Q2 | 2.52 | 6.07 |
Q3 | 2.88 | 17.05 |
Q4 | 3.16 | 39.1 |
Q5 | 3.97 | 24.49 |
Q6 | 0.61 | 1.56 |
Q7 | 2.64 | 20.66 |
Q8 | 3.23 | 23.16 |
Q9 | 13.91 | 63.56 |
Q10 | 4.81 | 13.92 |
Q11 | 1.66 | 3.82 |
Q12 | 6.42 | 13.56 |
Q13 | 8.82 | 21.55 |
Q14 | 0.78 | 1.7 |
Q15 | 1.9 | 3.86 |
Q16 | 1.65 | 4.25 |
Q17 | 3.69 | 107.32 |
Q18 | 14.51 | 85.78 |
Q19 | 8.66 | 16.08 |
Q20 | 2.23 | 25.75 |
Q21 | 23.68 | 49.78 |
Q22 | 4.82 | 14.49 |
SUM | 128.14 | 583.85 |
準備工作
使用阿里雲帳號或具備
AliyunGPDBFullAccess、AliyunECSFullAccess和AliyunOSSFullAccess許可權的RAM使用者，並為其建立AccessKey ID和AccessKey Secret。AnalyticDB for PostgreSQL執行個體和OSS儲存空間Bucket位於同一地區。
已在AnalyticDB for PostgreSQL執行個體中安裝了fixnumeric外掛程式。
已將ECS執行個體的私網IP地址添加到AnalyticDB for PostgreSQL執行個體的白名單中。
已在ECS上安裝psql。
測試環境
AnalyticDB for PostgreSQL執行個體規格 | ECS執行個體規格 |
|
|
產生測試資料
以下內容將指導您產生100 GB測試資料，並將測試資料上傳至OSS。
在ECS上下載並解壓dbqgen.tar。
本文中資料盤的路徑為
/mnt。# 下載dbqgen檔案。 wget https://help-static-aliyun-doc.aliyuncs.com/file-manage-files/zh-CN/20230821/bclo/dbqgen.tar # 解壓dbqgen檔案 tar -xvf dbqgen.tar開啟dbqgen檔案，修改dbgen_data.sh檔案。
進入dbqgen目錄。
cd dbqgen編輯dbgen_data.sh檔案。
vim dbgen_data.sh按i鍵進行編輯，將原檔案中的
./dbgen -s 450 -S $i -C 192 -f &替換為./dbgen -s 100 -S $i -C 32 -f &。說明其中-s參數為資料量，單位為GB；-C為檔案塊的數量，建議設定為Segment節點數量的整數倍。
按Esc鍵退出編輯，輸入
:wq進行儲存。
產生測試資料，產生的測試資料會在dbqgen目錄下。
sh dbgen_data.sh將測試資料上傳至OSS。
如果您是首次使用OSS，建議您查看OSS文檔瞭解ossutil工具的安裝與使用方法，具體資訊，請參見命令列工具ossutil 1.0。
上傳資料至OSS的命令如下：
./ossutil64 cp -r <測試資料所在的路徑> <OSS Bucket路徑> --exclude "*.tbl*"樣本如下：
./ossutil64 cp -r /mnt/dbqgen/ oss://adbpg --exclude "*.tbl*"
建立表並匯入資料
以下內容將指導您在AnalyticDB PostgreSQL版中建立測試表並將OSS上的測試資料匯入測試表中。
使用psql工具串連AnalyticDB PostgreSQL版執行個體。安裝工具和串連資料庫的方法，請參見用戶端串連。
建立TPC-H測試表。
DROP TABLE IF EXISTS NATION; CREATE TABLE NATION ( N_NATIONKEY INTEGER NOT NULL, N_NAME CHAR(25) NOT NULL, N_REGIONKEY INTEGER NOT NULL, N_COMMENT VARCHAR(152) ) USING beam WITH(compresstype=auto,compresslevel=1) DISTRIBUTED REPLICATED ; DROP TABLE IF EXISTS REGION; CREATE TABLE REGION ( R_REGIONKEY INTEGER NOT NULL, R_NAME CHAR(25) NOT NULL, R_COMMENT VARCHAR(152) ) USING beam WITH(compresstype=auto,compresslevel=1) DISTRIBUTED REPLICATED ; DROP TABLE IF EXISTS PART; 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 FIXNUMERIC(19) NOT NULL, P_COMMENT VARCHAR(23) NOT NULL ) USING beam WITH(compresstype=auto,compresslevel=1) DISTRIBUTED BY (P_PARTKEY) ; DROP TABLE IF EXISTS SUPPLIER; 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 FIXNUMERIC(19) NOT NULL, S_COMMENT VARCHAR(101) NOT NULL ) USING beam WITH(compresstype=auto,compresslevel=1) DISTRIBUTED BY (S_SUPPKEY) ; DROP TABLE IF EXISTS PARTSUPP; CREATE TABLE PARTSUPP ( PS_PARTKEY INTEGER NOT NULL, PS_SUPPKEY INTEGER NOT NULL, PS_AVAILQTY INTEGER NOT NULL, PS_SUPPLYCOST FIXNUMERIC(19) NOT NULL, PS_COMMENT VARCHAR(199) NOT NULL ) USING beam WITH(compresstype=auto,compresslevel=1) DISTRIBUTED BY (PS_PARTKEY) ; DROP TABLE IF EXISTS CUSTOMER; 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 VARCHAR(15) NOT NULL, C_ACCTBAL FIXNUMERIC(19) NOT NULL, C_MKTSEGMENT CHAR(10) NOT NULL, C_COMMENT VARCHAR(117) NOT NULL ) USING beam WITH(compresstype=auto,compresslevel=1) DISTRIBUTED BY (C_CUSTKEY) ; DROP TABLE IF EXISTS ORDERS; CREATE TABLE ORDERS ( O_ORDERKEY BIGINT NOT NULL, O_CUSTKEY INTEGER NOT NULL, O_ORDERSTATUS "CHAR" NOT NULL, O_TOTALPRICE FIXNUMERIC(19) 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 ) USING beam WITH(compresstype=auto,compresslevel=1) 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 FIXNUMERIC(19) NOT NULL, L_EXTENDEDPRICE FIXNUMERIC(19) NOT NULL, L_DISCOUNT FIXNUMERIC(19) NOT NULL, L_TAX FIXNUMERIC(19) 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 ) USING beam WITH(compresstype=auto,compresslevel=1) DISTRIBUTED BY (L_ORDERKEY) ORDER BY(L_SHIPDATE);建立OSS Foreign Table。OSS Foreign Table用於匯入OSS上的測試資料，更多關於OSS Foreign Table的使用方法，請參見OSS Foreign Table功能概覽。
--建立OSS Server。 CREATE SERVER oss_serv FOREIGN DATA WRAPPER oss_fdw OPTIONS ( endpoint 'oss-cn-hang****.aliyuncs.com', bucket 'testBucketName'); --建立User Mapping。 CREATE USER MAPPING FOR PUBLIC SERVER oss_serv OPTIONS ( id 'LTAI****************', key 'yourAccessKeySecret'); --建立OSS Foreign Table。 CREATE FOREIGN TABLE ext_nation( N_NATIONKEY INT, N_NAME VARCHAR(25), N_REGIONKEY INTEGER, N_COMMENT VARCHAR(152) ) SERVER oss_serv OPTIONS ( PREFIX '<Dir>/nation.tbl', FORMAT 'text', DELIMITER '|' ); CREATE FOREIGN TABLE ext_region( R_REGIONKEY INT, R_NAME CHAR(25), R_COMMENT VARCHAR(152) ) SERVER oss_serv OPTIONS ( PREFIX '<Dir>/region.tbl', FORMAT 'text', DELIMITER '|' ); CREATE FOREIGN TABLE ext_lineitem ( L_ORDERKEY BIGINT, L_PARTKEY BIGINT, L_SUPPKEY BIGINT, L_LINENUMBER BIGINT, L_QUANTITY FIXNUMERIC, L_EXTENDEDPRICE FIXNUMERIC, L_DISCOUNT FIXNUMERIC, L_TAX FIXNUMERIC, 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) ) SERVER oss_serv OPTIONS ( PREFIX '<Dir>/lineitem.tbl', FORMAT 'text', DELIMITER '|' ); CREATE FOREIGN TABLE ext_orders ( O_ORDERKEY BIGINT, O_CUSTKEY BIGINT, O_ORDERSTATUS CHAR(1) , O_TOTALPRICE FIXNUMERIC, O_ORDERDATE DATE, O_ORDERPRIORITY CHAR(15), O_CLERK CHAR(15), O_SHIPPRIORITY BIGINT, O_COMMENT VARCHAR(79) ) SERVER oss_serv OPTIONS ( PREFIX '<Dir>/orders.tbl', FORMAT 'text', DELIMITER '|' ); CREATE FOREIGN 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 FIXNUMERIC, P_COMMENT VARCHAR(23) ) SERVER oss_serv OPTIONS ( PREFIX '<Dir>/part.tbl', FORMAT 'text', DELIMITER '|' ); CREATE FOREIGN TABLE ext_partsupp ( PS_PARTKEY BIGINT, PS_SUPPKEY BIGINT, PS_AVAILQTY BIGINT, PS_SUPPLYCOST FIXNUMERIC, PS_COMMENT VARCHAR(199) ) SERVER oss_serv OPTIONS ( PREFIX '<Dir>/partsupp.tbl', FORMAT 'text', DELIMITER '|' ); CREATE FOREIGN 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) ) SERVER oss_serv OPTIONS ( PREFIX '<Dir>/supplier.tbl', FORMAT 'text', DELIMITER '|' ); CREATE FOREIGN TABLE ext_customer ( C_CUSTKEY BIGINT, C_NAME VARCHAR(25), C_ADDRESS VARCHAR(40), C_NATIONKEY BIGINT, C_PHONE CHAR(15), C_ACCTBAL FIXNUMERIC, C_MKTSEGMENT CHAR(10), C_COMMENT VARCHAR(117) ) SERVER oss_serv OPTIONS ( PREFIX '<Dir>/customer.tbl', FORMAT 'text', DELIMITER '|' ); CREATE FOREIGN TABLE ext_region (R_REGIONKEY INT, R_NAME CHAR(25), R_COMMENT VARCHAR(152)) SERVER oss_serv options ( prefix 'tpch500g/region.tbl', format 'text', DELIMITER '|' ); CREATE FOREIGN TABLE ext_lineitem ( l_orderkey bigint, l_partkey bigint, l_suppkey bigint, l_linenumber bigint, l_quantity FIXNUMERIC, l_extendedprice FIXNUMERIC, l_discount FIXNUMERIC, l_tax FIXNUMERIC, 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)) SERVER oss_serv options ( prefix 'tpch500g/lineitem.tbl', format 'text', DELIMITER '|' ); CREATE FOREIGN TABLE ext_orders ( o_orderkey BIGINT, o_custkey BIGINT, o_orderstatus CHAR(1), o_totalprice FIXNUMERIC, o_orderdate DATE, o_orderpriority CHAR(15), o_clerk CHAR(15), o_shippriority BIGINT, o_comment VARCHAR(79)) SERVER oss_serv options ( prefix 'tpch500g/orders.tbl', format 'text', DELIMITER '|' ); CREATE FOREIGN 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 FIXNUMERIC, p_comment VARCHAR(23)) SERVER oss_serv OPTIONS ( prefix 'tpch500g/part.tbl', format 'text', DELIMITER '|' ); CREATE FOREIGN TABLE ext_partsupp ( ps_partkey BIGINT, ps_suppkey BIGINT, ps_availqty BIGINT, ps_supplycost FIXNUMERIC , ps_comment VARCHAR(199)) SERVER oss_serv OPTIONS ( prefix 'tpch500g/partsupp.tbl', format 'text', DELIMITER '|' ); CREATE FOREIGN 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)) SERVER oss_serv OPTIONS ( prefix 'tpch500g/supplier.tbl', format 'text', DELIMITER '|' ); CREATE FOREIGN TABLE ext_customer ( c_custkey BIGINT, c_name VARCHAR(25), c_address VARCHAR(40), c_nationkey BIGINT, c_phone CHAR(15), c_acctbal FIXNUMERIC, c_mktsegment CHAR(10), c_comment VARCHAR(117)) SERVER oss_serv OPTIONS ( prefix 'tpch500g/customer.tbl', format 'text', DELIMITER '|' );將OSS上的資料匯入至TPC-H測試表中。
--從OSS Foreign Table匯入資料至測試表。 INSERT INTO nation SELECT * FROM ext_nation; INSERT INTO region SELECT * FROM ext_region; INSERT INTO orders SELECT * FROM ext_orders; INSERT INTO customer SELECT * FROM ext_customer; INSERT INTO partsupp SELECT * FROM ext_partsupp; INSERT INTO part SELECT * FROM ext_part; INSERT INTO supplier SELECT * FROM ext_supplier; INSERT INTO lineitem SELECT * FROM ext_lineitem; --刪除OSS Foreign Table。 DROP FOREIGN TABLE ext_lineitem; DROP FOREIGN TABLE ext_orders; DROP FOREIGN TABLE ext_customer; DROP FOREIGN TABLE ext_part; DROP FOREIGN TABLE ext_partsupp; DROP FOREIGN TABLE ext_supplier; DROP FOREIGN TABLE ext_nation; DROP FOREIGN TABLE ext_region;
統計資訊收集
執行以下命令，收集統計資訊。
ANALYZE (fullscan) lineitem ;
ANALYZE (fullscan) orders ;
ANALYZE (fullscan) customer ;
ANALYZE (fullscan) supplier ;
ANALYZE (fullscan) part ;
ANALYZE (fullscan) partsupp ;
ANALYZE (fullscan) region ;
ANALYZE (fullscan) nation ;運行測試查詢
-- 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 '93 day'
GROUP BY
l_returnflag,
l_linestatus
ORDER BY
l_returnflag,
l_linestatus;
-- 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 = 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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#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
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
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
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;