AnalyticDB:AnalyticDB for PostgreSQL V7.0 の TPC-H 性能テスト

TPC-H について

以下の説明は、TPC Benchmark™ H (TPC-H) 仕様書からの引用です。

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

完全な仕様については、TPC Benchmark™ H 標準仕様書をご参照ください。

パフォーマンステスト結果

このテストでは、100 GB のデータセットに対して 22 の標準 TPC-H ベンチマーククエリを実行し、当社独自の Laser ベクターコンピュートエンジンと Greenplum ネイティブコンピュートエンジンを比較しました。Laser ベクターコンピュートエンジンは、ネイティブコンピュートエンジンを大幅に上回り、約 5 分の 1 の時間でクエリを完了しました。

前提条件

• Alibaba Cloud アカウント (root ユーザー) または RAM ユーザーを所有しており、そのユーザーに AliyunGPDBFullAccess、AliyunECSFullAccess、AliyunOSSFullAccess の権限が付与されていること、および AccessKey ID と AccessKey Secret が作成されていること。

• ご利用の AnalyticDB for PostgreSQL インスタンスと ECS インスタンスが同じ VPC 内にあること。

• ご利用の AnalyticDB for PostgreSQL インスタンスと OSS バケットが同じリージョンにあること。

• ご利用の AnalyticDB for PostgreSQL インスタンスに fixnumeric 拡張がインストールされていること。

• ご利用の ECS インスタンスのプライベート IP アドレスが、ご利用の AnalyticDB for PostgreSQL インスタンスの ホワイトリストに追加されていること。

• ご利用の ECS インスタンスに psql がインストールされていること。

テスト環境

テストデータの生成

このセクションでは、100 GB のテストデータを生成し、OSS にアップロードする方法について説明します。

1. ECS インスタンスにログインします。

2. ECS インスタンスで dbqgen.tar パッケージをダウンロードして解凍します。

   この例では、データディスクのパスは /mnt です。

   # dbqgen.tar パッケージをダウンロードします。
   wget https://help-static-aliyun-doc.aliyuncs.com/file-manage-files/zh-CN/20230821/bclo/dbqgen.tar
   
   # パッケージを解凍します。
   tar -xvf dbqgen.tar

3. データ生成パラメーターを設定します。

   1. dbqgen ディレクトリに移動します。

      cd dbqgen

   2. dbgen_data.sh ファイルを開きます。

      vim dbgen_data.sh

   3. `i` キーを押して挿入モードに入り、./dbgen -s 450 -S $i -C 192 -f & を ./dbgen -s 100 -S $i -C 32 -f & に置き換えます。

      説明

      -s パラメーターはデータ量を GB 単位で指定します。-C パラメーターはファイルブロックの数を指定します。この値はセグメントノード数の倍数に設定してください。

   4. `Esc` キーを押して挿入モードを終了し、:wq と入力してファイルを保存し、終了します。

4. テストデータを生成します。スクリプトは出力を dbqgen ディレクトリに保存します。

   sh dbgen_data.sh

5. テストデータを OSS にアップロードします。

   インストールと使用方法については、「ossutil コマンドラインツール 1.0」をご参照ください。

   次のコマンドを実行して、データを OSS にアップロードします。

   ./ossutil64 cp -r <path-to-test-data> <oss-bucket-path> --exclude "*.tbl*"

   例：

   ./ossutil64 cp -r /mnt/dbqgen/ oss://adbpg --exclude "*.tbl*"

テーブルの作成とデータのインポート

このトピックでは、AnalyticDB for PostgreSQL にテストテーブルを作成し、OSS からテストデータをインポートする方法について説明します。

1. psql を使用して、ご利用の AnalyticDB for PostgreSQL インスタンスに接続します。インストールと接続の手順については、「クライアント接続」をご参照ください。

2. テストデータベースを作成します。

   CREATE DATABASE tpch TEMPLATE template0 ENCODING='UTF8' LC_COLLATE='C' LC_CTYPE='C';

3. TPC-H テストテーブルを作成します。

   DROP TABLE IF EXISTS NATION;
   CREATE TABLE NATION (
       N_NATIONKEY  INTEGER NOT NULL,
       N_NAME       CHAR(25) NOT NULL,
       N_REGIONKEY  INTEGER NOT NULL,
       N_COMMENT    VARCHAR(152)
   )
   USING beam
   WITH(compresstype=auto,compresslevel=9)
   DISTRIBUTED REPLICATED
   ;
   
   DROP TABLE IF EXISTS REGION;
   CREATE TABLE REGION (
       R_REGIONKEY  INTEGER NOT NULL,
       R_NAME       CHAR(25) NOT NULL,
       R_COMMENT    VARCHAR(152)
   )
   USING beam
   WITH(compresstype=auto,compresslevel=9)
   DISTRIBUTED REPLICATED
   ;
   
   DROP TABLE IF EXISTS PART;
   CREATE TABLE PART (
       P_PARTKEY     INTEGER NOT NULL,
       P_NAME        VARCHAR(55) NOT NULL,
       P_MFGR        CHAR(25) NOT NULL,
       P_BRAND       CHAR(10) NOT NULL,
       P_TYPE        VARCHAR(25) NOT NULL,
       P_SIZE        INTEGER NOT NULL,
       P_CONTAINER   CHAR(10) NOT NULL,
       P_RETAILPRICE FIXNUMERIC(19) NOT NULL,
       P_COMMENT     VARCHAR(23) NOT NULL
   )
   USING beam
   WITH(compresstype=auto,compresslevel=9)
   DISTRIBUTED BY (P_PARTKEY)
   ;
   
   DROP TABLE IF EXISTS SUPPLIER;
   CREATE TABLE SUPPLIER (
       S_SUPPKEY     INTEGER NOT NULL,
       S_NAME        CHAR(25) NOT NULL,
       S_ADDRESS     VARCHAR(40) NOT NULL,
       S_NATIONKEY   INTEGER NOT NULL,
       S_PHONE       CHAR(15) NOT NULL,
       S_ACCTBAL     FIXNUMERIC(19) NOT NULL,
       S_COMMENT     VARCHAR(101) NOT NULL
   )
   USING beam
   WITH(compresstype=auto,compresslevel=9)
   DISTRIBUTED BY (S_SUPPKEY)
   ;
   
   DROP TABLE IF EXISTS PARTSUPP;
   CREATE TABLE PARTSUPP (
       PS_PARTKEY     INTEGER NOT NULL,
       PS_SUPPKEY     INTEGER NOT NULL,
       PS_AVAILQTY    INTEGER NOT NULL,
       PS_SUPPLYCOST  FIXNUMERIC(19)  NOT NULL,
       PS_COMMENT     VARCHAR(199) NOT NULL
   )
   USING beam
   WITH(compresstype=auto,compresslevel=9)
   DISTRIBUTED BY (PS_PARTKEY)
   ;
   
   DROP TABLE IF EXISTS CUSTOMER;
   CREATE TABLE CUSTOMER (
       C_CUSTKEY     INTEGER NOT NULL,
       C_NAME        VARCHAR(25) NOT NULL,
       C_ADDRESS     VARCHAR(40) NOT NULL,
       C_NATIONKEY   INTEGER NOT NULL,
       C_PHONE       VARCHAR(15) NOT NULL,
       C_ACCTBAL     FIXNUMERIC(19)  NOT NULL,
       C_MKTSEGMENT  CHAR(10) NOT NULL,
       C_COMMENT     VARCHAR(117) NOT NULL
   )
   USING beam
   WITH(compresstype=auto,compresslevel=9)
   DISTRIBUTED BY (C_CUSTKEY)
   ;
   
   DROP TABLE IF EXISTS ORDERS;
   CREATE TABLE ORDERS (
       O_ORDERKEY       BIGINT NOT NULL,
       O_CUSTKEY        INTEGER NOT NULL,
       O_ORDERSTATUS    "char" NOT NULL,
       O_TOTALPRICE     FIXNUMERIC(19) NOT NULL,
       O_ORDERDATE      DATE NOT NULL,
       O_ORDERPRIORITY  CHAR(15) NOT NULL,
       O_CLERK          CHAR(15) NOT NULL,
       O_SHIPPRIORITY   INTEGER NOT NULL,
       O_COMMENT        VARCHAR(79) NOT NULL
   )
   USING beam
   WITH(compresstype=auto,compresslevel=9)
   DISTRIBUTED BY (O_ORDERKEY)
   ORDER BY(O_ORDERDATE);
   
   CREATE TABLE LINEITEM(
       L_ORDERKEY       BIGINT NOT NULL,
       L_PARTKEY        INTEGER NOT NULL,
       L_SUPPKEY        INTEGER NOT NULL,
       L_LINENUMBER     INTEGER NOT NULL,
       L_QUANTITY       FIXNUMERIC(19) NOT NULL,
       L_EXTENDEDPRICE  FIXNUMERIC(19) NOT NULL,
       L_DISCOUNT       FIXNUMERIC(19) NOT NULL,
       L_TAX            FIXNUMERIC(19) NOT NULL,
       L_RETURNFLAG     "char" NOT NULL,
       L_LINESTATUS     "char" NOT NULL,
       L_SHIPDATE       DATE NOT NULL,
       L_COMMITDATE     DATE NOT NULL,
       L_RECEIPTDATE    DATE NOT NULL,
       L_SHIPINSTRUCT   CHAR(25) NOT NULL,
       L_SHIPMODE       CHAR(10) NOT NULL,
       L_COMMENT        VARCHAR(44) NOT NULL
   )
   USING beam
   WITH(compresstype=auto,compresslevel=9)
   DISTRIBUTED BY (L_ORDERKEY)
   ORDER BY(L_SHIPDATE);

4. OSS 外部テーブルを作成して、テストデータをインポートします。OSS 外部テーブルの使用方法の詳細については、「OSS 外部テーブルの概要」をご参照ください。

   -- OSS サーバーを作成します。
   CREATE SERVER oss_serv
   FOREIGN DATA WRAPPER oss_fdw 
   OPTIONS (
     endpoint 'oss-cn-hang****.aliyuncs.com',
     bucket 'testBucketName');
   
   -- ユーザーマッピングを作成します。
   CREATE USER MAPPING FOR PUBLIC SERVER oss_serv 
   OPTIONS (
     id 'LTAI****************',
     key 'yourAccessKeySecret');
   
   -- OSS 外部テーブルを作成します。
   CREATE FOREIGN TABLE ext_nation(
       N_NATIONKEY  INT, 
       N_NAME       VARCHAR(25), 
       N_REGIONKEY  INTEGER, 
       N_COMMENT    VARCHAR(152)
   ) 
   SERVER oss_serv
       options (
           prefix 'tpch500g/nation.tbl',
           format 'text',
           DELIMITER '|'
       );
   
   CREATE FOREIGN TABLE ext_region
       (R_REGIONKEY INT, 
        R_NAME      CHAR(25), 
        R_COMMENT   VARCHAR(152))
   SERVER oss_serv
       options (
           prefix 'tpch500g/region.tbl',
           format 'text',
           DELIMITER '|'
       );
   
   CREATE FOREIGN TABLE ext_lineitem 
       ( l_orderkey         bigint, 
         l_partkey          bigint, 
         l_suppkey          bigint, 
         l_linenumber       bigint, 
         l_quantity         FIXNUMERIC, 
         l_extendedprice    FIXNUMERIC, 
         l_discount         FIXNUMERIC, 
         l_tax              FIXNUMERIC, 
         l_returnflag       CHAR(1), 
         l_linestatus       CHAR(1), 
         l_shipdate         DATE, 
         l_commitdate       DATE, 
         l_receiptdate      DATE, 
         l_shipinstruct     CHAR(25), 
         l_shipmode         CHAR(10), 
         l_comment          VARCHAR(44))
   SERVER oss_serv
       options (
           prefix 'tpch500g/lineitem.tbl',
           format 'text',
           DELIMITER '|'
       );
   
   CREATE FOREIGN TABLE ext_orders 
       ( o_orderkey       BIGINT, 
         o_custkey        BIGINT, 
         o_orderstatus    CHAR(1), 
         o_totalprice     FIXNUMERIC, 
         o_orderdate      DATE, 
         o_orderpriority  CHAR(15), 
         o_clerk          CHAR(15), 
         o_shippriority   BIGINT, 
         o_comment        VARCHAR(79))
   SERVER oss_serv
       options (
           prefix 'tpch500g/orders.tbl',
           format 'text',
           DELIMITER '|'
       );
   
   CREATE FOREIGN TABLE ext_part 
       ( p_partkey     BIGINT, 
         p_name        VARCHAR(55), 
         p_mfgr        CHAR(25), 
         p_brand       CHAR(10), 
         p_type        VARCHAR(25) , 
         p_size        BIGINT, 
         p_container   CHAR(10), 
         p_retailprice FIXNUMERIC, 
         p_comment     VARCHAR(23))
   SERVER oss_serv
       options (
           prefix 'tpch500g/part.tbl',
           format 'text',
           DELIMITER '|'
       );
   
   CREATE FOREIGN TABLE ext_partsupp 
       ( ps_partkey    BIGINT, 
         ps_suppkey    BIGINT, 
         ps_availqty   BIGINT, 
         ps_supplycost FIXNUMERIC , 
         ps_comment    VARCHAR(199))
   SERVER oss_serv
       options (
           prefix 'tpch500g/partsupp.tbl',
           format 'text',
           DELIMITER '|'
       );
   
   CREATE FOREIGN TABLE ext_supplier 
       (s_suppkey    BIGINT, 
        s_name       CHAR(25), 
        s_address    VARCHAR(40), 
        s_nationkey  BIGINT, 
        s_phone      CHAR(15), 
        s_acctbal    DECIMAL(15,2), 
        s_comment    VARCHAR(101))
   SERVER oss_serv
       options (
           prefix 'tpch500g/supplier.tbl',
           format 'text',
           DELIMITER '|'
       );
   
   CREATE FOREIGN TABLE ext_customer 
       ( c_custkey     BIGINT, 
         c_name        VARCHAR(25), 
         c_address     VARCHAR(40), 
         c_nationkey   BIGINT, 
         c_phone       CHAR(15), 
         c_acctbal     FIXNUMERIC, 
         c_mktsegment  CHAR(10), 
         c_comment     VARCHAR(117))
   SERVER oss_serv
       options (
           prefix 'tpch500g/customer.tbl',
           format 'text',
           DELIMITER '|'
       );

5. データを TPC-H テストテーブルにインポートし、その後 OSS 外部テーブルを削除します。

   -- OSS 外部テーブルからテストテーブルにデータをインポートします。
   INSERT INTO nation SELECT * FROM ext_nation;
   INSERT INTO region SELECT * FROM ext_region;
   INSERT INTO orders SELECT * FROM ext_orders;
   INSERT INTO customer SELECT * FROM ext_customer;
   INSERT INTO partsupp SELECT * FROM ext_partsupp;
   INSERT INTO part SELECT * FROM ext_part;
   INSERT INTO supplier SELECT * FROM ext_supplier;
   INSERT INTO lineitem SELECT * FROM ext_lineitem;
   
   -- OSS 外部テーブルを削除します。
   DROP FOREIGN TABLE ext_lineitem;
   DROP FOREIGN TABLE ext_orders;
   DROP FOREIGN TABLE ext_customer;
   DROP FOREIGN TABLE ext_part;
   DROP FOREIGN TABLE ext_partsupp;
   DROP FOREIGN TABLE ext_supplier;
   DROP FOREIGN TABLE ext_nation;
   DROP FOREIGN TABLE ext_region;

統計情報の収集

次のコマンドを実行して統計情報を収集します。

ANALYZE (fullscan) lineitem ;
ANALYZE (fullscan) orders   ;
ANALYZE (fullscan) customer ;
ANALYZE (fullscan) supplier ;
ANALYZE (fullscan) part     ;
ANALYZE (fullscan) partsupp ;
ANALYZE (fullscan) region   ;
ANALYZE (fullscan) nation   ;

テストクエリの実行

-- 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 '93 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 = 23
    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-24'
    AND l_shipdate > date '1995-03-24'
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-08-01'
    AND o_orderdate < date '1996-08-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 = 'MIDDLE EAST'
    AND o_orderdate >= date '1994-01-01'
    AND o_orderdate < date '1994-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 '1994-01-01'
    AND l_shipdate < date '1994-01-01' + interval '1' year
    AND l_discount BETWEEN 0.06 - 0.01 AND 0.06 + 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 = 'JORDAN' AND n2.n_name = 'INDONESIA')
                OR (n1.n_name = 'INDONESIA' AND n2.n_name = 'JORDAN')
            )
            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 = 'INDONESIA' 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 = 'ASIA'
            AND s_nationkey = n2.n_nationkey
            AND o_orderdate BETWEEN date '1995-01-01' AND date '1996-12-31'
            AND p_type = 'STANDARD BRUSHED BRASS'
    ) 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 '%chartreuse%'
    ) 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 '1994-08-01'
    AND o_orderdate < date '1994-08-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 = 'INDONESIA'
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 = 'INDONESIA'
        )
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 ('REG AIR', 'TRUCK')
    and l_commitdate < l_receiptdate
    and l_shipdate < l_commitdate
    and l_receiptdate >= date '1994-01-01'
    and l_receiptdate < date '1994-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)
        from
            customer left outer join orders on
                c_custkey = o_custkey
                and o_comment not like '%pending%requests%'
        group by
            c_custkey
    ) as c_orders (c_custkey, c_count)
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 '1994-11-01'
    and l_shipdate < date '1994-11-01' + interval '1' month;
    
-- Q15
create view revenue0 (supplier_no, total_revenue) as
    select
        l_suppkey,
        sum(l_extendedprice * (1 - l_discount))
    from
        lineitem
    where
        l_shipdate >= date '1997-10-01'
        and l_shipdate < date '1997-10-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;
drop view revenue0;

-- 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#44'
    and p_type not like 'SMALL BURNISHED%'
    and p_size in (36, 27, 34, 45, 11, 6, 25, 16)
    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#42'
    and p_container = 'JUMBO 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#43'
        and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
        and l_quantity >= 5 and l_quantity <= 5 + 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#45'
        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#11'
        and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
        and l_quantity >= 24 and l_quantity <= 24 + 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 'magenta%'
            )
            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 = 'RUSSIA'
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 = 'MOZAMBIQUE'
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 ('13', '31', '23', '29', '30', '18', '17')
                and c_acctbal > (
                    select
                        avg(c_acctbal)
                    from
                        customer
                    where
                        c_acctbal > 0.00
                        and substring(c_phone from 1 for 2) in ('13', '31', '23', '29', '30', '18', '17')
                )
                and not exists (
                    select
                        *
                    from
                        orders
                    where
                        o_custkey = c_custkey
                )
        ) as custsale
group by
        cntrycode
order by
        cntrycode;