（原创）select测试用例(TPC-H)-可加入到自动化MTR测试
在select测试中，我们需要覆盖select中各种支持语法等，因此进行了TPC-H的select优化以此来满足测试需求，主要优化的内容为：8张表，每张表插入的数据为5条，22个查询SQL，每个SQL至少能查询出一条测试结果。
创建8张表的SQL
#create 8 tables
CREATE TABLE `customer` (
`C_CUSTKEY` int(11) NOT NULL,
`C_NAME` varchar(25) NOT NULL,
`C_ADDRESS` varchar(40) NOT NULL,
`C_NATIONKEY` int(11) 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,
PRIMARY KEY (`C_CUSTKEY`)
) ENGINE=innodb DEFAULT CHARSET=utf8mb4;
CREATE TABLE `lineitem` (
`L_ORDERKEY` int(11) NOT NULL,
`L_PARTKEY` int(11) NOT NULL,
`L_SUPPKEY` int(11) NOT NULL,
`L_LINENUMBER` int(11) 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,
PRIMARY KEY (`L_ORDERKEY`,`L_LINENUMBER`)
) ENGINE=innodb DEFAULT CHARSET=utf8mb4;
CREATE TABLE `nation` (
`N_NATIONKEY` int(11) NOT NULL,
`N_NAME` char(25) NOT NULL,
`N_REGIONKEY` int(11) NOT NULL,
`N_COMMENT` varchar(152) DEFAULT NULL,
PRIMARY KEY (`N_NATIONKEY`)
) ENGINE=innodb DEFAULT CHARSET=utf8mb4;
CREATE TABLE `orders` (
`O_ORDERKEY` int(11) NOT NULL,
`O_CUSTKEY` int(11) 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` int(11) NOT NULL,
`O_COMMENT` varchar(79) NOT NULL,
PRIMARY KEY (`O_ORDERKEY`)
) ENGINE=innodb DEFAULT CHARSET=utf8mb4;
CREATE TABLE `part` (
`P_PARTKEY` int(11) 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` int(11) NOT NULL,
`P_CONTAINER` char(10) NOT NULL,
`P_RETAILPRICE` decimal(15,2) NOT NULL,
`P_COMMENT` varchar(23) NOT NULL,
PRIMARY KEY (`P_PARTKEY`)
) ENGINE=innodb DEFAULT CHARSET=utf8mb4;
CREATE TABLE `partsupp` (
`PS_PARTKEY` int(11) NOT NULL,
`PS_SUPPKEY` int(11) NOT NULL,
`PS_AVAILQTY` int(11) NOT NULL,
`PS_SUPPLYCOST` decimal(15,2) NOT NULL,
`PS_COMMENT` varchar(199) NOT NULL,
PRIMARY KEY (`PS_PARTKEY`,`PS_SUPPKEY`)
) ENGINE=innodb DEFAULT CHARSET=utf8mb4;
CREATE TABLE `region` (
`R_REGIONKEY` int(11) NOT NULL,
`R_NAME` char(25) NOT NULL,
`R_COMMENT` varchar(152) DEFAULT NULL,
PRIMARY KEY (`R_REGIONKEY`)
) ENGINE=innodb DEFAULT CHARSET=utf8mb4;
CREATE TABLE `supplier` (
`S_SUPPKEY` int(11) NOT NULL,
`S_NAME` char(25) NOT NULL,
`S_ADDRESS` varchar(40) NOT NULL,
`S_NATIONKEY` int(11) NOT NULL,
`S_PHONE` char(15) NOT NULL,
`S_ACCTBAL` decimal(15,2) NOT NULL,
`S_COMMENT` varchar(101) NOT NULL,
PRIMARY KEY (`S_SUPPKEY`)
) ENGINE=innodb DEFAULT CHARSET=utf8mb4;
#create revene0 view
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `revenue0`
AS SELECT
`lineitem`.`L_SUPPKEY` AS `supplier_no`,sum((`lineitem`.`L_EXTENDEDPRICE` * (1 - `lineitem`.`L_DISCOUNT`))) AS `total_revenue`
FROM `lineitem` where ((`lineitem`.`L_SHIPDATE` >= DATE'1996-01-01') and (`lineitem`.`L_SHIPDATE` < DATE'1996-04-01')) group by `lineitem`.`L_SUPPKEY`;
insert into 插入数据
#insert customer
INSERT INTO `customer` (`C_CUSTKEY`, `C_NAME`, `C_ADDRESS`, `C_NATIONKEY`, `C_PHONE`, `C_ACCTBAL`, `C_MKTSEGMENT`, `C_COMMENT`)
VALUES
(1, 'Customer#000000001', 'IVhzIApeRb ot,c,E', 15, '25-989-741-2988', 711.56, 'BUILDING', 'to the even, regular platelets. regular, ironic epitaphs nag e'),
(2, 'Customer#000000002', 'XSTf4,NCwDVaWNe6tEgvwfmRchLXak', 13, '23-768-687-3665', 121.65, 'AUTOMOBILE', 'l accounts. blithely ironic theodolites integrate boldly: caref'),
(3, 'Customer#000000003', 'MG9kdTD2WBHm', 1, '11-719-748-3364', 7498.12, 'AUTOMOBILE', ' deposits eat slyly ironic, even instructions. express foxes detect slyly. blithely even accounts abov'),
(4, 'Customer#000000004', 'XxVSJsLAGtn', 4, '14-128-190-5944', 2866.83, 'MACHINERY', ' requests. final, regular ideas sleep final accou'),
(5, 'Customer#000000005', 'KvpyuHCplrB84WgAiGV6sYpZq7Tj', 3, '13-750-942-6364', 794.47, 'HOUSEHOLD', 'n accounts will have to unwind. foxes cajole accor');
#insert lineitem
INSERT INTO `lineitem` (`L_ORDERKEY`, `L_PARTKEY`, `L_SUPPKEY`, `L_LINENUMBER`, `L_QUANTITY`, `L_EXTENDEDPRICE`, `L_DISCOUNT`, `L_TAX`, `L_RETURNFLAG`, `L_LINESTATUS`, `L_SHIPDATE`, `L_COMMITDATE`, `L_RECEIPTDATE`, `L_SHIPINSTRUCT`, `L_SHIPMODE`, `L_COMMENT`)
VALUES
(1, 1551894, 76910, 1, 17.00, 33078.94, 0.04, 0.02, 'N', 'O', '1996-03-13', '1996-02-12', '1996-03-22', 'DELIVER IN PERSON', 'TRUCK', 'egular courts above the'),
(1, 673091, 73092, 2, 36.00, 38306.16, 0.09, 0.06, 'N', 'O', '1996-04-12', '1996-02-28', '1996-04-20', 'TAKE BACK RETURN', 'MAIL', 'ly final dependencies: slyly bold '),
(1, 636998, 36999, 3, 8.00, 15479.68, 0.10, 0.02, 'N', 'O', '1996-01-29', '1996-03-05', '1996-01-31', 'TAKE BACK RETURN', 'REG AIR', 'riously. regular, express dep'),
(1, 21315, 46316, 4, 28.00, 34616.68, 0.09, 0.06, 'N', 'O', '1996-04-21', '1996-03-30', '1996-05-16', 'NONE', 'AIR', 'lites. fluffily even de'),
(1, 240267, 15274, 5, 24.00, 28974.00, 0.10, 0.04, 'N', 'O', '1996-03-30', '1996-03-14', '1996-04-01', 'NONE', 'FOB', ' pending foxes. slyly re');
#insert partsupp
INSERT INTO `partsupp` (`PS_PARTKEY`, `PS_SUPPKEY`, `PS_AVAILQTY`, `PS_SUPPLYCOST`, `PS_COMMENT`)
VALUES
(1, 2, 3325, 771.64, ', even theodolites. regular, final theodolites eat after the carefully pending foxes. furiously regular deposits sleep slyly. carefully bold realms above the ironic dependencies haggle careful'),
(1, 25002, 8076, 993.49, 'ven ideas. quickly even packages print. pending multipliers must have to are fluff'),
(1, 50002, 3956, 337.09, 'after the fluffily ironic deposits? blithely special dependencies integrate furiously even excuses. blithely silent theodolites could have to haggle pending, express requests; fu'),
(1, 75002, 4069, 357.84, 'al, regular dependencies serve carefully after the quickly final pinto beans. furiously even deposits sleep quickly final, silent pinto beans. fluffily reg'),
(2, 3, 8895, 378.49, 'nic accounts. final accounts sleep furiously about the ironic, bold packages. regular, regular accounts');
#insert orders
INSERT INTO `orders` (`O_ORDERKEY`, `O_CUSTKEY`, `O_ORDERSTATUS`, `O_TOTALPRICE`, `O_ORDERDATE`, `O_ORDERPRIORITY`, `O_CLERK`, `O_SHIPPRIORITY`, `O_COMMENT`)
VALUES
(1, 369001, 'O', 186600.18, '1996-01-02', '5-LOW', 'Clerk#000009506', 0, 'nstructions sleep furiously among '),
(2, 780017, 'O', 66219.63, '1996-12-01', '1-URGENT', 'Clerk#000008792', 0, ' foxes. pending accounts at the pending, silent asymptot'),
(3, 1233140, 'F', 270741.97, '1993-10-14', '5-LOW', 'Clerk#000009543', 0, 'sly final accounts boost. carefully regular ideas cajole carefully. depos'),
(4, 1367761, 'O', 41714.38, '1995-10-11', '5-LOW', 'Clerk#000001234', 0, 'sits. slyly regular warthogs cajole. regular, regular theodolites acro'),
(5, 444848, 'F', 122444.33, '1994-07-30', '5-LOW', 'Clerk#000009248', 0, 'quickly. bold deposits sleep slyly. packages use slyly');
#insert nation
INSERT INTO `nation` (`N_NATIONKEY`, `N_NAME`, `N_REGIONKEY`, `N_COMMENT`)
VALUES
(0, 'ALGERIA', 0, ' haggle. carefully final deposits detect slyly agai'),
(1, 'ARGENTINA', 1, 'al foxes promise slyly according to the regular accounts. bold requests alon'),
(2, 'BRAZIL', 1, 'y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special '),
(3, 'CANADA', 1, 'eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold'),
(4, 'EGYPT', 4, 'y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d');
#insert part
INSERT INTO `part` (`P_PARTKEY`, `P_NAME`, `P_MFGR`, `P_BRAND`, `P_TYPE`, `P_SIZE`, `P_CONTAINER`, `P_RETAILPRICE`, `P_COMMENT`)
VALUES
(1, 'goldenrod lavender spring chocolate lace', 'Manufacturer#1', 'Brand#13', 'PROMO BURNISHED COPPER', 7, 'JUMBO PKG', 901.00, 'ly. slyly ironi'),
(2, 'blush thistle blue yellow saddle', 'Manufacturer#1', 'Brand#13', 'LARGE BRUSHED BRASS', 1, 'LG CASE', 902.00, 'lar accounts amo'),
(3, 'spring green yellow purple cornsilk', 'Manufacturer#4', 'Brand#42', 'STANDARD POLISHED BRASS', 21, 'WRAP CASE', 903.00, 'egular deposits hag'),
(4, 'cornflower chocolate smoke green pink', 'Manufacturer#3', 'Brand#34', 'SMALL PLATED BRASS', 14, 'MED DRUM', 904.00, 'p furiously r'),
(5, 'forest brown coral puff cream', 'Manufacturer#3', 'Brand#32', 'STANDARD POLISHED TIN', 15, 'SM PKG', 905.00, ' wake carefully ');
#insert region
INSERT INTO `region` (`R_REGIONKEY`, `R_NAME`, `R_COMMENT`)
VALUES
(0, 'AFRICA', 'lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to '),
(1, 'AMERICA', 'hs use ironic, even requests. s'),
(2, 'ASIA', 'ges. thinly even pinto beans ca'),
(3, 'EUROPE', 'ly final courts cajole furiously final excuse'),
(4, 'MIDDLE EAST', 'uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl');
#insert supplier
INSERT INTO `supplier` (`S_SUPPKEY`, `S_NAME`, `S_ADDRESS`, `S_NATIONKEY`, `S_PHONE`, `S_ACCTBAL`, `S_COMMENT`)
VALUES
(1, 'Supplier#000000001', ' N kD4on9OM Ipw3,gf0JBoQDd7tgrzrddZ', 17, '27-918-335-1736', 5755.94, 'each slyly above the careful'),
(2, 'Supplier#000000002', '89eJ5ksX3ImxJQBvxObC,', 5, '15-679-861-2259', 4032.68, ' slyly bold instructions. idle dependen'),
(3, 'Supplier#000000003', 'q1,G3Pj6OjIuUYfUoH18BFTKP5aU9bEV3', 1, '11-383-516-1199', 4192.40, 'blithely silent requests after the express dependencies are sl'),
(4, 'Supplier#000000004', 'Bk7ah4CK8SYQTepEmvMkkgMwg', 15, '25-843-787-7479', 4641.08, 'riously even requests above the exp'),
(5, 'Supplier#000000005', 'Gcdm2rJRzl5qlTVzc', 11, '21-151-690-3663', -283.84, '. slyly regular pinto bea');
#update data
UPDATE lineitem SET L_PARTKEY = 1 WHERE L_LINENUMBER = 4;
22条查询SQL与对应的查询结果
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' day (3)
l_shipdate <= date '1998-12-01' - interval '90' day
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;
###查询结果
l_returnflag l_linestatus sum_qty sum_base_price sum_disc_price sum_charge avg_qty avg_price avg_disc count_order
N O 113.00 150455.46 138123.8788 144062.279752 22.600000 30091.092000 0.084000 5
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 = 1
and p_type like '%BRASS'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'AMERICA'
and ps_supplycost = (
select
min(ps_supplycost)
from
partsupp,
supplier,
nation,
region,
part
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'AMERICA'
)
order by
s_acctbal desc,
n_name,
s_name,
p_partkey
limit 100;
##测试结果
s_acctbal s_name n_name p_partkey p_mfgr s_address s_phone s_comment
4192.40 Supplier#000000003 ARGENTINA 2 Manufacturer#1 q1,G3Pj6OjIuUYfUoH18BFTKP5aU9bEV3 11-383-516-1199 blithely silent requests after the express dependencies are sl
Q3
select
l_orderkey,
sum(l_extendedprice * (1 - l_discount)) as revenue,
o_orderdate,
o_shippriority
from
customer,
orders,
lineitem
where
l_orderkey = o_orderkey
or o_orderdate < date '1993-03-15'
and l_shipdate > date '1997-01-01'
group by
l_orderkey,
o_orderdate,
o_shippriority
order by
revenue desc,
o_orderdate
limit 10;
###测试结果
l_orderkey revenue o_orderdate o_shippriority
1 690619.3940 1996-01-02 0
Q4
select
o_orderpriority,
count(*) as order_count
from
orders,lineitem
where
l_orderkey = o_orderkey
and o_orderdate >= date '1996-01-02'
and o_orderdate < date '1991-07-01' + interval '3' month
and l_commitdate < l_receiptdate
or o_orderkey <> l_orderkey
group by
o_orderpriority
order by
o_orderpriority;
##测试结果
o_orderpriority order_count
1-URGENT 5
5-LOW 15
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 or s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'AMERICA' and o_orderdate >= date '1996-01-01' and o_orderdate < date '1996-01-01' + interval '1' year group by n_name order by revenue desc;
##测试结果
n_name revenue
ARGENTINA 1381238.7880
Q6
select sum(l_extendedprice * l_discount) as revenue from lineitem where l_shipdate >= date '1996-03-13' and l_shipdate < date '1996-03-13' + interval '1' year and l_discount between 0.03 - 0.01 and 0.07 + 0.01 and l_quantity < 24;
###测试结果
revenue
1323.1576
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
or (
(n1.n_name = 'CANADA' and n2.n_name = 'ARGENTINA')
or (n1.n_name = 'BRAZIL' and n2.n_name = 'ALGERIA')
)
and l_shipdate between date '1996-01-29' and date '1996-12-31'
) as shipping
group by
supp_nation,
cust_nation,
l_year
order by
supp_nation,
cust_nation,
l_year;
###测试结果
supp_nation cust_nation l_year revenue
BRAZIL ALGERIA 1996 17265484.8500
CANADA ARGENTINA 1996 17265484.8500
Q8
select
o_year,
sum(case
when nation = 'ARGENTINA' 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
or o_orderdate between date '1996-01-02' and date '1996-12-31'
and p_type = 'PROMO BURNISHED COPPER'
) as all_nations
group by
o_year
order by
o_year;
##测试结果
o_year mkt_share
1996 0.20000000
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
or p_name like '%green%'
) as profit
group by
nation,
o_year
order by
nation,
o_year desc;
##测试结果
nation o_year sum_profit
ALGERIA 1996 7397264.8800
ALGERIA 1995 3698632.4400
ALGERIA 1994 3698632.4400
ALGERIA 1993 3698632.4400
ARGENTINA 1996 7397264.8800
ARGENTINA 1995 3698632.4400
ARGENTINA 1994 3698632.4400
ARGENTINA 1993 3698632.4400
BRAZIL 1996 7397264.8800
BRAZIL 1995 3698632.4400
BRAZIL 1994 3698632.4400
BRAZIL 1993 3698632.4400
CANADA 1996 7397264.8800
CANADA 1995 3698632.4400
CANADA 1994 3698632.4400
CANADA 1993 3698632.4400
EGYPT 1996 7397264.8800
EGYPT 1995 3698632.4400
EGYPT 1994 3698632.4400
EGYPT 1993 3698632.4400
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 '1996-01-01'
and o_orderdate < date '1993-10-01' + interval '3' month
and l_returnflag = 'N'
or 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;
###测试结果
c_custkey c_name revenue c_acctbal n_name c_address c_phone c_comment
3 Customer#000000003 690619.3940 7498.12 ARGENTINA MG9kdTD2WBHm 11-719-748-3364 deposits eat slyly ironic, even instructions. express foxes detect slyly. blithely even accounts abov
5 Customer#000000005 690619.3940 794.47 CANADA KvpyuHCplrB84WgAiGV6sYpZq7Tj 13-750-942-6364 n accounts will have to unwind. foxes cajole accor
4 Customer#000000004 690619.3940 2866.83 EGYPT XxVSJsLAGtn 14-128-190-5944 requests. final, regular ideas sleep final accou
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 = 'ARGENTINA'
group by
ps_partkey having
sum(ps_supplycost * ps_availqty) > (
select
sum(ps_supplycost * ps_availqty) * 0.0000010000
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'ARGENTINA'
)
order by
value desc;
###测试结果
ps_partkey value
2 3366668.55
Q12
select
l_shipmode,
sum(case
when o_orderpriority = '1-URGENT'
or o_orderpriority = '5-LOW'
then 1
else 0
end) as high_line_count,
sum(case
when o_orderpriority <> '1-URGENT'
and o_orderpriority <> '5-LOW'
then 1
else 0
end) as low_line_count
from
orders,
lineitem
where
o_orderkey = l_orderkey
and l_shipmode in ('TRUCK','MAIL','REG AIR','AIR','FOB')
group by
l_shipmode
order by
l_shipmode;
###测试结果
l_shipmode high_line_count low_line_count
AIR 1 0
FOB 1 0
MAIL 1 0
REG AIR 1 0
TRUCK 1 0
Q13
select
c_count,
count(*) as custdist
from
(
select
c_custkey as 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
) as c_orders
group by
c_count
order by
custdist desc,
c_count desc;
###测试结果
c_count custdist
0 5
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 '1996-04-21';
###测试结果
promo_revenue
100.0000000000
Q15
select
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
from
supplier,
revenue0
where
total_revenue = (
select
max(total_revenue)
from
revenue0
)
order by
s_suppkey;
###测试结果
s_suppkey s_name s_address s_phone total_revenue
1 Supplier#000000001 N kD4on9OM Ipw3,gf0JBoQDd7tgrzrddZ 27-918-335-1736 31755.7824
2 Supplier#000000002 89eJ5ksX3ImxJQBvxObC, 15-679-861-2259 31755.7824
3 Supplier#000000003 q1,G3Pj6OjIuUYfUoH18BFTKP5aU9bEV3 11-383-516-1199 31755.7824
4 Supplier#000000004 Bk7ah4CK8SYQTepEmvMkkgMwg 25-843-787-7479 31755.7824
5 Supplier#000000005 Gcdm2rJRzl5qlTVzc 21-151-690-3663 31755.7824
Q16
select
p_brand,
p_type,
p_size,
count(distinct ps_suppkey) as supplier_cnt
from
partsupp,
part,supplier
where
p_brand <> 'Brand#13'
and p_type not like 'PROMO BURNISHED%'
and p_size in (7,1,21,14,15)
and ps_suppkey <> s_suppkey
and s_comment like '%y%'
group by
p_brand,
p_type,
p_size
order by
supplier_cnt desc,
p_brand,
p_type,
p_size;
###测试结果
p_brand p_type p_size supplier_cnt
Brand#32 STANDARD POLISHED TIN 15 5
Brand#34 SMALL PLATED BRASS 14 5
Brand#42 STANDARD POLISHED BRASS 21 5
Q17
select
sum(l_extendedprice) / 7.0 as avg_yearly
from
lineitem,
part
where
p_partkey = l_partkey
and p_brand = '%Brand%'
or p_container = 'JUMBO PKG'
or l_quantity < (
select
0.2 * avg(l_quantity)
from
lineitem,part
where
l_partkey = p_partkey
);
###测试结果
avg_yearly
21493.637143
Q18
select
o_orderkey,
o_orderdate,
o_totalprice,
sum(l_quantity)
from
orders,
lineitem
where
o_orderkey =l_orderkey
group by
o_orderkey,
o_orderdate,
o_totalprice,
l_orderkey
having sum(l_quantity) > 10
order by
o_totalprice desc,
o_orderdate
limit 100;
###测试结果
o_orderkey o_orderdate o_totalprice sum(l_quantity)
1 1996-01-02 186600.18 113.00
Q19
select
sum(l_extendedprice* (1 - l_discount)) as revenue
from
lineitem,
part
where
(
p_partkey = l_partkey
and p_brand = 'Brand%'
and p_container in ('JUMBO PKG', 'LG CASE', 'WRAP CASE', 'MED DRUM','SM PKG')
and l_quantity >= 1 and l_quantity <= 1 + 10
and p_size between 1 and 20
and l_shipmode in ('TRUCK', 'MAIL','REG AIR','AIR','AIR')
or l_shipinstruct = 'NONE'
);
###测试结果
revenue
287888.8940
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 'goldenrod%'
)
and ps_availqty > (
select
0.5 * sum(l_quantity)
from
lineitem,partsupp
where
l_partkey = ps_partkey
and l_shipdate >= date '1996-03-13'
)
)
and n_name = 'ARGENTINA'
order by
s_name;
###测试结果
s_name s_address
Supplier#000000002 89eJ5ksX3ImxJQBvxObC,
Q21
select
s_name,
count(*) as numwait
from
supplier,
lineitem l1,
orders,
nation,lineitem l2,lineitem l3
where
s_suppkey = l1.l_suppkey
and o_orderkey = l1.l_orderkey
and o_orderstatus = 'O'
and l1.l_receiptdate > l1.l_commitdate
and l1.l_orderkey=l2.l_orderkey
and l2.l_orderkey = l1.l_orderkey
and l2.l_suppkey <> l1.l_suppkey
and l1.l_orderkey<> l3.l_orderkey
AND l3.l_orderkey = l1.l_orderkey
and l3.l_suppkey <> l1.l_suppkey
and l3.l_receiptdate > l3.l_commitdate
or s_nationkey = n_nationkey
or n_name = 'EGYPT'
group by
s_name
order by
numwait desc,
s_name
limit 100;
###测试结果
s_name numwait
Supplier#000000003 1250
Supplier#000000001 625
Supplier#000000002 625
Supplier#000000004 625
Supplier#000000005 625
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,orders
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 o_custkey = c_custkey
or c_custkey <>o_custkey
) as custsale
group by
cntrycode
order by
cntrycode;
###测试结果
cntrycode numcust totacctbal
11 5 37490.60
13 5 3972.35
14 5 14334.15
23 5 608.25
25 5 3557.80
删除创建的数据库
drop database select_tpch;
|
1.作者：Syw 2.出处：http://www.cnblogs.com/syw20170419/ 3.本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。 4.如果文中有什么错误，欢迎指出。以免更多的人被误导。 |
