TPC-H系列---6---TPC-H的22条查询语句分析(Q9--Q12)

 
Q9，产品类型利润估量查询

Q9语句是查询每个国家每一年所有被定购的零件在一年中的总利润。

Q9语句的特点是：带有分组、排序、聚集、子查询操作并存的查询操作。子查询的父层查询不存在其他查询对象，是格式相对简单的子查询，但子查询自身是多表连接的查询。子查询中使用了LIKE操作符，有的查询优化器不支持对LIKE操作符进行优化。

Q9的查询语句如下：

select

nation,

o_year,

sum(amount) as sum_profit //每个国家每一年所有被定购的零件在一年中的总利润

from

(select

n_name as nation, //国家

extract(year from o_orderdate) as o_year, //取出年份

l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount //利润

from

part,supplier,lineitem,partsupp,orders,nation //六表连接

where

s_suppkey = l_suppkey

and ps_suppkey = l_suppkey

and ps_partkey = l_partkey

and p_partkey = l_partkey

and o_orderkey = l_orderkey

and s_nationkey = n_nationkey

and p_name like '%[COLOR]%' //LIKE操作，查询优化器可能进行优化

) as profit

group by //按国家和年份分组

nation,

o_year

order by //按国家和年份排序，年份大者靠前

nation,

o_year desc;

Q10，货运存在问题的查询

Q10语句是查询每个国家在某时刻起的三个月内货运存在问题的客户和造成的损失。

Q10语句的特点是：带有分组、排序、聚集操作并存的多表连接查询操作。查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前10行（通常依赖于应用程序实现）。

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 '[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;

Q11，库存价值查询

Q11语句是查询库存中某个国家供应的零件的价值。

Q11语句的特点是：带有分组、排序、聚集、子查询操作并存的多表连接查询操作。子查询位于分组操作的HAVING条件中。

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 = '[NATION]'

group by

ps_partkey having //带有HAVING子句的分组操作

sum(ps_supplycost * ps_availqty) > ( //HAVING子句中包括有子查询

select

sum(ps_supplycost * ps_availqty) * [FRACTION] //子查询中存在聚集操作；FRACTION为0.0001/SF1

from

partsupp, supplier, nation //与父查询的表连接一致

where //与父查询的WHEWR条件一致

ps_suppkey = s_suppkey

and s_nationkey = n_nationkey

and n_name = '[NATION]' //指定国家

)

order by //按商品的价值降序排序

value desc;

Q12，货运模式和订单优先级查询

Q12语句查询获得货运模式和订单优先级。可以帮助决策：选择便宜的货运模式是否会导致消费者更多的在合同日期之后收到货物，而对紧急优先命令产生负面影响。

Q12语句的特点是：带有分组、排序、聚集操作并存的两表连接查询操作。

Q12的查询语句如下：

select

l_shipmode,

sum(case //聚集操作

when o_orderpriority ='1-URGENT' //OR运算，二者满足其一即可，选出URGENT或HIGH的

or o_orderpriority ='2-HIGH'

then 1

else 0

end) as high_line_count,

sum(case

when o_orderpriority <> '1-URGENT' //AND运算，二者都不满足，非URGENT非HIGH的

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 ('[SHIPMODE1]', '[SHIPMODE2]') /* 指定货运模式的类型，在TPC-H标准指定的范围内随机选择，SHIPMODE2必须有别于SHIPMODE1 */

and l_commitdate < l_receiptdate

and l_shipdate < l_commitdate

and l_receiptdate >= date '[DATE]' //从1993年到1997年中任一年的一月一号

and l_receiptdate < date '[DATE]' + interval '1' year //1年之内

group by //分组操作

l_shipmode

order by //排序操作

l_shipmode;

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）。