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 クエリシナリオテスト

Key/Value ポイントクエリシナリオテスト

Key/Value ポイントクエリシナリオテストでは、OLAP クエリシナリオテストで作成した hologres_tpch データベースと orders テーブルを引き続き使用できます。手順は次のとおりです。

1. テーブルの作成

   Key/Value ポイントクエリシナリオでは行指向テーブルを使用するため、OLAP クエリシナリオテストの orders テーブルを直接使用することはできません。新しいテーブルを作成する必要があります。PSQL クライアントを使用して Hologres に接続し、次のコマンドを実行して orders_row テーブルを作成します。

   説明

   PSQL クライアントを使用して Hologres に接続する方法の詳細については、「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 以降では、Serverless Computing がサポートされています。大規模なオフラインデータインポート、大規模な抽出、変換、ロード (ETL) ジョブ、外部テーブルに対する大量のクエリなどのシナリオでは、Serverless Computing を使用してこれらのタスクを実行できます。この機能は、インスタンス自体のリソースの代わりに、追加のサーバーレスリソースを使用します。インスタンスに追加の計算リソースを予約する必要はありません。これにより、インスタンスの安定性が大幅に向上し、メモリ不足 (OOM) エラーの可能性が減少し、個々のタスクに対してのみ課金されます。Serverless Computing の詳細については、「Serverless Computing」をご参照ください。Serverless Computing の使用方法については、「Serverless Computing の使用ガイド」をご参照ください。

   -- (オプション) Serverless Computing を使用して、大規模なオフラインデータインポートと ETL ジョブを実行します。
   SET hg_computing_resource = 'serverless';
   
   INSERT INTO public.orders_row SELECT * FROM public.orders;
   
   -- 不要な SQL 文がサーバーレスリソースを使用しないように設定をリセットします。
   RESET hg_computing_resource;

3. クエリの実行

   1. クエリ文の生成

      Key/Value ポイントクエリシナリオには、主に 2 種類のクエリがあります。クエリ文は次のとおりです。

      クエリメソッド	クエリ文	説明
      単一値フィルター	SELECT column_a ,column_b ,... ,column_x FROM table_x WHERE pk = value_x ;	このクエリ文は、単一値フィルタリングに使用され、SQL 文の WHERE 句には一意の値があります。
      複数値フィルター	SELECT column_a ,column_b ,... ,column_x FROM table_x WHERE pk IN ( value_a, value_b,..., value_x ) ;	このクエリ文は、複数値フィルタリングに使用され、SQL 文の 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。このスクリプトは、10 個の値をフィルタリングする SQL 文をランダムに生成します。

   2. クエリ統計の収集を容易にするために、pgbench ツールを使用します。次のコマンドを実行して、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 データベースを使用して Hologres に接続できます。ただし、このデータベースには割り当てられるリソースが少ないです。ビジネス開発のためには、新しいデータベースを作成してください。詳細については、「データベースの作成」をご参照ください。
      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 列がプライマリキーです。Hologres DDL は次のとおりです。

  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 ジョブスクリプト

  フルマネージド Flink に付属の乱数ジェネレータを使用して、Hologres にデータを書き込みます。プライマリキーの競合が発生した場合、行全体が更新されます。1 行のデータ量は 512 B を超えます。Flink ジョブスクリプトは次のとおりです。

  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>',  --ご利用の Alibaba Cloud アカウントの AccessKey ID。
      'password'='<yourPassword>',  --ご利用の Alibaba Cloud アカウントの AccessKey Secret。
      'endpoint'='<yourEndpoint>',  --Hologres インスタンスの VPC エンドポイント。
      'connectionSize' = '10',  --デフォルト値は 3 です。
      'jdbcWriteBatchSize' = '1024',  --デフォルト値は 256 です。
      'jdbcWriteBatchByteSize' = '2147483647',  --デフォルト値は 20971520 です。
      'mutatetype'='insertorreplace'  --データを挿入するか、既存の行全体を置き換えます。
    );
  
  -- ETL 操作を実行し、データを書き込みます。
  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ページで、RPS 値を表示できます。

22 個の 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;