TPC-H（二）：22个SQL语句说明（基于TPC-H2.17.3版本）

Q01 统计查询
Q02 WHERE条件中，使用子查询(=)
Q03 多表关联统计查询，并统计(SUM)
Q04 WHERE条件中，使用子查询(EXISTS)，并统计(COUNT)
Q05 多表关联查询(=)，并统计(SUM)
Q06 条件(BETWEEN AND)查询，并统计(SUM)
Q07 带有FROM子查询，从结果集中统计(SUM)
Q08 带有FROM多表子查询，从结果集中的查询列上带有逻辑判断(WHEN THEN ELSE)的统计(SUM)
Q09 带有FROM多表子查询，查询表中使用函数(EXTRACT)，从结果集中统计(SUM)
Q10 多表条件查询(>=, <)，并统计(SUM)
Q11 在GROUP BY中使用比较条件(HAVING >)，比较值从子查询中查出
Q12 带有逻辑判断(WHEN AND/ WHEN OR)的查询，并统计(SUM)
Q13 带有FROM子查询，子查询中使用外联结
Q14 使用逻辑判断(WHEN ELSE)的查询
Q15 使用视图和表关联查询
Q16 在WHERE子句中使用子查询，使用IN/ NOT IN判断条件，并统计(COUNT)
Q17 在WHERE子句中使用子查询，使用<比较，使用了AVG函数
Q18 在WHERE子句中使用IN条件从子查询结果中比较
Q19 多条件比较查询
Q20 WHERE条件子查询(三层)
Q21 在WHERE条件中使用子查询，使用EXISTS和NOT EXISTS判断
Q22 在WHERE条件中使用判断子查询、IN、NOT EXISTS，并统计(SUM、COUNT)查询结果

价格摘要报告查询（Q1）

这个查询报告已经付款的、已运送的和返回的生意的数量。

商业问题

价格摘要报告查询提供了给定日期的运送的所有行的价格摘要报告，这个日期在数据库包含的最大的运送日期的60－120天以内。查询列出了扩展价格、打折的扩展价格、打折的扩展价格加税收、平均数量、平均扩展价格和平均折扣的总和。这些统计值根据RETURNFLAG 和LINESTATUS进行分组，并按照RETURNFLAG 和LINESTATUS的升序排列。每一组都给出所包含的行数。

查询函数定义

 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 '[DELTA]' day (3)
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;

替代参数

DELTA在区间[60, 120]内随机选择。

注释：1998-12-01是数据库中定义的最大的最可能的运送日期。这个查询将包括这个日期减去DELTA天得到的日期之前的所有被运送的行。目的是选择DELTA的值以便表中95％到97％的行被扫描。

---

最小代价供应者查询（Q2）

这个查询给出在给定的区域内，对于指定的零件，应该选择哪个供应者来订货。

商业问题

最小代价供应者查询给出这一答案：在给定的区域内，对于某一类型和大小的零件，哪个供应者能以最低的价格供应它。如果那一区域的几个供应者以同样的价格供应所要求的零件，查询列出帐户余额在前100位的供应者。对于每一个供应者，查询列出供应者的帐户余额、名字和国家，零件的号码和生产者，供应者的地址、电话号码和备注信息。

查询函数定义

返回前100行

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 ]
AND p_type LIKE '%[TYPE]'
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = '[REGION]'
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;

替代参数

1. SIZE在区间[1, 50]内随机选择；
2. TYPE列表Syllable 3 中随机选择；
3. REGION在R_NAME定义的值的列表中随机选择。

e.g:
1. SIZE = 15;
2. TYPE = BRASS;
3. REGION = EUROPE.

---

运送优先权查询（Q3）

查询给出收入在前10位的尚未运送的订单。

商业问题

运送优先权查询给出在指定的日期之前尚未运送的订单中具有最大收入的订单的优先权和潜在的收入，潜在的收入被定义为l_extendedprice * (1-l_discount)的和。订单按照收入的降序列出。如果尚未运送的订单超过10个，只有收入在前10位的订单被列出。

查询函数定义

 SELECT
    l_orderkey,
    SUM (
        l_extendedprice * (1 - l_discount)
    ) AS revenue,
    o_orderdate,
    o_shippriority
FROM
    customer,
    orders,
    lineitem
WHERE
    c_mktsegment = '[SEGMENT]'
AND c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND o_orderdate < DATE '[DATE]'
AND l_shipdate > DATE '[DATE]'
GROUP BY
    l_orderkey,
    o_orderdate,
    o_shippriority
ORDER BY
    revenue DESC,
    o_orderdate;

替代参数

1． SEGNENT在Segment定义的值的列表中随机选择；
2． 日期在[1995-03-01, 1995-03-31]中随机选择。

---

订单优先权检查查询（Q4）

这个查询可以让我们了解订单优先权系统工作得如何，并给出顾客满意度的一个估计值。

商业问题

订单优先权检查查询计算给定的某一年的某一季度的订单的数量，在每个订单中至少有一行由顾客在它的提交日期之后收到。查询按照优先权的升序列出每一优先权的订单的数量。

查询函数定义

 SELECT
    o_orderpriority,
    COUNT (*) AS order_count
FROM
    orders
WHERE
    o_orderdate >= DATE '[DATE]'
AND o_orderdate < DATE '[DATE]' + INTERVAL '3' MONTH
AND EXISTS (
    SELECT
        *
    FROM
        lineitem
    WHERE
        l_orderkey = o_orderkey
    AND l_commitdate < l_receiptdate
)
GROUP BY
    o_orderpriority
ORDER BY
    o_orderpriority;

替代参数

DATE是在1993年1月和1997年10月之间随机选择的一个月的第一天。

---

当地供应者数量查询（Q5）

这个查询列出通过本地供应者获得的收入大小。

商业问题

当地供应者数量查询给出某一国家的某一地区的收入，这些收入是通过那些定购零件的顾客和供应零件的供应者都在那个国家的交易获得的。运行这个查询是为了决定在给定的区域是否需要建立一个当地分配中心。这个查询仅仅考虑在给定的一年中定购的零件。这个查询按照收入的降序显示出国家和收入大小。某一国家的收入大小被定义为sum(l_extendedprice * (1 -l_discount))。

查询函数定义

select
    n_name,
    sum(l_extendedprice * (1 - l_discount)) as revenue