TPCH模型規範、測試說明及22條語句
使用TPC-H進行效能測試，需要有很多工作配合才能獲得較高效能，如建立索引，表資料的合理分佈（使用表空間和聚簇技術）、廣播表或分散式表（會嚴重的影響效能）、列存（會嚴重的影響效能）、壓縮（不同的壓縮空間佔用會差異非常大，gp預設lz4，也支援zstd）等。
本文從查詢最佳化技術的角度，對TPC-H的22條查詢語句和主流資料庫執行每條語句對應的查詢執行計劃進行分析，目的在於瞭解各個主流資料庫的查詢最佳化技術，以TPC-H例項進一步掌握查詢最佳化技術，對比主流資料庫的實現情況對查詢最佳化技術融會貫通。
參考：關聯式資料庫效能測試規範TPC及工具hammerdb、benchmarksql一覽
規範及標準建表語句、SQL參見：http://tpc.org/tpc_documents_current_versions/pdf/tpc-h_v3.0.0.pdf
TPCH建表語句（是否採用分割槽，多少分割槽，壓縮等一般看具體資料庫實現，擇優）：https://www.cnblogs.com/klb561/p/10995086.html
1SF實際的資料量比例範圍如下：
所以分散式資料庫，模型設計時要跟著orders和lineitem做co-location。不然重分佈都可能會資料偏多。nation、region、supplier適合做廣播表。
TPCH測試工具可以使用官方提供的源程式編譯（不含建表語句），也可以使用hammerdb（支援測試oracle、mysql、pg、gp、sql server等）。https://blog.csdn.net/pursuitbeauty/article/details/48176069
1.Q1：價格統計報告查詢
Q1語句是查詢lineItems的一個定價總結報告。在單個表lineitem上查詢某個時間段內，對已經付款的、已經運送的等各類商品進行統計，包括業務量的計費、發貨、折扣、稅、平均價格等資訊。
Q1語句的特點是：帶有分組、排序、聚集操作並存的單表查詢操作。這個查詢會導致表上的資料有95%到97%行被讀取到。
Q1的查詢語句如下：
select
l_returnflag, //返回標誌
l_linestatus,
sum(l_quantity) as sum_qty, //總的數量
sum(l_extendedprice) as sum_base_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的查詢語句如下：
select
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 = ( //子查詢
select
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的查詢語句如下：
select
l_orderkey,
sum(l_extendedprice*(1-l_discount)) as revenue, //潛在的收入，聚集操作
o_orderdate,
o_shippriority
from
customer, orders, lineitem //三表連線
where
c_mktsegment = '[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的查詢語句如下：
select
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 ( //子查詢
select
*
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的查詢語句如下：
select
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的查詢語句如下：
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]中隨機選擇7.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;8.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，與上述查詢語句格式上基本相同，主要是目標列使用了不同的表達方式，在此不再贅述。
9.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;10.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;11.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;12.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）。
13.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;14.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，與上述查詢語句格式上基本相同，主要是目標列使用了不同的表達方式，在此不再贅述。
15.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;16.Q16: 零件/供貨商關係查詢
Q16語句查詢獲得能夠以指定的貢獻條件供應零件的供貨商數量。可用於決定在訂單量大，任務緊急時，是否有充足的供貨商。
Q16語句的特點是：帶有分組、排序、聚集、去重、NOT IN子查詢操作並存的兩表連線操作。
Q16的查詢語句如下：
select
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是兩個字母，代表兩個數值，相互獨立，取值在1到5之間
and p_type not like '[TYPE]%' //消費者不感興趣的型別和尺寸
and p_size in ([SIZE1], [SIZE2], [SIZE3], [SIZE4], [SIZE5], [SIZE6], [SIZE7], [SIZE8])
//TYPEX是在1到50之間任意選擇的一組八個不同的值
and ps_suppkey not in ( //NOT IN子查詢，消費者排除某些供貨商
select
s_suppkey
from
supplier
where
s_comment like '%Customer%Complaints%'
)
group by //分組操作
p_brand,
p_type,
p_size
order by //排序操作
supplier_cnt desc, //按數量降序排列，按品牌、種類、尺寸升序排列
p_brand,
p_type,
p_size;17.Q17: 小訂單收入查詢
Q17語句查詢獲得比平均供貨量的百分之二十還低的小批次訂單。對於指定品牌和指定包裝型別的零件，決定在一個七年資料庫的所有訂單中這些訂單零件的平均專案數量（過去的和未決的）。如果這些零件中少於平均數20％的訂單不再被接納，那平均一年會損失多少呢？所以此查詢可用於計算出如果沒有沒有小量訂單，平均年收入將損失多少（因為大量商品的貨運，將降低管理費用）。
Q17語句的特點是：帶有聚集、聚集子查詢操作並存的兩表連線操作。
Q17的查詢語句如下：
select
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是兩個字母，代表兩個數值，相互獨立，取值在1到5之間 */
and p_container = '[CONTAINER]' //指定包裝型別。在TPC-H標準指定的範圍內隨機選擇
and l_quantity < ( //聚集子查詢
select
0.2 * avg(l_quantity)
from
lineitem
where
l_partkey = p_partkey
);18.Q18: 大訂單顧客查詢
Q18語句查詢獲得比指定供貨量大的供貨商資訊。可用於決定在訂單量大，任務緊急時，驗證否有充足的供貨商。
Q18語句的特點是：帶有分組、排序、聚集、IN子查詢操作並存的三表連線操作。查詢語句沒有從語法上限制返回多少條元組，但是TPC-H標準規定，查詢結果只返回前100行（通常依賴於應用程式實現）。
Q18的查詢語句如下：
select
c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, //基本資訊
sum(l_quantity) //訂貨總數
from
customer, orders, lineitem
where
o_orderkey in ( //帶有分組操作的IN子查詢
select
l_orderkey
from
lineitem
group by
l_orderkey having
sum(l_quantity) > [QUANTITY] // QUANTITY是位於312到315之間的任意值
)
and c_custkey = o_custkey
and o_orderkey = l_orderkey
group by
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice
order by
o_totalprice desc,
o_orderdate;19.Q19: 折扣收入查詢
Q19語句查詢得到對一些空運或人工運輸零件三個不同種類的所有訂單的總折扣收入。零件的選擇考慮特定品牌、包裝和尺寸範圍。本查詢是用資料探勘工具產生格式化程式碼的一個例子。
Q19語句的特點是：帶有分組、排序、聚集、IN子查詢操作並存的三表連線操作。
Q19的查詢語句如下：
select
sum(l_extendedprice * (1 - l_discount) ) as revenue
from
lineitem, part
where
(
p_partkey = l_partkey
and p_brand = ‘[BRAND1]’ /*特定品牌。BRAND1、BRAND2、BRAND3＝‘Brand＃MN’，M和N是兩個字母，代表兩個數值，相互獨立，取值在1到5之間 */
and p_container in ( ‘SM CASE’, ‘SM BOX’, ‘SM PACK’, ‘SM PKG’) //包裝範圍
and l_quantity >= [QUANTITY1] and l_quantity <= [QUANTITY1] + 10 /* QUANTITY1 是1到10之間的任意取值 */
and p_size between 1 and 5 //尺寸範圍
and l_shipmode in (‘AIR’, ‘AIR REG’) //運輸模式，如下帶有陰影的粗體表示的條件是相同的，存在條件化簡的可能
and l_shipinstruct = ‘DELIVER IN PERSON’
)
or
(
p_partkey = l_partkey
and p_brand = ‘[BRAND2]’
and p_container in (‘MED BAG’, ‘MED BOX’, ‘MED PKG’, ‘MED PACK’)
and l_quantity >= [QUANTITY2] and l_quantity <= [QUANTITY2] + 10 /* QUANTITY2 是10到20之間的任意取值 */
and p_size between 1 and 10
and l_shipmode in (‘AIR’, ‘AIR REG’)
and l_shipinstruct = ‘DELIVER IN PERSON’
)
or
(
p_partkey = l_partkey
and p_brand = ‘[BRAND3]’
and p_container in ( ‘LG CASE’, ‘LG BOX’, ‘LG PACK’, ‘LG PKG’)
and l_quantity >= [QUANTITY3] and l_quantity <= [QUANTITY3] + 10 /* QUANTITY3 是20到30之間的任意取值 */
and p_size between 1 and 15
and l_shipmode in (‘AIR’, ‘AIR REG’)
and l_shipinstruct = ‘DELIVER IN PERSON’
);20.Q20: 供貨商競爭力查詢
Q20語句查詢確定在某一年內，找出指定國家的能對某一零件商品提供更有競爭力價格的供貨貨。所謂更有競爭力的供貨商，是指那些零件有過剩的供貨商，超過供或商在某一年中貨運給定國的某一零件的50％則為過剩。
Q20語句的特點是：帶有排序、聚集、IN子查詢、普通子查詢操作並存的兩表連線操作。
Q20的查詢語句如下：
select
s_name, s_address
from
supplier, nation
where
s_suppkey in ( //第一層的IN子查詢
select
ps_suppkey
from
partsupp
where
ps_partkey in ( //第二層巢狀的IN子查詢
select
p_partkey
from
part
where
p_name like '[COLOR]%' //COLOR為產生P_NAME的值的列表中的任意值
)
and ps_availqty > (//第二層巢狀的子查詢
select
0.5 * sum(l_quantity) //聚集子查詢
from
lineitem
where
l_partkey = ps_partkey
and l_suppkey = ps_suppkey
and l_shipdate >= date('[DATE]’) //DATE為在1993年至1997年的任一年的一月一號
and l_shipdate < date('[DATE]’) + interval ‘1’ year //1年內
)
)
and s_nationkey = n_nationkey
and n_name = '[NATION]' //TPC-H標準定義的任意值
order by
s_name;21.Q21: 不能按時交貨供貨商查詢
Q21語句查詢獲得不能及時交貨的供貨商。
Q21語句的特點是：帶有分組、排序、聚集、EXISTS子查詢、NOT EXISTS子查詢操作並存的四表連線操作。查詢語句沒有從語法上限制返回多少條元組，但是TPC-H標準規定，查詢結果只返回前100行（通常依賴於應用程式實現）。
Q21的查詢語句如下：
select
s_name, count(*) as numwait
from
supplier, lineitem l1, orders, nation
where
s_suppkey = l1.l_suppkey
and o_orderkey = l1.l_orderkey
and o_orderstatus = 'F'
and l1.l_receiptdate > l1.l_commitdate
and exists ( //EXISTS子查詢
select
*
from
lineitem l2
where
l2.l_orderkey = l1.l_orderkey
and l2.l_suppkey <> l1.l_suppkey
)
and not exists ( //NOT EXISTS子查詢
select
*
from
lineitem l3
where
l3.l_orderkey = l1.l_orderkey
and l3.l_suppkey <> l1.l_suppkey
and l3.l_receiptdate > l3.l_commitdate
)
and s_nationkey = n_nationkey
and n_name = '[NATION]' //TPC-H標準定義的任意值
group by
s_name
order by
numwait desc,
s_name;22.Q22: 全球銷售機會查詢
Q22語句查詢獲得消費者可能購買的地理分佈。本查詢計算在指定的國家，比平均水平更持肯定態度但還沒下七年訂單的消費者數量。能反應出普通消費者的的態度，即購買意向。
Q22語句的特點是：帶有分組、排序、聚集、EXISTS子查詢、NOT EXISTS子查詢操作並存的四表連線操作。
Q22的查詢語句如下：
select
cntrycode,
count(*) as numcust,
sum(c_acctbal) as totacctbal
from ( //第一層子查詢
select
substring(c_phone from 1 for 2) as cntrycode,
c_acctbal
from
customer
where
// I1…I7是在TPC-H中定義國家程式碼的可能值中不重複的任意值
substring(c_phone from 1 for 2) in ('[I1]','[I2]’,'[I3]','[I4]','[I5]','[I6]','[I7]')
and c_acctbal > (//第二層聚集子查詢
select
avg(c_acctbal)
from
customer
where
c_acctbal > 0.00
and substr (c_phone from 1 for 2)
in ('[I1]','[I2]','[I3]','[I4]','[I5]','[I6]','[I7]')
)
and not exists (//第二層NOT EXISTS子查詢
select
*
from
orders
where
o_custkey = c_custkey
)
) as custsale
group by
cntrycode
order by
cntrycode;https://wiki.postgresql.org/wiki/TPC-H
