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 'bisque%'
            )
            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 '1995-01-01'
                    and l_shipdate < date '1995-01-01' + interval '1' year
            )
    )
    and s_nationkey = n_nationkey
    and n_name = 'CHINA'
order by
    s_name;

这个主查询很简单，但过滤条件很复杂，嵌套了好几层。

过滤条件主要是针对 supplier 表的 S_SUPPKEY 字段。这个表相对小，只要计算出符合条件的 SUPPKEY 集合，就容易得到 S_NAME，S_ADDRESS。可以分步骤来计算 SUPPKEY 集合，思路就清楚了。

原 SQL 是用 partsupp 过滤 supplier，调整为用 supplier 过滤 partsupp，得到的 PS_SUPPKEY 集合相同。partsupp 表较大，把 supplier 和 part 一起当成维表来过滤事实表 partsupp 更有利于性能。计算步骤为：

1. 按条件过滤 nation 表，结果再去过滤 supplier 表。

2. 按条件过滤 part 表。

3. 用 1 和 2 的结果当作外键表去匹配过滤 partsupp 表。

这三个步骤可以看做是一个视图 v1，写成 SQL 是这样的：

select
	ps_suppkey
from
	partsupp,
	(
		select
			p_partkey
		from
			part
		where
			p_name like 'bisque%'
	),
	(
		select
			s_suppkey
		from
			supplier,nation
		where
			s_nationkey = n_nationkey
			and n_name = 'CHINA'
	)
where
	ps_partkey=p_partkey
	and ps_suppkey=s_suppkey

有了 v1 后，原 SQL 变成这样：

select
    s_name,s_address
from
    supplier,
	(
	    select
            distinct ps_suppkey
        from
            v1
        where
            ps_availqty > (
                select
                    0.5 * sum(l_quantity)
                from
                    lineitem
                where
                    l_partkey = ps_partkey
                    and l_suppkey = ps_suppkey
                    and l_shipdate >= date '1995-01-01'
                    and l_shipdate < date '1995-01-01' + interval '1' year
            ) 
	)  q_suppkey
where s_suppkey=ps_suppkey；

观察子查询 q_suppkey，v1 和 lineitem 的计算属于子查询内用等值条件与主表关联的情况，可以用前面题目介绍的方法，转换成 v1 和 lineitem 的 JOIN 计算，转换后，这句 SQL 可以再变成这样：

select
    s_name,s_address
from
    supplier,
	(
		select
			distinct ps_suppkey
		from
			v1,
			(select 
				l_partkey,
				l_suppkey,
				0.5 * sum(l_quantity) quantity
			from 
				lineitem
			where
				l_shipdate >= date '1995-01-01'
				and l_shipdate < date '1995-01-01' + interval '1' year
			group by 
				l_partkey,
				l_suppkey
			) 
		where
			ps_partkey = l_partkey
			and ps_suppkey = l_suppkey
			and ps_availqty > quantity
	)  q_suppkey
where s_suppkey=ps_suppkey；

接下来，还要继续改造子查询 q_suppkey。lineitem 表很大，要先和 v1 连接，过滤掉不符合条件的记录，然后再分组汇总，最后过滤出 L_SUPPKEY 集合相同。SQL 再改写成：

select
    s_name,s_address
from
    supplier,
	(
		select
			distinct l_suppkey
		from
			(select 
				l_partkey,
				l_suppkey,
				ps_availqty,
				0.5 * sum(l_quantity) quantity
			from 
				lineitem,v1
			where
				ps_partkey = l_partkey
				and ps_suppkey = l_suppkey
				and l_shipdate >= date '1995-01-01'
				and l_shipdate < date '1995-01-01' + interval '1' year
			group by 
				l_partkey,
				l_suppkey,
				ps_availqty
			) 
		where
			ps_availqty > quantity
	) suppkey
where s_suppkey=l_suppkey；

1. 数据存储

参加计算的表都没有特殊的有序要求，按照主键有序存储。

继续使用题目 Q3 中的 lineitem.ctx 和 Q2 的 part.ctx、supplier.ctx、partsupp.ctx、nation.btx，复制到本题的主目录中。

2. 一般实现

计算代码：

A9 相当于计算出了前面说的视图 v1。

A8 取出 supplier 表的 S_SUPPKEY 的同时，也取出了 S_NAME，S_ADDRESS，可以避免 A14 再次读取。

A13 相当于计算出了子查询 q_suppkey。

A12 中使用了前面题目介绍的冗余分组键，PS_AVAILQTY 不再作为分组字段，可以提高性能。

A13 将 sum(L_QUANTITY)*0.5 改成 PS_AVAILQTY*2，L_QUANTITY、PS_AVAILQTY 都是整数，这样做可以将小数乘法变成整数乘法。

A14 中使用 join@im，选项 @i 表示删除关联不上的记录，选项 @m 表示 S_SUPPKEY 和 A13 都有序时，用有序归并连接加快速度。

测试结果：

3. 数据转换

本题中要使用前面题目介绍的维表主键序号化方法和日期整数化优化方法，将 Q2 中的 part_2.ctx、supplier_2.ctx、partsupp_2.ctx、nation_2.btx 和 Q14 中的 lineitem_14_4.ctx 复制到本题的主目录中。

计算代码：

A7、B8、B10、B11 都是对位序列。其中，A10 的值是 null 或者 supplier 的记录，这样在 A17 中就可以直接使用记录了，不必再次读取。

测试结果：

4. 列式计算

计算代码：

测试结果：