InnoDB 让 MySQL 流行，DuckDB 使其伟大
司马辽太杰约 20 分钟作者介绍
司马辽太杰，10 余年互联网、金融、运营商等行业数据库管理经验，擅长常见关系型、NoSQL、MPP 、云原生等类型数据库的架构设计和运维管理。工作之余，热爱历史、足球，也喜欢读点闲书。欢迎关注我的个人公众号“程序猿读历史”。
MySQL 现状：事务很稳，分析很痛
事务很稳
这两年 MySQL 在全球热度持续下降，但把时间拉到过去二十余年，无疑是全球最成功、影响力最广泛的数据库产品之一，得益于其接入层、执行引擎层与存储引擎层的清晰分层设计，以及线程执行模型，MySQL 在互联网应用中展现出了极高的稳定性和性能。
互联网的高并发、小事务的业务模型，使 MySQL 成为其事实标准。尤其是在 InnoDB 引擎逐步成熟之后，事务一致性、行级锁以及并发控制的完善支持，进一步巩固了在互联网在线事务处理领域的统治地位。
分析很痛
MySQL的AP能力一直为行业诟病。从存储结构上看，InnoDB 行存 、BTree 适合高选择性访问，不擅长大范围顺序扫描；从执行模型上看，MySQL 的执行引擎以 标量执行、瀑布式处理为主，难以充分利用现代 CPU 的 SIMD 能力； 从优化器策略上看，其核心假设仍然围绕 OLTP 工作负载展开，在复杂 Join 顺序、统计信息失真以及大表关联时，容易产生非最优执行计划。
这些都决定了MySQL不具备AP能力的数据库，实际上更严重的是，分析 SQL 带来的问题并不仅限于自身执行时间：
大范围扫描会污染 Buffer Pool，挤出 OLTP 热数据
长查询持有资源时间过长，放大锁与调度竞争
CPU 与 IO 被分析任务占用，事务延迟抖动明显
在生产环境中，这种分析拖垮交易的风险。而另一方面，今天的企业用户对数据库是既要、又要、还要。
用户需求：既要、又要、还要
随着数据库技术与开源生态日趋成熟，用户的数据库选型不再局限于少数产品。据 DB-Engines 统计，全球数据库产品已超 400 种，国内墨天轮统计亦达 200 种以上。选择愈发丰富，企业对数据库的诉求也升级为既要、又要、还要。
既要混合负载
在实际业务中，企业通常既要支撑实时交易 OLTP，又要支撑分析报表OLAP。比如笔者曾经维护的电商系统：
商品数据需要在下单等交易环节使用，又要用于分析。
订单数据不仅用于支付、履约，还需要生成经营报表统计。
用户行为数据，要实时支撑业务决策，也要做离线或准实时分析。
MySQL 缓冲池同时缓存数据和索引，OLAP 扫描会占用大量内存，降低事务命中率；优化器对大表 join、聚合等分析任务优化有限，hash join 至今也无法解决内存溢出时退化 nested loop join 或者 sort merge join ；日志和持久化机制也会与分析查询争用磁盘带宽等等问题。
目前常见的做法是在 MySQL 实现交易数据，再通过 ETL 将数据搬到分析系统。这种方式存在明显问题：数据延迟高、链路复杂、同步过程中容易出现不一致，运维成本也大。因此，无论从业务要求，以及系统性能、稳定性、总拥有成本等多个角度看，在一个数据库中解决这两类需求是最佳实践。
又要资源隔离
与此同时，用户并不希望分析查询与在线交易操作争抢同一份资源。在生产系统中，为了在线交易系统的稳定性，往往会要求做好两则的资源隔离。理想状态下：
OLTP 工作负载应保持稳定、可预测
OLAP 查询即便执行缓慢，也不应反向影响交易
这意味着，单纯依使用一个引擎同时做好两件事，在工程挑战极大。而用户需要的是在数据一致的前提下， 将分析负载和交易负载分离开来的能力。因此，两种类型的资源隔离成了用户选择的基础。
还要兼容 MySQL
站在企业视角兼容MySQL 是一个自然而然的能力。一方面，存量系统中已经沉淀了大量基于 MySQL 语法和行为特性的 SQL。无论是复杂的业务查询、历史兼容写法，还是隐含依赖执行计划的灰色 SQL，都很难在短时间内系统性改写。
另一方面，应用侧并不只是连着一个数据库。ORM 框架、数据库中间件、连接池、SQL 解析与路由组件，普遍假设后端是一个 MySQL 协议与语义完整实现的系统。哪怕 SQL 层面差异不大，协议、返回结果、事务行为上的细微不一致，也可能引发连锁问题。
同时，组织层面的技术能力也是基于MySQL 而构建。现有的监控指标、告警策略、故障处理流程，以及 DBA 和研发团队长期积累的经验，几乎都围绕 MySQL 构建。彻底替换数据库，意味着不仅要迁移数据和应用，还要重建一整套稳定性保障体系。
因此，在大多数企业里是否替换 MySQL并不是一个可以自由讨论的问题。通常只能是在不改变 MySQL 使用方式的前提下，引入新的能力。
DuckDB：小身材、大算力
DuckDB 是一个 In-Process 的 OLAP 嵌入式数据库，常被称为 OLAP 的 SQLite，采用 MIT 开源协议¹。开源地址：
https://github.com/duckdb/duckdb2019年，由荷兰 Centrum Wiskunde & Informatica（CWI） 数据库组发布（CWI数据库组非常牛逼，曾推出过 MonetDB、Vectorwise 等顶级数据库产品）。同年，DuckDB 的核心设计与实现以论文 《DuckDB: An Embedded Analytical Database》 被 SIGMOD 2019 正式收录，系统性地介绍了其整体架构与关键技术原理 ²。
小身材
DuckDB 是以轻巧、敏捷著称，安装部署不需独立服务器，可直接集成到应用程序中，启动迅速、资源占用极低，单个二进制文件仅几十MB。笔者本地部署的duckdb 二进制文件仅为55MB，相比于 MySQL 8.0 的二进制文件则有1.3GB，相差25倍。
[root@centos8-03 duckdb]# du -sh duckdb
55M duckdb
[root@centos8-03 duckdb]#
[root@centos8-03 duckdb]#
[root@centos8-03 mysql]#cd /usr/local/mysql
[root@centos8-03 mysql]# du -sh bin
1.3G bin
[root@centos8-03 mysql]#
DuckDB 没有复杂的集群管理、没有独立的服务进程，甚至可以直接以内嵌库的形式运行在应用进程中。这使 DuckDB 在部署、运维与资源使用上，具备极低的门槛，适合作为 分析侧的计算引擎而存在。但这种小身材的背后，并不是性能的缺失。
大算力
与小身材形成鲜明对比的是Duckdb大算力。根据DuckDB的官方说明，随机生成了1亿数据，不确定的多列筛选条件，DuckDB 在使用 Morton 和 Hilbert 算法排序，查询在毫秒级 ³。DuckDB 采用：
列式存储
向量化执行引擎
批量算子处理
现代 CPU 友好的内存布局
在扫描、过滤、聚合、Join 等典型分析算子上，DuckDB 能够充分利用 CPU 缓存与 SIMD 指令集，将每一次内存访问的价值最大化。更重要的是，DuckDB 的设计假设与 MySQL 截然不同：它并不试图兼顾高并发写入与复杂事务，而是将重心放在 读密集、大计算量、复杂 SQL 的执行效率上。这使得两则成为一个非常理想的互补角色。
AliSQL的就要、又要、还要
最近阿里云发布了 AliSQL DuckDB 项目，通过DuckDB的极致性能、灵活以及兼容性，以及AliSQL的极致单机性能、稳定性，满足用户既要、又要、还要的需求。
AliSQL 即 阿里云 RDS MySQL 的开源品牌，最初源于淘宝数据库团队。2018年后，阿里云主推云原生数据库 PolarDB ，AliSQL 沉寂了数年，甚至开源版本多年以来一直停留在 MySQL 5.6.32 。最近，AliSQL以王者之势归来，开源版本直接跃升至 8.0.44，并融入了 DuckDB 和向量检索能力。⁴
AliSQL Duckdb 架构图
AliSQL 在阿里旗下淘宝、支付宝的双十一超高并发事务、稳定性上表现卓越，却难以承载复杂分析负载，一旦运行报表或大查询，极易拖垮核心交易系统。今天，AliSQL 通过内置 DuckDB，实现了 一套数据、两个引擎、物理隔离，100% 兼容 MySQL 协议与语法，应用无需修改一行代码，真正实现既要、又要、还要的企业级混合负载需求。
AliSQL DuckDB 的实际性能究竟如何？我们基于 TPCH 基准，对 RDS MySQL 集成的 DuckDB 引擎展开实测验证。
AliSQL DuckDB 测试结果
使用TPCH 测试阿里云RDS MySQL DuckDB OLAP的性能，并对比 innodb 和duckdb 在分析场景下的性能差距。其中 RDS 规格是 8C16G300GB 高性能磁盘，DuckDB 作为只读分析实例。
测试结论
TPCH的22个SQL，RDS MySQL InnoDB 22条SQL 接近4000秒。而 duckdb 22 条SQL 一共耗时48.218 秒，相差约2个数量级。实际上不具备可比性。
测试方法
TPCH
TPC-H 是国际事务处理性能委员会（TPC）发布的面向决策支持系统的标准基准测试，主要用于评估数据库在复杂分析型查询场景下的性能。本次测试采用 TPC-H v3.0.1 版本，数据规模为 100GB，共包含 22 条分析型查询，不涉及数据更新或事务操作。⁵
测试模型
region：区域表
nation：国家表
supplier：供应商表
customer：客户表
part：商品表
partsupp：供应商物件表
orders：订单表，
lineitem：订单明细表
构建数据集
本次在客户端机器上安装TPCH服务，安装过程略。TPC-H测试的数据量大小为100GB，最大的 lineitem 表数据量约6亿条。构建命令如下：
./dbgen -s 100 -f -v 详细的测试过程，见文末附录。
总结：鱼和熊掌 MySQL 都要
过去很长时间，在MySQL 生态开源数据库世界中，OLTP和OLAP如同鱼与熊掌难以兼得。传统的ETL方案，不仅提高了使用门槛，也带来了数据延迟、链路复杂与运维负担。
AliSQL DuckDB 项目出现，提供了一条新颖解决方案。对应用而言，一切访问习惯、协议接口乃至运维体系都保持不变；对数据而言，无需经历漫长的抽取、转换与加载，即可享受专用引擎带来的算力飞跃。真正实现了企业级用户对 MySQL 既要、又要、还要的需求。
有需求和兴趣的同学，建议下载试用以及POC，大概率会爱上它。
引用
1、https://github.com/duckdb/duckdb
2、https://duckdb.org/pdf/SIGMOD2019-demo-duckdb.pdf
3、https://duckdb.org/2025/06/06/advanced-sorting-for-fast-selective-queries
4、https://github.com/alibaba/AliSQL/wiki
5、http://www.tpc.org/tpch
创建表
CREATE TABLE region (
r_regionkey INT NOT NULL,
r_name CHAR(25),
r_comment VARCHAR(152),
PRIMARY KEY (r_regionkey)
) ENGINE=InnoDB;
CREATE TABLE nation (
n_nationkey INT NOT NULL,
n_name CHAR(25),
n_regionkey INT,
n_comment VARCHAR(152),
PRIMARY KEY (n_nationkey)
) ENGINE=InnoDB;
CREATE TABLE supplier (
s_suppkey BIGINT NOT NULL,
s_name CHAR(25),
s_address VARCHAR(40),
s_nationkey INT,
s_phone CHAR(15),
s_acctbal DECIMAL(15,2),
s_comment VARCHAR(101),
PRIMARY KEY (s_suppkey)
) ENGINE=InnoDB;
CREATE TABLE part (
p_partkey BIGINT NOT NULL,
p_name VARCHAR(55),
p_mfgr CHAR(25),
p_brand CHAR(10),
p_type VARCHAR(25),
p_size INT,
p_container CHAR(10),
p_retailprice DECIMAL(15,2),
p_comment VARCHAR(23),
PRIMARY KEY (p_partkey)
) ENGINE=InnoDB;
CREATE TABLE partsupp (
ps_partkey BIGINT NOT NULL,
ps_suppkey BIGINT NOT NULL,
ps_availqty INT,
ps_supplycost DECIMAL(15,2),
ps_comment VARCHAR(199),
PRIMARY KEY (ps_partkey, ps_suppkey)
) ENGINE=InnoDB;
CREATE TABLE customer (
c_custkey BIGINT NOT NULL,
c_name VARCHAR(25),
c_address VARCHAR(40),
c_nationkey INT,
c_phone CHAR(15),
c_acctbal DECIMAL(15,2),
c_mktsegment CHAR(10),
c_comment VARCHAR(117),
PRIMARY KEY (c_custkey)
) ENGINE=InnoDB;
CREATE TABLE orders (
o_orderkey BIGINT NOT NULL,
o_custkey BIGINT,
o_orderstatus CHAR(1),
o_totalprice DECIMAL(15,2),
o_orderdate DATE,
o_orderpriority CHAR(15),
o_clerk CHAR(15),
o_shippriority INT,
o_comment VARCHAR(79),
PRIMARY KEY (o_orderkey)
) ENGINE=InnoDB;
CREATE TABLE lineitem (
l_orderkey BIGINT NOT NULL,
l_partkey BIGINT,
l_suppkey BIGINT,
l_linenumber INT,
l_quantity DECIMAL(15,2),
l_extendedprice DECIMAL(15,2),
l_discount DECIMAL(15,2),
l_tax DECIMAL(15,2),
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),
PRIMARY KEY (l_orderkey, l_linenumber)
) ENGINE=InnoDB;
导入数据
通过RDS 集群地址导入，innodb和duckdb引擎会自动活得数据。
load data local infile '/data/tpch-v3/dbgen/customer.tbl' into table customer fields terminated by '|';
load data local infile '/data/tpch-v3/dbgen/lineitem.tbl' into table lineitem fields terminated by '|';
load data local infile '/data/tpch-v3/dbgen/nation.tbl' into table nation fields terminated by '|';
load data local infile '/data/tpch-v3/dbgen/orders.tbl' into table orders fields terminated by '|';
load data local infile '/data/tpch-v3/dbgen/partsupp.tbl' into table partsupp fields terminated by '|';
load data local infile '/data/tpch-v3/dbgen/part.tbl' into table part fields terminated by '|';
load data local infile '/data/tpch-v3/dbgen/region.tbl' into table region fields terminated by '|';
load data local infile '/data/tpch-v3/dbgen/supplier.tbl' into table supplier fields terminated by '|';
测试SQL
-- 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 <= CAST('1998-09-02' AS date)
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 = 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;
-- Q3
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 < CAST('1995-03-15' AS date)
AND l_shipdate > CAST('1995-03-15' AS date)
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 >= CAST('1993-07-01' AS date)
AND o_orderdate < CAST('1993-10-01' AS date)
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 = 'ASIA'
AND o_orderdate >= CAST('1994-01-01' AS date)
AND o_orderdate < CAST('1995-01-01' AS date)
GROUP BY
n_name
ORDER BY
revenue DESC;
-- Q6
SELECT
sum(l_extendedprice * l_discount) AS revenue
FROM
lineitem
WHERE
l_shipdate >= CAST('1994-01-01' AS date)
AND l_shipdate < CAST('1995-01-01' AS date)
AND l_discount BETWEEN 0.05 AND 0.07
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 = 'FRANCE'
AND n2.n_name = 'GERMANY')
OR (n1.n_name = 'GERMANY'
AND n2.n_name = 'FRANCE'))
AND l_shipdate BETWEEN CAST('1995-01-01' AS date)
AND CAST('1996-12-31' AS date)) 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 CAST('1995-01-01' AS date)
AND CAST('1996-12-31' AS date)
AND p_type = 'ECONOMY ANODIZED STEEL') 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 '%green%') 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 >= CAST('1993-10-01' AS date)
AND o_orderdate < CAST('1994-01-01' AS date)
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 = 'GERMANY'
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 = 'GERMANY')
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 ('MAIL', 'SHIP')
AND l_commitdate < l_receiptdate
AND l_shipdate < l_commitdate
AND l_receiptdate >= CAST('1994-01-01' AS date)
AND l_receiptdate < CAST('1995-01-01' AS date)
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 '%special%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 '1995-09-01'
AND l_shipdate < CAST('1995-10-01' AS date);
-- Q15
SELECT
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
FROM
supplier,
(
SELECT
l_suppkey AS supplier_no,
sum(l_extendedprice * (1 - l_discount)) AS total_revenue
FROM
lineitem
WHERE
l_shipdate >= CAST('1996-01-01' AS date)
AND l_shipdate < CAST('1996-04-01' AS date)
GROUP BY
supplier_no) revenue0
WHERE
s_suppkey = supplier_no
AND total_revenue = (
SELECT
max(total_revenue)
FROM (
SELECT
l_suppkey AS supplier_no,
sum(l_extendedprice * (1 - l_discount)) AS total_revenue
FROM
lineitem
WHERE
l_shipdate >= CAST('1996-01-01' AS date)
AND l_shipdate < CAST('1996-04-01' AS date)
GROUP BY
supplier_no) revenue1)
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 '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;
-- Q17
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 = 'MED BOX'
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) > 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;
-- Q19
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#23'
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#34'
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');
-- 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 '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 >= CAST('1994-01-01' AS date)
AND l_shipdate < CAST('1995-01-01' AS date)))
AND s_nationkey = n_nationkey
AND n_name = 'CANADA'
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 = 'SAUDI ARABIA'
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;