Q6,预测收入变化查询
Q6语句查询得到某一年中通过变换折扣带来的增量收入。这是典型的“what-if”判断,用来寻找增加收入的途径。预测收入变化查询考虑了指定的一年中折扣在“DISCOUNT-0.01”和“DISCOUNT+0.01”之间的已运送的所有订单,求解把l_quantity小于quantity的订单的折扣消除之后总收入增加的数量。
Q6语句的特点是:带有聚集操作的单表查询操作。查询语句使用了BETWEEN-AND操作符,有的数据库可以对BETWEEN-AND进行优化。
Q6的查询语句如下:
select
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]中随机选择
Q7,货运盈利情况查询
Q7语句是查询从供货商国家与销售商品的国家之间通过销售获利情况的查询。此查询确定在两国之间货运商品的量用以帮助重新谈判货运合同。
Q7语句的特点是:带有分组、排序、聚集、子查询操作并存的多表查询操作。子查询的父层查询不存在其他查询对象,是格式相对简单的子查询。
Q7的查询语句如下:
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 ( // 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;
Q8,国家市场份额查询
Q8语句是查询在过去的两年中一个给定零件类型在某国某地区市场份额的变化情况。
Q8语句的特点是:带有分组、排序、聚集、子查询操作并存的查询操作。子查询的父层查询不存在其他查询对象,是格式相对简单的子查询,但子查询自身是多表连接的查询。
Q8的查询语句如下:
select
o_year, //年份
sum(case
when nation = '[NATION]'//指定国家,在TPC-H标准指定的范围内随机选择
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 = '[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,与上述查询语句格式上基本相同,主要是目标列使用了不同的表达方式,在此不再赘述。