TPC-H系列---7---TPC-H的22条查询语句分析(Q13--Q15)

Q13，消费者订单数量查询

Q13语句查询获得消费者的订单数量，包括过去和现在都没有订单记录的消费者。

Q13语句的特点是：带有分组、排序、聚集、子查询、左外连接操作并存的查询操作。

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 ‘%[WORD1]%[WORD2]%’ //LIKE操作

//WORD1 为以下四个可能值中任意一个：special、pending、unusual、express

//WORD2 为以下四个可能值中任意一个：packages、requests、accounts、deposits

group by //子查询中的分组操作

c_custkey

)as c_orders (c_custkey, c_count)

group by //分组操作

c_count

order by //排序操作

custdist desc, //从大到小降序排序

c_count desc;

TPC-H标准定义了Q13语句等价的变形SQL，与上述查询语句格式上不相同，上述语句使用子查询作为查询的对象，变形的SQL把子查询部分变为视图，然后基于视图做查询，这种做法的意义在于有些数据库不支持如上语法，但存在等价的其他语法，如MySQL就不支持如上语法，需要使用如下等价形式。

create view orders_per_cust:s (custkey, ordercount) as //创建视图，相当与标准Q13的子查询内容

select

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;

select

ordercount,

count(*) as custdist

from

orders_per_cust:s //对视图进行查询

group by

ordercount

order by

custdist desc,

ordercount desc;

drop view orders_per_cust:s;

Q14，促销效果查询

Q14语句查询获得某一个月的收入中有多大的百分比是来自促销零件。用以监视促销带来的市场反应。

Q14语句的特点是：带有分组、排序、聚集、子查询、左外连接操作并存的查询操作。

Q14的查询语句如下：

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 '[DATE]' // DATE是从1993年到1997年中任一年的任一月的一号

and l_shipdate < date '[DATE]' + interval '1' month;

TPC-H标准定义了Q14语句等价的变形SQL，与上述查询语句格式上基本相同，主要是目标列使用了不同的表达方式，在此不再赘述。

Q15，头等供货商查询

Q15语句查询获得某段时间内为总收入贡献最多的供货商（排名第一）的信息。可用以决定对哪些头等供货商给予奖励、给予更多订单、给予特别认证、给予鼓舞等激励。

Q15语句的特点是：带有分排序、聚集、聚集子查询操作并存的普通表与视图的连接操作。

Q15的查询语句如下：

create view revenue[STREAM_ID] (supplier_no, total_revenue) as //创建复杂视图（带有分组操作）

select

l_suppkey,

sum(l_extendedprice * (1 - l_discount)) //获取供货商为公司带来的总利润

from

lineitem

where

l_shipdate >= date '[DATE]' //DATE 是从1993年一月到1997年十月中任一月的一号

and l_shipdate < date '[DATE]' + interval '3' month //3个月内

group by //分组键与查询对象之一相同

l_suppkey;

//查询语句

select

s_suppkey,

s_name,

s_address,

s_phone,

total_revenue

from

supplier,revenue[STREAM_ID] //普通表与复杂视图进行连接操作

where

s_suppkey = supplier_no

and total_revenue = (//聚集子查询

select

max(total_revenue)

from

revenue[STREAM_ID] //聚集子查询从视图获得数据

)

order by

s_suppkey;

//删除视图

drop view revenue[STREAM_ID];

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_revenue

FROM

supplier,

revenue

WHERE

s_suppkey = supplier_no

AND total_revenue = (

SELECT

MAX(total_revenue)

FROM

revenue

)

ORDER BY

s_suppkey;