Hologres:Ikhtisar rencana pengujian

TPC-H

Berikut ini adalah kutipan dari spesifikasi TPC Benchmark™ H (TPC-H):

TPC-H adalah benchmark decision support yang terdiri dari serangkaian kueri ad-hoc berorientasi bisnis dan modifikasi data konkuren. Kueri serta data yang mengisi database dipilih karena relevansinya yang luas di berbagai industri. Benchmark ini merepresentasikan sistem decision support yang memproses volume data besar, menjalankan kueri dengan kompleksitas tinggi, serta memberikan jawaban atas pertanyaan bisnis kritis.

Unduh selengkapnya Spesifikasi TPC-H.

Dataset

TPC-H adalah benchmark decision support yang dikembangkan oleh Transaction Processing Performance Council (TPC) dan secara luas digunakan untuk mengevaluasi performa kueri analitis.

TPC-H memodelkan gudang data untuk sistem penjualan berdasarkan lingkungan produksi nyata. Dataset ini mencakup delapan tabel dengan volume data yang dapat diskalakan dari 1 GB hingga 3 TB. Benchmark ini mencakup 22 kueri, dengan metrik utama berupa waktu respons kueri sebagaimana didefinisikan dalam benchmark TPC-H.

Kasus penggunaan

Catatan

Untuk memastikan hasil pengujian yang konsisten, buat instans baru untuk setiap pengujian. Jangan gunakan instans yang telah diubah ukurannya.

Pengujian skenario kueri OLAP

Pengujian kueri titik key/value

Pengujian ini menggunakan database hologres_tpch dan tabel orders dari pengujian kueri OLAP.

1. Buat tabel

   Kueri titik key/value memerlukan tabel berorientasi baris. Hubungkan ke Hologres dengan client psql dan jalankan pernyataan berikut untuk membuat tabel orders_row.

   Catatan

   Untuk menghubungkan dengan client psql, ikuti langkah-langkah dalam Hubungkan ke Hologres untuk Pengembangan.

   DROP TABLE IF EXISTS public.orders_row;
   BEGIN;
   CREATE TABLE public.orders_row(
       O_ORDERKEY       BIGINT         NOT NULL PRIMARY KEY
       ,O_CUSTKEY       INT            NOT NULL
       ,O_ORDERSTATUS   TEXT           NOT NULL
       ,O_TOTALPRICE    DECIMAL(15,2)  NOT NULL
       ,O_ORDERDATE     TIMESTAMPTZ    NOT NULL
       ,O_ORDERPRIORITY TEXT           NOT NULL
       ,O_CLERK         TEXT           NOT NULL
       ,O_SHIPPRIORITY  INT            NOT NULL
       ,O_COMMENT       TEXT           NOT NULL
   );
   CALL SET_TABLE_PROPERTY('public.orders_row', 'orientation', 'row');
   CALL SET_TABLE_PROPERTY('public.orders_row', 'clustering_key', 'o_orderkey');
   CALL SET_TABLE_PROPERTY('public.orders_row', 'distribution_key', 'o_orderkey');
   COMMIT;

2. Impor data

   Gunakan pernyataan INSERT INTO berikut untuk mengimpor data dari tabel orders dalam dataset TPC-H ke tabel orders_row.

   Catatan

   Hologres V2.1.17 dan versi lebih baru mendukung Serverless Computing. Untuk skenario seperti impor data offline skala besar, pekerjaan ETL besar, dan kueri volume tinggi pada tabel eksternal, Anda dapat menggunakan Serverless Computing untuk menjalankan tugas-tugas tersebut. Fitur ini menggunakan sumber daya serverless tambahan alih-alih sumber daya instans Anda, sehingga meningkatkan stabilitas instans dan mengurangi kemungkinan error kehabisan memori (OOM). Anda tidak perlu menyediakan sumber daya komputasi tambahan untuk instans Anda, dan hanya dikenakan biaya untuk tugas yang dijalankan. Untuk informasi lebih lanjut tentang Serverless Computing, lihat Serverless Computing. Untuk petunjuk cara menggunakan Serverless Computing, lihat Gunakan Serverless Computing.

   -- (Opsional) Disarankan menggunakan Serverless Computing untuk impor offline massal dan pekerjaan ETL.
   SET hg_computing_resource = 'serverless';
   INSERT INTO public.orders_row SELECT * FROM public.orders;
   -- Setel ulang konfigurasi agar pernyataan SQL berikutnya tidak menggunakan sumber daya serverless.
   RESET hg_computing_resource;

3. Jalankan kueri

   1. Hasilkan pernyataan kueri.

      Kueri titik key/value terdiri dari dua jenis:

      Jenis	Pernyataan kueri	Deskripsi
      single-value point query	SELECT column_a ,column_b ,... ,column_x FROM table_x WHERE pk = value_x ;	Kueri ini melakukan filter berdasarkan satu nilai dalam klausa WHERE.
      multi-value point query	SELECT column_a ,column_b ,... ,column_x FROM table_x WHERE pk IN ( value_a, value_b,..., value_x ) ;	Kueri ini melakukan filter berdasarkan beberapa nilai dalam klausa WHERE.

      Jalankan skrip berikut untuk menghasilkan pernyataan SQL yang diperlukan.

      rm -rf kv_query
      mkdir kv_query
      cd kv_query
      echo "
      \set column_values random(1,99999999)
      select O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_TOTALPRICE,O_ORDERDATE,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT from public.orders_row WHERE o_orderkey =:column_values;
      " >> kv_query_single.sql
      echo "
      \set column_values1 random(1,99999999)
      \set column_values2 random(1,99999999)
      \set column_values3 random(1,99999999)
      \set column_values4 random(1,99999999)
      \set column_values5 random(1,99999999)
      \set column_values6 random(1,99999999)
      \set column_values7 random(1,99999999)
      \set column_values8 random(1,99999999)
      \set column_values9 random(1,99999999)
      select O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_TOTALPRICE,O_ORDERDATE,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT from public.orders_row WHERE o_orderkey in(:column_values1,:column_values2,:column_values3,:column_values4,:column_values5,:column_values6,:column_values7,:column_values8,:column_values9);
      " >> kv_query_in.sql

      Skrip ini menghasilkan dua file SQL:

      • kv_query_single.sql: Berisi SQL untuk kueri titik single-value.

      • kv_query_in.sql: Berisi SQL untuk kueri titik multi-value. Skrip ini menghasilkan pernyataan SQL yang melakukan filter berdasarkan sembilan nilai acak.

   2. Untuk mengumpulkan statistik kueri, gunakan tool pgbench. Instal dengan menjalankan perintah berikut.

      yum install postgresql-contrib -y

      Instal pgbench versi 13 atau lebih baru. Jika pgbench sudah terinstal, pastikan versinya 9.6 atau lebih baru. Untuk memeriksa versi:

      pgbench --version

   3. Jalankan pernyataan pengujian.

      Catatan

      Jalankan perintah berikut di direktori tempat Anda menghasilkan pernyataan kueri.

      • Untuk skenario kueri titik single-value, gunakan pgbench untuk menjalankan uji stres.

        PGUSER=<AccessKey ID> PGPASSWORD=<AccessKey Secret> PGDATABASE=<database> pgbench -h <endpoint> -p <port> -c <Client_Num> -T <Query_Seconds> -M prepared -n -f kv_query_single.sql

      • Untuk skenario kueri titik multi-value, gunakan pgbench untuk menjalankan uji stres.

        PGUSER=<AccessKey ID> PGPASSWORD=<AccessKey Secret> PGDATABASE=<database> pgbench -h <endpoint> -p <port> -c <Client_Num> -T <Query_Seconds> -M prepared -n -f kv_query_in.sql

      Deskripsi parameter:

      Parameter	Deskripsi
      AccessKey ID	ID AccessKey Akun Alibaba Cloud Anda. Buka halaman Manajemen AccessKey untuk mendapatkan ID AccessKey.
      AccessKey Secret	Rahasia AccessKey Akun Alibaba Cloud Anda. Buka halaman Manajemen AccessKey untuk mendapatkan Rahasia AccessKey.
      database	Nama database Hologres. Setelah membuat instans Hologres, sistem secara otomatis membuat database bernama postgres. Anda dapat menggunakan database postgres default, tetapi sumber dayanya terbatas. Untuk beban kerja produksi, Buat database.
      endpoint	Titik akhir instans Hologres. Buka halaman Detail Instans di Konsol Hologres dan dapatkan titik akhir dari bagian Network Information.
      port	Port instans Hologres. Buka halaman Instance Details di Konsol Hologres untuk mendapatkan port.
      Client_Num	Jumlah client konkuren. Karena pengujian ini mengukur performa kueri alih-alih konkurensi, atur parameter ini menjadi 1.
      Query_Seconds	Durasi total uji stres, dalam detik. Misalnya, Anda dapat mengatur parameter ini menjadi 300.

Pembaruan data

Skenario ini menguji performa mesin OLAP pada pembaruan full-row yang dipicu oleh konflik primary key.

• Hasilkan file kueri.

  echo "
  \set O_ORDERKEY random(1,99999999)
  INSERT INTO public.orders_row(o_orderkey,o_custkey,o_orderstatus,o_totalprice,o_orderdate,o_orderpriority,o_clerk,o_shippriority,o_comment) VALUES (:O_ORDERKEY,1,'demo',1.1,'2021-01-01','demo','demo',1,'demo') on conflict(o_orderkey) do update set (o_orderkey,o_custkey,o_orderstatus,o_totalprice,o_orderdate,o_orderpriority,o_clerk,o_shippriority,o_comment)= ROW(excluded.*);
  " > /root/insert_on_conflict.sql

• Insert dan update data. Parameter.

  PGUSER=<AccessKey_ID> PGPASSWORD=<AccessKey_Secret> PGDATABASE=<Database> pgbench -h <Endpoint> -p <Port> -c <Client_Num> -T <Query_Seconds> -M prepared -n -f /root/insert_on_conflict.sql

• Contoh hasil:

  transaction type: Custom query
  scaling factor: 1
  query mode: prepared
  number of clients: 249
  number of threads: 1
  duration: 60 s
  number of transactions actually processed: 1923038
  tps = 32005.850214 (including connections establishing)
  tps = 36403.145722 (excluding connections establishing)

Penulisan real-time Flink

Kasus penggunaan ini mengevaluasi performa penulisan data real-time.

• DDL Hologres

  Tabel Hologres yang digunakan dalam kasus ini memiliki 10 kolom, dan kolom key berfungsi sebagai primary key.

  DROP TABLE IF EXISTS flink_insert;
  BEGIN ;
  CREATE TABLE IF NOT EXISTS flink_insert(
    key INT PRIMARY KEY
    ,value1 TEXT
    ,value2 TEXT
    ,value3 TEXT
    ,value4 TEXT
    ,value5 TEXT
    ,value6 TEXT
    ,value7 TEXT
    ,value8 TEXT
    ,value9 TEXT
  );
  CALL SET_TABLE_PROPERTY('flink_insert', 'orientation', 'row');
  CALL SET_TABLE_PROPERTY('flink_insert', 'clustering_key', 'key');
  CALL SET_TABLE_PROPERTY('flink_insert', 'distribution_key', 'key');
  COMMIT;

• Skrip pekerjaan Flink

  Skrip ini menggunakan generator data acak bawaan dari Realtime Compute for Apache Flink untuk menulis data ke Hologres. Skrip ini memperbarui seluruh baris saat terjadi konflik primary key, dan setiap baris berukuran lebih dari 512 B.

  CREATE TEMPORARY TABLE flink_case_1_source (
      key INT,
      value1 VARCHAR,
      value2 VARCHAR,
      value3 VARCHAR,
      value4 VARCHAR,
      value5 VARCHAR,
      value6 VARCHAR,
      value7 VARCHAR,
      value8 VARCHAR,
      value9 VARCHAR
    )
  WITH (
      'connector' = 'datagen',
       -- optional options --
      'rows-per-second' = '1000000000',
      'fields.key.min'='1',
      'fields.key.max'='2147483647',
      'fields.value1.length' = '57',
      'fields.value2.length' = '57',
      'fields.value3.length' = '57',
      'fields.value4.length' = '57',
      'fields.value5.length' = '57',
      'fields.value6.length' = '57',
      'fields.value7.length' = '57',
      'fields.value8.length' = '57',
      'fields.value9.length' = '57'
    );
  -- Create a Hologres sink table.
  CREATE TEMPORARY TABLE flink_case_1_sink (
      key INT,
      value1 VARCHAR,
      value2 VARCHAR,
      value3 VARCHAR,
      value4 VARCHAR,
      value5 VARCHAR,
      value6 VARCHAR,
      value7 VARCHAR,
      value8 VARCHAR,
      value9 VARCHAR
    )
  WITH (
      'connector' = 'hologres',
      'dbname'='<yourDbname>',  -- The name of the Hologres database.
      'tablename'='<yourTablename>',  -- The name of the Hologres table that receives the data.
      'username'='<yourUsername>',  -- Your AccessKey ID.
      'password'='<yourPassword>',  -- Your AccessKey Secret.
      'endpoint'='<yourEndpoint>',  -- The VPC endpoint of your Hologres instance.
      'connectionSize' = '10',  -- The default value is 3.
      'jdbcWriteBatchSize' = '1024',  -- The default value is 256.
      'jdbcWriteBatchByteSize' = '2147483647',  -- The default value is 20971520.
      'mutatetype'='insertorreplace'  -- Inserts a new row or replaces an existing row on conflict.
    );
  -- Write data from the source to the sink.
  insert into flink_case_1_sink
  select key,
    value1,
    value2,
    value3,
    value4,
    value5,
    value6,
    value7,
    value8,
    value9
  from
    flink_case_1_source
  ;

  Tabel sink Hologres.

• Contoh hasil

  Di Konsol Hologres, buka halaman Monitoring Information. Pada grafik Query Latency (ms), metrik select_avg mencapai puncak sekitar 400 ms. Pada grafik Real-time Import RPS (records/second), metrik sdk berfluktuasi antara 200.000 hingga 400.000 records/second.

22 pernyataan kueri TPC-H

• 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(*) as order_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
          o_year,
          sum(case
                  when nation = 'BRAZIL' 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 = 'AMERICA'
                          and s_nationkey = n2.n_nationkey
                          and o_orderdate between date '1995-01-01' and date '1996-12-31'
                          and p_type = 'LARGE ANODIZED COPPER'
          ) as all_nations
  group by
          o_year
  order by
          o_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

  with revenue0(SUPPLIER_NO, TOTAL_REVENUE)  as
      (
      select
          l_suppkey,
          sum(l_extendedprice * (1 - l_discount))
      from
          lineitem
      where
          l_shipdate >= date '1995-02-01'
          and l_shipdate < date '1995-02-01' + interval '3' month
      group by
          l_suppkey
      )
  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 'SMALL ANODIZED%'
          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.0 as avg_yearly
  from
          lineitem,
          part
  where
          p_partkey = l_partkey
          and p_brand = 'Brand#51'
          and p_container = 'WRAP PACK'
          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 ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
                  and l_quantity >= 3 and l_quantity <= 3 + 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 = 'Brand#43'
                  and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
                  and l_quantity >= 12 and l_quantity <= 12 + 10
                  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 = 'Brand#52'
                  and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
                  and l_quantity >= 21 and l_quantity <= 21 + 10
                  and p_size between 1 and 15
                  and l_shipmode in ('AIR', 'AIR REG')
                  and l_shipinstruct = 'DELIVER IN PERSON'
          );

• 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;