-
大数据云计算
站-
热门城市 全国站>
-
其他省市
-
-
15692118659
小标
2019-02-25
来源 ：
阅读 1327
评论 0
摘要：本文主要向大家介绍了【云计算】GreenplumTPC-H测试实例，通过具体的内容向大家展现，希望对大家学习云计算有所帮助。
本文主要向大家介绍了【云计算】GreenplumTPC-H测试实例，通过具体的内容向大家展现，希望对大家学习云计算有所帮助。
1、参考资料
2、工具下载
3、解压软件
# unzip TPC-H_Tools_v2.17.3_new.zip
# ll
2.17.3 TPC-H_Tools_v2.17.3_new.zip
4、修改makefile参数
4.1 备份文件
# cd 2.17.3/dbgen/
## cp makefile.suite{,_back}
修改一下文件即可
4.2 修改配置文件
# mv makefile.suite makefile
4.3 添加文件
# vi makefile
****************
102 ################
103 CC = gcc
104 # Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)
105 # SQLSERVER, SYBASE, ORACLE, VECTORWISE
106 # Current values for MACHINE are: ATT, DOS, HP, IBM, ICL, MVS,
107 # SGI, SUN, U2200, VMS, LINUX, WIN32
108 # Current values for WORKLOAD are: TPCH
109 DATABASE=ORACLE
110 MACHINE = LINUX
111 WORKLOAD = TPCH
*******************
5、编译
# make
6、查看帮助
# ./dbgen -h
TPC-H Population Generator (Version 2.17.3 build 0)
Copyright Transaction Processing Performance Council 1994 - 2010
USAGE:
dbgen [-{vf}][-T {pcsoPSOL}]
[-s ][-C ][-S ]
dbgen [-v] [-O m] [-s ] [-U ]
Basic Options
===========================
-C -- separate data set into chunks (requires -S, default: 1)
-f -- force. Overwrite existing files
-h -- display this message
-q -- enable QUIET mode
-s -- set Scale Factor (SF) to (default: 1)
-S -- build the th step of the data/update set (used with -C or -U)
-U -- generate update sets
-v -- enable VERBOSE mode
Advanced Options
===========================
-b -- load distributions for (default: dists.dss)
-d -- split deletes between files (requires -U)
-i -- split inserts between files (requires -U)
-T c -- generate cutomers ONLY
-T l -- generate nation/region ONLY
-T L -- generate lineitem ONLY
-T n -- generate nation ONLY
-T o -- generate orders/lineitem ONLY
-T O -- generate orders ONLY
-T p -- generate parts/partsupp ONLY
-T P -- generate parts ONLY
-T r -- generate region ONLY
-T s -- generate suppliers ONLY
-T S -- generate partsupp ONLY
To generate the SF=1 (1GB), validation database population, use:
dbgen -vf -s 1
To generate updates for a SF=1 (1GB), use:
dbgen -v -U 1 -s 1
7、生成数据
使用dbgen产生一些测试数据, -s 表示scale（单位为GB），根据需要测试的数据量指定
# ./dbgen -s 100 -f
#ll -h *.tbl
-rw-r--r-- 1 digoal users 12G Nov 19 13:35 customer.tbl
-rw-r--r-- 1 digoal users 6.6G Nov 19 13:39 lineitem.tbl
-rw-r--r-- 1 digoal users 2.2K Nov 19 12:48 nation.tbl
-rw-r--r-- 1 digoal users 1.5G Nov 19 13:39 orders.tbl
-rw-r--r-- 1 digoal users 1.2G Nov 19 12:48 partsupp.tbl
-rw-r--r-- 1 digoal users 233M Nov 19 12:48 part.tbl
-rw-r--r-- 1 digoal users 389 Nov 19 12:48 region.tbl
-rw-r--r-- 1 digoal users 686M Nov 19 13:30 supplier.tbl
8、查看生成的数据
# tail -n 1 customer.tbl
150000|Customer#000150000|DD7m6OBUJqlbTpiYmzK9SYuJBG266UKoAsKH|10|20-354-401-2016|2530.68|AUTOMOBILE|kages nag quickly for the regular theodolites. carefully even pin|
9、生成GP数据库识别的数据格式
将测试数据转换为postgresql识别的格式，删除末尾的分隔符|。
# for i in `ls *.tbl`; do sed 's/|$//' $i > ${i/tbl/csv}; done
查看转换后的数据
# ll -rth *.csv
-rw-r--r-- 1 digoal users 12G Dec 1 13:32 customer.csv
-rw-r--r-- 1 digoal users 6.5G Dec 1 13:33 lineitem.csv
-rw-r--r-- 1 digoal users 2.2K Dec 1 13:33 nation.csv
-rw-r--r-- 1 digoal users 1.5G Dec 1 13:34 orders.csv
-rw-r--r-- 1 digoal users 1.2G Dec 1 13:34 partsupp.csv
-rw-r--r-- 1 digoal users 231M Dec 1 13:34 part.csv
-rw-r--r-- 1 digoal users 384 Dec 1 13:34 region.csv
-rw-r--r-- 1 digoal users 681M Dec 1 13:34 supplier.csv
10、把包含csv文件的目录，软链接到/tmp/dss-data
# ln -s `pwd` /tmp/dss-data
11、把数据加载到数据库中
加压后按照语句创建表并执行SQL即可，一定要看https://wenku.baidu.com/view/024e682cbd64783e09122bf4.html这个说明，Q1，Q2，Q3....都代表什么意思
链接：https://pan.baidu.com/s/1UZRxjOtfugOKLe6llwyI3g 密码：eaud
13、生成测试SQL , SF= dbgen指定的scale值
#SF=
mkdir dss/queries
for q in `seq 1 22`
do
DSS_QUERY=dss/templates ./qgen -s $SF $q > dss/queries/$q.sql
sed 's/^select/explain select/' dss/queries/$q.sql > dss/queries/$q.explain.sql
done
14、在greenplum数据库中创建数据库和用户
psql
postgres=# create role digoal login;
CREATE ROLE
postgres=# create database postgres template template0 owner digoal;
CREATE DATABASE
15、配置pg_hba.conf
$ vi $MASTER_DATA_DIRECTORY/pg_hba.conf
host all all 127.0.0.1/32 trust
$ gpstop -u
16、设置几个参数
gpconfig -c enable_nestloop -v off
gpconfig -c work_mem -v 256MB
gpstop -u
17、使用digoal用户连接到postgres数据库
测试，使用digoal用户连接到postgres数据库，结果输出到./results目录：
自动创建表，加载数据。详见tpch.sh脚本
$ ./tpch.sh ./results ip port tpch-db tpch-user password {row|column}
18、如果要测试redshift可以这样
Redshift (copy by ssh):
// add manifest file to s3 first
// manifest file must in $S3/${table}.manifest
$ ./tpch.sh ./results ip port tpch-db tpch-user password redshift S3 EC2_ID EC2_KEY
19、在results中查看一些信息
可以在results中查看一些信息，例如每个SQL的explain，时间。
结束后，可以使用以下方法生成CSV报告。
$ php process.php ./results output.csv
20、测试结果
2015-12-08 10:40:23 [1449542423] : running TPC-H benchmark
2015-12-08 10:40:23 [1449542423] : running queries defined in TPC-H benchmark
2015-12-08 10:40:23 [1449542423] : running query 1
2015-12-08 10:40:43 [1449542443] : query 1 finished OK (10 seconds)
2015-12-08 10:40:43 [1449542443] : running query 2
2015-12-08 10:40:47 [1449542447] : query 2 finished OK (2 seconds)
2015-12-08 10:40:47 [1449542447] : running query 3
2015-12-08 10:40:54 [1449542454] : query 3 finished OK (4 seconds)
2015-12-08 10:40:54 [1449542454] : running query 4
2015-12-08 10:40:56 [1449542456] : query 4 finished OK (1 seconds)
2015-12-08 10:40:56 [1449542456] : running query 5
2015-12-08 10:40:58 [1449542458] : query 5 finished OK (1 seconds)
2015-12-08 10:40:58 [1449542458] : running query 6
2015-12-08 10:40:59 [1449542459] : query 6 finished OK (1 seconds)
2015-12-08 10:40:59 [1449542459] : running query 7
2015-12-08 10:41:10 [1449542470] : query 7 finished OK (6 seconds)
2015-12-08 10:41:10 [1449542470] : running query 8
2015-12-08 10:41:18 [1449542478] : query 8 finished OK (4 seconds)
2015-12-08 10:41:18 [1449542478] : running query 9
2015-12-08 10:41:54 [1449542514] : query 9 finished OK (18 seconds)
2015-12-08 10:41:54 [1449542514] : running query 10
2015-12-08 10:41:55 [1449542515] : query 10 finished OK (1 seconds)
2015-12-08 10:41:55 [1449542515] : running query 11
2015-12-08 10:41:57 [1449542517] : query 11 finished OK (1 seconds)
2015-12-08 10:41:57 [1449542517] : running query 12
2015-12-08 10:41:59 [1449542519] : query 12 finished OK (1 seconds)
2015-12-08 10:41:59 [1449542519] : running query 13
2015-12-08 10:42:04 [1449542524] : query 13 finished OK (3 seconds)
2015-12-08 10:42:04 [1449542524] : running query 14
2015-12-08 10:42:05 [1449542525] : query 14 finished OK (1 seconds)
2015-12-08 10:42:05 [1449542525] : running query 15
2015-12-08 10:42:07 [1449542527] : query 15 finished OK (1 seconds)
2015-12-08 10:42:07 [1449542527] : running query 16
2015-12-08 10:42:10 [1449542530] : query 16 finished OK (2 seconds)
2015-12-08 10:42:10 [1449542530] : running query 17
2015-12-08 10:42:26 [1449542546] : query 17 finished OK (8 seconds)
2015-12-08 10:42:26 [1449542546] : running query 18
2015-12-08 10:42:46 [1449542566] : query 18 finished OK (10 seconds)
2015-12-08 10:42:46 [1449542566] : running query 19
2015-12-08 10:42:49 [1449542569] : query 19 finished OK (2 seconds)
2015-12-08 10:42:49 [1449542569] : running query 20
2015-12-08 10:42:51 [1449542571] : query 20 finished OK (1 seconds)
2015-12-08 10:42:51 [1449542571] : running query 21
2015-12-08 10:43:06 [1449542586] : query 21 finished OK (8 seconds)
2015-12-08 10:43:06 [1449542586] : running query 22
2015-12-08 10:43:10 [1449542590] : query 22 finished OK (2 seconds)
2015-12-08 10:43:10 [1449542590] : finished TPC-H benchmark
21、建表语句
GreenPlum：
BEGIN;
CREATE TABLE PART (
P_PARTKEY SERIAL8,
P_NAME VARCHAR(55),
P_MFGR CHAR(25),
P_BRAND CHAR(10),
P_TYPE VARCHAR(25),
P_SIZE INTEGER,
P_CONTAINER CHAR(10),
P_RETAILPRICE DECIMAL,
P_COMMENT VARCHAR(23)
) with (APPENDONLY=true,BLOCKSIZE=2097152,ORIENTATION=COLUMN,CHECKSUM=true,OIDS=false) DISTRIBUTED BY (p_partkey);
COPY part FROM '/tmp/dss-data/part.csv' WITH csv DELIMITER '|';
COMMIT;
BEGIN;
CREATE TABLE REGION (
R_REGIONKEY SERIAL8,
R_NAME CHAR(25),
R_COMMENT VARCHAR(152)
) with (APPENDONLY=true,BLOCKSIZE=2097152,ORIENTATION=COLUMN,CHECKSUM=true,OIDS=false) DISTRIBUTED BY (r_regionkey);
COPY region FROM '/tmp/dss-data/region.csv' WITH csv DELIMITER '|';
COMMIT;
BEGIN;
CREATE TABLE NATION (
N_NATIONKEY SERIAL8,
N_NAME CHAR(25),
N_REGIONKEY BIGINT NOT NULL, -- references R_REGIONKEY
N_COMMENT VARCHAR(152)
) with (APPENDONLY=true,BLOCKSIZE=2097152,ORIENTATION=COLUMN,CHECKSUM=true,OIDS=false) DISTRIBUTED BY (n_nationkey);
COPY nation FROM '/tmp/dss-data/nation.csv' WITH csv DELIMITER '|';
COMMIT;
BEGIN;
CREATE TABLE SUPPLIER (
S_SUPPKEY SERIAL8,
S_NAME CHAR(25),
S_ADDRESS VARCHAR(40),
S_NATIONKEY BIGINT NOT NULL, -- references N_NATIONKEY
S_PHONE CHAR(15),
S_ACCTBAL DECIMAL,
S_COMMENT VARCHAR(101)
) with (APPENDONLY=true,BLOCKSIZE=2097152,ORIENTATION=COLUMN,CHECKSUM=true,OIDS=false) DISTRIBUTED BY (s_suppkey);
COPY supplier FROM '/tmp/dss-data/supplier.csv' WITH csv DELIMITER '|';
COMMIT;
BEGIN;
CREATE TABLE CUSTOMER (
C_CUSTKEY SERIAL8,
C_NAME VARCHAR(25),
C_ADDRESS VARCHAR(40),
C_NATIONKEY BIGINT NOT NULL, -- references N_NATIONKEY
C_PHONE CHAR(15),
C_ACCTBAL DECIMAL,
C_MKTSEGMENT CHAR(10),
C_COMMENT VARCHAR(117)
) with (APPENDONLY=true,BLOCKSIZE=2097152,ORIENTATION=COLUMN,CHECKSUM=true,OIDS=false) DISTRIBUTED BY (c_custkey);
COPY customer FROM '/tmp/dss-data/customer.csv' WITH csv DELIMITER '|';
COMMIT;
BEGIN;
CREATE TABLE PARTSUPP (
PS_PARTKEY BIGINT NOT NULL, -- references P_PARTKEY
PS_SUPPKEY BIGINT NOT NULL, -- references S_SUPPKEY
PS_AVAILQTY INTEGER,
PS_SUPPLYCOST DECIMAL,
PS_COMMENT VARCHAR(199)
) with (APPENDONLY=true,BLOCKSIZE=2097152,ORIENTATION=COLUMN,CHECKSUM=true,OIDS=false) DISTRIBUTED BY (ps_partkey,ps_suppkey);
COPY partsupp FROM '/tmp/dss-data/partsupp.csv' WITH csv DELIMITER '|';
COMMIT;
BEGIN;
CREATE TABLE ORDERS (
O_ORDERKEY SERIAL8,
O_CUSTKEY BIGINT NOT NULL, -- references C_CUSTKEY
O_ORDERSTATUS CHAR(1),
O_TOTALPRICE DECIMAL,
O_ORDERDATE DATE,
O_ORDERPRIORITY CHAR(15),
O_CLERK CHAR(15),
O_SHIPPRIORITY INTEGER,
O_COMMENT VARCHAR(79)
) with (APPENDONLY=true,BLOCKSIZE=2097152,ORIENTATION=COLUMN,CHECKSUM=true,OIDS=false) DISTRIBUTED BY (o_orderkey);
COPY orders FROM '/tmp/dss-data/orders.csv' WITH csv DELIMITER '|';
COMMIT;
BEGIN;
CREATE TABLE LINEITEM (
L_ORDERKEY BIGINT NOT NULL, -- references O_ORDERKEY
L_PARTKEY BIGINT NOT NULL, -- references P_PARTKEY (compound fk to PARTSUPP)
L_SUPPKEY BIGINT NOT NULL, -- references S_SUPPKEY (compound fk to PARTSUPP)
L_LINENUMBER INTEGER,
L_QUANTITY DECIMAL,
L_EXTENDEDPRICE DECIMAL,
L_DISCOUNT DECIMAL,
L_TAX DECIMAL,
L_RETURNFLAG CHAR(1),
L_LINESTATUS CHAR(1),
L_SHIPDATE DATE,
L_COMMITDATE DATE,
L_RECEIPTDATE DATE,
L_SHIPINSTRUCT CHAR(25),
L_SHIPMODE CHAR(10),
L_COMMENT VARCHAR(44)
) with (APPENDONLY=true,BLOCKSIZE=2097152,ORIENTATION=COLUMN,CHECKSUM=true,OIDS=false) DISTRIBUTED BY (l_orderkey, l_linenumber);
COPY lineitem FROM '/tmp/dss-data/lineitem.csv' WITH csv DELIMITER '|';
COMMIT;
MySql:
--
-- Table structure for table `customer`
--
DROP TABLE IF EXISTS `customer`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `customer` (
`C_CUSTKEY` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`C_NAME` varchar(25) DEFAULT NULL,
`C_ADDRESS` varchar(40) DEFAULT NULL,
`C_NATIONKEY` bigint(20) NOT NULL,
`C_PHONE` char(15) DEFAULT NULL,
`C_ACCTBAL` decimal(10,0) DEFAULT NULL,
`C_MKTSEGMENT` char(10) DEFAULT NULL,
`C_COMMENT` varchar(117) DEFAULT NULL,
UNIQUE KEY `C_CUSTKEY` (`C_CUSTKEY`)
) ENGINE=MyISAM AUTO_INCREMENT=150001 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `lineitem`
--
DROP TABLE IF EXISTS `lineitem`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `lineitem` (
`L_ORDERKEY` bigint(20) NOT NULL,
`L_PARTKEY` bigint(20) NOT NULL,
`L_SUPPKEY` bigint(20) NOT NULL,
`L_LINENUMBER` int(11) DEFAULT NULL,
`L_QUANTITY` decimal(10,0) DEFAULT NULL,
`L_EXTENDEDPRICE` decimal(10,0) DEFAULT NULL,
`L_DISCOUNT` decimal(10,0) DEFAULT NULL,
`L_TAX` decimal(10,0) DEFAULT NULL,
`L_RETURNFLAG` char(1) DEFAULT NULL,
`L_LINESTATUS` char(1) DEFAULT NULL,
`L_SHIPDATE` date DEFAULT NULL,
`L_COMMITDATE` date DEFAULT NULL,
`L_RECEIPTDATE` date DEFAULT NULL,
`L_SHIPINSTRUCT` char(25) DEFAULT NULL,
`L_SHIPMODE` char(10) DEFAULT NULL,
`L_COMMENT` varchar(44) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `nation`
--
DROP TABLE IF EXISTS `nation`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `nation` (
`N_NATIONKEY` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`N_NAME` char(25) DEFAULT NULL,
`N_REGIONKEY` bigint(20) NOT NULL,
`N_COMMENT` varchar(152) DEFAULT NULL,
UNIQUE KEY `N_NATIONKEY` (`N_NATIONKEY`)
) ENGINE=MyISAM AUTO_INCREMENT=25 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `orders`
--
DROP TABLE IF EXISTS `orders`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `orders` (
`O_ORDERKEY` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`O_CUSTKEY` bigint(20) NOT NULL,
`O_ORDERSTATUS` char(1) DEFAULT NULL,
`O_TOTALPRICE` decimal(10,0) DEFAULT NULL,
`O_ORDERDATE` date DEFAULT NULL,
`O_ORDERPRIORITY` char(15) DEFAULT NULL,
`O_CLERK` char(15) DEFAULT NULL,
`O_SHIPPRIORITY` int(11) DEFAULT NULL,
`O_COMMENT` varchar(79) DEFAULT NULL,
UNIQUE KEY `O_ORDERKEY` (`O_ORDERKEY`)
) ENGINE=MyISAM AUTO_INCREMENT=6000001 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `part`
--
DROP TABLE IF EXISTS `part`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `part` (
`P_PARTKEY` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`P_NAME` varchar(55) DEFAULT NULL,
`P_MFGR` char(25) DEFAULT NULL,
`P_BRAND` char(10) DEFAULT NULL,
`P_TYPE` varchar(25) DEFAULT NULL,
`P_SIZE` int(11) DEFAULT NULL,
`P_CONTAINER` char(10) DEFAULT NULL,
`P_RETAILPRICE` decimal(10,0) DEFAULT NULL,
`P_COMMENT` varchar(23) DEFAULT NULL,
UNIQUE KEY `P_PARTKEY` (`P_PARTKEY`)
) ENGINE=MyISAM AUTO_INCREMENT=200001 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `partsupp`
--
DROP TABLE IF EXISTS `partsupp`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `partsupp` (
`PS_PARTKEY` bigint(20) NOT NULL,
`PS_SUPPKEY` bigint(20) NOT NULL,
`PS_AVAILQTY` int(11) DEFAULT NULL,
`PS_SUPPLYCOST` decimal(10,0) DEFAULT NULL,
`PS_COMMENT` varchar(199) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `region`
--
DROP TABLE IF EXISTS `region`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `region` (
`R_REGIONKEY` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`R_NAME` char(25) DEFAULT NULL,
`R_COMMENT` varchar(152) DEFAULT NULL,
UNIQUE KEY `R_REGIONKEY` (`R_REGIONKEY`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `supplier`
--
DROP TABLE IF EXISTS `supplier`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `supplier` (
`S_SUPPKEY` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`S_NAME` char(25) DEFAULT NULL,
`S_ADDRESS` varchar(40) DEFAULT NULL,
`S_NATIONKEY` bigint(20) NOT NULL,
`S_PHONE` char(15) DEFAULT NULL,
`S_ACCTBAL` decimal(10,0) DEFAULT NULL,
`S_COMMENT` varchar(101) DEFAULT NULL,
UNIQUE KEY `S_SUPPKEY` (`S_SUPPKEY`)
) ENGINE=MyISAM AUTO_INCREMENT=10001 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
DROP TABLE IF EXISTS `partsupp`;
CREATE TABLE `partsupp`(
`PS_PARTKEY` bigint(20) unsigned NOT NULL,
`PS_SUPPKEY` bigint(20) unsigned NOT NULL,
`PS_AVAILQTY` int(11),
`PS_SUPPLYCOST` decimal,
`PS_COMMENT` varchar(199)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
22、 查询语句
Q1:
-- using 1471398061 as a seed to the RNG
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 '85' day
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus
LIMIT 1;
Q2:
-- using 1471398061 as a seed to the RNG
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 '%STEEL'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'AFRICA'
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 = 'AFRICA'
)
order by
s_acctbal desc,
n_name,
s_name,
p_partkey
LIMIT 100;
Q3:
-- using 1471398061 as a seed to the RNG
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-03'
and l_shipdate > date '1995-03-03'
group by
l_orderkey,
o_orderdate,
o_shippriority
order by
revenue desc,
o_orderdate
LIMIT 10;
Q4:
-- using 1471398061 as a seed to the RNG
select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate >= date '1993-06-01'
and o_orderdate < date '1993-06-01' + interval '3' month
and exists (
select
*
from
lineitem
where
l_orderkey = o_orderkey
and l_commitdate < l_receiptdate
)
group by
o_orderpriority
order by
o_orderpriority
LIMIT 1;
Q5:
-- using 1471398061 as a seed to the RNG
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 '1993-01-01'
and o_orderdate < date '1993-01-01' + interval '1' year
group by
n_name
order by
revenue desc
LIMIT 1;
Q6:
-- using 1471398061 as a seed to the RNG
select
sum(l_extendedprice * l_discount) as revenue
from
lineitem
where
l_shipdate >= date '1993-01-01'
and l_shipdate < date '1993-01-01' + interval '1' year
and l_discount between 0.02 - 0.01 and 0.02 + 0.01
and l_quantity < 24
LIMIT 1;
Q7:
-- using 1471398061 as a seed to the RNG
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 = 'BRAZIL' and n2.n_name = 'INDONESIA')
or (n1.n_name = 'INDONESIA' and n2.n_name = 'BRAZIL')
)
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
LIMIT 1;
Q8:
-- using 1471398061 as a seed to the RNG
select
o_year,
sum(case
when nation = 'INDONESIA' then volume
else 0
end) / sum(volume) as mkt_share
from
(
select
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) as volume,
n2.n_name as nation
from
part,
supplier,
lineitem,
orders,
customer,
nation n1,
nation n2,
region
where
p_partkey = l_partkey
and s_suppkey = l_suppkey
and l_orderkey = o_orderkey
and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey
and r_name = 'ASIA'
and s_nationkey = n2.n_nationkey
and o_orderdate between date '1995-01-01' and date '1996-12-31'
and p_type = 'ECONOMY BURNISHED BRASS'
) as all_nations
group by
o_year
order by
o_year
LIMIT 1;
Q9:
-- using 1471398061 as a seed to the RNG
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 '%powder%'
) as profit
group by
nation,
o_year
order by
nation,
o_year desc
LIMIT 1;-- using 1471398061 as a seed to the RNG
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-06-01'
and o_orderdate < date '1993-06-01' + interval '3' month
and l_returnflag = 'R'
and c_nationkey = n_nationkey
group by
c_custkey,
c_name,
c_acctbal,
c_phone,
n_name,
c_address,
c_comment
order by
revenue desc
LIMIT 20;
Q11
-- using 1471398061 as a seed to the RNG
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 = 'PERU'
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 = 'PERU'
)
order by
value desc
LIMIT 1;-- using 1471398061 as a seed to the RNG
Q12
select
l_shipmode,
sum(case
when o_orderpriority = '1-URGENT'
or o_orderpriority = '2-HIGH'
then 1
else 0
end) as high_line_count,
sum(case
when o_orderpriority <> '1-URGENT'
and o_orderpriority <> '2-HIGH'
then 1
else 0
end) as low_line_count
from
orders,
lineitem
where
o_orderkey = l_orderkey
and l_shipmode in ('REG AIR', 'RAIL')
and l_commitdate < l_receiptdate
and l_shipdate < l_commitdate
and l_receiptdate >= date '1993-01-01'
and l_receiptdate < date '1993-01-01' + interval '1' year
group by
l_shipmode
order by
l_shipmode
LIMIT 1;-- using 1471398061 as a seed to the RNG
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%packages%'
group by
c_custkey
) as c_orders (c_custkey, c_count)
group by
c_count
order by
custdist desc,
c_count desc
Q14
LIMIT 1;-- using 1471398061 as a seed to the RNG
select
100.00 * sum(case
when p_type like 'PROMO%'
then l_extendedprice * (1 - l_discount)
else 0
end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
from
lineitem,
part
where
l_partkey = p_partkey
and l_shipdate >= date '1993-09-01'
and l_shipdate < date '1993-09-01' + interval '1' month
LIMIT 1;
Q15
-- using 1471398061 as a seed to the RNG
create view revenue0 (supplier_no, total_revenue) as
select
l_suppkey,
sum(l_extendedprice * (1 - l_discount))
from
lineitem
where
l_shipdate >= date '1994-11-01'
and l_shipdate < date '1994-11-01' + interval '3' month
group by
l_suppkey;
select
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
from
supplier,
revenue0
where
s_suppkey = supplier_no
and total_revenue = (
select
max(total_revenue)
from
revenue0
)
order by
s_suppkey
LIMIT 1;
Q16
drop view revenue0;-- using 1471398061 as a seed to the RNG
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#22'
and p_type not like 'STANDARD PLATED%'
and p_size in (34, 17, 18, 16, 15, 49, 1, 48)
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
LIMIT 1;
Q17:
-- using 1471398061 as a seed to the RNG
select
sum(l_extendedprice) / 7.0 as avg_yearly
from
lineitem,
part,
(SELECT l_partkey AS agg_partkey, 0.2 * avg(l_quantity) AS avg_quantity FROM lineitem GROUP BY l_partkey) part_agg
where
p_partkey = l_partkey
and agg_partkey = l_partkey
and p_brand = 'Brand#21'
and p_container = 'JUMBO JAR'
and l_quantity < avg_quantity
LIMIT 1;
Q18
-- using 1471398061 as a seed to the RNG
select
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice,
sum(l_quantity)
from
customer,
orders,
lineitem
where
o_orderkey in (
select
l_orderkey
from
lineitem
group by
l_orderkey having
sum(l_quantity) > 312
)
and c_custkey = o_custkey
and o_orderkey = l_orderkey
group by
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice
order by
o_totalprice desc,
o_orderdate
LIMIT 100;-- using 1471398061 as a seed to the RNG
Q19
select
sum(l_extendedprice* (1 - l_discount)) as revenue
from
lineitem,
part
where
(
p_partkey = l_partkey
and p_brand = 'Brand#42'
and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
and l_quantity >= 7 and l_quantity <= 7 + 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#22'
and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
and l_quantity >= 20 and l_quantity <= 20 + 10
and p_size between 1 and 10
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinst
本文由职坐标整理并发布，希望对同学们有所帮助。了解更多详情请关注职坐标大数据云计算大数据安全频道！
喜欢 | 0
不喜欢 | 0
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了，明天再试试吧！
我们会在第一时间安排职业规划师联系您！
您也可以联系我们的职业规划师咨询：
版权所有 职坐标-一站式AI+学习就业服务平台 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com，All Rights Reserved.
沪公网安备 31011502005948号
资料领取
答疑解惑
技术交流
职业测评
面试技巧
高薪秘笈
