AnalyticDB:Testes TPC-H

Ative os mecanismos de computação Native e XUANWU_V2 (opcional)

Ignore esta etapa se quiser testar apenas os mecanismos de computação native e XUANWU. Para testar os mecanismos de computação Native e XUANWU_V2, execute os seguintes comandos antes de criar as tabelas de teste:

SET ADB_CONFIG native_engine_opt_enabled = true;
SET ADB_CONFIG rc_ddl_engine_rewrite_xuanwuv2 = true;

Crie o banco de dados e as tabelas de teste

Crie um banco de dados de teste. Se já existir um, ignore esta etapa.

CREATE DATABASE IF NOT EXISTS tpch;

Crie as oito tabelas e a visualização a seguir no banco de dados tpch.

Tabela NATION

CREATE TABLE nation (
  n_nationkey INT NOT NULL COMMENT '',
  n_name VARCHAR NOT NULL COMMENT '',
  n_regionkey INT NOT NULL COMMENT '',
  n_comment VARCHAR COMMENT '',
  dummy VARCHAR,
  PRIMARY KEY (n_nationkey)
) DISTRIBUTED BY BROADCAST INDEX_ALL = 'Y' compression='lz4hc';

Tabela REGION

CREATE TABLE region (
  r_regionkey INT NOT NULL COMMENT '',
  r_name VARCHAR NOT NULL COMMENT '',
  r_comment VARCHAR COMMENT '',
  dummy VARCHAR,
  PRIMARY KEY (r_regionkey)
) DISTRIBUTED BY BROADCAST INDEX_ALL = 'Y' compression='lz4hc';

Tabela PART

CREATE TABLE part (
  p_partkey INT NOT NULL COMMENT '',
  p_name VARCHAR NOT NULL COMMENT '',
  p_mfgr VARCHAR NOT NULL COMMENT '',
  p_brand VARCHAR(10) NOT NULL COMMENT '',
  p_type VARCHAR NOT NULL COMMENT '',
  p_size INT NOT NULL COMMENT '',
  p_container VARCHAR(10) NOT NULL COMMENT '',
  p_retailprice DOUBLE NOT NULL COMMENT '',
  p_comment VARCHAR NOT NULL COMMENT '',
  dummy VARCHAR,
  PRIMARY KEY (p_partkey)
) DISTRIBUTED BY HASH (p_partkey) INDEX_ALL = 'Y' compression='lz4hc';

Tabela SUPPLIER

CREATE TABLE supplier (
  s_suppkey INT NOT NULL COMMENT '',
  s_name VARCHAR NOT NULL COMMENT '',
  s_address VARCHAR NOT NULL COMMENT '',
  s_nationkey INT NOT NULL COMMENT '',
  s_phone VARCHAR(15) NOT NULL COMMENT '',
  s_acctbal DOUBLE NOT NULL COMMENT '',
  s_comment VARCHAR NOT NULL COMMENT '',
  dummy VARCHAR,
  PRIMARY KEY (s_suppkey)
) DISTRIBUTED BY HASH (s_suppkey) INDEX_ALL = 'Y' compression='lz4hc';

Tabela PARTSUPP

CREATE TABLE partsupp (
  ps_partkey INT NOT NULL COMMENT '',
  ps_suppkey INT NOT NULL COMMENT '',
  ps_availqty INT NOT NULL COMMENT '',
  ps_supplycost DOUBLE NOT NULL COMMENT '',
  ps_comment VARCHAR NOT NULL COMMENT '',
  dummy VARCHAR,
  PRIMARY KEY (ps_partkey, ps_suppkey)
) DISTRIBUTED BY HASH (ps_partkey) INDEX_ALL = 'Y' compression='lz4hc';

Tabela CUSTOMER

CREATE TABLE customer (
  c_custkey INT NOT NULL COMMENT '',
  c_name VARCHAR NOT NULL COMMENT '',
  c_address VARCHAR NOT NULL COMMENT '',
  c_nationkey INT NOT NULL COMMENT '',
  c_phone VARCHAR(15) NOT NULL COMMENT '',
  c_acctbal DOUBLE NOT NULL COMMENT '',
  c_mktsegment VARCHAR(10) NOT NULL COMMENT '',
  c_comment VARCHAR NOT NULL COMMENT '',
  dummy VARCHAR,
  PRIMARY KEY (c_custkey)
) DISTRIBUTED BY HASH (c_custkey) INDEX_ALL = 'Y' compression='lz4hc';

Tabela ORDERS

CREATE TABLE orders (
  o_orderkey BIGINT NOT NULL COMMENT '',
  o_custkey INT NOT NULL COMMENT '',
  o_orderstatus VARCHAR(1) NOT NULL COMMENT '',
  o_totalprice DOUBLE NOT NULL COMMENT '',
  o_orderdate DATE NOT NULL COMMENT '',
  o_orderpriority VARCHAR(15) NOT NULL COMMENT '',
  o_clerk VARCHAR(15) NOT NULL COMMENT '',
  o_shippriority INT NOT NULL COMMENT '',
  o_comment VARCHAR NOT NULL COMMENT '',
  dummy VARCHAR,
  KEY idx_o_custkey (o_custkey),
  KEY idx_o_orderdate (o_orderdate),
  KEY idx_o_orderkey (o_orderkey),
  KEY idx_o_orderstatus (o_orderstatus)
) DISTRIBUTED BY HASH (o_orderkey) PARTITION BY VALUE(date_format(o_orderdate, '%Y%m')) PARTITION NUM 1000 INDEX_ALL = 'N' compression='lz4hc';

Tabela LINEITEM

CREATE TABLE lineitem (
  l_orderkey BIGINT NOT NULL COMMENT '',
  l_partkey INT NOT NULL COMMENT '',
  l_suppkey INT NOT NULL COMMENT '',
  l_linenumber INT NOT NULL COMMENT '',
  l_quantity DOUBLE NOT NULL COMMENT '',
  l_extendedprice DOUBLE NOT NULL COMMENT '',
  l_discount DOUBLE NOT NULL COMMENT '',
  l_tax DOUBLE NOT NULL COMMENT '',
  l_returnflag VARCHAR(1) NOT NULL COMMENT '',
  l_linestatus VARCHAR(1) NOT NULL COMMENT '',
  l_shipdate DATE NOT NULL COMMENT '',
  l_commitdate DATE NOT NULL COMMENT '',
  l_receiptdate DATE NOT NULL COMMENT '',
  l_shipinstruct VARCHAR(25) NOT NULL COMMENT '',
  l_shipmode VARCHAR(10) NOT NULL COMMENT '',
  l_comment VARCHAR NOT NULL COMMENT '',
  dummy VARCHAR,
  KEY idx_l_orderkey (l_orderkey),
  KEY idx_l_partkey (l_partkey),
  KEY idx_l_receiptdate (l_receiptdate),
  KEY idx_l_returnflag (l_returnflag),
  KEY idx_l_shipdate (l_shipdate),
  KEY idx_l_shipinstruct (l_shipinstruct),
  KEY idx_l_shipmode (l_shipmode),
  KEY idx_l_suppkey (l_suppkey),
  clustered key clustered_index(`l_orderkey` ASC)
) DISTRIBUTED BY HASH  (l_orderkey) PARTITION BY VALUE(date_format(l_shipdate, '%Y%m')) PARTITION NUM 1000 INDEX_ALL='N'COMPRESSION='lz4hc';

Visualização REVENUE0

CREATE VIEW `revenue0` AS
SELECT
  `l_suppkey` supplier_no,
  `sum`(
    (
      `l_extendedprice` * (1 - `l_discount`)
    )
  ) total_revenue
FROM
  lineitem
WHERE
  (
    (`l_shipdate` >= DATE '1996-01-01')
    AND (
      `l_shipdate` < (
        DATE '1996-01-01' + INTERVAL '3' MONTH
      )
    )
  )
GROUP BY
  `l_suppkey`;

Importe a partir de tabelas externas do OSS (recomendado)

O AnalyticDB for MySQL fornece um conjunto de dados TPC-H pré-construído de 1 TB no OSS. O caminho do conjunto de dados varia conforme a região. Use o caminho correspondente à região do seu cluster.

Etapa 1: Crie um banco de dados externo.

CREATE EXTERNAL DATABASE IF NOT EXISTS external_tpch;

Etapa 2: Crie oito tabelas externas.

Substitua o caminho do OSS no parâmetro LOCATION pelo caminho da região do seu cluster.

CREATE EXTERNAl TABLE external_tpch.nation (
	n_nationkey int NOT NULL COMMENT '',
	n_name varchar NOT NULL COMMENT '',
	n_regionkey int NOT NULL COMMENT '',
	n_comment varchar COMMENT '',
	`dummy` varchar
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/nation/';

CREATE EXTERNAl TABLE external_tpch.customer (
	c_custkey int NOT NULL COMMENT '',
	c_name varchar NOT NULL COMMENT '',
	c_address varchar NOT NULL COMMENT '',
	c_nationkey int NOT NULL COMMENT '',
	c_phone varchar(15) NOT NULL COMMENT '',
	c_acctbal double NOT NULL COMMENT '',
	c_mktsegment varchar(10) NOT NULL COMMENT '',
	c_comment varchar NOT NULL COMMENT '',
	`dummy` varchar
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/customer/';

CREATE EXTERNAl TABLE external_tpch.lineitem (
	l_orderkey bigint NOT NULL COMMENT '',
	l_partkey int NOT NULL COMMENT '',
	l_suppkey int NOT NULL COMMENT '',
	l_linenumber int NOT NULL COMMENT '',
	l_quantity double NOT NULL COMMENT '',
	l_extendedprice double NOT NULL COMMENT '',
	l_discount double NOT NULL COMMENT '',
	l_tax double NOT NULL COMMENT '',
	l_returnflag varchar(1) NOT NULL COMMENT '',
	l_linestatus varchar(1) NOT NULL COMMENT '',
	l_shipdate date NOT NULL COMMENT '',
	l_commitdate date NOT NULL COMMENT '',
	l_receiptdate date NOT NULL COMMENT '',
	l_shipinstruct varchar(25) NOT NULL COMMENT '',
	l_shipmode varchar(10) NOT NULL COMMENT '',
	l_comment varchar NOT NULL COMMENT '',
	dummy varchar
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/lineitem/';

CREATE EXTERNAl TABLE external_tpch.orders (
	o_orderkey bigint NOT NULL COMMENT '',
	o_custkey int NOT NULL COMMENT '',
	o_orderstatus varchar(1) NOT NULL COMMENT '',
	o_totalprice double NOT NULL COMMENT '',
	o_orderdate date NOT NULL COMMENT '',
	o_orderpriority varchar(15) NOT NULL COMMENT '',
	o_clerk varchar(15) NOT NULL COMMENT '',
	o_shippriority int NOT NULL COMMENT '',
	o_comment varchar NOT NULL COMMENT '',
	dummy varchar
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/orders/';

CREATE EXTERNAl TABLE external_tpch.part (
	p_partkey int NOT NULL COMMENT '',
	p_name varchar NOT NULL COMMENT '',
	p_mfgr varchar NOT NULL COMMENT '',
	p_brand varchar(10) NOT NULL COMMENT '',
	p_type varchar NOT NULL COMMENT '',
	p_size int NOT NULL COMMENT '',
	p_container varchar(10) NOT NULL COMMENT '',
	p_retailprice double NOT NULL COMMENT '',
	p_comment varchar NOT NULL COMMENT '',
	dummy varchar
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/part/';

CREATE EXTERNAl TABLE external_tpch.partsupp (
	ps_partkey int NOT NULL COMMENT '',
	ps_suppkey int NOT NULL COMMENT '',
	ps_availqty int NOT NULL COMMENT '',
	ps_supplycost double NOT NULL COMMENT '',
	ps_comment varchar NOT NULL COMMENT '',
	dummy varchar
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/partsupp/';

CREATE EXTERNAl TABLE external_tpch.region (
	r_regionkey int NOT NULL COMMENT '',
	r_name varchar NOT NULL COMMENT '',
	r_comment varchar COMMENT '',
	dummy varchar
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/region/';

CREATE EXTERNAl TABLE external_tpch.supplier (
	s_suppkey int NOT NULL COMMENT '',
	s_name varchar NOT NULL COMMENT '',
	s_address varchar NOT NULL COMMENT '',
	s_nationkey int NOT NULL COMMENT '',
	s_phone varchar(15) NOT NULL COMMENT '',
	s_acctbal double NOT NULL COMMENT '',
	s_comment varchar NOT NULL COMMENT '',
	dummy varchar
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/supplier/';

Etapa 3: Carregue os dados das tabelas externas para as tabelas internas.

INSERT OVERWRITE INTO customer
SELECT * FROM external_tpch.customer;

INSERT OVERWRITE INTO lineitem
SELECT * FROM external_tpch.lineitem;

INSERT INTO nation
SELECT * FROM external_tpch.nation;

INSERT OVERWRITE INTO orders
SELECT * FROM external_tpch.orders;

INSERT OVERWRITE INTO part
SELECT * FROM external_tpch.part;

INSERT OVERWRITE INTO partsupp
SELECT * FROM external_tpch.partsupp;

INSERT INTO region
SELECT * FROM external_tpch.region;

INSERT OVERWRITE INTO supplier
SELECT * FROM external_tpch.supplier;

Etapa 4: Colete estatísticas.

ANALYZE TABLE customer UPDATE HISTOGRAM;
ANALYZE TABLE lineitem UPDATE HISTOGRAM;
ANALYZE TABLE nation UPDATE HISTOGRAM;
ANALYZE TABLE orders UPDATE HISTOGRAM;
ANALYZE TABLE part UPDATE HISTOGRAM;
ANALYZE TABLE partsupp UPDATE HISTOGRAM;
ANALYZE TABLE region UPDATE HISTOGRAM;
ANALYZE TABLE supplier UPDATE HISTOGRAM;

O otimizador de consultas usa estatísticas de histograma para gerar planos de execução de alta qualidade. Coletar estatísticas após a importação de dados é essencial para obter o melhor desempenho nas consultas. Para mais detalhes, consulte Estatísticas .

Importe dados usando LOAD DATA

Utilize este método se o caminho da tabela externa do OSS não estiver disponível para sua região ou se desejar testar com um fator de escala personalizado.

Etapa 1: Gere dados de teste usando DBGEN.

Baixe a ferramenta de geração de dados TPC-H DBGEN no site do TPC, compile-a e gere os arquivos de dados. Para detalhes de uso, consulte tpch-dbgen.

./dbgen -s $scale -C $chunks -S $i -f

Cada comando gera uma parte dos dados. Para gerar o conjunto de dados em múltiplas partes, execute o comando várias vezes com valores diferentes de -S . Para mais informações sobre como usar o dbgen, consulte tpch-dbgen .

Etapa 2: Carregue os arquivos de dados no AnalyticDB for MySQL.

Este exemplo usa terminações de linha do Windows (\r\n). Ajuste LINES TERMINATED BY de acordo com seu sistema operacional.

LOAD DATA LOCAL INFILE 'customer.tbl' INTO TABLE CUSTOMER
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'orders.tbl'   INTO TABLE ORDERS
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'lineitem.tbl' INTO TABLE LINEITEM
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'nation.tbl'   INTO TABLE NATION
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'partsupp.tbl' INTO TABLE PARTSUPP
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'part.tbl'     INTO TABLE PART
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'region.tbl'   INTO TABLE REGION
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'supplier.tbl' INTO TABLE SUPPLIER
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';

Etapa 3: Colete estatísticas.

ANALYZE TABLE customer UPDATE HISTOGRAM;
ANALYZE TABLE lineitem UPDATE HISTOGRAM;
ANALYZE TABLE nation UPDATE HISTOGRAM;
ANALYZE TABLE orders UPDATE HISTOGRAM;
ANALYZE TABLE part UPDATE HISTOGRAM;
ANALYZE TABLE partsupp UPDATE HISTOGRAM;
ANALYZE TABLE region UPDATE HISTOGRAM;
ANALYZE TABLE supplier UPDATE HISTOGRAM;

O otimizador de consultas usa estatísticas de histograma para gerar planos de execução de alta qualidade. Coletar estatísticas após a importação de dados é essencial para obter o melhor desempenho nas consultas. Para mais detalhes, consulte Estatísticas .

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 '90' DAY
GROUP BY l_returnflag, l_linestatus
ORDER BY l_returnflag, l_linestatus;

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 = 15
	AND p_type LIKE '%BRASS'
	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;

SELECT l_orderkey, sum(l_extendedprice * (1 - l_discount)) AS revenue
	, o_orderdate, o_shippriority
FROM customer, orders, lineitem
WHERE c_mktsegment = 'BUILDING'
	AND c_custkey = o_custkey
	AND l_orderkey = o_orderkey
	AND o_orderdate < DATE '1995-03-15'
	AND l_shipdate > DATE '1995-03-15'
GROUP BY l_orderkey, o_orderdate, o_shippriority
ORDER BY revenue DESC, o_orderdate
LIMIT 10;

SELECT o_orderpriority, count(*) AS order_count
FROM orders
WHERE o_orderdate >= DATE '1993-07-01'
	AND o_orderdate < DATE '1993-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;

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 = 'ASIA'
	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;

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;

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 = 'FRANCE'
				AND n2.n_name = 'GERMANY')
			OR (n1.n_name = 'GERMANY'
				AND n2.n_name = 'FRANCE'))
		AND l_shipdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31'
) shipping
GROUP BY supp_nation, cust_nation, l_year
ORDER BY supp_nation, cust_nation, l_year;

SELECT o_year
	, sum(CASE
		WHEN nation = 'PERU' 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 = 'PROMO BURNISHED TIN'
) all_nations
GROUP BY o_year
ORDER BY o_year;

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 '%green%'
) profit
GROUP BY nation, o_year
ORDER BY nation, o_year DESC;

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-09-01'
	AND o_orderdate < DATE '1993-09-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

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 = 'GERMANY'
GROUP BY ps_partkey
HAVING sum(ps_supplycost * ps_availqty) > (
	SELECT sum(ps_supplycost * ps_availqty) * 0.0000001000
	FROM partsupp, supplier, nation
	WHERE ps_suppkey = s_suppkey
		AND s_nationkey = n_nationkey
		AND n_name = 'GERMANY'
)
ORDER BY value DESC;

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 ('TRUCK', 'AIR')
	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;

SELECT c_count, count(*) AS custdist
FROM (
	SELECT c_custkey, count(o_orderkey) AS c_count
	FROM customer
		LEFT JOIN orders
		ON c_custkey = o_custkey
			AND o_comment NOT LIKE '%special%requests%'
	GROUP BY c_custkey
) AS c_orders (c_custkey, c_count)
GROUP BY c_count
ORDER BY custdist DESC, c_count DESC;

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 '1995-09-01'
	AND l_shipdate < DATE '1995-09-01' + INTERVAL '1' MONTH;

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;

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 'MEDIUM POLISHED%'
	AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9)
	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;

SELECT sum(l_extendedprice) / 7.0 AS avg_yearly
FROM lineitem, part
WHERE p_partkey = l_partkey
	AND p_brand = 'Brand#23'
	AND p_container = 'SM CAN'
	AND l_quantity < (
		SELECT 0.2 * avg(l_quantity)
		FROM lineitem
		WHERE l_partkey = p_partkey
	);

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) > 300
	)
	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;

SELECT sum(l_extendedprice * (1 - l_discount)) AS revenue
FROM lineitem, part
WHERE (p_partkey = l_partkey
		AND p_brand = 'Brand#12'
		AND p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
		AND l_quantity >= 1
		AND l_quantity <= 1 + 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#42'
		AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
		AND l_quantity >= 10
		AND l_quantity <= 10 + 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#45'
		AND p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
		AND l_quantity >= 20
		AND l_quantity <= 20 + 10
		AND p_size BETWEEN 1 AND 15
		AND l_shipmode IN ('AIR', 'AIR REG')
		AND l_shipinstruct = 'DELIVER IN PERSON');

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 'forest%'
			)
			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 '1994-01-01'
					AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' YEAR
			)
	)
	AND s_nationkey = n_nationkey
	AND n_name = 'CANADA'
ORDER BY s_name;

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 = 'SAUDI ARABIA'
GROUP BY s_name
ORDER BY numwait DESC, s_name
LIMIT 100;

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
		)
) custsale
GROUP BY cntrycode
ORDER BY cntrycode;