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;

This is the grouping and aggregation operation after multiple tables are associated and filtered, which includes the association between the primary table orders and the sub table lineitem.

1. Data storage

Store the big tables orders and lineitem in order by the primary key orderkey, which makes it possible to merge them in order when joining. For other tables, there is no special sorting requirement, they are all stored in order by primary key.

So, we can continue to use orders.ctx, lineitem.ctx and customer.ctx from Q3.

For nation and supplier, we can directly use nation.btx and supplier.ctx from Q2.

Copy these tables to the main directory of this query.

2. General method

Calculation code:

Here we use the techniques mentioned in the previous articles: filter at cursor creation, order-based primary-sub table association, and convert join field to foreign key table pointers. Similar to Q5, A12 here also performs grouping on integer key values instead of strings, and then uses strings again in A13.

Test result:

3. Data conversion

Utilize the conversion methods mentioned in previous articles: convert primary keys of dimension tables to sequence numbers, convert date to integers.

For region, nation and supplier, we can directly use region_2.btx, nation_2.btx and supplier_2.ctx converted in Q2.

For customer, we can use customer_3.ctx from Q5.

For orders and lineitem, we can use orders_5.ctx and lineitem_5.ctx from Q3.

Copy these tables to the main directory of this query.

Calculation code:

A4, B5 and B7 are aligned sequence.

A11 replaces values of O_CUSTKEY and L_SUPPKEY with corresponding values of A5’s aligned sequence, i.e., 1 or 2, corresponding to CHINA and RUSSIA respectively. A12 uses the grouping key technique explained in Q1 to convert the grouping operation by three fields into one by a single filed.

After integerizing the date in A12, the year function can still be used to calculate the year to which the date belongs.

Test result:

4. Column-wise computing

For column-wise computing, try to avoid using switch() function. Therefore, instead of using O_CUSTKEY:B7:# that performs association and filtering simultaneously, A9 uses B7(O_CUSTKEY) to filter data only.

A11 first uses O_CUSTKEY to find the corresponding NATIONKEY from customer in B7, and then takes the value of A4. To achieve this, the value of aligned sequence calculated in B7 will be NATIONKEY when the condition is satisfied, or null when the condition is not satisfied.

The association between lineitem and supplier is handled similarly to that of orders and customer.

Test result: