Hologres:テスト計画の概要

TPC-H

以下は、TPC Benchmark™ H (TPC-H) 仕様からの抜粋です。

TPC-H は意思決定支援ベンチマークです。ビジネス指向のアドホッククエリと同時データ変更のスイートで構成されています。クエリとデータベースに投入されるデータは、業界全体で広く関連性を持つように選択されています。このベンチマークは、大量のデータを調査し、複雑性の高いクエリを実行し、重要なビジネス上の問題に対する回答を提供する意思決定支援システムを示しています。

完全な TPC-H 仕様をダウンロードしてください。

データセット

TPC-H は、Transaction Processing Performance Council (TPC) が開発した意思決定支援ベンチマークで、分析クエリのパフォーマンス評価に広く使用されています。

TPC-H は、実際の本番環境に基づいた販売システムのデータウェアハウスをモデル化しています。8つのテーブルで構成され、データ量は 1 GB から 3 TB までスケールします。このベンチマークには 22 のクエリが含まれています。主な指標は、TPC-H ベンチマークで定義されているクエリ応答時間です。

ユースケース

注意事項

テスト結果の一貫性を確保するため、テストごとに新しいインスタンスを作成してください。リサイズされたインスタンスは使用しないでください。

OLAP クエリシナリオのテスト

キー/値ポイントクエリのテスト

このテストでは、OLAP クエリテスト の hologres_tpch データベースと orders テーブルを使用します。

1. テーブルの作成

   キー/値ポイントクエリには、行指向テーブルが必要です。psql クライアントで Hologres に接続し、次のステートメントを実行して orders_row テーブルを作成します。

   説明

   psql クライアントでの接続方法については、「開発のための Hologres への接続」をご参照ください。

   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. データのインポート

   次の INSERT INTO ステートメントを使用して、TPC-H データセットの orders テーブルから orders_row テーブルにデータをインポートします。

   説明

   Hologres V2.1.17 以降では、サーバーレスコンピューティングがサポートされています。大規模なオフラインデータインポート、大規模な ETL ジョブ、外部テーブルに対する大量のクエリなどのシナリオでは、サーバーレスコンピューティングを使用してこれらのタスクを実行できます。この機能は、インスタンスのリソースの代わりに、追加のサーバーレスリソースを使用するため、インスタンスの安定性が向上し、メモリ不足 (OOM) エラーの可能性が低くなります。インスタンスに追加のコンピューティングリソースを予約する必要はなく、実行したタスクに対してのみ課金されます。サーバーレスコンピューティングの詳細については、「サーバーレスコンピューティング」をご参照ください。サーバーレスコンピューティングの使用方法については、「サーバーレスコンピューティングの使用」をご参照ください。

   -- (任意) 大量のオフラインインポートや ETL ジョブには、サーバーレスコンピューティングの使用を推奨します。
   SET hg_computing_resource = 'serverless';
   INSERT INTO public.orders_row SELECT * FROM public.orders;
   -- 後続の SQL ステートメントがサーバーレスリソースを使用しないように、設定をリセットします。
   RESET hg_computing_resource;

3. クエリの実行

   1. クエリステートメントの生成

      キー/値ポイントクエリには、次の 2 種類があります。

      タイプ	クエリステートメント	説明
      単一値ポイントクエリ	SELECT column_a ,column_b ,... ,column_x FROM table_x WHERE pk = value_x ;	このクエリは、WHERE 句で単一の値をフィルタリングします。
      複数値ポイントクエリ	SELECT column_a ,column_b ,... ,column_x FROM table_x WHERE pk IN ( value_a, value_b,..., value_x ) ;	このクエリは、WHERE 句で複数の値をフィルタリングします。

      次のスクリプトを実行して、必要な SQL ステートメントを生成します。

      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

      このスクリプトは、次の 2 つの SQL ファイルを生成します。

      • kv_query_single.sql：単一値ポイントクエリの SQL が含まれます。

      • kv_query_in.sql：複数値ポイントクエリの SQL が含まれます。このスクリプトは、9 つのランダムな値でフィルタリングする SQL ステートメントを生成します。

   2. クエリの統計情報を収集するには、pgbench ツールを使用します。次のコマンドを実行してインストールしてください。

      yum install postgresql-contrib -y

      pgbench 13 以降をインストールしてください。pgbench が既にインストールされている場合は、バージョン 9.6 以降であることを確認してください。バージョンを確認するには、次のコマンドを実行します。

      pgbench --version

   3. テストステートメントの実行

      説明

      次のコマンドは、クエリステートメントを生成したディレクトリで実行してください。

      • 単一値ポイントクエリのシナリオでは、pgbench を使用してストレステストを実行します。

        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

      • 複数値ポイントクエリのシナリオでは、pgbench を使用してストレステストを実行します。

        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

      パラメーターの説明：

      パラメーター	説明
      AccessKey ID	ご使用の Alibaba Cloud アカウントの AccessKey ID です。 AccessKey 管理ページに移動して AccessKey ID を取得してください。
      AccessKey Secret	ご使用の Alibaba Cloud アカウントの AccessKey Secret です。 AccessKey 管理ページに移動して AccessKey Secret を取得してください。
      database	Hologres データベースの名前です。 Hologres インスタンスを作成すると、システムは自動的に postgres という名前のデータベースを作成します。 デフォルトの postgres データベースを使用できますが、リソースは限られています。本番ワークロードの場合は、「データベースの作成」をご参照ください。
      endpoint	Hologres インスタンスのエンドポイントです。 Hologres コンソールのNetwork Information セクションからエンドポイントを取得してください。
      port	Hologres インスタンスのポートです。 Hologres コンソールの Instance Details ページに移動してポートを取得してください。
      Client_Num	同時クライアントの数です。 このテストでは、同時実行性ではなくクエリパフォーマンスを測定するため、このパラメーターを 1 に設定してください。
      Query_Seconds	ストレステストの合計時間 (秒) です。たとえば、このパラメーターを 300 に設定できます。

データ更新

このシナリオでは、プライマリキーの競合によって発生する全行更新時の OLAP エンジンのパフォーマンスをテストします。

• クエリファイルを生成します。

  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

• データを挿入・更新します。パラメーターを参照してください。

  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

• 結果の例：

  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)

Flink リアルタイム書き込み

このユースケースでは、リアルタイムデータ書き込みのパフォーマンスを評価します。

• Hologres DDL

  このケースで使用する Hologres テーブルは 10 列で構成されており、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;

• Flink ジョブスクリプト

  このスクリプトは、Realtime Compute for Apache Flink の組み込みランダムデータジェネレーターを使用して Hologres にデータを書き込みます。プライマリーキーの競合が発生した場合は行全体を更新し、各行のサイズは 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'
    );
  -- Hologres シンクテーブルを作成します。
  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>',  -- Hologres データベースの名前。
      'tablename'='<yourTablename>',  -- データを受信する Hologres テーブルの名前。
      'username'='<yourUsername>',  -- AccessKey ID。
      'password'='<yourPassword>',  -- AccessKey Secret。
      'endpoint'='<yourEndpoint>',  -- Hologres インスタンスの VPC エンドポイント。
      'connectionSize' = '10',  -- デフォルト値は 3 です。
      'jdbcWriteBatchSize' = '1024',  -- デフォルト値は 256 です。
      'jdbcWriteBatchByteSize' = '2147483647',  -- デフォルト値は 20971520 です。
      'mutatetype'='insertorreplace'  -- 競合時に新しい行を挿入するか、既存の行を置き換えます。
    );
  -- ソースからシンクにデータを書き込みます。
  insert into flink_case_1_sink
  select key,
    value1,
    value2,
    value3,
    value4,
    value5,
    value6,
    value7,
    value8,
    value9
  from
    flink_case_1_source
  ;

  詳細については、「Hologres シンクテーブル」をご参照ください。

• 結果のサンプル

  Hologres コンソールで、Monitoring Information ページに移動します。クエリレイテンシー (ms) グラフでは、select_avg メトリックは約 400 ms でピークに達します。リアルタイムインポート RPS (records/second) グラフでは、sdk メトリックは 200,000～400,000 records/second の間で変動します。

TPC-H 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(*) 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;