TPC-H的标准查询可以从TCP-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;