TPC-H的标准查询可以从TPC-H（Transaction ProcessingPerformance Council）官网或第三方github获取，本文提供22个查询示例供您测试使用。

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'120'day
group by 
    l_returnflag, l_linestatus
order by 
    l_returnflag, l_linestatus;

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=48
    and p_type like'%STEEL'
    and s_nationkey=n_nationkey
    and n_regionkey=r_regionkey
    and r_name='EUROPE'
    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='EUROPE'
)
order by
    s_acctbal desc,
    n_name,
    s_name,
    p_partkey
limit 100;

Q3

select
    l_orderkey,
    sum(l_extendedprice*(1-l_discount)) as revenue,
    o_orderdate,
    o_shippriority
from
    customer,
    orders,
    lineitem
where
    c_mktsegment='MACHINERY'
    and c_custkey=o_custkey
    and l_orderkey=o_orderkey
    and o_orderdate<date'1995-03-23'
    and l_shipdate>date'1995-03-23'
group by
    l_orderkey,
    o_orderdate,
    o_shippriority
order by
    revenue desc,
    o_orderdate
limit 10;

Q4

select
    o_orderpriority,
    count(*)asorder_count
from
    orders
where
    o_orderdate>=date'1996-07-01'
    and o_orderdate<date'1996-07-01'+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;

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='EUROPE'
    and o_orderdate>=date'1996-01-01'
    and o_orderdate<date'1996-01-01'+interval'1'year
group by
    n_name
order by
    revenue desc;

Q6

select
    sum(l_extendedprice*l_discount) as revenue
from
    lineitem
where
l_shipdate>=date'1996-01-01'
and l_shipdate<date'1996-01-01'+interval'1'year
and l_discount between 0.02-0.01 and 0.02+0.01
and l_quantity<24;

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(
        (n1.n_name='CANADA' and n2.n_name='BRAZIL')
        or(n1.n_name='BRAZIL' and n2.n_name='CANADA')
        )
        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

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(
                (n1.n_name='CANADA' and n2.n_name='BRAZIL')
                or(n1.n_name='BRAZIL' and n2.n_name='CANADA')
            )
            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;

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'%maroon%'
)as profit
group by
    nation,
    o_year
order by
    nation,
    o_year desc;

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'1993-02-01'
    and o_orderdate<date'1993-02-01'+interval'3'month
    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
    limit 20;

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='EGYPT'
group by
    ps_partkey having
        sum(ps_supplycost*ps_availqty)>(
            select
                sum(ps_supplycost*ps_availqty)*0.0001000000
        from
            partsupp,
            supplier,
            nation
where
    ps_suppkey=s_suppkey
    and s_nationkey=n_nationkey
    and n_name='EGYPT'
            )
    order by
        value desc;

Q12

select
    l_shipmode,
    sum(case
        when o_orderpriority='1-URGENT'
            or o_orderpriority='2-HIGH'
            then 1
        else 0
    end)as high_line_count,
    sum(case
        when o_orderpriority<>'1-URGENT'
            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('FOB','AIR')
    and l_commitdate<l_receiptdate
    and l_shipdate<l_commitdate
    and l_receiptdate>=date'1997-01-01'
    and l_receiptdate<date'1997-01-01'+interval'1'year
group by
    l_shipmode
order by
    l_shipmode;

Q13

select
    c_count,
    count(*) as custdist
from
    (
        select
            c_custkey,
            count(o_orderkey) as c_count
from
    customer left outer join orders on
        c_custkey=o_custkey
        and o_comment not like'%special%deposits%'
group by
    c_custkey
)c_orders
group by
    c_count
order by
    custdist desc,
    c_count desc;

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'1997-06-01'
    and l_shipdate<date'1997-06-01'+interval'1'month;

Q15

select
    s_suppkey,
    s_name,
    s_address,
    s_phone,
    total_revenue
from
    supplier,
    revenue0
where
    s_suppkey=supplier_no
    and total_revenue=(
        select
            max(total_revenue)
from
    revenue0
)
order by
    s_suppkey;

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#45'
    and p_type not like'SMALLANODIZED%'
    and p_size in(47,15,37,30,46,16,18,6)
    and ps_suppkey 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;

Q17

select
sum(l_extendedprice)/7.0asavg_yearly
from
    lineitem,
    part
where
    p_partkey=l_partkey
    and p_brand='Brand#51'
    and p_container='WRAPPACK'
    and l_quantity<(
        select
            0.2*avg(l_quantity)
from
    lineitem
where
    l_partkey=p_partkey
);

Q18

select
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice,
    sum(l_quantity)
from
    customer,
    orders,
    lineitem
where
    o_orderkey in(
        select
            l_orderkey
        from
            lineitem
        group by
            l_orderkey having
                sum(l_quantity)>312
    )
    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
limit 100;

Q19

select
    sum(l_extendedprice*(1-l_discount)) as revenue
from
    lineitem,
    part
where
(
    p_partkey=l_partkey
    and p_brand='Brand#52'
    and p_container in('SMCASE','SMBOX','SMPACK','SMPKG')
    and l_quantity>=3 and l_quantity<=3+10
    and p_size between 1 and 5
    and l_shipmode in('AIR','AIRREG')
    and l_shipinstruct='DELIVERINPERSON'
)
or
(
    p_partkey=l_partkey
    and p_brand='Brand#43'
    and p_container in('MEDBAG','MEDBOX','MEDPKG','MEDPACK')
    and l_quantity>=12 and l_quantity<=12+10
    and p_size between 1 and 10
    and l_shipmode in('AIR','AIRREG')
    and l_shipinstruct='DELIVERINPERSON'
)
or
(
    p_partkey=l_partkey
    and p_brand='Brand#52'
    and p_container in('LGCASE','LGBOX','LGPACK','LGPKG')
    and l_quantity>=21 and l_quantity<=21+10
    and p_size between 1 and 15
    and l_shipmode in('AIR','AIRREG')
    and l_shipinstruct='DELIVERINPERSON'
);

Q20

select
    s_name,
    s_address
from
    supplier,
    nation
where
    s_suppkey in(
        select
            ps_suppkey
    from
        partsupp
    where
        ps_partkey in(
        select
            p_partkey
        from
            part
        where
            p_name like'drab%'
        )
        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'1996-01-01'
                and l_shipdate<date'1996-01-01'+interval'1'year
        )
)
and s_nationkey=n_nationkey
and n_name='KENYA'
order by
    s_name
;

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(
        select
            *
        from
            lineitem l2
        where
            l2.l_orderkey=l1.l_orderkey
            and l2.l_suppkey<>l1.l_suppkey
)
and 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='PERU'
group by
    s_name
order by
    numwait desc,
    s_name
limit 100;

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
    substring(c_phone from 1 for 2)in
    ('24','32','17','18','12','14','22')
    and c_acctbal>(
        select
            avg(c_acctbal)
        from
            customer
        where
            c_acctbal>0.00
            and substring(c_phone from 1 for 2)in
            ('24','32','17','18','12','14','22')
)
and not exists(
    select
        *
    from
        orders
    where
        o_custkey=c_custkey
    )
)as custsale
group by
    cntrycode
order by
    cntrycode;