AnalyticDB for PostgreSQL V6.0のTPC-Hパフォーマンステスト - - Alibaba Cloud ドキュメントセンター

AnalyticDB for PostgreSQL V6.0は、原子性、一貫性、分離、耐久性 (ACID) および分散トランザクションをサポートします。また、優れた大量並列処理 (MPP) パフォーマンスも提供します。このトピックでは、TPC-Hクエリを使用してAnalyticDB for PostgreSQL V6.0のパフォーマンスをテストする方法について説明します。

TPC-Hについて

以下の説明はTPCベンチマークから引用されています™H (TPC-H) の指定:

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

詳細については、「TPCベンチマーク」をご参照ください。™H標準仕様

説明

このトピックで説明するTPC-Hのパフォーマンステストは、TPC-Hベンチマークテストに基づいて実装されますが、TPC-Hベンチマークテストのすべての要件を満たすことはできません。したがって、このトピックで説明されているテスト結果は、TPC-Hのベンチマークテストの公開結果と比較することはできません。

前提条件

Alibaba Cloudアカウントが作成されました。
AnalyticDB for PostgreSQLインスタンスが作成されました。詳細は、インスタンスの作成をご参照ください。
このトピックでは、テストに使用されるAnalyticDB for PostgreSQLインスタンスに次の仕様があります。
- エンジンバージョン: 6.0 Standard Edition
- 計算ノード仕様: 2コア、16 GB
- 計算ノードの数: 32
- ディスクタイプ: 拡張SSD (ESSD)
- 計算ノードあたりのストレージ容量: 200 GB
Elastic Compute Service (ECS) インスタンスが作成されました。詳細については、「作成方法」をご参照ください。
このトピックでは、テストに使用されるECSインスタンスの仕様は次のとおりです。
- インスタンスタイプ: ecs.g6e.4xlarge
- オペレーティングシステム: CentOS 7.x
- システムディスク: PL1 ESSD、40 GiBのストレージ容量
- データディスク: 2,048 GiBのストレージ容量を持つPL3 ESSD
  説明
  ECSインスタンスにデータベースをアタッチする必要があります。詳細については、「Linuxインスタンスのサイズが2 TiBを超えないデータディスクの初期化」をご参照ください。
Object Storage Service (OSS) が有効化され、OSSバケットが作成されます。詳細については、「バケットの作成」をご参照ください。
ECSインスタンスのIPアドレスがAnalyticDB for PostgreSQLインスタンスのホワイトリストに追加されます。詳細については、「IPアドレスホワイトリストの設定」をご参照ください。
ECSインスタンスにpsqlがインストールされています。詳細については、「クライアントツールを使用してインスタンスに接続する」トピックの「psql」セクションを参照してください。

テストデータの生成

ECS インスタンスにログインします。詳細については、「インスタンスへの接続」をご参照ください。

ECSインスタンスで次のコマンドを実行して、TPC-H DBGENコードをデータディスクにダウンロードし、コードをコンパイルします。

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

wget https://github.com/electrum/tpch-dbgen/archive/refs/heads/master.zip
yum install -y unzip zip
unzip master.zip
cd tpch-dbgen-master/
echo "#define EOL_HANDLING 1" >> config.h # Delete the vertical bars (|) at the end of each row of data.
make
./dbgen --help

ECSインスタンスで次のコマンドを実行し、1テラバイトのテストデータセットを生成します。データセットを複数のデータファイルに分割することを推奨します。ファイルの数は、AnalyticDB for PostgreSQLインスタンスの計算ノードの数に等しくなります。たとえば、この例のAnalyticDB For PostgreSQLインスタンスには32個の計算ノードがあるため、32個のデータファイルが次のサンプルコードで作成されます。
```
for((i=1;i<=32;i++));
do
    ./dbgen -s 1000 -S $i -C 32 -f &
done 
```
説明
- データ量はクエリ速度に影響します。 TPC-Hでは、データ量を記述するためにスケールファクタ (SF) が使用される。 1つのSFは1 GBに等しく、1,000のSFは1テラバイトに等しい。 1つのSFは、インデックスによって占有されるストレージを除いて、8つのテーブルのデータ量を含む。複数のSFのデータストレージを予約する必要があります。
- データセットの生成には長い時間がかかる場合があります。 ps -fHU $USER | grep dbgenコマンドを実行して、生成プロセスが完了したかどうかを確認できます。

テストテーブルの作成

psqlを使用してAnalyticDB for PostgreSQLインスタンスに接続します。詳細については、「クライアントツールを使用してインスタンスに接続する」トピックの「psql」セクションを参照してください。

次のステートメントを実行して、テスト用の8つのテーブルを作成します。

CREATE TABLE NATION (
    N_NATIONKEY  INTEGER NOT NULL,
    N_NAME       CHAR(25) NOT NULL,
    N_REGIONKEY  INTEGER NOT NULL,
    N_COMMENT    VARCHAR(152)
)
WITH (APPENDONLY=TRUE, ORIENTATION=COLUMN, COMPRESSTYPE=LZ4, COMPRESSLEVEL=9)
DISTRIBUTED Replicated
;

CREATE TABLE REGION (
    R_REGIONKEY  INTEGER NOT NULL,
    R_NAME       CHAR(25) NOT NULL,
    R_COMMENT    VARCHAR(152)
)
WITH (APPENDONLY=TRUE, ORIENTATION=COLUMN, COMPRESSTYPE=LZ4, COMPRESSLEVEL=9)
DISTRIBUTED Replicated
;

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 DECIMAL(15,2) NOT NULL,
    P_COMMENT     VARCHAR(23) NOT NULL
)
WITH (APPENDONLY=TRUE, ORIENTATION=COLUMN, COMPRESSTYPE=LZ4, COMPRESSLEVEL=9)
DISTRIBUTED BY (P_PARTKEY)
;

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     DECIMAL(15,2) NOT NULL,
    S_COMMENT     VARCHAR(101) NOT NULL
)
WITH (APPENDONLY=TRUE, ORIENTATION=COLUMN, COMPRESSTYPE=LZ4, COMPRESSLEVEL=9)
DISTRIBUTED BY (S_SUPPKEY)
;

CREATE TABLE PARTSUPP (
    PS_PARTKEY     INTEGER NOT NULL,
    PS_SUPPKEY     INTEGER NOT NULL,
    PS_AVAILQTY    INTEGER NOT NULL,
    PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL,
    PS_COMMENT     VARCHAR(199) NOT NULL
)
WITH (APPENDONLY=TRUE, ORIENTATION=COLUMN, COMPRESSTYPE=LZ4, COMPRESSLEVEL=9)
DISTRIBUTED BY (PS_PARTKEY)
;

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       CHAR(15) NOT NULL,
    C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
    C_MKTSEGMENT  CHAR(10) NOT NULL,
    C_COMMENT     VARCHAR(117) NOT NULL
)
WITH (APPENDONLY=TRUE, ORIENTATION=COLUMN, COMPRESSTYPE=LZ4, COMPRESSLEVEL=9)
DISTRIBUTED BY (C_CUSTKEY)
;

CREATE TABLE ORDERS (
    O_ORDERKEY       BIGINT NOT NULL,
    O_CUSTKEY        INTEGER NOT NULL,
    O_ORDERSTATUS    "char" NOT NULL,
    O_TOTALPRICE     DECIMAL(15,2) 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
)
WITH (APPENDONLY=TRUE, ORIENTATION=COLUMN, COMPRESSTYPE=LZ4, 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    DECIMAL(15,2) NOT NULL,
    L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
    L_DISCOUNT    DECIMAL(15,2) NOT NULL,
    L_TAX         DECIMAL(15,2) 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
)
WITH (APPENDONLY=TRUE, ORIENTATION=COLUMN, COMPRESSTYPE=LZ4, COMPRESSLEVEL=9)
DISTRIBUTED BY (L_ORDERKEY)
ORDER BY(L_SHIPDATE)
;

MySQL からのデータインポート

ここでは、AnalyticDB for PostgreSQLインスタンスのデータベースにデータをインポートする方法について説明します。

次のステートメントを実行して、国と地域のテーブルをデータベースにインポートします。
```
\copy nation from '/mnt/tpch-dbgen-master/nation.tbl' DELIMITER '|';
\copy region from '/mnt/tpch-dbgen-master/region.tbl' DELIMITER '|';
```
説明
サンプルステートメントの /mnt/tpch-dbgen-masterを、nation.tblおよびregion.tblの実際のディレクトリに置き換える必要があります。
ossutilを使用して、他の6つのテーブルをOSSバケットにアップロードします。 ossutilの使用方法の詳細については、「概要」をご参照ください。
説明
COPYステートメントはコーディネーターノードを使用してデータを直列に書き込むため、大量のデータを並列に書き込むことはできません。したがって、データをインポートするにはOSSバケットが必要です。
1. 次のコマンドを実行して、ECSインスタンスにossutilをダウンロードします。
```
wget http://gosspublic.alicdn.com/ossutil/1.7.3/ossutil64
```
2. 次のコマンドを実行して、ファイルに対する実行権限を付与します。
```
chmod 755 ossutil64
```
3. 次のステートメントを実行して、. tblossutilを使用して、6つのテーブルのファイルをOSSバケットに追加します。
```
ls <table_name>.tbl* | while read line;
do
~/ossutil64 -e <EndPoint> -i <AccessKey ID> -k <Access Key Secret> cp $line oss://<OSS Bucket>/<Directory>/ &
done
```

すべてのテーブルがOSSバケットにアップロードされたら、次のステートメントを実行して、バケットからAnalyticDB for PostgreSQLデータベースにテーブルをインポートします。詳細については、「COPYまたはUNLOADステートメントを使用してOSS外部テーブルとAnalyticDB For PostgreSQLテーブル間でデータをインポートまたはエクスポートする」をご参照ください。

COPY customer
FROM 'oss://<OSS Bucket>/<Directory>/customer.tbl'
ACCESS_KEY_ID '<AccessKey ID>'
SECRET_ACCESS_KEY '<Access Key Secret>'
FORMAT AS text
"delimiter" '|'
"null" ''
ENDPOINT '<EndPoint>'
FDW 'oss_fdw'
;

COPY lineitem
FROM 'oss://<OSS Bucket>/<Directory>/lineitem.tbl'
ACCESS_KEY_ID '<AccessKey ID>'
SECRET_ACCESS_KEY '<Access Key Secret>'
FORMAT AS text
"delimiter" '|'
"null" ''
ENDPOINT '<EndPoint>'
FDW 'oss_fdw'
;

-- The lineitem table defines the sort key column based on which you can sort the data after the import is complete.
sort lineitem;

COPY orders
FROM 'oss://<OSS Bucket>/<Directory>/orders.tbl'
ACCESS_KEY_ID '<AccessKey ID>'
SECRET_ACCESS_KEY '<Access Key Secret>'
FORMAT AS text
"delimiter" '|'
"null" ''
ENDPOINT '<EndPoint>'
FDW 'oss_fdw'
;

-- The orders table defines the sort key column based on which you can sort the data after the import is complete.
sort orders;

COPY part
FROM 'oss://<OSS Bucket>/<Directory>/part.tbl'
ACCESS_KEY_ID '<AccessKey ID>'
SECRET_ACCESS_KEY '<Access Key Secret>'
FORMAT AS text
"delimiter" '|'
"null" ''
ENDPOINT '<EndPoint>'
FDW 'oss_fdw'
;

COPY supplier
FROM 'oss://<OSS Bucket>/<Directory>/supplier.tbl'
ACCESS_KEY_ID '<AccessKey ID>'
SECRET_ACCESS_KEY '<Access Key Secret>'
FORMAT AS text
"delimiter" '|'
"null" ''
ENDPOINT '<EndPoint>'
FDW 'oss_fdw'
;

COPY partsupp
FROM 'oss://<OSS Bucket>/<Directory>/partsupp.tbl'
ACCESS_KEY_ID '<AccessKey ID>'
SECRET_ACCESS_KEY '<Access Key Secret>'
FORMAT AS text
"delimiter" '|'
ENDPOINT '<EndPoint>'
FDW 'oss_fdw'
;

クエリの実行

シェルスクリプトを実行してテストを開始したり、psqlなどのクライアントツールを使用してSQLクエリを1つずつ実行したりできます。このセクションでは、2つの方法について説明します。

シェルスクリプトの実行

tpch_query.tar.gzパッケージをダウンロードし、/tpch_queryディレクトリに解凍します。

query.shという名前のシェルスクリプトを作成します。 Shellスクリプトには次の内容が含まれています。クエリを実行し、各クエリの実行時間とすべてのクエリの合計実行時間を記録するために使用されます。

#!/bin/bash

total_cost=0

for i in {1..22}
do
        echo "begin run Q${i}, tpch_query/q$i.sql , `date`"
        begin_time=`date +%s.%N`
        ./psql ${Instance endpoint} -p ${Port number} -U ${Database username} -f ~/tpch_query/q${i}.sql > ~/log/log_q${i}.out
        rc=$?
        end_time=`date +%s.%N`
        cost=`echo "$end_time-$begin_time"|bc`
        total_cost=`echo "$total_cost+$cost"|bc`
        if [ $rc -ne 0 ] ; then
              printf "run Q%s fail, cost: %.2f, totalCost: %.2f, `date`\n" $i $cost $total_cost
         else
              printf "run Q%s succ, cost: %.2f, totalCost: %.2f, `date`\n" $i $cost $total_cost
         fi
done

ECSインスタンスでquery.shスクリプトを実行します。
```
nohup bash ~/query.sh > /tmp/tpch.log &
```
次のコマンドを実行して結果を表示します。
```
cat /tmp/tpch.log
```

クライアントツールの使用

AnalyticDB for PostgreSQLデータベースに接続した後、次のステートメントを1つずつ実行し、結果を比較します。

-- Create the Laser vector compute engine.
create extension if not exists laser;

-- Q1
-- Enable Odyssey, which is the vector compute acceleration engine for AnalyticDB for PostgreSQL.
set laser.enable = on;
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
-- Enable Odyssey.
set laser.enable = on;
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
-- Enable Odyssey.
set laser.enable = on;
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
-- Enable Odyssey.
set laser.enable = on;
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
-- Enable Odyssey.
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
-- Enable Odyssey.
set laser.enable = on;
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
-- Enable Odyssey.
set laser.enable = on;
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
-- Enable Odyssey.
set laser.enable = on;
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
-- Enable Odyssey.
set laser.enable = on;
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
-- Enable Odyssey.
set laser.enable = on;
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
-- Enable Odyssey.
set laser.enable = on;
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
-- Enable Odyssey.
set laser.enable = on;
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
-- Enable Odyssey.
set laser.enable = on;
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
-- Enable Odyssey.
set laser.enable = on;
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
-- Enable Odyssey.
set laser.enable = on;
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
-- Enable Odyssey.
set laser.enable = on;
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
-- Enable Odyssey.
set laser.enable = on;
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
-- Enable Odyssey.
set laser.enable = on;
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
-- Enable Odyssey.
set laser.enable = on;
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
-- Enable Odyssey.
set laser.enable = on;
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
-- Enable Odyssey.
set laser.enable = on;
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
-- Enable Odyssey.
set laser.enable = on;
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;