TPCH22条SQL语句分析
使⽤TPC-H进⾏性能测试，需要有很多⼯作配合才能获得较⾼性能，如建⽴索引，表数据的合理分布（使⽤表空间和聚簇技术）等。
本⽂从查询优化技术的⾓度，对TPC-H的22条查询语句和主流数据库执⾏每条语句对应的查询执⾏计划进⾏分析，⽬的在于了解各个主流
数据库的查询优化技术，以TPC-H实例进⼀步掌握查询优化技术，对⽐主流数据库的实现情况对查询优化技术融会贯通。
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) * (
杨的成语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标准指定的范围内随机选
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
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⽉之间随
红参泡酒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_na
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
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, 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 //市场份额：特定种类的产品收⼊的
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 //利
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年⼀⽉到
11.Q11: 库存价值查询
Q11语句是查询库存中某个国家供应的零件的价值。
亲情类的作文
Q11语句的特点是：带有分组、排序、聚集、⼦查询操作并存的多表连接查询操作。⼦查询位于分组操作的HAVING条件中。
Q11的查询语句如下：
lect
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 = '[NATION]' group by ps_partkey having //带有
12.Q12: 货运模式和订单优先级查询
Q12语句查询获得货运模式和订单优先级。可以帮助决策：选择便宜的货运模式是否会导致消费者更多的在合同⽇期之后收到货物，⽽对紧
急优先命令产⽣负⾯影响。
Q12语句的特点是：带有分组、排序、聚集操作并存的两表连接查询操作。
Q12的查询语句如下：
lect
l_shipmode,
sum(ca //聚集操作
when o_orderpriority ='1-URGENT' //OR运算，⼆者满⾜其⼀即可，选出URGENT或HIGH的 or o_orderpriority ='2-HIGH' then 1 el 0 end) as high_line_count,
TPC-H标准定义了Q12语句等价的变形SQL，与上述查询语句格式上基本相同，主要是⽬标列使⽤了不同的表达⽅式，在此不再赘述。
晚修
1 SF，Scale Factor ，数据库的⽐例因⼦。TPC-H标准规定，测试数据库的⽐例因⼦必须从下列固定值中选择：
1，10,30,100,1000,3000,10000 (相当于1GB，10GB,30GB,100GB,1000GB,3000GB,10000GB)。数据库的⼤⼩缺省定义为
1（例如：SF＝1；近似于1GB）。
13.Q13: 消费者订单数量查询
Q13语句查询获得消费者的订单数量，包括过去和现在都没有订单记录的消费者。
Q13语句的特点是：带有分组、排序、聚集、⼦查询、左外连接操作并存的查询操作。
Q13的查询语句如下：
lect
c_count, count(*) as custdist //聚集操作，统计每个组的个数
from //⼦查询
(lect c_custkey, count(o_orderkey) from customer left outer join orders on //⼦查询中包括左外连接操作 c_custkey = o_custkey and o_comment not like ‘%[WOR
TPC-H标准定义了Q13语句等价的变形SQL，与上述查询语句格式上不相同，上述语句使⽤⼦查询作为查询的对象，变形的SQL把⼦查询
部分变为视图，然后基于视图做查询，这种做法的意义在于有些数据库不⽀持如上语法，但存在等价的其他语法，如MySQL就不⽀持如上
语法，需要使⽤如下等价形式。
create view orders_per_cust:s (custkey, ordercount) as //创建视图，相当与标准Q13的⼦查询内容
lect
c_custkey,
count(o_orderkey) from customer left outer join orders on c_custkey = o_custkey and o_comment not like '%:1%:2%' group by c_custkey; lect ordercount,
14.Q14: 促销效果查询
Q14语句查询获得某⼀个⽉的收⼊中有多⼤的百分⽐是来⾃促销零件。⽤以监视促销带来的市场反应。
Q14语句的特点是：带有分组、排序、聚集、⼦查询、左外连接操作并存的查询操作。
Q14的查询语句如下：
lect
100.00 * sum(ca
when p_type like 'PROMO%' //促销零件 then l_extendedprice*(1-l_discount) //某⼀特定时间的收⼊ el 0 end) / sum(l_extendedprice * (1 - l_discount)) as promo_
TPC-H标准定义了Q14语句等价的变形SQL，与上述查询语句格式上基本相同，主要是⽬标列使⽤了不同的表达⽅式，在此不再赘述。
15.Q15: 头等供货商查询
Q15语句查询获得某段时间内为总收⼊贡献最多的供货商（排名第⼀）的信息。可⽤以决定对哪些头等供货商给予奖励、给予更多订单、给
予特别认证、给予⿎舞等激励。
Q15语句的特点是：带有分排序、聚集、聚集⼦查询操作并存的普通表与视图的连接操作。
Q15的查询语句如下：
create view revenue[STREAM_ID](supplier_no, total_revenue) as //创建复杂视图（带有分组操作）
lect
l_suppkey,
sum(l_extendedprice * (1 - l_discount)) //获取供货商为公司带来的总利润 from lineitem where l_shipdate >= date '[DATE]' //DATE 是从1993年⼀⽉到1997年⼗⽉中任
TPC-H标准定义了Q15语句等价的变形SQL，与上述查询语句格式上不相同，上述查询语句⾸先定义了视图，然后⽤表与视图连接；变形
的SQL定了WITH语句，然后⽤WITH的对象与表进⾏连接。变形SQL的语句如下：
WITH revenue (supplier_no, total_revenue) as (
SELECT
l_suppkey,
SUM(l_extendedprice * (1-l_discount))
FROM手机划痕
lineitem
WHERE l_shipdate >= date ':1' AND l_shipdate < date ':1' + interval '3' month GROUP BY l_suppkey ) SELECT s_suppkey, s_name, s_address, s_phone, total_re
16.Q16: 零件/供货商关系查询
Q16语句查询获得能够以指定的贡献条件供应零件的供货商数量。可⽤于决定在订单量⼤，任务紧急时，是否有充⾜的供货商。
Q16语句的特点是：带有分组、排序、聚集、去重、NOT IN⼦查询操作并存的两表连接操作。
Q16的查询语句如下：
lect
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]' // BRAND＝Brand＃MN ，M和N是两个字母，代表两个数值，相互独⽴，取值在
17.Q17: ⼩订单收⼊查询
Q17语句查询获得⽐平均供货量的百分之⼆⼗还低的⼩批量订单。对于指定品牌和指定包装类型的零件，决定在⼀个七年数据库的所有订单
中这些订单零件的平均项⽬数量（过去的和未决的）。如果这些零件中少于平均数20％的订单不再被接纳，那平均⼀年会损失多少呢？所
以此查询可⽤于计算出如果没有没有⼩量订单，平均年收⼊将损失多少（因为⼤量商品的货运，将降低管理费⽤）。
Q17语句的特点是：带有聚集、聚集⼦查询操作并存的两表连接操作。东湖磨山
Q17的查询语句如下：
lect
sum(l_extendedprice) / 7.0 as avg_yearly //聚集操作
from lineitem, part where p_partkey = l_partkey and p_brand = '[BRAND]' /*指定品牌。 BRAND＝’Brand#MN’ ，M和N是两个字母，代表两个数值，相互独⽴，取值
18.Q18: ⼤订单顾客查询
Q18语句查询获得⽐指定供货量⼤的供货商信息。可⽤于决定在订单量⼤，任务紧急时，验证否有充⾜的供货商。
Q18语句的特点是：带有分组、排序、聚集、IN⼦查询操作并存的三表连接操作。查询语句没有从语法上限制返回多少条元组，但是TPC-
H标准规定，查询结果只返回前100⾏（通常依赖于应⽤程序实现）。
Q18的查询语句如下：
