MatrixOne 从入门到实践07——TPCH性能测试

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
部署方式

均为源码部署

数据库版本	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 查询

查询详情

查询语句

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)

查询语句

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)

查询语句

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)

查询语句

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)

查询语句

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)

查询语句

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)

查询语句

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)

查询语句

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)

查询语句

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查询

查询详情

查询语句：

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)

查询语句：

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)

查询语句：

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)

查询语句：

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)

查询语句：

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)

查询语句：

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)

查询语句：

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)

查询语句：

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)

查询语句：

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。

posted @ 2022-10-10 09:24 自由如风fly 阅读(811) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

MatrixOne 从入门到实践07——TPCH性能测试

MatrixOne 从入门到实践——TPCH性能测试

环境准备

测试环境

TPCH环境

MatrixOne 测试环境

部署

建表

stoneDB 测试环境

部署

建表

导入

MatrixOne导入

导入语句

导入速度

stoneDB导入

导入语句

导入速度

查询

MatrixOne 查询

查询详情

查询报告

stoneDB查询

查询详情

查询报告

测试报告

公告