TPCH模型规范及测试说明
使⽤TPC-H进⾏性能测试，需要有很多⼯作配合才能获得较⾼性能，如建⽴索引，表数据的合理分布（使⽤表空间和聚簇技术）等。
本⽂从查询优化技术的⾓度，对TPC-H的22条查询语句和主流数据库执⾏每条语句对应的查询执⾏计划进⾏分析，⽬的在于了解各个主流数据库的查询优化技术，以TPC-H实例进⼀步掌握查询优化技术，对⽐主流数据库的实现情况对查询优化技术融会贯通。
参考：
规范及标准建表语句、SQL参见：
TPCH建表语句（是否采⽤分区，多少分区，压缩等⼀般看具体数据库实现，择优）：
1SF实际的数据量⽐例范围如下：
所以分布式数据库，模型设计时要跟着orders和lineitem做co-location。不然重分布都可能会数据偏多。nation、region、supplier适合做⼴播表。
个人税起征点 TPCH测试⼯具可以使⽤官⽅提供的源程序编译（不含建表语句），也可以使⽤（⽀持测试oracle、mysql、pg、gp、sql rver等）。
1.Q1：价格统计报告查询
Q1语句是查询lineItems的⼀个定价总结报告。在单个表lineitem上查询某个时间段内，对已经付款的、已经运送的等各类商品进⾏统计，包括业务量的计费、发货、折扣、税、平均价格等信息。
Q1语句的特点是：带有分组、排序、聚集操作并存的单表查询操作。这个查询会导致表上的数据有95%到97%⾏被读取到。
Q1的查询语句如下：
lect
l_returnflag, //返回标志
l_linestatus,
sum(l_quantity) as sum_qty, //总的数量
sum(l_extendedprice) as sum_ba_price, //聚集函数操作
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order //每个分组所包含的⾏数
from
lineitem
where
l_shipdate <= date'1998-12-01' - interval '90' day //时间段是随机⽣成的
group by //分组操作
l_returnflag,
l_linestatus
order by //排序操作
l_returnflag,
l_linestatus;
2.Q2: 最⼩代价供货商查询
Q2语句查询获得最⼩代价的供货商。得到给定的区域内，对于指定的零件（某⼀类型和⼤⼩的零件），哪个供应者能以最低的价格供应它，就可以选择哪个供应者来订货。
Q2语句的特点是：带有排序、聚集操作、⼦查询并存的多表查询操作。查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前100⾏（通常依赖于应⽤程序实现）。
Q2的查询语句如下：
lect
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 = [SIZE] //指定⼤⼩，在区间[1, 50]内随机选择
and p_type like '%[TYPE]' //指定类型，在TPC-H标准指定的范围内随机选择
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = '[REGION]' //指定地区，在TPC-H标准指定的范围内随机选择
and ps_supplycost = ( //⼦查询
lect
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 = '[REGION]'
)
order by //排序
s_acctbal desc,
n_name,
s_name,
p_partkey;
3.Q3: 运送优先级查询
Q3语句查询得到收⼊在前10位的尚未运送的订单。在指定的⽇期之前还没有运送的订单中具有最⼤收⼊的订单的运送优先级（订单按照收⼊的降序排序）
和潜在的收⼊（潜在的收⼊为l_extendedprice * (1-l_discount)的和）。
Q3语句的特点是：带有分组、排序、聚集操作并存的三表查询操作。查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前10⾏（通常依赖于应⽤程序实现）。
Q3的查询语句如下：
lect
l_orderkey,
sum(l_extendedprice*(1-l_discount)) as revenue, //潜在的收⼊，聚集操作
o_orderdate,
o_shippriority
from
customer, orders, lineitem //三表连接
where
c_mktgment = '[SEGMENT]' //在TPC-H标准指定的范围内随机选择
and c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate < date '[DATE]' //指定⽇期段，在在[1995-03-01, 1995-03-31]中随机选择
and l_shipdate > date '[DATE]'
group by //分组操作
l_orderkey, //订单标识
o_orderdate, //订单⽇期
o_shippriority //运输优先级
order by //排序操作
revenue desc, //降序排序，把潜在最⼤收⼊列在前⾯
o_orderdate;
4.Q4: 订单优先级查询
Q4语句查询得到订单优先级统计值。计算给定的某三个⽉的订单的数量，在每个订单中⾄少有⼀⾏由顾客在它的提交⽇期之后收到。
Q4语句的特点是：带有分组、排序、聚集操作、⼦查询并存的单表查询操作。⼦查询是相关⼦查询。
Q4的查询语句如下：
lect
真丝服装
o_orderpriority, //订单优先级
count(*) as order_count //订单优先级计数
from orders //单表查询
where
o_orderdate >= date '[DATE]'
and o_orderdate < date '[DATE]' + interval '3' month //指定订单的时间段--某三个⽉，DATE是在1993年1⽉和1997年10⽉之间随机选择的⼀个⽉的第⼀天
and exists ( //⼦查询
lect
*
from
lineitem
where
l_orderkey = o_orderkey
and l_commitdate < l_receiptdate
)
group by //按订单优先级分组
o_orderpriority
order by //按订单优先级排序
o_orderpriority;
5.Q5: 某地区供货商为公司带来的收⼊查询
Q5语句查询得到通过某个地区零件供货商⽽获得的收⼊（收⼊按sum(l_extendedprice * (1 -l_discount))计算）统计信息。可⽤于决定在给定的区域是否需要建⽴⼀个当地分配中⼼。
Q5语句的特点是：带有分组、排序、聚集操作、⼦查询并存的多表连接查询操作。
Q5的查询语句如下：
lect
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 = '[REGION]' //指定地区，在TPC-H标准指定的范围内随机选择
and o_orderdate >= date '[DATE]' //DATE是从1993年到1997年中随机选择的⼀年的1⽉1⽇
and o_orderdate < date '[DATE]' + interval '1' year
group by //按名字分组
n_name
order by //按收⼊降序排序，注意分组和排序⼦句不同
revenue desc;
6.Q6: 预测收⼊变化查询
Q6语句查询得到某⼀年中通过变换折扣带来的增量收⼊。这是典型的“what-if”判断，⽤来寻找增加收
⼊的途径。预测收⼊变化查询考虑了指定的⼀年中折扣在“DISCOUNT-0.01”和“DISCOUNT＋0.01”之间的已运送的所有订单，求解把l_quantity⼩于quantity的订单的折扣消除之后总收⼊增加的数量。
Q6语句的特点是：带有聚集操作的单表查询操作。查询语句使⽤了BETWEEN-AND操作符，有的数据库可以对BETWEEN-AND进⾏优化。
Q6的查询语句如下：
lect
sum(l_extendedprice*l_discount) as revenue //潜在的收⼊增加量
from
lineitem //单表查询
where
l_shipdate >= date '[DATE]' //DATE是从[1993, 1997]中随机选择的⼀年的1⽉1⽇
and l_shipdate < date '[DATE]' + interval '1' year //⼀年内
and l_discount between [DISCOUNT] - 0.01 and [DISCOUNT] + 0.01 //between
and l_quantity < [QUANTITY]; // QUANTITY在区间[24, 25]中随机选择
7.Q7: 货运盈利情况查询
Q7语句是查询从供货商国家与销售商品的国家之间通过销售获利情况的查询。此查询确定在两国之间货运商品的量⽤以帮助重新谈判货运合同。
Q7语句的特点是：带有分组、排序、聚集、⼦查询操作并存的多表查询操作。⼦查询的⽗层查询不存在其他查询对象，是格式相对简单的⼦查询。
Q7的查询语句如下：公民道德宣传日
lect
supp_nation, //供货商国家
cust_nation, //顾客国家
l_year, sum(volume) as revenue //年度、年度的货运收⼊
from ( //⼦查询
lect
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 ( // NATION2和NATION1的值不同，表⽰查询的是跨国的货运情况
(n1.n_name = '[NATION1]' and n2.n_name = '[NATION2]')
or (n1.n_name = '[NATION2]' and n2.n_name = '[NATION1]')
)
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;
8.Q8: 国家市场份额查询
Q8语句是查询在过去的两年中⼀个给定零件类型在某国某地区市场份额的变化情况。
Q8语句的特点是：带有分组、排序、聚集、⼦查询操作并存的查询操作。⼦查询的⽗层查询不存在其他查询对象，是格式相对简单的⼦查询，但⼦查询⾃⾝是多表连接的查询。
Q8的查询语句如下：
lect
小区物业管理方案o_year, //年份
sum(ca
when nation = '[NATION]'//指定国家，在TPC-H标准指定的范围内随机选择
then volume
el 0
end) / sum(volume) as mkt_share //市场份额：特定种类的产品收⼊的百分⽐；聚集操作
from //⼦查询
(lect
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 = '[REGION]' //指定地区，在TPC-H标准指定的范围内随机选择
and s_nationkey = n2.n_nationkey
and o_orderdate between date '1995-01-01' and date '1996-12-31' //只查95、96年的情况
and p_type = '[TYPE]' //指定零件类型，在TPC-H标准指定的范围内随机选择
) as all_nations
group by //按年分组
o_year
order by //按年排序
o_year;
TPC-H标准定义了Q8语句等价的变形SQL，与上述查询语句格式上基本相同，主要是⽬标列使⽤了不同的表达⽅式，在此不再赘述。
9.Q9: 产品类型利润估量查询
Q9语句是查询每个国家每⼀年所有被定购的零件在⼀年中的总利润。
Q9语句的特点是：带有分组、排序、聚集、⼦查询操作并存的查询操作。⼦查询的⽗层查询不存在其他查询对象，是格式相对简单的⼦查询，但⼦查询⾃⾝是多表连接的查询。⼦查询中使⽤了LIKE操作符，有的查询优化器不⽀持对LIKE操作符进⾏优化。
Q9的查询语句如下：
lect
nation,
o_year,
sum(amount) as sum_profit //每个国家每⼀年所有被定购的零件在⼀年中的总利润
from
(lect
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 '%[COLOR]%' //LIKE操作，查询优化器可能进⾏优化
)
as profit
group by //按国家和年份分组
nation,
o_year
order by //按国家和年份排序，年份⼤者靠前
nation,
o_year desc;
10.Q10: 货运存在问题的查询
Q10语句是查询每个国家在某时刻起的三个⽉内货运存在问题的客户和造成的损失。
Q10语句的特点是：带有分组、排序、聚集操作并存的多表连接查询操作。查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前10⾏（通常依赖于应⽤程序实现）。
Q10的查询语句如下：
lect
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 '[DATE]' // DATE是位于1993年⼀⽉到1994年⼗⼆⽉中任⼀⽉的⼀号
and o_orderdate < date '[DATE]' + interval '3' month //3个⽉内
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;
11.Q11: 库存价值查询
Q11语句是查询库存中某个国家供应的零件的价值。
Q11语句的特点是：带有分组、排序、聚集、⼦查询操作并存的多表连接查询操作。⼦查询位于分组操作的HAVING条件中。
Q11的查询语句如下：
