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 = 'CHINA' and n2.n_name = 'RUSSIA')
                or (n1.n_name = 'RUSSIA' and n2.n_name = 'CHINA')
            )
            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;

这就是多表关联过滤后的分组聚合运算，其中包括主子表 orders 和 lineitem 关联。

1. 数据存储

大表 orders、lineitem 按照主键 orderkey 有序存储，连接的时候可以有序归并。其他表没有特殊的排序要求，就按照主键有序存储。

这样，就可以继续使用题目 Q3 中的 orders.ctx、lineitem.ctx、customer.ctx。

nation、supplier 表直接使用题目 Q2 的 nation.btx 和 supplier.ctx。

将这些表复制到本题的主目录中。

2. 一般实现

计算代码：

这里使用了前面题目中说过的游标建立时过滤、主子表有序关联和将关联字段转换成外键表指针的技巧，在 A12 中也是类似 Q5 中用整数键值代替字符串分组，在 A13 中再替换回来。

测试结果：

3. 数据变换

数据变换要采用前面题目提到的维表主键序号化、日期整数化手段。

region、nation、supplier 表，可以直接使用以前的题目 Q2 转换的 region_2.btx，nation_2.btx 和 supplier_2.ctx。

customer 使用 Q5 中的 customer_3.ctx。

orders、lineitem 表使用 Q3 中的 orders_5.ctx、lineitem_5.ctx。

将这些表复制到本题的主目录中。

计算代码：

A4、B5、B7 是对位序列。

A11 中把 O_CUSTKEY 和 L_SUPPKEY 替换成 A5 对位序列中的值，即 1 或 2，分别对应 CHINA 和 RUSSIA。A12 中使用 Q1 中介绍的分组键技巧，把按 3 个字段分组转变成按一个字段分组。

A12 中日期整数化以后仍然可以用 year 函数计算出它所属年份。

测试结果：

4. 列式计算

在列式计算中，要尽量避免使用 switch 操作。A9 中使用 B7(O_CUSTKEY) 仅仅过滤数据，而不再使用 O_CUSTKEY:B7:# 这种关联与过滤同时进行的方法。

A11 中先用 O_CUSTKEY 在 B7 中的 customer 数据找到对应的 NATIONKEY，然后再取得 A4 的值。为了达到这个目的，在 B7 中计算的对位序列中，满足条件的是 NATIONKEY，不满足条件的是 null。

对于 lineitem 和 supplier 的关联，也是和 orders、customer 类似的处理。

测试结果：