全部产品
云市场

查询数据

更新时间:2019-12-04 10:46:05

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

Q1

  1. select
  2. l_returnflag,
  3. l_linestatus,
  4. sum(l_quantity) as sum_qty,
  5. sum(l_extendedprice) as sum_base_price,
  6. sum(l_extendedprice*(1-l_discount)) as sum_disc_price,
  7. sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,
  8. avg(l_quantity) as avg_qty,
  9. avg(l_extendedprice) as avg_price,
  10. avg(l_discount) as avg_disc,
  11. count(*) as count_order
  12. from
  13. lineitem
  14. where
  15. l_shipdate<=date'1998-12-01'-interval'120'day
  16. group by
  17. l_returnflag, l_linestatus
  18. order by
  19. l_returnflag, l_linestatus;

Q2

  1. select
  2. s_acctbal,
  3. s_name, n_name,
  4. p_partkey,
  5. p_mfgr,
  6. s_address,
  7. s_phone,
  8. s_comment
  9. from
  10. part,
  11. supplier,
  12. partsupp,
  13. nation,
  14. region
  15. where
  16. p_partkey=ps_partkey
  17. and s_suppkey=ps_suppkey
  18. and p_size=48
  19. and p_type like'%STEEL'
  20. and s_nationkey=n_nationkey
  21. and n_regionkey=r_regionkey
  22. and r_name='EUROPE'
  23. and ps_supplycost=(
  24. select
  25. min(ps_supplycost)
  26. from
  27. partsupp,
  28. supplier,
  29. nation,
  30. region
  31. where
  32. p_partkey=ps_partkey
  33. and s_suppkey=ps_suppkey
  34. and s_nationkey=n_nationkey
  35. and n_regionkey=r_regionkey
  36. and r_name='EUROPE'
  37. )
  38. order by
  39. s_acctbal desc,
  40. n_name,
  41. s_name,
  42. p_partkey
  43. limit 100;

Q3

  1. select
  2. l_orderkey,
  3. sum(l_extendedprice*(1-l_discount)) as revenue,
  4. o_orderdate,
  5. o_shippriority
  6. from
  7. customer,
  8. orders,
  9. lineitem
  10. where
  11. c_mktsegment='MACHINERY'
  12. and c_custkey=o_custkey
  13. and l_orderkey=o_orderkey
  14. and o_orderdate<date'1995-03-23'
  15. and l_shipdate>date'1995-03-23'
  16. group by
  17. l_orderkey,
  18. o_orderdate,
  19. o_shippriority
  20. order by
  21. revenue desc,
  22. o_orderdate
  23. limit 10;

Q4

  1. select
  2. o_orderpriority,
  3. count(*)asorder_count
  4. from
  5. orders
  6. where
  7. o_orderdate>=date'1996-07-01'
  8. and o_orderdate<date'1996-07-01'+interval'3'month
  9. and exists(
  10. select
  11. *
  12. from
  13. lineitem
  14. where
  15. l_orderkey=o_orderkey
  16. and l_commitdate<l_receiptdate
  17. )
  18. group by
  19. o_orderpriority
  20. order by
  21. o_orderpriority;

Q5

  1. select
  2. n_name,
  3. sum(l_extendedprice*(1-l_discount)) as revenue
  4. from
  5. customer,
  6. orders,
  7. lineitem,
  8. supplier,
  9. nation,
  10. region
  11. where
  12. c_custkey=o_custkey
  13. and l_orderkey=o_orderkey
  14. and l_suppkey=s_suppkey
  15. and c_nationkey=s_nationkey
  16. and s_nationkey=n_nationkey
  17. and n_regionkey=r_regionkey
  18. and r_name='EUROPE'
  19. and o_orderdate>=date'1996-01-01'
  20. and o_orderdate<date'1996-01-01'+interval'1'year
  21. group by
  22. n_name
  23. order by
  24. revenue desc;

Q6

  1. select
  2. sum(l_extendedprice*l_discount) as revenue
  3. from
  4. lineitem
  5. where
  6. l_shipdate>=date'1996-01-01'
  7. and l_shipdate<date'1996-01-01'+interval'1'year
  8. and l_discount between 0.02-0.01 and 0.02+0.01
  9. and l_quantity<24;

Q7

  1. select
  2. supp_nation,
  3. cust_nation,
  4. l_year,
  5. sum(volume) as revenue
  6. from
  7. (
  8. select
  9. n1.n_name as supp_nation,
  10. n2.n_name as cust_nation,
  11. extract(year from l_shipdate) as l_year,
  12. l_extendedprice*(1-l_discount) as volume
  13. from
  14. supplier,
  15. lineitem,
  16. orders,
  17. customer,
  18. nation n1,
  19. nation n2
  20. where
  21. s_suppkey=l_suppkey
  22. and o_orderkey=l_orderkey
  23. and c_custkey=o_custkey
  24. and s_nationkey=n1.n_nationkey
  25. and c_nationkey=n2.n_nationkey
  26. and(
  27. (n1.n_name='CANADA' and n2.n_name='BRAZIL')
  28. or(n1.n_name='BRAZIL' and n2.n_name='CANADA')
  29. )
  30. and l_shipdate between date'1995-01-01' and date'1996-12-31'
  31. )as shipping
  32. group by
  33. supp_nation,
  34. cust_nation,
  35. l_year
  36. order by
  37. supp_nation,
  38. cust_nation,
  39. l_year;

Q8

  1. select
  2. supp_nation,
  3. cust_nation,
  4. l_year,
  5. sum(volume) as revenue
  6. from
  7. (
  8. select
  9. n1.n_name as supp_nation,
  10. n2.n_name as cust_nation,
  11. extract(year from l_shipdate) as l_year,
  12. l_extendedprice*(1-l_discount) as volume
  13. from
  14. supplier,
  15. lineitem,
  16. orders,
  17. customer,
  18. nation n1,
  19. nation n2
  20. where
  21. s_suppkey=l_suppkey
  22. and o_orderkey=l_orderkey
  23. and c_custkey=o_custkey
  24. and s_nationkey=n1.n_nationkey
  25. and c_nationkey=n2.n_nationkey
  26. and(
  27. (n1.n_name='CANADA' and n2.n_name='BRAZIL')
  28. or(n1.n_name='BRAZIL' and n2.n_name='CANADA')
  29. )
  30. and l_shipdate between date'1995-01-01'and date'1996-12-31'
  31. ) as shipping
  32. group by
  33. supp_nation,
  34. cust_nation,
  35. l_year
  36. order by
  37. supp_nation,
  38. cust_nation,
  39. l_year;

Q9

  1. select
  2. nation,
  3. o_year,
  4. sum(amount) as sum_profit
  5. from
  6. (
  7. select
  8. n_name as nation,
  9. extract(year from o_orderdate) as o_year,
  10. l_extendedprice*(1-l_discount)-ps_supplycost*l_quantity as amount
  11. from
  12. part,
  13. supplier,
  14. lineitem,
  15. partsupp,
  16. orders,
  17. nation
  18. where
  19. s_suppkey=l_suppkey
  20. and ps_suppkey=l_suppkey
  21. and ps_partkey=l_partkey
  22. and p_partkey=l_partkey
  23. and o_orderkey=l_orderkey
  24. and s_nationkey=n_nationkey
  25. and p_name like'%maroon%'
  26. )as profit
  27. group by
  28. nation,
  29. o_year
  30. order by
  31. nation,
  32. o_year desc;

Q10

  1. select
  2. c_custkey,
  3. c_name,
  4. sum(l_extendedprice*(1-l_discount)) as revenue,
  5. c_acctbal,
  6. n_name,
  7. c_address,
  8. c_phone,
  9. c_comment
  10. from
  11. customer,
  12. orders,
  13. lineitem,
  14. nation
  15. where
  16. c_custkey=o_custkey
  17. and l_orderkey=o_orderkey
  18. and o_orderdate>=date'1993-02-01'
  19. and o_orderdate<date'1993-02-01'+interval'3'month
  20. and l_returnflag='R'
  21. and c_nationkey=n_nationkey
  22. group by
  23. c_custkey,
  24. c_name,
  25. c_acctbal,
  26. c_phone,
  27. n_name,
  28. c_address,
  29. c_comment
  30. order by
  31. revenue desc
  32. limit 20;

Q11

  1. select
  2. ps_partkey,
  3. sum(ps_supplycost*ps_availqty) as value
  4. from
  5. partsupp,
  6. supplier,
  7. nation
  8. where
  9. ps_suppkey=s_suppkey
  10. and s_nationkey=n_nationkey
  11. and n_name='EGYPT'
  12. group by
  13. ps_partkey having
  14. sum(ps_supplycost*ps_availqty)>(
  15. select
  16. sum(ps_supplycost*ps_availqty)*0.0001000000
  17. from
  18. partsupp,
  19. supplier,
  20. nation
  21. where
  22. ps_suppkey=s_suppkey
  23. and s_nationkey=n_nationkey
  24. and n_name='EGYPT'
  25. )
  26. order by
  27. value desc;

Q12

  1. select
  2. l_shipmode,
  3. sum(case
  4. when o_orderpriority='1-URGENT'
  5. or o_orderpriority='2-HIGH'
  6. then 1
  7. else 0
  8. end)as high_line_count,
  9. sum(case
  10. when o_orderpriority<>'1-URGENT'
  11. and o_orderpriority<>'2-HIGH'
  12. then 1
  13. else 0
  14. end)as low_line_count
  15. from
  16. orders,
  17. lineitem
  18. where
  19. o_orderkey=l_orderkey
  20. and l_shipmode in('FOB','AIR')
  21. and l_commitdate<l_receiptdate
  22. and l_shipdate<l_commitdate
  23. and l_receiptdate>=date'1997-01-01'
  24. and l_receiptdate<date'1997-01-01'+interval'1'year
  25. group by
  26. l_shipmode
  27. order by
  28. l_shipmode;

Q13

  1. select
  2. c_count,
  3. count(*) as custdist
  4. from
  5. (
  6. select
  7. c_custkey,
  8. count(o_orderkey) as c_count
  9. from
  10. customer left outer join orders on
  11. c_custkey=o_custkey
  12. and o_comment not like'%special%deposits%'
  13. group by
  14. c_custkey
  15. )c_orders
  16. group by
  17. c_count
  18. order by
  19. custdist desc,
  20. c_count desc;

Q14

  1. select
  2. 100.00*sum(case
  3. when p_type like'PROMO%'
  4. then l_extendedprice*(1-l_discount)
  5. else 0
  6. end)/sum(l_extendedprice*(1-l_discount)) as promo_revenue
  7. from
  8. lineitem,
  9. part
  10. where
  11. l_partkey=p_partkey
  12. and l_shipdate>=date'1997-06-01'
  13. and l_shipdate<date'1997-06-01'+interval'1'month;

Q15

  1. select
  2. s_suppkey,
  3. s_name,
  4. s_address,
  5. s_phone,
  6. total_revenue
  7. from
  8. supplier,
  9. revenue0
  10. where
  11. s_suppkey=supplier_no
  12. and total_revenue=(
  13. select
  14. max(total_revenue)
  15. from
  16. revenue0
  17. )
  18. order by
  19. s_suppkey;

Q16

  1. select
  2. p_brand,
  3. p_type,
  4. p_size,
  5. count(distinct ps_suppkey) as supplier_cnt
  6. from
  7. partsupp,
  8. part
  9. where
  10. p_partkey=ps_partkey
  11. and p_brand<>'Brand#45'
  12. and p_type not like'SMALLANODIZED%'
  13. and p_size in(47,15,37,30,46,16,18,6)
  14. and ps_suppkey not in(
  15. select
  16. s_suppkey
  17. from
  18. supplier
  19. where
  20. s_comment like'%Customer%Complaints%'
  21. )
  22. group by
  23. p_brand,
  24. p_type,
  25. p_size
  26. order by
  27. supplier_cnt desc,
  28. p_brand,
  29. p_type,
  30. p_size;

Q17

  1. select
  2. sum(l_extendedprice)/7.0asavg_yearly
  3. from
  4. lineitem,
  5. part
  6. where
  7. p_partkey=l_partkey
  8. and p_brand='Brand#51'
  9. and p_container='WRAPPACK'
  10. and l_quantity<(
  11. select
  12. 0.2*avg(l_quantity)
  13. from
  14. lineitem
  15. where
  16. l_partkey=p_partkey
  17. );

Q18

  1. select
  2. c_name,
  3. c_custkey,
  4. o_orderkey,
  5. o_orderdate,
  6. o_totalprice,
  7. sum(l_quantity)
  8. from
  9. customer,
  10. orders,
  11. lineitem
  12. where
  13. o_orderkey in(
  14. select
  15. l_orderkey
  16. from
  17. lineitem
  18. group by
  19. l_orderkey having
  20. sum(l_quantity)>312
  21. )
  22. and c_custkey=o_custkey
  23. and o_orderkey=l_orderkey
  24. group by
  25. c_name,
  26. c_custkey,
  27. o_orderkey,
  28. o_orderdate,
  29. o_totalprice
  30. order by
  31. o_totalprice desc,
  32. o_orderdate
  33. limit 100;

Q19

  1. select
  2. sum(l_extendedprice*(1-l_discount)) as revenue
  3. from
  4. lineitem,
  5. part
  6. where
  7. (
  8. p_partkey=l_partkey
  9. and p_brand='Brand#52'
  10. and p_container in('SMCASE','SMBOX','SMPACK','SMPKG')
  11. and l_quantity>=3 and l_quantity<=3+10
  12. and p_size between 1 and 5
  13. and l_shipmode in('AIR','AIRREG')
  14. and l_shipinstruct='DELIVERINPERSON'
  15. )
  16. or
  17. (
  18. p_partkey=l_partkey
  19. and p_brand='Brand#43'
  20. and p_container in('MEDBAG','MEDBOX','MEDPKG','MEDPACK')
  21. and l_quantity>=12 and l_quantity<=12+10
  22. and p_size between 1 and 10
  23. and l_shipmode in('AIR','AIRREG')
  24. and l_shipinstruct='DELIVERINPERSON'
  25. )
  26. or
  27. (
  28. p_partkey=l_partkey
  29. and p_brand='Brand#52'
  30. and p_container in('LGCASE','LGBOX','LGPACK','LGPKG')
  31. and l_quantity>=21 and l_quantity<=21+10
  32. and p_size between 1 and 15
  33. and l_shipmode in('AIR','AIRREG')
  34. and l_shipinstruct='DELIVERINPERSON'
  35. );

Q20

  1. select
  2. s_name,
  3. s_address
  4. from
  5. supplier,
  6. nation
  7. where
  8. s_suppkey in(
  9. select
  10. ps_suppkey
  11. from
  12. partsupp
  13. where
  14. ps_partkey in(
  15. select
  16. p_partkey
  17. from
  18. part
  19. where
  20. p_name like'drab%'
  21. )
  22. and ps_availqty>(
  23. select
  24. 0.5*sum(l_quantity)
  25. from
  26. lineitem
  27. where
  28. l_partkey=ps_partkey
  29. and l_suppkey=ps_suppkey
  30. and l_shipdate>=date'1996-01-01'
  31. and l_shipdate<date'1996-01-01'+interval'1'year
  32. )
  33. )
  34. and s_nationkey=n_nationkey
  35. and n_name='KENYA'
  36. order by
  37. s_name
  38. ;

Q21

  1. select
  2. s_name,
  3. count(*) as numwait
  4. from
  5. supplier,
  6. lineitem l1,
  7. orders,
  8. nation
  9. where
  10. s_suppkey=l1.l_suppkey
  11. and o_orderkey=l1.l_orderkey
  12. and o_orderstatus='F'
  13. and l1.l_receiptdate>l1.l_commitdate
  14. and exists(
  15. select
  16. *
  17. from
  18. lineitem l2
  19. where
  20. l2.l_orderkey=l1.l_orderkey
  21. and l2.l_suppkey<>l1.l_suppkey
  22. )
  23. and not exists(
  24. select
  25. *
  26. from
  27. lineitem l3
  28. where
  29. l3.l_orderkey=l1.l_orderkey
  30. and l3.l_suppkey<>l1.l_suppkey
  31. and l3.l_receiptdate>l3.l_commitdate
  32. )
  33. and s_nationkey=n_nationkey
  34. and n_name='PERU'
  35. group by
  36. s_name
  37. order by
  38. numwait desc,
  39. s_name
  40. limit 100;

Q22

  1. select
  2. cntrycode,
  3. count(*) as numcust,
  4. sum(c_acctbal) as totacctbal
  5. from
  6. (
  7. select
  8. substring(c_phone from 1 for 2) as cntrycode,
  9. c_acctbal
  10. from
  11. customer
  12. where
  13. substring(c_phone from 1 for 2)in
  14. ('24','32','17','18','12','14','22')
  15. and c_acctbal>(
  16. select
  17. avg(c_acctbal)
  18. from
  19. customer
  20. where
  21. c_acctbal>0.00
  22. and substring(c_phone from 1 for 2)in
  23. ('24','32','17','18','12','14','22')
  24. )
  25. and not exists(
  26. select
  27. *
  28. from
  29. orders
  30. where
  31. o_custkey=c_custkey
  32. )
  33. )as custsale
  34. group by
  35. cntrycode
  36. order by
  37. cntrycode;