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 '%chocolate%'
    ) profit
group by
    nation,
    o_year
order by
    nation,
o_year desc;

This is a grouping and aggregation operation on the filtered result set of multi-table association, which involves a primary-sub table association between orders table and lineitem table and an association with the foreign key table partsupp having a two-field primary key.

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 tables part, supplier and partsupp, we can directly use part.ctx, supplier.ctx and partsupp.ctx from Q2.

For tables region and nation, we can directly use region.btx and nation.btx from Q2.

Copy these tables to the main directory of this query.

2. General method

There is no filtering on orders table. The filtering action only happens on lineitem table. The number of data rows after filtering is much less than that in orders table, so we handle the primary-sub table association differently from the previous example. First, A7 creates a lineitem table cursor. Then A8 filters the primary table orders according to the cursor. Then, the number of data rows retrieved is considerably decreased and efficiency is increased.

A9 uses join@i method to join the foreign key table’s PS_SUPPLYCOSTA field to the cursor. As the foreign key table has a two-field primary key, the method of matching and conversion at cursor creation becomes inapplicable.

A10 performs grouping on NATIONKEY instead of the string NAME, and A11 converts the grouping result to string.

3. Data conversion

Utilize the conversion methods mentioned in previous articles: convert primary key of dimension table to sequence number, convert date to integer.

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

To convert the enumeration string field type in the part table to number, we can use p_type.btx from Q2.

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

For the orders table, we can use orders_5.ctx from Q3.

For the lineitem table, we can use lineitem_4.ctx from Q8.

Copy these tables to the main directory of this query.

Calculation code:

A11 searches A5 for target records according to L_PARTKEY and L_SUPPKEY, and assigns L_PARTKEY as PS_SUPPLYCOST values – during which the current PS_SUPPLYCOST value is null if no corresponding A5 record is found and select() function will filter away the current record. This method has equal performance with the previously explained join@i method.

A13 uses the grouping key technique explained in Q1 to convert the grouping operation by two fields into one by a single field gk.

Test result:

4. Column-wise computing

For column-wise computing, try to avoid using switch() function. Therefore, instead of using switch to assign value to the existing column, A11 uses join to concatenate new column.

Test result: