MatrixOne 从入门到实践——TPCH性能测试
TPC Benchmark™H（TPC-H）是决策支持基准。它由一套面向业务的即时查询（ad-hoc）和并发数据修改组成。选择查询和填充数据库的数据具有广泛的行业相关性。该基准测试解释说明了决策支持系统，该系统可检查大量数据，执行高度复杂的查询并为关键业务问题提供答案。TPC-H 是 OLAP 数据库广泛使用的基准测试。
本文通过MatrixOne进行TPCH，输出性能测试报告，并尝试和同类型HTAP数据库stoneDB进行对比。
环境准备
测试环境
-
环境描述
数据库版本 CPU核数 内存大小 磁盘容量及性能 节点数量 MatrixOne:latest 6C 36G 40G 1 stonedb:v0.1 6C 36G 40G 1 -
部署方式
均为源码部署
TPCH环境
-
构建tpch的依赖环境
yum -y install gcc gcc-c++ make cmake git -
下载TPCH代码
git clone https://github.com/electrum/tpch-dbgen.git如果因为网络问题导致clone失败，建议使用gitee将上述链接项目导入到自己的仓库，然后使用gitee的链接clone，具体操作如下：
-
注册gitee账号
-
将项目加入到自己仓库中，如下页面
导入完成后，使用Gitee的链接进行clone
-
-
编译代码
修改编译代码：
# cd /tpch-dbgen # vi makefile修改以下内容：
################ ## CHANGE NAME OF ANSI COMPILER HERE ################ # 修改为gcc 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= MYSQL MACHINE = LINUX WORKLOAD = TPCH #编译：
make -
生成数据
这里准备生成TPCH 1仓的数据，总数据量大约1G
./dbgen -s 1执行完成后，会有以下内容：
-rw-r--r--. 1 root root 24M Sep 13 16:57 customer.tbl -rw-r--r--. 1 root root 725M Sep 13 16:57 lineitem.tbl -rw---S---. 1 root root 2.2K Sep 13 16:57 nation.tbl -rw-r--r--. 1 root root 164M Sep 13 16:57 orders.tbl -rw-r--r--. 1 root root 114M Sep 13 16:57 partsupp.tbl -rw-r--r--. 1 root root 24M Sep 13 16:57 part.tbl -rw---S---. 1 root root 389 Sep 13 16:57 region.tbl -rw-r--r--. 1 root root 1.4M Sep 13 16:57 supplier.tbl
MatrixOne 测试环境
部署
部署请参考：部署MatrixOne
建表
MatrixOne 暂不支持复合主键和分区，PARTSUPP 和 LINEITEM 表的创建代码有以下修改：
- 移除了
PARTSUPP和LINEITEM表的复合主键。 - 移除了
LINEITEM表的PARTITION BY KEY()。
建表语句如下：
drop database if exists TPCH;
create database if not exists TPCH;
use tpch;
CREATE TABLE NATION(
N_NATIONKEY INTEGER NOT NULL,
N_NAME CHAR(25) NOT NULL,
N_REGIONKEY INTEGER NOT NULL,
N_COMMENT VARCHAR(152),
PRIMARY KEY (N_NATIONKEY)
);
CREATE TABLE REGION(
R_REGIONKEY INTEGER NOT NULL,
R_NAME CHAR(25) NOT NULL,
R_COMMENT VARCHAR(152),
PRIMARY KEY (R_REGIONKEY)
);
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,
PRIMARY KEY (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,
PRIMARY KEY (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
);
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,
PRIMARY KEY (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,
PRIMARY KEY (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 VARCHAR(1) NOT NULL,
L_LINESTATUS VARCHAR(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
);
stoneDB 测试环境
部署
部署请参考：stoneDB部署
建表
create table nation ( n_nationkey integer not null,
n_name char(25) not null,
n_regionkey integer not null,
n_comment varchar(152),primary key (n_nationkey))engine=STONEDB;
create table region ( r_regionkey integer not null,
r_name char(25) not null,
r_comment varchar(152),primary key (r_regionkey))engine=STONEDB;
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,primary key (p_partkey) )engine=STONEDB;
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,primary key (s_suppkey))engine=STONEDB;
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,primary key (ps_partkey,ps_suppkey) )engine=STONEDB;
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,primary key (c_custkey))engine=STONEDB;
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,primary key (o_orderkey))engine=STONEDB;
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,primary key (l_orderkey,l_linenumber))engine=STONEDB;
导入
MatrixOne导入
导入语句
load data infile '/YOUR_TPCH_DATA_PATH/nation.tbl' into table NATION FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
load data infile '/YOUR_TPCH_DATA_PATH/region.tbl' into table REGION FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
load data infile '/YOUR_TPCH_DATA_PATH/part.tbl' into table PART FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
load data infile '/YOUR_TPCH_DATA_PATH/supplier.tbl' into table SUPPLIER FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
load data infile '/YOUR_TPCH_DATA_PATH/partsupp.tbl' into table PARTSUPP FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
load data infile '/YOUR_TPCH_DATA_PATH/orders.tbl' into table ORDERS FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
load data infile '/YOUR_TPCH_DATA_PATH/customer.tbl' into table CUSTOMER FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
load data infile '/YOUR_TPCH_DATA_PATH/lineitem.tbl' into table LINEITEM FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
例如，我的数据存储在：/home/tpch/tpch-dbgen目录下，则相应的导入语句为：
load data infile '/home/tpch/tpch-dbgen/nation.tbl' into table NATION FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
load data infile '/home/tpch/tpch-dbgen/region.tbl' into table REGION FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
load data infile '/home/tpch/tpch-dbgen/part.tbl' into table PART FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
load data infile '/home/tpch/tpch-dbgen/supplier.tbl' into table SUPPLIER FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
load data infile '/home/tpch/tpch-dbgen/partsupp.tbl' into table PARTSUPP FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
load data infile '/home/tpch/tpch-dbgen/orders.tbl' into table ORDERS FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
load data infile '/home/tpch/tpch-dbgen/customer.tbl' into table CUSTOMER FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
load data infile '/home/tpch/tpch-dbgen/lineitem.tbl' into table LINEITEM FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
导入速度
| 表名 | 数据条数 | 耗费时间 | 平均速度 |
|---|---|---|---|
| nation | 25 rows | 0.01 sec | 2500/s |
| region | 5 rows | 0.01 sec | 500/s |
| part | 200000 rows | 0.86 sec | 232558/s |
| supplier | 10000 rows | 0.13 sec | 76923/s |
| partsupp | 800000 rows | 9.25 sec | 86486/s |
| orders | 1500000 rows | 1 min 25.55 sec | 17533/s |
| customer | 150000 rows | 4.76 sec | 31512/s |
| lineitem | 6001215 rows | 1 min 5.75 sec | 91273/s |
stoneDB导入
导入语句
load data local infile '/home/tpch/1s/nation.tbl' into table nation fields terminated by '|';
load data local infile '/home/tpch/1s/region.tbl' into table region fields terminated by '|';
load data local infile '/home/tpch/1s/part.tbl' into table part fields terminated by '|';
load data local infile '/home/tpch/1s/supplier.tbl' into table supplier fields terminated by '|';
load data local infile '/home/tpch/1s/partsupp.tbl' into table partsupp fields terminated by '|';
load data local infile '/home/tpch/1s/orders.tbl' into table orders fields terminated by '|';
load data local infile '/home/tpch/1s/customer.tbl' into table customer fields terminated by '|';
load data local infile '/home/tpch/1s/lineitem.tbl' into table lineitem fields terminated by '|';
导入速度
| 表名 | 数据条数 | 耗费时间 | 平均速度 |
|---|---|---|---|
| nation | 25 rows | 0.08 sec | 312/s |
| region | 5 rows | 0.03 sec | 166/s |
| part | 200000 rows | 1.32 sec | 151515/s |
| supplier | 10000 rows | 0.11 sec | 90909/s |
| partsupp | 800000 rows | 4.54 sec | 176211/s |
| orders | 1500000 rows | 6.40 sec | 234375/s |
| customer | 150000 rows | 1.03 sec | 145631/s |
| lineitem | 6001215 rows | 36.99 sec | 162239/s |
查询
MatrixOne 查询
查询详情
-
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 '112' 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 | +--------------+--------------+-------------+-----------------+-------------------+---------------------+---------+-----------+----------+-------------+ | A | F | 37734107.00 | 56586554400.73 | 53758257134.8700 | 55909065222.827692 | 25.52 | 38273.13 | 0.05 | 1478493 | | N | F | 991417.00 | 1487504710.38 | 1413082168.0541 | 1469649223.194375 | 25.52 | 38284.47 | 0.05 | 38854 | | N | O | 73295769.00 | 109931611187.71 | 104436646745.0693 | 108617847377.142872 | 25.50 | 38248.46 | 0.05 | 2874145 | | R | F | 37719753.00 | 56568041380.90 | 53741292684.6040 | 55889619119.831932 | 25.51 | 38250.85 | 0.05 | 1478870 | +--------------+--------------+-------------+-----------------+-------------------+---------------------+---------+-----------+----------+-------------+ 4 rows in set (2.63 sec) -
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 = 48 and p_type like '%TIN' 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;查询结果
+-----------+--------------------+--------------+-----------+----------------+------------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------+ | s_acctbal | s_name | n_name | p_partkey | p_mfgr | s_address | s_phone | s_comment | +-----------+--------------------+--------------+-----------+----------------+------------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------+ | 9973.93 | Supplier#000004194 | JORDAN | 14193 | Manufacturer#1 | A8AoX9AK,qhf,CpEF | 23-944-413-2681 | t fluffily. regular requests about the regular, unusual somas play of the furiously busy | | 9956.34 | Supplier#000005108 | IRAN | 140079 | Manufacturer#5 | d3PLCdpPP3uE4GzbbAh4bWmU 7ecOifL9e1mNnzG | 20-842-882-7047 | ronic accounts. carefully bold accounts sleep beyond | | 9836.43 | Supplier#000000489 | IRAN | 190488 | Manufacturer#2 | y9NMoYGxDUPfrB1GwjYhLtCeV7pOt | 20-375-500-2226 | quickly carefully pending accounts. fina | | 9825.95 | Supplier#000007554 | IRAQ | 40041 | Manufacturer#5 | Huq0k qKET hByp3RcMcr | 21-787-637-9651 | ending, final requests thrash pending, | | 9806.52 | Supplier#000005276 | IRAQ | 132762 | Manufacturer#2 | inh0KOhRfLM7WRhdRNvJJDQx | 21-834-496-7360 | the slyly unusual theodolites; carefully even accounts use slyly. sl | | 9796.31 | Supplier#000005847 | IRAQ | 188292 | Manufacturer#1 | obol3bfh0zWi | 21-530-950-2847 | equests. blithely regular deposits should have to impress. final platelets integrate fluffily | | 9775.37 | Supplier#000007245 | IRAQ | 169696 | Manufacturer#5 | 5VOUnvxlJeOJ | 21-663-724-2985 | ic deposits about the slyly bold requests | | 9755.43 | Supplier#000002439 | EGYPT | 172438 | Manufacturer#5 | rXxojWV0VefSK7A0dhF | 14-410-168-5354 | p quickly packages. carefully pending pinto beans after the | | 9714.60 | Supplier#000007317 | EGYPT | 29810 | Manufacturer#4 | nJGsPl2ruoq4Ydtv0fwWG385eOFV6 VUokbCZ | 14-666-351-2592 | ons integrate furiously? slyly ironic requests sl | | 9557.33 | Supplier#000007367 | EGYPT | 197366 | Manufacturer#3 | bTP8DLvaRAB0n | 14-234-934-5255 | ep slyly regular accounts-- regular, regular excuses dazzle furiously about the reg | | 9538.15 | Supplier#000000979 | IRAN | 55968 | Manufacturer#1 | cdvHjrKZR7iDlmSWU2a | 20-151-688-1408 | ckages cajole quietly carefully regular in | | 9513.31 | Supplier#000004163 | SAUDI ARABIA | 109142 | Manufacturer#4 | 2VnQypC7pJPJRu6HzYRg7nAvhzckcYAQFbI | 30-544-852-3772 | he regular requests. blithely final | | 9450.15 | Supplier#000002067 | EGYPT | 9566 | Manufacturer#3 | 9dO68x0XLMCUDuFk1J6k9hpvLoKx 4qasok4lIb | 14-252-246-5791 | rding to the regular dolphins. quickly ir | | 9359.59 | Supplier#000005087 | JORDAN | 55086 | Manufacturer#4 | q0c6r9wYVQx31IeGBZKfe | 23-335-392-5204 | osits. quickly regular packages haggle among the quickly silent ins | | 9343.35 | Supplier#000006337 | IRAN | 173819 | Manufacturer#1 | ilQgNOdCAysclt3SgODb6LeJ7d4RzYD | 20-950-345-8173 | quickly ironic packages affix aft | | 9318.47 | Supplier#000003834 | SAUDI ARABIA | 11332 | Manufacturer#5 | cxGQnW3nShks59xA5bPoaC bIvcBWUt | 30-823-353-6520 | regular instructions. express packages run slyly pending | | 9318.47 | Supplier#000003834 | SAUDI ARABIA | 108813 | Manufacturer#2 | cxGQnW3nShks59xA5bPoaC bIvcBWUt | 30-823-353-6520 | regular instructions. express packages run slyly pending | | 9315.33 | Supplier#000003009 | IRAN | 40504 | Manufacturer#2 | oTbwfQ,Yfdcf3ysgc60NKtTpSIc | 20-306-556-2450 | the carefully special requests. express instructions wake | | 9296.31 | Supplier#000008213 | JORDAN | 163180 | Manufacturer#2 | YhdN9ESxYvhJp9ngr12Bbeo4t3zLPD, | 23-197-507-9431 | g to the blithely regular accounts! deposit | | 9284.57 | Supplier#000009781 | EGYPT | 4780 | Manufacturer#4 | 49NAd1iDiY4, | 14-410-636-4312 | its. ironic pinto beans are blithely. express depths use caref | | 9261.13 | Supplier#000000664 | EGYPT | 125639 | Manufacturer#5 | ln6wISAnC8Bpj q4V | 14-244-772-4913 | ly special foxes cajole slyly ironic reque | | 9260.78 | Supplier#000001949 | IRAN | 86932 | Manufacturer#2 | W79M2lpYiSY76Ujo6fSRUQiu | 20-531-767-2819 | thinly ironic excuses haggle slyly. f | | 9227.16 | Supplier#000009461 | EGYPT | 126948 | Manufacturer#2 | Eweba 0sfaF,l4sAxXGTgEjzsJsNWWIGjYhFkRWV | 14-983-137-4957 | lly bold packages. carefully express deposits integrate about the unusual accounts. regular, | | 9185.89 | Supplier#000007888 | EGYPT | 27887 | Manufacturer#1 | nq06Y48amPfS8YBuduy1RYu | 14-462-704-3828 | ole slyly-- blithely careful foxes wake against the furiously ironic accounts. pending ideas | | 9185.89 | Supplier#000007888 | EGYPT | 190330 | Manufacturer#4 | nq06Y48amPfS8YBuduy1RYu | 14-462-704-3828 | ole slyly-- blithely careful foxes wake against the furiously ironic accounts. pending ideas | | 9132.92 | Supplier#000007878 | IRAN | 92859 | Manufacturer#3 | aq6T3tUVq1, | 20-861-274-6282 | waters cajole ironic, ironic requests. furi | | 9058.94 | Supplier#000002789 | IRAN | 142788 | Manufacturer#4 | 7EkIldjP7M6psSWcJ11tf65GT7ZC7UaiCh | 20-842-716-4307 | s. platelets use carefully. busily regular accounts cajole fluffily above the slowly final pinto be | | 9026.80 | Supplier#000005436 | SAUDI ARABIA | 92926 | Manufacturer#3 | 3HiusYZGYmHItExgX5VfNCdJwkW8W7R | 30-453-280-6340 | . blithely unusual requests | | 9007.16 | Supplier#000001747 | EGYPT | 121746 | Manufacturer#3 | UyDlFjVxanP,ifej7L5jtNaubC | 14-141-360-9722 | maintain bravely across the busily express pinto beans. sometimes | | 8846.35 | Supplier#000005446 | EGYPT | 57930 | Manufacturer#2 | Nusoq0ckv9SwnJZV8Rw2dUqE,V0ylm Bon | 14-468-853-6477 | deposits. ironic, bold ideas wake. fluffily ironic deposits must have to sleep furiously pending | | 8837.21 | Supplier#000007210 | JORDAN | 144695 | Manufacturer#3 | G7MYkWkkJDVu,rr23aXjQCwNqZ2Vk6 | 23-560-295-1805 | en, express foxes use across the blithely bold | | 8800.91 | Supplier#000008182 | EGYPT | 143153 | Manufacturer#5 | KGMyipBiAF00tSB99DwH | 14-658-338-3635 | thely even excuses integrate blithel | | 8788.46 | Supplier#000003437 | IRAN | 118414 | Manufacturer#4 | JfgZDO9fsP4ljfzsi,s7431Ld3A7zXtHfrF74 | 20-547-871-1712 | ickly unusual dependencies. carefully regular dolphins ha | | 8750.12 | Supplier#000001064 | IRAQ | 31063 | Manufacturer#5 | QgmUIaEs5KpuW ,oREZV2b4wr3HEC1z4F | 21-440-809-7599 | sly even deposits? furiously regular pack | | 8594.80 | Supplier#000007553 | IRAN | 5052 | Manufacturer#4 | wAU2Lui w9 | 20-663-409-7956 | old, stealthy accounts are blithely. fluffily final | | 8594.80 | Supplier#000007553 | IRAN | 195033 | Manufacturer#1 | wAU2Lui w9 | 20-663-409-7956 | old, stealthy accounts are blithely. fluffily final | | 8588.63 | Supplier#000008094 | SAUDI ARABIA | 148093 | Manufacturer#1 | SNS6FCscBNZBFecA | 30-465-665-6735 | ake quickly blithely ironic theodolites. quickly ironic ideas boost. furiously iro | | 8522.70 | Supplier#000004208 | IRAQ | 166659 | Manufacturer#5 | x3jZYF7ZYN 8a4LY1c1kEsh | 21-468-998-1571 | furiously regular accounts! | | 8514.86 | Supplier#000006347 | JORDAN | 181310 | Manufacturer#5 | wwR5j4kdIAwLe33uBwo | 23-340-942-3641 | uests breach blithely ironic deposi | | 8473.01 | Supplier#000003912 | IRAQ | 33911 | Manufacturer#3 | Op7,1zt3MAxs34Qo4O W | 21-474-809-6508 | es. regular, brave instructions wa | | 8405.28 | Supplier#000007886 | IRAQ | 192847 | Manufacturer#4 | sFTj5nzc5EIVmzjXwenFTXD U | 21-735-778-5786 | ven dependencies boost blithely ironic de | | 8375.58 | Supplier#000001259 | IRAQ | 38755 | Manufacturer#2 | 32cJBGFFpGEkEjx1sF8JZAy0A72uXL5qU | 21-427-422-4993 | ironic accounts haggle slyly alongside of the carefully ironic deposit | | 8351.75 | Supplier#000007495 | IRAQ | 114983 | Manufacturer#4 | 3jQQGvfs,5Aryhn0Z | 21-953-463-7239 | requests. carefully final accounts after the qui | | 8230.12 | Supplier#000001058 | SAUDI ARABIA | 68551 | Manufacturer#2 | fJ8egP,xkLygXGv8bmtc9T1FJ | 30-496-504-3341 | requests haggle? regular, regular pinto beans integrate fluffily. dependenc | | 8195.44 | Supplier#000009805 | IRAQ | 4804 | Manufacturer#4 | dTTmLRYJNat,JbhlQlxwWp HjMR | 21-838-243-3925 | lets. quickly even theodolites dazzle slyly even a | | 8175.17 | Supplier#000003172 | IRAN | 55656 | Manufacturer#5 | 8ngbGS7BQoTDmJyMa5WV9XbaM31u5FAayd2vT3 | 20-834-374-7746 | ss deposits use furiously after the quickly final sentiments. fluffily ruthless ideas believe regu | | 8159.13 | Supplier#000007486 | EGYPT | 17485 | Manufacturer#1 | AjfdzbrrJE1 | 14-970-643-1521 | ld accounts. enticingly furious requests cajole. final packages s | | 8111.40 | Supplier#000007567 | IRAN | 197566 | Manufacturer#1 | 7W4k2qEVoBkRehprGliXRSYVOQEh | 20-377-181-7435 | gular foxes. silent attainments boost furiousl | | 8046.55 | Supplier#000001625 | IRAQ | 14121 | Manufacturer#2 | yKlKMbENR6bfmIu7aCFmbs | 21-769-404-7617 | deposits. ideas boost blithely. slyly even Tiresias according to the platelets are q | | 8040.16 | Supplier#000001925 | SAUDI ARABIA | 4424 | Manufacturer#4 | Cu5Ub AAdXT | 30-969-417-1108 | pending packages across the regular req | | 8031.68 | Supplier#000002370 | SAUDI ARABIA | 147341 | Manufacturer#5 | xGQB9xSPqRtCuMZaJavOrFuTY7km | 30-373-388-2352 | dependencies. carefully express deposits use slyly among the slyly unusual pearls. dogge | | 8031.42 | Supplier#000008216 | IRAN | 83199 | Manufacturer#2 | jsqlyr1ViAo | 20-224-305-7298 | to the carefully even excuses haggle blithely against the pending pinto be | | 8007.83 | Supplier#000006266 | JORDAN | 81249 | Manufacturer#1 | XWBf5Jd2V5SOurbn11Tt1 | 23-363-445-7184 | as cajole carefully against the quickly special ac | | 7995.78 | Supplier#000006957 | IRAN | 161924 | Manufacturer#1 | 8lvRhU5xtXv | 20-312-173-2216 | ly ironic accounts. stealthily regular foxes about the blithely ironic requests play blithely abo | | 7913.40 | Supplier#000003148 | JORDAN | 58137 | Manufacturer#1 | CpCJWI4PHeiwYuq0 | 23-767-770-9172 | ove the quickly final packages boost fluffily among the furiously final platelets. carefully s | | 7910.16 | Supplier#000002102 | IRAQ | 99592 | Manufacturer#2 | 1kuyUn5q6czLOGB60fAVgpv68M2suwchpmp2nK | 21-367-198-9930 | accounts after the blithely | | 7893.58 | Supplier#000000918 | SAUDI ARABIA | 13414 | Manufacturer#1 | e0sB7xAU3,cWF7pzXrpIbATUNydCUZup | 30-303-831-1662 | ependencies wake carefull | | 7885.17 | Supplier#000004001 | JORDAN | 38994 | Manufacturer#2 | 3M39sZY1XeQXPDRO | 23-109-632-6806 | efully express packages integrate across the regular pearls. blithely unusual packages mainta | | 7880.20 | Supplier#000005352 | JORDAN | 351 | Manufacturer#3 | PP9gHTn946hXqUF5E7idIPLkhnN | 23-557-756-7951 | egular frays. final instructions sleep a | | 7844.31 | Supplier#000006987 | IRAQ | 44482 | Manufacturer#5 | UH1zBxTNjTminnmHRe h YUT1eR | 21-963-444-7397 | nag quickly carefully regular requests. ironic theo | | 7812.27 | Supplier#000006967 | SAUDI ARABIA | 151936 | Manufacturer#4 | S4i1HfrSM4m3di3R9Cxxp59M1 | 30-193-457-6365 | ely. dependencies cajole quickly. final warhorses across the furiously ironic foxes integr | | 7767.63 | Supplier#000004306 | IRAN | 31802 | Manufacturer#2 | SkZkJZflW5mDg9wL fJ | 20-911-180-1895 | uickly regular ideas. blithely express accounts along the carefully sile | | 7741.42 | Supplier#000000899 | IRAQ | 53383 | Manufacturer#5 | oLlkiVghtro IwzcwFuzwMCG94rRpux | 21-980-994-3905 | equests wake quickly special, express accounts. courts promi | | 7741.42 | Supplier#000000899 | IRAQ | 105878 | Manufacturer#3 | oLlkiVghtro IwzcwFuzwMCG94rRpux | 21-980-994-3905 | equests wake quickly special, express accounts. courts promi | | 7741.10 | Supplier#000001059 | IRAN | 103528 | Manufacturer#4 | 4tBenOMokWbWVRB8i8HwENeO cQjM9 | 20-620-710-8984 | to the carefully special courts. | | 7599.20 | Supplier#000006596 | SAUDI ARABIA | 184077 | Manufacturer#2 | k8qeFxfXKIGYdQ82RXAfCwddSrc | 30-804-947-3851 | ously unusual deposits boost carefully after the enticing | | 7598.31 | Supplier#000008857 | IRAQ | 63844 | Manufacturer#4 | dP2th8vneyOLIUFwNBwqixkFD6 | 21-691-170-4769 | s. quickly ironic frays detect carefully | | 7591.79 | Supplier#000009723 | JORDAN | 104702 | Manufacturer#2 | Q1CkkpDdlLOpCJiV,zIf,Mv86otWhxj7slGc | 23-710-907-3873 | e fluffily even instructions. packages impress enticingly. | | 7575.12 | Supplier#000007557 | IRAQ | 77556 | Manufacturer#1 | udLvpjNvIx9qeRNdjL1ZAO0OZNOBo6h | 21-629-935-9941 | ally special accounts nod; f | | 7496.91 | Supplier#000005828 | IRAN | 103317 | Manufacturer#1 | Xt0EqDCNU6X00sNsIO7nd0ws3H | 20-435-850-8703 | furiously about the fluffily careful idea | | 7472.88 | Supplier#000004204 | EGYPT | 14203 | Manufacturer#1 | 0rGZJ6VZXdH | 14-520-667-4690 | y pending pinto beans. even, final requests sleep care | | 7472.88 | Supplier#000004204 | EGYPT | 161687 | Manufacturer#3 | 0rGZJ6VZXdH | 14-520-667-4690 | y pending pinto beans. even, final requests sleep care | | 7467.63 | Supplier#000003270 | IRAN | 45757 | Manufacturer#2 | 7j4n5FnNEHVJxFhiyz | 20-450-599-9591 | regular, even instructions boost deposits | | 7465.41 | Supplier#000008686 | EGYPT | 188685 | Manufacturer#4 | 4Onf4yxuNwHCRIC0y | 14-454-946-4151 | ly final ideas. bravely unusual deposits doze carefully. expr | | 7460.80 | Supplier#000008701 | IRAQ | 83684 | Manufacturer#3 | PLR2QehcW08 | 21-747-984-4244 | ideas use carefully pending, final deposits. ironic, pe | | 7447.86 | Supplier#000005877 | JORDAN | 120852 | Manufacturer#2 | EyqOHClZZMJkq grnOX9 4alZx6P7B2fq | 23-419-288-6451 | lar pinto beans breach carefully final pinto | | 7445.03 | Supplier#000009802 | IRAQ | 164769 | Manufacturer#5 | y6wLN KiZuTf5HT9Hbm0BELn1GUTD6yl | 21-116-708-2013 | nic requests. pinto beans across the carefully regular grouches snooze among the final pin | | 7401.46 | Supplier#000008677 | IRAN | 123652 | Manufacturer#5 | WNa780JZzivxuGBEsDszqoT1Pj | 20-899-256-5288 | onic instructions along the furiously ironic accounts haggle fluffily silently un | | 7393.50 | Supplier#000007056 | IRAQ | 54550 | Manufacturer#1 | M5cAJQvW9D5zwC7o2qkoe | 21-175-383-4727 | slyly even requests. forges haggle boldly express requests. furio | | 7376.11 | Supplier#000003982 | IRAQ | 118959 | Manufacturer#1 | jx9EloF33Ez | 21-890-236-4160 | s the furiously special warhorses affix after the car | | 7264.42 | Supplier#000001565 | IRAQ | 14061 | Manufacturer#4 | bOwKHdBteMkZoZcxdigk4Tnu07w1gDztmV7hvCw | 21-791-273-8592 | to beans. express accounts nag around the | | 7256.46 | Supplier#000009116 | IRAQ | 4115 | Manufacturer#3 | ULjaQwNbcB XUG9dvbZDHvJVwLo08utswt | 21-241-469-8343 | ending deposits. slyly ironic dependencies breach. blithely speci | | 7256.46 | Supplier#000009116 | IRAQ | 99115 | Manufacturer#1 | ULjaQwNbcB XUG9dvbZDHvJVwLo08utswt | 21-241-469-8343 | ending deposits. slyly ironic dependencies breach. blithely speci | | 7256.46 | Supplier#000009116 | IRAQ | 131576 | Manufacturer#4 | ULjaQwNbcB XUG9dvbZDHvJVwLo08utswt | 21-241-469-8343 | ending deposits. slyly ironic dependencies breach. blithely speci | | 7254.81 | Supplier#000005664 | EGYPT | 35663 | Manufacturer#2 | b8VWuTXRt66wF9bfrgTmNGuxf1PU0x3O9e | 14-214-171-8987 | ts across the quickly pending pin | | 7186.63 | Supplier#000006958 | IRAN | 71943 | Manufacturer#4 | 0n9BD,gRzUc3B,PsFcxDBGp4BFf4P | 20-185-413-5590 | against the instructions. requests are. speci | | 7166.36 | Supplier#000003541 | EGYPT | 116007 | Manufacturer#1 | DbwyOxoaMEdhEtIB3y045QrKCi2fQpGRu, | 14-508-763-1850 | ages. carefully unusual requests across the pending instructions aff | | 7128.81 | Supplier#000000677 | JORDAN | 50676 | Manufacturer#4 | 8mhrffG7D2WJBSQbOGstQ | 23-290-639-3315 | nder blithely. slyly unusual theod | | 7051.73 | Supplier#000003349 | IRAQ | 125812 | Manufacturer#3 | wtTK9df9kY7mQ5QUM0Xe5bHLMRLgwE | 21-614-525-7451 | ar theodolites cajole fluffily across the pending requests. slyly final requests a | | 7023.47 | Supplier#000009543 | SAUDI ARABIA | 47038 | Manufacturer#1 | VYKinyOBNXRr Hdqn8kOxfTw | 30-785-782-6088 | sts. furiously pending packages sleep slyly even requests. final excuses print deposits. final pac | | 6985.93 | Supplier#000006409 | IRAQ | 131382 | Manufacturer#1 | eO8JDNM19HrlQMR | 21-627-356-3992 | sts. slyly final deposits around the regular accounts are along the furiously final pac | | 6964.75 | Supplier#000009931 | EGYPT | 57425 | Manufacturer#1 | peQYiRFk G0xZKfJ | 14-989-166-5782 | deposits according to the sometimes silent requests wake along the packages-- blithely f | | 6964.04 | Supplier#000007399 | IRAQ | 77398 | Manufacturer#2 | zdxjENOGR4QiCFP | 21-859-733-1999 | e blithely after the even requests. carefully ironic packages use slyly a | | 6913.81 | Supplier#000002625 | IRAQ | 22624 | Manufacturer#3 | a4V0rWemgbsT ZMj w7DB8rUbZ4F4lqqW5VKljQF | 21-136-564-3910 | . asymptotes among the express requests cajole furiously after the ca | | 6880.18 | Supplier#000006704 | IRAN | 26703 | Manufacturer#4 | 97rxJlAImbO1 sUlChUWoOJ0ZzvQ2NI3KI6VDOwk | 20-588-916-1286 | old accounts wake quickly. ca | | 6878.62 | Supplier#000001697 | IRAQ | 146668 | Manufacturer#5 | 37nm ODTeHy0xWTWegplgdWQqelh | 21-377-544-4864 | ironic theodolites. furiously regular d | | 6790.39 | Supplier#000008703 | IRAN | 123678 | Manufacturer#4 | wMslK1A8SEUTIIdApQ | 20-782-266-2552 | eep blithely regular, pending w | | 6763.46 | Supplier#000007882 | EGYPT | 137881 | Manufacturer#5 | JDv8BZiYG0UlZ | 14-111-252-9120 | the silent accounts wake foxes. furious | | 6751.81 | Supplier#000003156 | EGYPT | 165607 | Manufacturer#2 | alRWaW4FTFERMM4vf2rHKIKE | 14-843-946-7775 | are furiously. final theodolites affix slyly bold deposits. even packages haggle idly slyly specia | | 6702.07 | Supplier#000006276 | EGYPT | 31269 | Manufacturer#2 | ,dE1anEjKQGZfgquYfkx2fkGcXH | 14-896-626-7847 | ze about the carefully regular pint | +-----------+--------------------+--------------+-----------+----------------+------------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------+ 100 rows in set (0.09 sec) -
Q3
查询语句
select l_orderkey, sum(l_extendedprice * (1 - l_discount)) as revenue, o_orderdate, o_shippriority from customer, orders, lineitem where c_mktsegment = 'HOUSEHOLD' and c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate < date '1995-03-29' and l_shipdate > date '1995-03-29' group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate limit 10 ;查询结果
+------------+-------------+-------------+----------------+ | l_orderkey | revenue | o_orderdate | o_shippriority | +------------+-------------+-------------+----------------+ | 2152675 | 431309.8065 | 1995-03-28 | 0 | | 4994400 | 423834.7976 | 1995-03-09 | 0 | | 2160291 | 401149.7805 | 1995-03-18 | 0 | | 2845094 | 401094.1393 | 1995-03-06 | 0 | | 1902471 | 400497.3847 | 1995-03-01 | 0 | | 5624358 | 395710.6806 | 1995-03-20 | 0 | | 2346242 | 392580.0394 | 1995-03-17 | 0 | | 2529826 | 387365.1560 | 1995-02-17 | 0 | | 5168933 | 385433.6198 | 1995-03-20 | 0 | | 2839239 | 380503.7310 | 1995-03-22 | 0 | +------------+-------------+-------------+----------------+ 10 rows in set (0.17 sec) -
Q4
查询语句
select o_orderpriority, count(*) as order_count from orders where o_orderdate >= date '1997-07-01' and o_orderdate < date '1997-07-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 ;查询结果
+-----------------+-------------+ | o_orderpriority | order_count | +-----------------+-------------+ | 1-URGENT | 10623 | | 2-HIGH | 10465 | | 3-MEDIUM | 10309 | | 4-NOT SPECIFIED | 10618 | | 5-LOW | 10541 | +-----------------+-------------+ 5 rows in set (0.84 sec) -
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 '1994-01-01' and o_orderdate < date '1994-01-01' + interval '1' year group by n_name order by revenue desc ;查询结果
+---------------+---------------+ | n_name | revenue | +---------------+---------------+ | PERU | 56206762.5035 | | CANADA | 56052846.0161 | | ARGENTINA | 54595012.8076 | | BRAZIL | 53601776.5201 | | UNITED STATES | 50890580.8962 | +---------------+---------------+ 5 rows in set (0.28 sec) -
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.03 - 0.01 and 0.03 + 0.01 and l_quantity < 24;查询结果
+---------------+ | revenue | +---------------+ | 61660051.7967 | +---------------+ 1 row in set (0.59 sec) -
Q7
查询语句
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.03 - 0.01 and 0.03 + 0.01 and l_quantity < 24; 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 = 'FRANCE' and n2.n_name = 'ARGENTINA') or (n1.n_name = 'ARGENTINA' and n2.n_name = 'FRANCE') ) 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 ;查询结果
+-------------+-------------+--------+---------------+ | supp_nation | cust_nation | l_year | revenue | +-------------+-------------+--------+---------------+ | ARGENTINA | FRANCE | 1995 | 57928886.8015 | | ARGENTINA | FRANCE | 1996 | 55535134.8474 | | FRANCE | ARGENTINA | 1995 | 52916227.7375 | | FRANCE | ARGENTINA | 1996 | 51077995.8841 | +-------------+-------------+--------+---------------+ 4 rows in set (1.04 sec) -
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 and o_orderdate between date '1995-01-01' and date '1996-12-31' and p_type = 'ECONOMY BURNISHED TIN' ) as all_nations group by o_year order by o_year ;查询结果
+--------+----------------------+ | o_year | mkt_share | +--------+----------------------+ | 1995 | 0.035094304475112484 | | 1996 | 0.03724375099464825 | +--------+----------------------+ 2 rows in set (0.18 sec) -
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 '%pink%' ) as profit group by nation, o_year order by nation, o_year desc ;查询结果
+----------------+--------+---------------+ | nation | o_year | sum_profit | +----------------+--------+---------------+ | ALGERIA | 1998 | 29931671.4862 | | ALGERIA | 1997 | 49521023.1139 | | ALGERIA | 1996 | 51283603.7356 | | ALGERIA | 1995 | 50206939.3447 | | ALGERIA | 1994 | 48738988.5891 | | ALGERIA | 1993 | 48084070.1204 | | ALGERIA | 1992 | 49725592.1793 | | ARGENTINA | 1998 | 26407044.9262 | | ARGENTINA | 1997 | 46224601.0785 | | ARGENTINA | 1996 | 44579611.0571 | | ARGENTINA | 1995 | 45081953.2540 | | ARGENTINA | 1994 | 48291282.8512 | | ARGENTINA | 1993 | 48063838.9130 | | ARGENTINA | 1992 | 45277890.2991 | | BRAZIL | 1998 | 28577022.6384 | | BRAZIL | 1997 | 46808660.3688 | | BRAZIL | 1996 | 47119265.0765 | | BRAZIL | 1995 | 47706399.9100 | | BRAZIL | 1994 | 48377469.9386 | | BRAZIL | 1993 | 46933565.7471 | | BRAZIL | 1992 | 47272215.5408 | | CANADA | 1998 | 30500303.6521 | | CANADA | 1997 | 50046257.5687 | | CANADA | 1996 | 52638586.9029 | | CANADA | 1995 | 50433911.3289 | | CANADA | 1994 | 51605251.7124 | | CANADA | 1993 | 50117218.8464 | | CANADA | 1992 | 50347111.2789 | | CHINA | 1998 | 26956001.9487 | | CHINA | 1997 | 48311246.7866 | | CHINA | 1996 | 51133929.1033 | | CHINA | 1995 | 48024289.1049 | | CHINA | 1994 | 50027433.6557 | | CHINA | 1993 | 48240226.3801 | | CHINA | 1992 | 47769117.6007 | | EGYPT | 1998 | 26972573.1604 | | EGYPT | 1997 | 46708654.7666 | | EGYPT | 1996 | 46095050.4457 | | EGYPT | 1995 | 44901908.2949 | | EGYPT | 1994 | 48522762.8892 | | EGYPT | 1993 | 49055807.7642 | | EGYPT | 1992 | 46909796.1083 | | ETHIOPIA | 1998 | 26364411.6457 | | ETHIOPIA | 1997 | 44889623.0645 | | ETHIOPIA | 1996 | 47554295.2892 | | ETHIOPIA | 1995 | 44747639.5440 | | ETHIOPIA | 1994 | 46497570.0631 | | ETHIOPIA | 1993 | 43853718.5460 | | ETHIOPIA | 1992 | 44005773.0397 | | FRANCE | 1998 | 27033406.6353 | | FRANCE | 1997 | 45763555.5515 | | FRANCE | 1996 | 47178544.9301 | | FRANCE | 1995 | 48821282.1929 | | FRANCE | 1994 | 46444640.9397 | | FRANCE | 1993 | 46602311.0590 | | FRANCE | 1992 | 47769356.5113 | | GERMANY | 1998 | 26165681.8305 | | GERMANY | 1997 | 46600844.4431 | | GERMANY | 1996 | 44873520.1979 | | GERMANY | 1995 | 47761215.6058 | | GERMANY | 1994 | 42283120.0209 | | GERMANY | 1993 | 46954873.9820 | | GERMANY | 1992 | 46263626.6361 | | INDIA | 1998 | 27651103.0250 | | INDIA | 1997 | 46000888.8340 | | INDIA | 1996 | 43993476.7354 | | INDIA | 1995 | 44015709.1914 | | INDIA | 1994 | 44281439.6282 | | INDIA | 1993 | 45367255.7857 | | INDIA | 1992 | 45350810.5330 | | INDONESIA | 1998 | 27120545.3120 | | INDONESIA | 1997 | 45745362.3667 | | INDONESIA | 1996 | 45347554.8232 | | INDONESIA | 1995 | 45685709.4978 | | INDONESIA | 1994 | 44738603.1901 | | INDONESIA | 1993 | 45172063.2033 | | INDONESIA | 1992 | 44623924.3942 | | IRAN | 1998 | 27876287.0949 | | IRAN | 1997 | 47184621.5647 | | IRAN | 1996 | 47397859.7878 | | IRAN | 1995 | 49579120.6991 | | IRAN | 1994 | 48032316.8744 | | IRAN | 1993 | 48295593.2066 | | IRAN | 1992 | 50531453.3934 | | IRAQ | 1998 | 29997323.2927 | | IRAQ | 1997 | 52851471.1377 | | IRAQ | 1996 | 53671825.6297 | | IRAQ | 1995 | 53251012.1025 | | IRAQ | 1994 | 50934553.4361 | | IRAQ | 1993 | 51961214.1186 | | IRAQ | 1992 | 50840364.3833 | | JAPAN | 1998 | 26054615.4955 | | JAPAN | 1997 | 43557394.2595 | | JAPAN | 1996 | 46531743.0980 | | JAPAN | 1995 | 41688293.4741 | | JAPAN | 1994 | 45526719.0728 | | JAPAN | 1993 | 45619475.4478 | | JAPAN | 1992 | 44545639.3069 | | JORDAN | 1998 | 24793092.4101 | | JORDAN | 1997 | 42050730.7748 | | JORDAN | 1996 | 42562783.8663 | | JORDAN | 1995 | 42253019.5330 | | JORDAN | 1994 | 45027034.7721 | | JORDAN | 1993 | 44797510.9808 | | JORDAN | 1992 | 41313405.2890 | | KENYA | 1998 | 24550926.4693 | | KENYA | 1997 | 42767120.5848 | | KENYA | 1996 | 45000095.1105 | | KENYA | 1995 | 43250458.0109 | | KENYA | 1994 | 42891596.7158 | | KENYA | 1993 | 43599201.5126 | | KENYA | 1992 | 45286145.8141 | | MOROCCO | 1998 | 23482053.5970 | | MOROCCO | 1997 | 41503033.0020 | | MOROCCO | 1996 | 45645555.9409 | | MOROCCO | 1995 | 44462858.7689 | | MOROCCO | 1994 | 44768368.8310 | | MOROCCO | 1993 | 44611871.2477 | | MOROCCO | 1992 | 43057959.1352 | | MOZAMBIQUE | 1998 | 28824737.9244 | | MOZAMBIQUE | 1997 | 48682746.5995 | | MOZAMBIQUE | 1996 | 50816940.9909 | | MOZAMBIQUE | 1995 | 50010039.0178 | | MOZAMBIQUE | 1994 | 48794892.1253 | | MOZAMBIQUE | 1993 | 48451128.3332 | | MOZAMBIQUE | 1992 | 50113858.5449 | | PERU | 1998 | 30575758.1899 | | PERU | 1997 | 49323405.6808 | | PERU | 1996 | 50063490.6085 | | PERU | 1995 | 51272843.6555 | | PERU | 1994 | 50690589.2334 | | PERU | 1993 | 49086129.3668 | | PERU | 1992 | 50067216.3450 | | ROMANIA | 1998 | 27367992.9903 | | ROMANIA | 1997 | 45668932.7094 | | ROMANIA | 1996 | 46594220.7498 | | ROMANIA | 1995 | 44576835.1623 | | ROMANIA | 1994 | 45640971.0684 | | ROMANIA | 1993 | 46374545.0712 | | ROMANIA | 1992 | 47130533.3076 | | RUSSIA | 1998 | 27486839.8755 | | RUSSIA | 1997 | 44050712.6907 | | RUSSIA | 1996 | 45604597.4983 | | RUSSIA | 1995 | 48972490.6009 | | RUSSIA | 1994 | 45652045.5872 | | RUSSIA | 1993 | 47139548.1597 | | RUSSIA | 1992 | 47159990.1221 | | SAUDI ARABIA | 1998 | 29766229.7961 | | SAUDI ARABIA | 1997 | 51473031.6922 | | SAUDI ARABIA | 1996 | 52859666.6646 | | SAUDI ARABIA | 1995 | 50946175.0229 | | SAUDI ARABIA | 1994 | 53085288.9954 | | SAUDI ARABIA | 1993 | 50907571.2046 | | SAUDI ARABIA | 1992 | 50334063.0381 | | UNITED KINGDOM | 1998 | 27904712.8220 | | UNITED KINGDOM | 1997 | 48170994.4362 | | UNITED KINGDOM | 1996 | 46498116.9611 | | UNITED KINGDOM | 1995 | 43210619.0456 | | UNITED KINGDOM | 1994 | 47339709.9122 | | UNITED KINGDOM | 1993 | 44308436.3275 | | UNITED KINGDOM | 1992 | 45870809.6693 | | UNITED STATES | 1998 | 25856187.3719 | | UNITED STATES | 1997 | 44934753.2208 | | UNITED STATES | 1996 | 44826974.2915 | | UNITED STATES | 1995 | 44160425.4086 | | UNITED STATES | 1994 | 43193241.6843 | | UNITED STATES | 1993 | 45126307.2619 | | UNITED STATES | 1992 | 44205926.3317 | | VIETNAM | 1998 | 28289193.6726 | | VIETNAM | 1997 | 48284585.4019 | | VIETNAM | 1996 | 48360225.9084 | | VIETNAM | 1995 | 48742082.6165 | | VIETNAM | 1994 | 49035537.3894 | | VIETNAM | 1993 | 47222674.6352 | | VIETNAM | 1992 | 48628336.9011 | +----------------+--------+---------------+ 175 rows in set (0.75 sec) -
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-03-01' and o_orderdate < date '1993-03-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 ;查询结果
+-----------+--------------------+-------------+-----------+----------------+------------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------------------+ | c_custkey | c_name | revenue | c_acctbal | n_name | c_address | c_phone | c_comment | +-----------+--------------------+-------------+-----------+----------------+------------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------------------+ | 95962 | Customer#000095962 | 704336.0774 | -9.33 | MOZAMBIQUE | 83wOMt9iAb9OJ0HbkQ1PaX3odXVBNEIMXaE | 26-127-693-7436 | nusual theodolites maintain furiously fluffily iro | | 87064 | Customer#000087064 | 684037.4349 | 5244.68 | BRAZIL | 0xej6ldT8zi7MwLdDJ1II3YWwprkvwB1 I0kwsf | 12-930-206-2571 | de of the ironic, silent warthogs. bold, r | | 56416 | Customer#000056416 | 661218.0492 | 4303.82 | INDIA | CEuBN,xZVmP | 18-212-984-8331 | al waters cajole along the slyly unusual dugouts. carefully regular deposits use slyly? packages h | | 46450 | Customer#000046450 | 646205.6835 | 2400.59 | UNITED STATES | rzWQxB9iFpd8i4KUCAPdv | 34-765-320-4326 | ss, final deposits cajole sly | | 128713 | Customer#000128713 | 643240.1183 | 7200.30 | ARGENTINA | mm0kxtHFCchaZX4eYSCCyQHno7vq,SRmv4 | 11-174-994-6880 | ording to the express accounts cajole carefully across the bravely special packages. carefully regular account | | 102187 | Customer#000102187 | 637493.0787 | -896.03 | ETHIOPIA | EAi6vcGnWHUMb6rJwn,PtUgSH74tR Aixa | 15-877-462-6534 | gular packages. carefully regular deposits cajole carefully of the regular requests. carefully special accou | | 42541 | Customer#000042541 | 634546.9756 | 8082.14 | IRAN | IccOGHgp8g | 20-442-159-1337 | cross the final asymptotes. final packages wake furiously ironic dec | | 51595 | Customer#000051595 | 611926.8265 | 7236.80 | UNITED STATES | wQFWZk 7JCpeg50O0KCzSmUFnNNwX1aEQ7V3Q | 34-844-269-9070 | sts. always express accounts use carefully along the quickly speci | | 66391 | Customer#000066391 | 608385.5852 | 9404.57 | UNITED STATES | V0XvU1Nh9NU4zsyOkm,RBa | 34-149-224-8119 | ages cajole carefully carefully bold deposits: fluffily unusual deposits promise slyly carefully ironic co | | 48358 | Customer#000048358 | 603621.4823 | -611.15 | ETHIOPIA | ycg3uMG7iDdwQvJ1irr | 15-687-936-5181 | the slyly unusual foxes-- carefully regular | | 99175 | Customer#000099175 | 602125.3304 | 2218.76 | INDONESIA | 9wbW52xx9T84E0dZ Rvz1ozQ1 | 19-125-912-6494 | ide of the slyly ironic foxes boost silently ironic, even instructions. blithe | | 122509 | Customer#000122509 | 601580.1203 | 2613.83 | KENYA | ZN1sc0eJrkD8t6X5Q1d3 | 24-421-308-3881 | brave deposits haggle across the even deposits. instr | | 148055 | Customer#000148055 | 601003.6812 | 455.31 | PERU | Y,RCZ3Bislx64nTsPaRL,5gjx7xgC6y, yKYnCw | 27-473-476-4382 | uickly final accounts wake carefully sl | | 117451 | Customer#000117451 | 599792.7063 | 1090.48 | UNITED STATES | bSwr7mNPiaf1f lNK9 uTJxWCL2sn1Lak5NIB | 34-354-586-6011 | ding to the furiously express accounts boost carefully af | | 104110 | Customer#000104110 | 588194.3118 | 2762.52 | JORDAN | mm7 ZuDX5Z5nAQbKObB 80XBCy,1nyW | 23-639-800-5768 | urts sleep furiously alongside of the packages! slyly ironic packages sleep | | 13666 | Customer#000013666 | 579926.1679 | 7453.98 | EGYPT | DLRUWGcprmWqdROJvmZwpE | 14-316-135-4381 | ross the silent requests. special theodolit | | 96202 | Customer#000096202 | 571017.3398 | 4703.04 | CANADA | 4Vcxcx3w4zMjVYNQaqrweweQY6TJO AP9rdvQaLl | 13-194-779-9597 | en packages use. fluffily regular dependencies boost. never pending requ | | 70279 | Customer#000070279 | 561369.3650 | 9109.34 | CHINA | ltie8o3ihwffMrqMrkvN957KZVWmH5 | 28-842-825-1717 | theodolites sleep: blithely final requests are fur | | 16972 | Customer#000016972 | 560435.8065 | 6408.66 | ROMANIA | X6T8vRKy6kSO0f2wJJt | 29-483-958-3347 | sts. pending deposits are across the regular, express instructions. carefully daring foxes cajol | | 113443 | Customer#000113443 | 557272.6706 | -72.67 | UNITED KINGDOM | SUHbS85cYxgVkKbfh9sUpEa6ezVSlQuCKe3CV | 33-819-742-6112 | ic foxes cajole thinly furiously stealthy instructions. pinto beans are. quickly regular accounts integrate car | +-----------+--------------------+-------------+-----------+----------------+------------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------------------+ 20 rows in set (0.39 sec) -
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 = 'JAPAN' 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 = 'JAPAN' ) order by value desc ;查询结果
... ... ... | 190782 | 7613203.42 | | 113818 | 7612852.48 | | 178091 | 7611457.30 | | 87603 | 7611343.68 | | 108317 | 7610509.71 | | 106552 | 7609868.84 | | 28679 | 7609292.20 | | 192350 | 7609140.81 | | 154801 | 7607944.38 | | 5768 | 7607785.68 | | 127689 | 7606313.94 | | 62847 | 7605651.45 | | 111212 | 7605052.00 | | 156065 | 7603327.60 | | 115140 | 7601161.68 | | 19597 | 7601153.46 | | 55233 | 7600940.23 | | 89353 | 7600929.84 | | 75701 | 7600492.60 | | 64974 | 7599754.80 | | 116156 | 7597452.48 | | 59491 | 7596352.84 | | 6138 | 7594861.54 | | 62317 | 7594854.10 | | 106575 | 7594520.08 | | 161092 | 7594454.40 | | 9872 | 7593734.34 | | 77711 | 7593431.60 | | 61206 | 7593153.00 | | 123776 | 7592736.80 | | 185141 | 7592617.12 | | 5542 | 7592513.04 | | 185296 | 7591439.31 | | 72597 | 7591142.40 | +------------+-------------+ 1225 rows in set (0.07 sec) -
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 ('FOB', 'TRUCK') and l_commitdate < l_receiptdate and l_shipdate < l_commitdate and l_receiptdate >= date '1996-01-01' and l_receiptdate < date '1996-01-01' + interval '1' year group by l_shipmode order by l_shipmode ;查询结果
+------------+-----------------+----------------+ | l_shipmode | high_line_count | low_line_count | +------------+-----------------+----------------+ | FOB | 6273 | 9429 | | TRUCK | 6336 | 9300 | +------------+-----------------+----------------+ 2 rows in set (0.67 sec) -
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%accounts%' group by c_custkey ) as c_orders (c_custkey, c_count) group by c_count order by custdist desc, c_count desc ;查询结果
+---------+----------+ | c_count | custdist | +---------+----------+ | 0 | 50005 | | 10 | 6574 | | 9 | 6554 | | 11 | 6072 | | 8 | 5934 | | 12 | 5598 | | 13 | 5032 | | 19 | 4685 | | 7 | 4663 | | 20 | 4607 | | 17 | 4550 | | 18 | 4515 | | 14 | 4480 | | 15 | 4476 | | 16 | 4341 | | 21 | 4176 | | 22 | 3710 | | 6 | 3303 | | 23 | 3172 | | 24 | 2670 | | 25 | 2111 | | 5 | 1954 | | 26 | 1605 | | 27 | 1195 | | 4 | 1030 | | 28 | 898 | | 29 | 620 | | 3 | 408 | | 30 | 353 | | 31 | 225 | | 32 | 135 | | 2 | 128 | | 33 | 82 | | 34 | 54 | | 35 | 33 | | 1 | 18 | | 36 | 17 | | 37 | 7 | | 41 | 3 | | 40 | 3 | | 38 | 3 | | 39 | 1 | +---------+----------+ 42 rows in set (0.70 sec) -
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-01' and l_shipdate < date '1996-04-01' + interval '1' month;查询结果
+-------------------+ | promo_revenue | +-------------------+ | 16.65118731292795 | +-------------------+ 1 row in set (0.15 sec) -
Q15
查询语句
with q15_revenue0 as ( select l_suppkey as supplier_no, sum(l_extendedprice * (1 - l_discount)) as total_revenue from lineitem where l_shipdate >= date '1995-12-01' and l_shipdate < date '1995-12-01' + interval '3' month group by l_suppkey ) select s_suppkey, s_name, s_address, s_phone, total_revenue from supplier, q15_revenue0 where s_suppkey = supplier_no and total_revenue = ( select max(total_revenue) from q15_revenue0 ) order by s_suppkey ;查询结果
+-----------+--------------------+----------------------------------+-----------------+---------------+ | s_suppkey | s_name | s_address | s_phone | total_revenue | +-----------+--------------------+----------------------------------+-----------------+---------------+ | 7895 | Supplier#000007895 | NYl,i8UhxTykLxGJ2voIRn20Ugk1KTzz | 14-559-808-3306 | 1678635.2636 | +-----------+--------------------+----------------------------------+-----------------+---------------+ 1 row in set (0.16 sec) -
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#35' and p_type not like 'ECONOMY BURNISHED%' and p_size in (14, 7, 21, 24, 35, 33, 2, 20) 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 ;查询结果
... ... ... | Brand#55 | STANDARD POLISHED STEEL | 2 | 4 | | Brand#55 | STANDARD POLISHED STEEL | 14 | 4 | | Brand#55 | STANDARD POLISHED STEEL | 24 | 4 | | Brand#55 | STANDARD POLISHED STEEL | 33 | 4 | | Brand#12 | LARGE POLISHED TIN | 33 | 3 | | Brand#13 | MEDIUM POLISHED NICKEL | 20 | 3 | | Brand#15 | MEDIUM BRUSHED NICKEL | 33 | 3 | | Brand#22 | MEDIUM POLISHED TIN | 7 | 3 | | Brand#23 | ECONOMY BRUSHED BRASS | 21 | 3 | | Brand#23 | LARGE BURNISHED NICKEL | 21 | 3 | | Brand#23 | SMALL BRUSHED STEEL | 2 | 3 | | Brand#24 | ECONOMY PLATED BRASS | 24 | 3 | | Brand#25 | LARGE BRUSHED STEEL | 35 | 3 | | Brand#31 | PROMO ANODIZED COPPER | 20 | 3 | | Brand#41 | LARGE BURNISHED STEEL | 20 | 3 | | Brand#43 | SMALL BRUSHED COPPER | 7 | 3 | | Brand#52 | MEDIUM POLISHED BRASS | 21 | 3 | | Brand#52 | SMALL POLISHED TIN | 2 | 3 | +----------+---------------------------+--------+--------------+ 18341 rows in set (0.50 sec) -
Q17
查询语句
select sum(l_extendedprice) / 7.0 as avg_yearly from lineitem, part where p_partkey = l_partkey and p_brand = 'Brand#54' and p_container = 'LG BAG' and l_quantity < ( select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey );查询结果
+---------------+ | avg_yearly | +---------------+ | 343478.600000 | +---------------+ 1 row in set (0.78 sec) -
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) > 314 ) 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 ;查询结果
+--------------------+-----------+------------+-------------+--------------+-----------------+ | c_name | c_custkey | o_orderkey | o_orderdate | o_totalprice | sum(l_quantity) | +--------------------+-----------+------------+-------------+--------------+-----------------+ | Customer#000128120 | 128120 | 4722021 | 1994-04-07 | 544089.09 | 323.00 | | Customer#000144617 | 144617 | 3043270 | 1997-02-12 | 530604.44 | 317.00 | | Customer#000066790 | 66790 | 2199712 | 1996-09-30 | 515531.82 | 327.00 | | Customer#000015619 | 15619 | 3767271 | 1996-08-07 | 480083.96 | 318.00 | | Customer#000147197 | 147197 | 1263015 | 1997-02-02 | 467149.67 | 320.00 | | Customer#000117919 | 117919 | 2869152 | 1996-06-20 | 456815.92 | 317.00 | | Customer#000126865 | 126865 | 4702759 | 1994-11-07 | 447606.65 | 320.00 | | Customer#000036619 | 36619 | 4806726 | 1995-01-17 | 446704.09 | 328.00 | | Customer#000119989 | 119989 | 1544643 | 1997-09-20 | 434568.25 | 320.00 | +--------------------+-----------+------------+-------------+--------------+-----------------+ 9 rows in set (0.82 sec) -
Q19
查询语句
select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part where ( p_partkey = l_partkey and p_brand = 'Brand#23' 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#15' and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') and l_quantity >= 14 and l_quantity <= 14 + 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#44' and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') and l_quantity >= 28 and l_quantity <= 28 + 10 and p_size between 1 and 15 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' );查询结果
+--------------+ | revenue | +--------------+ | 4028540.3287 | +--------------+ 1 row in set (0.66 sec) -
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 'lime%' ) 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 '1993-01-01' and l_shipdate < date '1993-01-01' + interval '1' year ) ) and s_nationkey = n_nationkey and n_name = 'VIETNAM' order by s_name ;查询结果
| Supplier#000008997 | KY MmMEcyQ6FEDCooFj xa uCwF2GbaeA8 | | Supplier#000009065 | ZELuiqWrWbJV9zAuco1OnXKTJClhR | | Supplier#000009114 | nkn6bcPvlP5w,lUpO0nZTBSj | | Supplier#000009125 | IQbCXbN1mmght | | Supplier#000009131 | gDBXgWtg4rTxu0WUJhhV | | Supplier#000009149 | yKX,bKryD6YtvF,cVLIKC0Z6rN | | Supplier#000009182 | z56kNgeqaWQ1kHFBp | | Supplier#000009220 | N4y,vP kdArpcmdypBh,fJVVB | | Supplier#000009226 | yzT10vNTFJ | | Supplier#000009288 | 251AA4ziZ3d7TTWXLGnXjb4BnXv | | Supplier#000009360 | 1NVjjX8zMjyBX2UapDTP0Sz | | Supplier#000009381 | rhCTm7QehIznqd8 Np7VT,H5J5zSGr | | Supplier#000009403 | 70841REghyWBrHyyg762Jh4sjCG7CKaIc | | Supplier#000009504 | Rqt07,ANI92kj1oU | | Supplier#000009598 | PnTAz7rNRLVDFO3zoo2QRTlh4o | | Supplier#000009609 | LV2rJUGfr0k3dPNRqufG1IoYHzV | | Supplier#000009619 | K0RwcJ9S75Xil jqKukFoDNkD | | Supplier#000009626 | Nm1FnIh4asUR3EnXv2Pvy3gXqI9es | | Supplier#000009738 | 15RRSVTuOzwdMP LmfCtIguMGXK | | Supplier#000009770 | Ag, SZfowit580QPDdbP8kmFHdpZ9ASI | | Supplier#000009865 | extcOh9ZrdDCMsHhhsFTkTUAh,HM2UQ2qa8sRo | | Supplier#000009866 | Auh6aZnOnQG1pPYKZ5o9ATramJBA | | Supplier#000009890 | izJXemCM Ikpgxk | | Supplier#000009937 | edZ9HQJ0KJAU6EWknTiDghKfRLHq6vtFqdey,0l | | Supplier#000009954 | VzElx9ihlXFJLIQw2Hn4bC2 | | Supplier#000009958 | ggiiSA4CSyvhwQUYjdJhWlKEY9PAfs | +--------------------+------------------------------------------+ 177 rows in set (0.45 sec) -
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 = 'BRAZIL' group by s_name order by numwait desc, s_name limit 100 ;查询结果
+--------------------+---------+ | s_name | numwait | +--------------------+---------+ | Supplier#000009302 | 21 | | Supplier#000000342 | 20 | | Supplier#000000632 | 19 | | Supplier#000002196 | 19 | | Supplier#000003325 | 18 | | Supplier#000003915 | 18 | | Supplier#000005045 | 18 | | Supplier#000006442 | 18 | | Supplier#000003093 | 17 | | Supplier#000004498 | 17 | | Supplier#000000906 | 16 | | Supplier#000001183 | 16 | | Supplier#000001477 | 16 | | Supplier#000006043 | 16 | | Supplier#000000689 | 15 | | Supplier#000001955 | 15 | | Supplier#000002066 | 15 | | Supplier#000002146 | 15 | | Supplier#000003253 | 15 | | Supplier#000003527 | 15 | | Supplier#000003947 | 15 | | Supplier#000004915 | 15 | | Supplier#000005248 | 15 | | Supplier#000006718 | 15 | | Supplier#000007773 | 15 | | Supplier#000008121 | 15 | | Supplier#000008169 | 15 | | Supplier#000008645 | 15 | | Supplier#000008684 | 15 | | Supplier#000009079 | 15 | | Supplier#000009956 | 15 | | Supplier#000000737 | 14 | | Supplier#000000775 | 14 | | Supplier#000001474 | 14 | | Supplier#000001502 | 14 | | Supplier#000003196 | 14 | | Supplier#000004415 | 14 | | Supplier#000004940 | 14 | | Supplier#000005253 | 14 | | Supplier#000005703 | 14 | | Supplier#000006308 | 14 | | Supplier#000006789 | 14 | | Supplier#000007161 | 14 | | Supplier#000007952 | 14 | | Supplier#000008062 | 14 | | Supplier#000008414 | 14 | | Supplier#000008442 | 14 | | Supplier#000008508 | 14 | | Supplier#000000300 | 13 | | Supplier#000000727 | 13 | | Supplier#000000921 | 13 | | Supplier#000000992 | 13 | | Supplier#000001282 | 13 | | Supplier#000001582 | 13 | | Supplier#000001662 | 13 | | Supplier#000001683 | 13 | | Supplier#000002933 | 13 | | Supplier#000003177 | 13 | | Supplier#000003428 | 13 | | Supplier#000003640 | 13 | | Supplier#000004842 | 13 | | Supplier#000004951 | 13 | | Supplier#000005795 | 13 | | Supplier#000005981 | 13 | | Supplier#000006118 | 13 | | Supplier#000006433 | 13 | | Supplier#000006484 | 13 | | Supplier#000007268 | 13 | | Supplier#000008599 | 13 | | Supplier#000008675 | 13 | | Supplier#000009474 | 13 | | Supplier#000009521 | 13 | | Supplier#000009853 | 13 | | Supplier#000000021 | 12 | | Supplier#000000211 | 12 | | Supplier#000000743 | 12 | | Supplier#000000951 | 12 | | Supplier#000001654 | 12 | | Supplier#000001868 | 12 | | Supplier#000002089 | 12 | | Supplier#000002879 | 12 | | Supplier#000003060 | 12 | | Supplier#000003215 | 12 | | Supplier#000003365 | 12 | | Supplier#000003873 | 12 | | Supplier#000003985 | 12 | | Supplier#000004452 | 12 | | Supplier#000004639 | 12 | | Supplier#000005122 | 12 | | Supplier#000005633 | 12 | | Supplier#000005671 | 12 | | Supplier#000005782 | 12 | | Supplier#000006088 | 12 | | Supplier#000006477 | 12 | | Supplier#000006508 | 12 | | Supplier#000006750 | 12 | | Supplier#000006802 | 12 | | Supplier#000008236 | 12 | | Supplier#000009294 | 12 | | Supplier#000009329 | 12 | +--------------------+---------+ 100 rows in set (1.48 sec) -
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 ('10', '11', '26', '22', '19', '20', '27') and c_acctbal > ( select avg(c_acctbal) from customer where c_acctbal > 0.00 and substring(c_phone from 1 for 2) in ('10', '11', '26', '22', '19', '20', '27') ) and not exists ( select * from orders where o_custkey = c_custkey ) ) as custsale group by cntrycode order by cntrycode ;查询结果
+-----------+---------+------------+ | cntrycode | numcust | totacctbal | +-----------+---------+------------+ | 10 | 882 | 6606081.31 | | 11 | 899 | 6702253.34 | | 19 | 963 | 7230776.82 | | 20 | 916 | 6824676.02 | | 22 | 894 | 6636740.03 | | 26 | 861 | 6404695.86 | | 27 | 877 | 6565078.99 | +-----------+---------+------------+ 7 rows in set (0.19 sec)
查询报告
经过五次测试后，统计每次的查询速度和平均查询速度。
| 查询ID | 第一次测试 | 第二次测试 | 第三次测试 | 第四次测试 | 第五次测试 | 平均查询速度 |
|---|---|---|---|---|---|---|
| Q1 | 2.63 sec | 2.57 sec | 2.71 sec | 2.70 sec | 2.63 sec | 2.648 sec |
| Q2 | 0.09 sec | 0.07 sec | 0.07 sec | 0.07 sec | 0.07 sec | 0.074 sec |
| Q3 | 0.17 sec | 0.15 sec | 0.16 sec | 0.15 sec | 0.16 sec | 0.158 sec |
| Q4 | 0.84 sec | 0.75 sec | 0.57 sec | 0.71 sec | 0.66 sec | 0.706 sec |
| Q5 | 0.28 sec | 0.28 sec | 0.27 sec | 0.25 sec | 0.26 sec | 0.268 sec |
| Q6 | 0.59 sec | 0.60 sec | 0.59 sec | 0.60 sec | 0.59 sec | 0.594 sec |
| Q7 | 1.04 sec | 1.11 sec | 1.00 sec | 0.98 sece | 1.03 sec | 1.032 sec |
| Q8 | 0.18 sec | 0.19 sec | 0.20 sec | 0.19 sec | 0.20 sec | 0.192 sec |
| Q9 | 0.75 sec | 0.76 sec | 0.75 sec | 0.70 sec | 0.76 sec | 0.744 sec |
| Q10 | 0.39 sec | 0.42 sec | 0.41 sec | 0.43 sec | 0.40 sec | 0.41 sec |
| Q11 | 0.07 sec | 0.06 sec | 0.05 sec | 0.05 sec | 0.07 sec | 0.06 sec |
| Q12 | 0.67 sec | 0.66 sec | 0.60 sec | 0.61 sec | 0.61 sec | 0.63 sec |
| Q13 | 0.70 sec | 0.75 sec | 0.72 sec | 0.73 sec | 0.73 sec | 0.726 sec |
| Q14 | 0.15 sec | 0.13 sec | 0.13 sec | 0.13 sec | 0.13 sec | 0.134 sec |
| Q15 | 0.16 sec | 0.14 sec | 0.15 sec | 0.15 sec | 0.16 sec | 0.152 sec |
| Q16 | 0.50 sec | 0.47 sec | 0.35 sec | 0.31 sec | 0.32 sec | 0.39 sec |
| Q17 | 0.78 sec | 0.78 sec | 0.79 sec | 0.79 sec | 0.77 sec | 0.782 sec |
| Q18 | 0.82 sec | 0.77 sec | 0.74 sec | 0.76 sec | 0.73 sec | 0.764 sec |
| Q19 | 0.66 sec | 0.65 sec | 0.71 sec | 0.68 sec | 0.64 sec | 0.668 sec |
| Q20 | 0.45 sec | 0.41 sec | 0.44 sec | 0.43 sec | 0.44 sec | 0.434 sec |
| Q21 | 1.48 sec | 1.49 sec | 1.97 sec | 1.56 sec | 1.54 sec | 1.608 sec |
| Q22 | 0.19 sec | 0.23 sec | 0.20 sec | 0.20 sec | 0.19 sec | 0.202 sec |
| 查询汇总 | 13.59 sec | 13.44 sec | 13.58 sec | 13.18 sec | 13.09 sec | 13.376 sec |
stoneDB查询
查询详情
-
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 '112' 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 | +--------------+--------------+-------------+-----------------+-------------------+---------------------+-----------+--------------+----------+-------------+ | A | F | 37734107.00 | 56586554400.73 | 53758257134.8700 | 55909065222.827692 | 25.522006 | 38273.129735 | 0.049985 | 1478493 | | N | F | 991417.00 | 1487504710.38 | 1413082168.0541 | 1469649223.194375 | 25.516472 | 38284.467761 | 0.050093 | 38854 | | N | O | 74476040.00 | 111701729697.74 | 106118230307.6056 | 110367043872.497010 | 25.502227 | 38249.117989 | 0.049997 | 2920374 | | R | F | 37719753.00 | 56568041380.90 | 53741292684.6040 | 55889619119.831932 | 25.505794 | 38250.854626 | 0.050009 | 1478870 | +--------------+--------------+-------------+-----------------+-------------------+---------------------+-----------+--------------+----------+-------------+ 4 rows in set (5.25 sec) -
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 = 48 and p_type like '%TIN' 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 ;查询结果：
... ... ... | 7051.73 | Supplier#000003349 | IRAQ | 125812 | Manufacturer#3 | wtTK9df9kY7mQ5QUM0Xe5bHLMRLgwE | 21-614-525-7451 | ar theodolites cajole fluffily across the pending requests. slyly final requests a | | 7023.47 | Supplier#000009543 | SAUDI ARABIA | 47038 | Manufacturer#1 | VYKinyOBNXRr Hdqn8kOxfTw | 30-785-782-6088 | sts. furiously pending packages sleep slyly even requests. final excuses print deposits. final pac | | 6985.93 | Supplier#000006409 | IRAQ | 131382 | Manufacturer#1 | eO8JDNM19HrlQMR | 21-627-356-3992 | sts. slyly final deposits around the regular accounts are along the furiously final pac | | 6964.75 | Supplier#000009931 | EGYPT | 57425 | Manufacturer#1 | peQYiRFk G0xZKfJ | 14-989-166-5782 | deposits according to the sometimes silent requests wake along the packages-- blithely f | | 6964.04 | Supplier#000007399 | IRAQ | 77398 | Manufacturer#2 | zdxjENOGR4QiCFP | 21-859-733-1999 | e blithely after the even requests. carefully ironic packages use slyly a | | 6913.81 | Supplier#000002625 | IRAQ | 22624 | Manufacturer#3 | a4V0rWemgbsT ZMj w7DB8rUbZ4F4lqqW5VKljQF | 21-136-564-3910 | . asymptotes among the express requests cajole furiously after the ca | | 6880.18 | Supplier#000006704 | IRAN | 26703 | Manufacturer#4 | 97rxJlAImbO1 sUlChUWoOJ0ZzvQ2NI3KI6VDOwk | 20-588-916-1286 | old accounts wake quickly. ca | | 6878.62 | Supplier#000001697 | IRAQ | 146668 | Manufacturer#5 | 37nm ODTeHy0xWTWegplgdWQqelh | 21-377-544-4864 | ironic theodolites. furiously regular d | | 6790.39 | Supplier#000008703 | IRAN | 123678 | Manufacturer#4 | wMslK1A8SEUTIIdApQ | 20-782-266-2552 | eep blithely regular, pending w | | 6763.46 | Supplier#000007882 | EGYPT | 137881 | Manufacturer#5 | JDv8BZiYG0UlZ | 14-111-252-9120 | the silent accounts wake foxes. furious | | 6751.81 | Supplier#000003156 | EGYPT | 165607 | Manufacturer#2 | alRWaW4FTFERMM4vf2rHKIKE | 14-843-946-7775 | are furiously. final theodolites affix slyly bold deposits. even packages haggle idly slyly specia | | 6702.07 | Supplier#000006276 | EGYPT | 31269 | Manufacturer#2 | ,dE1anEjKQGZfgquYfkx2fkGcXH | 14-896-626-7847 | ze about the carefully regular pint | +-----------+--------------------+--------------+-----------+----------------+------------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------+ 100 rows in set (8.09 sec) -
Q3
查询语句：
select l_orderkey, sum(l_extendedprice * (1 - l_discount)) as revenue, o_orderdate, o_shippriority from customer, orders, lineitem where c_mktsegment = 'HOUSEHOLD' and c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate < date '1995-03-29' and l_shipdate > date '1995-03-29' group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate limit 10 ;查询结果：
+------------+-------------+-------------+----------------+ | l_orderkey | revenue | o_orderdate | o_shippriority | +------------+-------------+-------------+----------------+ | 2456423 | 406181.0111 | 1995-03-05 | 0 | | 3459808 | 405838.6989 | 1995-03-04 | 0 | | 492164 | 390324.0610 | 1995-02-19 | 0 | | 1188320 | 384537.9359 | 1995-03-09 | 0 | | 2435712 | 378673.0558 | 1995-02-26 | 0 | | 4878020 | 378376.7952 | 1995-03-12 | 0 | | 5521732 | 375153.9215 | 1995-03-13 | 0 | | 2628192 | 373133.3094 | 1995-02-22 | 0 | | 993600 | 371407.4595 | 1995-03-05 | 0 | | 2300070 | 367371.1452 | 1995-03-13 | 0 | +------------+-------------+-------------+----------------+ 10 rows in set (0.45 sec) -
Q4
查询语句：
select o_orderpriority, count(*) as order_count from orders where o_orderdate >= date '1997-07-01' and o_orderdate < date '1997-07-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 ;查询结果：
+-----------------+-------------+ | o_orderpriority | order_count | +-----------------+-------------+ | 1-URGENT | 11598 | | 2-HIGH | 11406 | | 3-MEDIUM | 11305 | | 4-NOT SPECIFIED | 11550 | | 5-LOW | 11500 | +-----------------+-------------+ 5 rows in set (5.60 sec) -
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 '1994-01-01' and o_orderdate < date '1994-01-01' + interval '1' year group by n_name order by revenue desc ;查询结果：
+---------------+---------------+ | n_name | revenue | +---------------+---------------+ | PERU | 56206762.5035 | | CANADA | 56052846.0161 | | ARGENTINA | 54595012.8076 | | BRAZIL | 53601776.5201 | | UNITED STATES | 50890580.8962 | +---------------+---------------+ 5 rows in set (0.43 sec) -
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.03 - 0.01 and 0.03 + 0.01 and l_quantity < 24;查询结果：
+---------------+ | revenue | +---------------+ | 61660051.7967 | +---------------+ 1 row in set (0.06 sec) -
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 = 'FRANCE' and n2.n_name = 'ARGENTINA') or (n1.n_name = 'ARGENTINA' and n2.n_name = 'FRANCE') ) 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 ;查询结果：
+-------------+-------------+--------+---------------+ | supp_nation | cust_nation | l_year | revenue | +-------------+-------------+--------+---------------+ | ARGENTINA | FRANCE | 1995 | 57928886.8015 | | ARGENTINA | FRANCE | 1996 | 55535134.8474 | | FRANCE | ARGENTINA | 1995 | 52916227.7375 | | FRANCE | ARGENTINA | 1996 | 51077995.8841 | +-------------+-------------+--------+---------------+ 4 rows in set (2.30 sec) -
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 and o_orderdate between date '1995-01-01' and date '1996-12-31' and p_type = 'ECONOMY BURNISHED TIN' ) as all_nations group by o_year order by o_year ;查询结果：
+--------+------------+ | o_year | mkt_share | +--------+------------+ | 1995 | 0.03509430 | | 1996 | 0.03724375 | +--------+------------+ 2 rows in set (0.70 sec) -
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 '%pink%' ) as profit group by nation, o_year order by nation, o_year desc ;查询结果：
... ... ... | UNITED STATES | 1996 | 44826974.2915 | | UNITED STATES | 1995 | 44160425.4086 | | UNITED STATES | 1994 | 43193241.6843 | | UNITED STATES | 1993 | 45126307.2619 | | UNITED STATES | 1992 | 44205926.3317 | | VIETNAM | 1998 | 28289193.6726 | | VIETNAM | 1997 | 48284585.4019 | | VIETNAM | 1996 | 48360225.9084 | | VIETNAM | 1995 | 48742082.6165 | | VIETNAM | 1994 | 49035537.3894 | | VIETNAM | 1993 | 47222674.6352 | | VIETNAM | 1992 | 48628336.9011 | +----------------+--------+---------------+ 175 rows in set (1.04 sec) -
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-03-01' and o_orderdate < date '1993-03-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 ;查询结果：
... ... ... | | 117451 | Customer#000117451 | 599792.7063 | 1090.48 | UNITED STATES | bSwr7mNPiaf1f lNK9 uTJxWCL2sn1Lak5NIB | 34-354-586-6011 | ding to the furiously express accounts boost carefully af | | 104110 | Customer#000104110 | 588194.3118 | 2762.52 | JORDAN | mm7 ZuDX5Z5nAQbKObB 80XBCy,1nyW | 23-639-800-5768 | urts sleep furiously alongside of the packages! slyly ironic packages sleep | | 13666 | Customer#000013666 | 579926.1679 | 7453.98 | EGYPT | DLRUWGcprmWqdROJvmZwpE | 14-316-135-4381 | ross the silent requests. special theodolit | | 96202 | Customer#000096202 | 571017.3398 | 4703.04 | CANADA | 4Vcxcx3w4zMjVYNQaqrweweQY6TJO AP9rdvQaLl | 13-194-779-9597 | en packages use. fluffily regular dependencies boost. never pending requ | | 70279 | Customer#000070279 | 561369.3650 | 9109.34 | CHINA | ltie8o3ihwffMrqMrkvN957KZVWmH5 | 28-842-825-1717 | theodolites sleep: blithely final requests are fur | | 16972 | Customer#000016972 | 560435.8065 | 6408.66 | ROMANIA | X6T8vRKy6kSO0f2wJJt | 29-483-958-3347 | sts. pending deposits are across the regular, express instructions. carefully daring foxes cajol | | 113443 | Customer#000113443 | 557272.6706 | -72.67 | UNITED KINGDOM | SUHbS85cYxgVkKbfh9sUpEa6ezVSlQuCKe3CV | 33-819-742-6112 | ic foxes cajole thinly furiously stealthy instructions. pinto beans are. quickly regular accounts integrate car | +-----------+--------------------+-------------+-----------+----------------+------------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------------------+ 20 rows in set (0.76 sec) -
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 = 'JAPAN' 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 = 'JAPAN' ) order by value desc ;查询结果：
... ... ... | 62317 | 7594854.10 | | 106575 | 7594520.08 | | 161092 | 7594454.40 | | 9872 | 7593734.34 | | 77711 | 7593431.60 | | 61206 | 7593153.00 | | 123776 | 7592736.80 | | 185141 | 7592617.12 | | 5542 | 7592513.04 | | 185296 | 7591439.31 | | 72597 | 7591142.40 | +------------+-------------+ 1225 rows in set (0.08 sec) -
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 ('FOB', 'TRUCK') and l_commitdate < l_receiptdate and l_shipdate < l_commitdate and l_receiptdate >= date '1996-01-01' and l_receiptdate < date '1996-01-01' + interval '1' year group by l_shipmode order by l_shipmode ;查询结果：
+------------+-----------------+----------------+ | l_shipmode | high_line_count | low_line_count | +------------+-----------------+----------------+ | FOB | 6273 | 9429 | | TRUCK | 6336 | 9300 | +------------+-----------------+----------------+ 2 rows in set (0.14 sec) -
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%accounts%' group by c_custkey ) as c_orders group by c_count order by custdist desc, c_count desc ;查询结果：
... ... ... | 32 | 148 | | 2 | 134 | | 33 | 75 | | 34 | 50 | | 35 | 37 | | 1 | 17 | | 36 | 14 | | 38 | 5 | | 37 | 5 | | 40 | 4 | | 41 | 2 | | 39 | 1 | +---------+----------+ 42 rows in set (1.12 sec) -
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-01' and l_shipdate < date '1996-04-01' + interval '1' month;查询结果：
+---------------+ | promo_revenue | +---------------+ | 16.6511873129 | +---------------+ 1 row in set (0.12 sec) -
Q15
查询语句：
CREATE VIEW revenue0 AS SELECT l_suppkey AS supplier_no, sum(l_extendedprice * (1 - l_discount)) AS total_revenue FROM lineitem WHERE l_shipdate >= DATE '1996-01-01' AND l_shipdate < DATE '1996-04-01' 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;查询结果：
+-----------+--------------------+-------------------+-----------------+---------------+ | s_suppkey | s_name | s_address | s_phone | total_revenue | +-----------+--------------------+-------------------+-----------------+---------------+ | 8449 | Supplier#000008449 | Wp34zim9qYFbVctdW | 20-469-856-8873 | 1772627.2087 | +-----------+--------------------+-------------------+-----------------+---------------+ 1 row in set (0.18 sec) -
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#35' and p_type not like 'ECONOMY BURNISHED%' and p_size in (14, 7, 21, 24, 35, 33, 2, 20) 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 ;查询结果：
... ... ... | Brand#23 | SMALL BRUSHED STEEL | 2 | 3 | | Brand#24 | ECONOMY PLATED BRASS | 24 | 3 | | Brand#25 | LARGE BRUSHED STEEL | 35 | 3 | | Brand#31 | PROMO ANODIZED COPPER | 20 | 3 | | Brand#41 | LARGE BURNISHED STEEL | 20 | 3 | | Brand#43 | SMALL BRUSHED COPPER | 7 | 3 | | Brand#52 | MEDIUM POLISHED BRASS | 21 | 3 | | Brand#52 | SMALL POLISHED TIN | 2 | 3 | +----------+---------------------------+--------+--------------+ 18341 rows in set (0.63 sec) -
Q17
查询语句：
select sum(l_extendedprice) / 7.0 as avg_yearly from lineitem, part where p_partkey = l_partkey and p_brand = 'Brand#54' and p_container = 'LG BAG' and l_quantity < ( select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey );查询结果：
+---------------+ | avg_yearly | +---------------+ | 343478.602857 | +---------------+ 1 row in set (1 min 39.57 sec) -
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) > 314 ) 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 ;查询结果：
+--------------------+-----------+------------+-------------+--------------+-----------------+ | c_name | c_custkey | o_orderkey | o_orderdate | o_totalprice | sum(l_quantity) | +--------------------+-----------+------------+-------------+--------------+-----------------+ | Customer#000128120 | 128120 | 4722021 | 1994-04-07 | 544089.09 | 323.00 | | Customer#000144617 | 144617 | 3043270 | 1997-02-12 | 530604.44 | 317.00 | | Customer#000066790 | 66790 | 2199712 | 1996-09-30 | 515531.82 | 327.00 | | Customer#000015619 | 15619 | 3767271 | 1996-08-07 | 480083.96 | 318.00 | | Customer#000147197 | 147197 | 1263015 | 1997-02-02 | 467149.67 | 320.00 | | Customer#000117919 | 117919 | 2869152 | 1996-06-20 | 456815.92 | 317.00 | | Customer#000126865 | 126865 | 4702759 | 1994-11-07 | 447606.65 | 320.00 | | Customer#000036619 | 36619 | 4806726 | 1995-01-17 | 446704.09 | 328.00 | | Customer#000119989 | 119989 | 1544643 | 1997-09-20 | 434568.25 | 320.00 | +--------------------+-----------+------------+-------------+--------------+-----------------+ 9 rows in set (2.26 sec) -
Q19
查询语句：
select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part where ( p_partkey = l_partkey and p_brand = 'Brand#23' 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#15' and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') and l_quantity >= 14 and l_quantity <= 14 + 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#44' and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') and l_quantity >= 28 and l_quantity <= 28 + 10 and p_size between 1 and 15 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' );查询结果：
+--------------+ | revenue | +--------------+ | 4028540.3287 | +--------------+ 1 row in set (0.65 sec) -
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 'lime%' ) 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 '1993-01-01' and l_shipdate < date '1993-01-01' + interval '1' year ) ) and s_nationkey = n_nationkey and n_name = 'VIETNAM' order by s_name ;查询结果：
... ... ... | Supplier#000009609 | LV2rJUGfr0k3dPNRqufG1IoYHzV | | Supplier#000009619 | K0RwcJ9S75Xil jqKukFoDNkD | | Supplier#000009626 | Nm1FnIh4asUR3EnXv2Pvy3gXqI9es | | Supplier#000009738 | 15RRSVTuOzwdMP LmfCtIguMGXK | | Supplier#000009770 | Ag, SZfowit580QPDdbP8kmFHdpZ9ASI | | Supplier#000009865 | extcOh9ZrdDCMsHhhsFTkTUAh,HM2UQ2qa8sRo | | Supplier#000009866 | Auh6aZnOnQG1pPYKZ5o9ATramJBA | | Supplier#000009890 | izJXemCM Ikpgxk | | Supplier#000009937 | edZ9HQJ0KJAU6EWknTiDghKfRLHq6vtFqdey,0l | | Supplier#000009954 | VzElx9ihlXFJLIQw2Hn4bC2 | | Supplier#000009958 | ggiiSA4CSyvhwQUYjdJhWlKEY9PAfs | +--------------------+------------------------------------------+ 177 rows in set (5 min 3.55 sec) -
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 = 'BRAZIL' group by s_name order by numwait desc, s_name limit 100 ;查询结果：
Empty set (19 min 27.61 sec) -
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 ('10', '11', '26', '22', '19', '20', '27') and c_acctbal > ( select avg(c_acctbal) from customer where c_acctbal > 0.00 and substring(c_phone from 1 for 2) in ('10', '11', '26', '22', '19', '20', '27') ) and not exists ( select * from orders where o_custkey = c_custkey ) ) as custsale group by cntrycode order by cntrycode ;查询结果：
+-----------+---------+------------+ | cntrycode | numcust | totacctbal | +-----------+---------+------------+ | 10 | 882 | 6606081.31 | | 11 | 899 | 6702253.34 | | 19 | 963 | 7230776.82 | | 20 | 916 | 6824676.02 | | 22 | 894 | 6636740.03 | | 26 | 861 | 6404695.86 | | 27 | 877 | 6565078.99 | +-----------+---------+------------+ 7 rows in set (1 min 24.99 sec)
查询报告
经过五次测试后，统计每次的查询速度和平均查询速度。
| 查询ID | 第一次测试 | 第二次测试 | 第三次测试 | 第四次测试 | 第五次测试 | 平均查询速度 |
|---|---|---|---|---|---|---|
| Q1 | 4.84 sec | 4.91 sec | 4.88 sec | 4.91 sec | 4.87 sec | 4.882 sec |
| Q2 | 8.21 sec | 8.01 sec | 8.09 sec | 8.18 sec | 8.20 sec | 8.138 sec |
| Q3 | 0.32 sec | 0.32 sec | 0.32 sec | 0.31 sec | 0.31 sec | 0.316 sec |
| Q4 | 5.66 sec | 5.58 sec | 5.58 sec | 5.67 sec | 5.51 sec | 5.6 sec |
| Q5 | 0.48 sec | 0.43 sec | 0.43 sec | 0.43 sec | 0.44 sec | 0.442 sec |
| Q6 | 0.07 sec | 0.06 sec | 0.06 sec | 0.06 sec | 0.06 sec | 0.062 sec |
| Q7 | 2.33 sec | 2.35 sec | 2.34 sec | 2.30 sece | 2.30 sec | 2.324 sec |
| Q8 | 0.59 sec | 0.59 sec | 0.59 sec | 0.60 sec | 0.59 sec | 0.592 sec |
| Q9 | 1.21 sec | 1.04 sec | 1.07 sec | 1.02 sec | 1.06 sec | 1.08 sec |
| Q10 | 0.74 sec | 0.73 sec | 0.73 sec | 0.75 sec | 0.78 sec | 0.746 sec |
| Q11 | 0.07 sec | 0.07 sec | 0.07 sec | 0.08 sec | 0.08 sec | 0.074 sec |
| Q12 | 0.12 sec | 0.12 sec | 0.11 sec | 0.12 sec | 0.11 sec | 0.116 sec |
| Q13 | 1.07 sec | 1.04 sec | 1.07 sec | 1.06 sec | 1.06 sec | 1.06 sec |
| Q14 | 0.13 sec | 0.13 sec | 0.12 sec | 0.12 sec | 0.13 sec | 0.126 sec |
| Q15 | 0.20 sec | 0.20 sec | 0.18 sec | 0.20 sec | 0.18 sec | 0.192 sec |
| Q16 | 0.64 sec | 0.63 sec | 0.62 sec | 0.63 sec | 0.62 sec | 0.628 sec |
| Q17 | 1.61 sec | 1.59 sec | 1.62 sec | 1.61 sec | 1.66 sec | 1.618 sec |
| Q18 | 5.49 sec | 5.43 sec | 5.33 sec | 5.32 sec | 5.47 sec | 5.408 sec |
| Q19 | 0.57 sec | 0.56 sec | 0.55 sec | 0.54 sec | 0.56 sec | 0.556 sec |
| Q20 | 5 min 3.55 sec | 5 min 2.56 sec | 5 min 3.15 sec | 5 min 2.76 sec | 5 min 3.38 sec | 303.08 sec |
| Q21 | 不同过滤条件，执行时间差异过大 | |||||
| Q22 | 1 min 24.99 sec | 1 min 25.05 sec | 1 min 24.81 sec | 1 min 25.12 sec | 1 min 25.29 sec | 85.052 se |
测试报告
由于stoneDB官方的查询语句Q21 和 Q22执行没有结果，换用其他 过滤条件后，执行时间过长，因此不具有对比下，这里只挑选前 19个查询作为两种数据库的查询对比。
在 TPCH -1 的场景下，在前19个查询中，MatrixOne 整体相比较同类产品StoneDB具有3倍左右的查询优势，部分查询具有100倍左右的性能优势，同时在复杂查询场景下，MatrixOne具有更明显的查询优势，而stoneDB在复杂查询的场景下，表现大幅度落后于MatrixOne。
标签：07,suppkey,name,Supplier,sec,MatrixOne,TPCH,select,Manufacturer From： https://www.cnblogs.com/mklzl/p/16774505.html