本文详细介绍了如何在CentOS 6.5上搭建PostgreSQL 9.5单节点环境，并使用TPC-H 2.17.3工具生成测试数据，包括安装、配置、数据导入及执行22个查询SQL的全过程。
环境
- CentOS 6.5
- PostgreSQL 9.5
- TPC-H 2.17.3
1. 安装单节点PostgreSQL
1.1 安装repo PRM
yum install https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-6-x86_64/pgdg-centos95-9.5-3.noarch.rpm
1.2重建yum元数据
yum clean all
yum makecache1.3 安装PG
yum install postgresql95
yum install postgresql95-server1.4 初始化PG
service postgresql-9.5 initdb1.5 设置自PG启动
chkconfig postgresql-9.5 on1.6 启动PG
service postgresql-9.5 start1.8 备注
官网安装地址：点击查看
PG路径：/var/lib/pgsql/9.5/data
2. 配置PG
2.1 设置远程访问
vi /var/lib/pgsql/9.5/data/postgresql.conf
# listen_addresses项值修改为：listen_addresses="*"
vi /var/lib/pgsql/9.5/data/pg_hba.conf
# TYPE DATABASE USER CIDR-ADDRESS METHOD 选项下添加：
host all all 0.0.0.0/0 md52.2 修改PG数据库密码
切换pg用户
#su postgres
进入数据库
$psql
设置密码
postgres-# \password postgres
新建数据库
postgres-# create database tpch ;2.2 重启PG
service postgresql-9.5 restart3. 生成TPC-H测试数据
3.1 导入tpc-h-tool2.17.3到/root目录下
3.2 修改print.c适配PG
- TPC-H本身不支持PG，默认生成的数据的每一行结尾有“|”，PG的官方文档说明导入的数据不可以以“|”结尾，需要修改print.c文件
vi /root/tpc-h/dbgen/print.c
注释掉以下两句话，在145行和147行
#ifdef EOL_HANDLING
if (sep)
#endif /* EOL_HANDLING */修改为：
//#ifdef EOL_HANDLING
if (sep)
//#endif /* EOL_HANDLING */3.3 配置makefile
- 创建makefile复本
cp makefile.suite makefile - 修改makefile为：
CC = gcc
# Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)
# SQLSERVER, SYBASE, ORACLE
# Current values for MACHINE are: ATT, DOS, HP, IBM, ICL, MVS,
# SGI, SUN, U2200, VMS, LINUX, WIN32
# Current values for WORKLOAD are: TPCH
DATABASE= POSTGRESQL
MACHINE = LINUX
WORKLOAD = TPCH- 添加以下PG参数到tpcd.h
#ifdef POSTGRESQL
#define GEN_QUERY_PLAN "EXPLAIN"
#define START_TRAN "BEGIN TRANSACTION"
#define END_TRAN "COMMIT;"
#define SET_OUTPUT ""
#define SET_ROWCOUNT "LIMIT %d\n"
#define SET_DBASE ""
#endif3.4 编译
make3.5 生成数据
本操作会在目录下生成8个数据表格的相应数据文件：
customer.tbl lineitem.tbl nation.tbl orders.tbl partsupp.tbl part.tbl region.tbl supplier.tbl命令：
./dbgen -s 1 -f - -s 后接生成的数据大小，单位为G
- -f 覆盖原有的数据
4. 导入数据到PG
4.1 在PG新建相应的表格
# su postgres
bash-4.1$ psql
postgres=# \c tpch
您现在已经连接到数据库 "tpch",用户 "postgres".
testdb=# 执行以下语句：
- 建表的sql语句在dss.ddl文件中
CREATE TABLE NATION ( N_NATIONKEY INTEGER NOT NULL,
N_NAME CHAR(25) NOT NULL,
N_REGIONKEY INTEGER NOT NULL,
N_COMMENT VARCHAR(152));
CREATE TABLE REGION ( R_REGIONKEY INTEGER NOT NULL,
R_NAME CHAR(25) NOT NULL,
R_COMMENT VARCHAR(152));
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 );
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);
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 );
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);
CREATE TABLE ORDERS ( O_ORDERKEY INTEGER 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);
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 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);
4.2 导入数据
# su postgres
bash-4.1$ psql
postgres=# \c tpch
您现在已经连接到数据库 "tpch",用户 "postgres".
testdb=# 导入数据SQL语句
Copy region FROM '/root/tpc-h/dbgen/region.tbl' WITH DELIMITER AS '|';
Copy nation FROM '/root/tpc-h/dbgen/nation.tbl' WITH DELIMITER AS '|';
Copy part FROM '/root/tpc-h/dbgen/part.tbl' WITH DELIMITER AS '|';
Copy supplier FROM '/root/tpc-h/dbgen/supplier.tbl' WITH DELIMITER AS '|';
Copy customer FROM '/root/tpc-h/dbgen/customer.tbl' WITH DELIMITER AS '|';
Copy lineitem FROM '/root/tpc-h/dbgen/lineitem.tbl' WITH DELIMITER AS '|';
Copy partsupp FROM '/root/tpc-h/dbgen/partsupp.tbl' WITH DELIMITER AS '|';
Copy orders FROM '/root/tpc-h/dbgen/orders.tbl' WITH DELIMITER AS '|';4.3 添加主外键
添加主外键的sql语句在dss.ri文件中，需要做以下修改：
（1）去掉”CONNECT TO TPCD;”
（2）去掉对象前的”TPCD.”
（3）去掉外键名称
（4）去掉”COMMIT WORK;”修改后的SQL：
-- For table REGION
ALTER TABLE REGION ADD PRIMARY KEY (R_REGIONKEY);
-- For table NATION
ALTER TABLE NATION ADD PRIMARY KEY (N_NATIONKEY);
ALTER TABLE NATION ADD FOREIGN KEY (N_REGIONKEY) REFERENCES REGION;
-- For table PART
ALTER TABLE PART ADD PRIMARY KEY (P_PARTKEY);
-- For table SUPPLIER
ALTER TABLE SUPPLIER ADD PRIMARY KEY (S_SUPPKEY);
ALTER TABLE SUPPLIER ADD FOREIGN KEY (S_NATIONKEY) REFERENCES NATION;
-- For table PARTSUPP
ALTER TABLE PARTSUPP ADD PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY);
-- For table CUSTOMER
ALTER TABLE CUSTOMER ADD PRIMARY KEY (C_CUSTKEY);
ALTER TABLE CUSTOMER ADD FOREIGN KEY (C_NATIONKEY) REFERENCES NATION;
-- For table LINEITEM
ALTER TABLE LINEITEM ADD PRIMARY KEY (L_ORDERKEY, L_LINENUMBER);
-- For table ORDERS
ALTER TABLE ORDERS ADD PRIMARY KEY (O_ORDERKEY);
-- For table PARTSUPP
ALTER TABLE PARTSUPP ADD FOREIGN KEY (PS_SUPPKEY) REFERENCES SUPPLIER;
ALTER TABLE PARTSUPP ADD FOREIGN KEY (PS_PARTKEY) REFERENCES PART;
-- For table ORDERS
ALTER TABLE ORDERS ADD FOREIGN KEY (O_CUSTKEY) REFERENCES CUSTOMER;
-- For table LINEITEM
ALTER TABLE LINEITEM ADD FOREIGN KEY (L_ORDERKEY) REFERENCES ORDERS;
ALTER TABLE LINEITEM ADD FOREIGN KEY (L_PARTKEY, L_SUPPKEY) REFERENCES PARTSUPP;
COMMIT WORK;5. 执行测试
所有测试均已跑通
编写查询SQL生成脚本：
vi qgen_sql.sh
#!/bin/sh
# generate queries from query templates with qgen
DIR=.
mkdir $DIR/testSQL
cp $DIR/queries/*.sql $DIR
for FILE in $(find $DIR -maxdepth 1 -name "[0-9]*.sql")
do
DIGIT=$(echo $FILE | tr -cd '[[:digit:]]')
./qgen $DIGIT > $DIR/finals/$DIGIT.sql
done
rm *.sql修改权限生成查询sql
# chmod +x qgen_sql.sh
# ./qgen_sql.sh
# ls testSQL/
10.sql 12.sql 14.sql 16.sql 18.sql 1.sql 21.sql 2.sql 4.sql 6.sql 8.sql
11.sql 13.sql 15.sql 17.sql 19.sql 20.sql 22.sql 3.sql 5.sql 7.sql 9.sql查询的sql文件存放在testSQL目录下，需要做以下修改：
1. interval '90' day(3) 改为：interval '90'
2. PG的Limit不支持负数，遇到相应的情况，需要修改。5.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 '90'
GROUP BY
l_returnflag,
l_linestatus
ORDER BY
l_returnflag,
l_linestatus
LIMIT 10;
5.2 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 = 22
and p_type like '%NICKEL'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'MIDDLE EAST'
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 = 'MIDDLE EAST'
)
order by
s_acctbal desc,
n_name,
s_name,
p_partkey
LIMIT 100;
5.3 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-27'
and l_shipdate > date '1995-03-27'
group by
l_orderkey,
o_orderdate,
o_shippriority
order by
revenue desc,
o_orderdate
LIMIT 10;5.4 Q4
SELECT
o_orderpriority,
COUNT (*) AS order_count
FROM
orders
WHERE
o_orderdate >= DATE '1997-02-01'
AND o_orderdate < DATE '1997-02-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 10;5.5 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 = 'AMERICA'
AND o_orderdate >= DATE '1995-01-01'
AND o_orderdate < DATE '1995-01-01' + INTERVAL '1' YEAR
GROUP BY
n_name
ORDER BY
revenue DESC
LIMIT 10;5.6 Q6
SELECT
SUM (l_extendedprice * l_discount) AS revenue
FROM
lineitem
WHERE
l_shipdate >= DATE '1995-01-01'
AND l_shipdate < DATE '1995-01-01' + INTERVAL '1' YEAR
AND l_discount BETWEEN 0.07 - 0.01
AND 0.07 + 0.01
AND l_quantity < 24;
5.7 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 = 'INDONESIA' and n2.n_name = 'UNITED KINGDOM')
or (n1.n_name = 'UNITED KINGDOM' and n2.n_name = 'INDONESIA')
)
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;5.8 Q8
SELECT
o_year,
SUM (
CASE
WHEN nation = 'UNITED KINGDOM' 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 = 'EUROPE'
AND s_nationkey = n2.n_nationkey
AND o_orderdate BETWEEN DATE '1995-01-01'
AND DATE '1996-12-31'
AND p_type = 'SMALL BRUSHED COPPER'
) AS all_nations
GROUP BY
o_year
ORDER BY
o_year;5.9 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 '%hot%'
) AS profit
GROUP BY
nation,
o_year
ORDER BY
nation,
o_year DESC;
5.10 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-07-01'
and o_orderdate < date '1993-07-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;
5.11 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;5.12 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', '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;
5.13 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 '%unusual%packages%'
group by
c_custkey
) as c_orders (c_custkey, c_count)
group by
c_count
order by
custdist desc,
c_count desc;5.14 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 '1993-11-01'
and l_shipdate < date '1993-11-01' + interval '1' month;
5.15 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-06-01'
and l_shipdate < date '1994-06-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;5.16 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 'MEDIUM POLISHED%'
and p_size in (12, 40, 7, 10, 29, 33, 31, 19)
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;
5.17 Q17
select
sum(l_extendedprice) / 7.0 as avg_yearly
from
lineitem,
part
where
p_partkey = l_partkey
and p_brand = 'Brand#45'
and p_container = 'LG PACK'
and l_quantity < (
select
0.2 * avg(l_quantity)
from
lineitem
where
l_partkey = p_partkey
);
5.18 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) > 315
)
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;5.19 Q19
select
sum(l_extendedprice* (1 - l_discount)) as revenue
from
lineitem,
part
where
(
p_partkey = l_partkey
and p_brand = 'Brand#21'
and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
and l_quantity >= 1 and l_quantity <= 1 + 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#44'
and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
and l_quantity >= 13 and l_quantity <= 13 + 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#33'
and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
and l_quantity >= 26 and l_quantity <= 26 + 10
and p_size between 1 and 15
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
);
5.20 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 'cornsilk%'
)
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 = 'CHINA'
order by
s_name;5.21 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 = 'ROMANIA'
group by
s_name
order by
numwait desc,
s_name;
5.22 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
('28', '22', '11', '14', '21', '27', '18')
and c_acctbal > (
select
avg(c_acctbal)
from
customer
where
c_acctbal > 0.00
and substring(c_phone from 1 for 2) in
('28', '22', '11', '14', '21', '27', '18')
)
and not exists (
select
*
from
orders
where
o_custkey = c_custkey
)
) as custsale
group by
cntrycode
order by
cntrycode;
扫一扫
644
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
