create view revenue (supplier_no, total_revenue) as
    select
        l_suppkey,
        sum(l_extendedprice * (1 - l_discount))
    from
        lineitem
    where
        l_shipdate >= date '1995-04-01'
        and l_shipdate < date '1995-04-01' + interval '3' month
    group by
        l_suppkey;

select
    s_suppkey,
    s_name,
    s_address,
    s_phone,
    total_revenue
from
    supplier,
    revenue
where
    s_suppkey = supplier_no
    and total_revenue = (
        select
            max(total_revenue)
        from
            revenue
    )
order by
    s_suppkey;

drop view revenue;

This code is to generate a view revenue first, and then associate it with supplier, and finally select the record containing the maximum value.

1. Data storage

There is no special requirement for tables lineitem and supplier, they are stored in order by primary key.

We can continue to use lineitem.ctx from Q3 and supplier.ctx from Q2.

Copy these tables to the main directory of this query.

2. General method

We divide this query into two phases. The first phase generates a view revenue, and the second phase gets the record where total_revenue is the biggest from revenue. The former is a regular grouping & aggregation operation, where parallel processing is used to improve performance.

SPL offers A.maxp method that can directly return the record containing the maximum value. Since the number of records corresponding to the maximum value will not be too large, the big dimension table search method mentioned in Q10 can be adopted.

The code utilizes optimization methods mentioned in previous articles, such as dimension table attributization, multi-thread parallel processing.

A8 uses the big dimension table search method to implement the join between A6 and big dimension table supplier.

Test result:

3. Data conversion

There is a need to convert the L_SHIPDATE in lineitem table to integer as described in previous articles, and convert the primary key of lineitem’s dimension table supplier to sequence number so as to perform sequence-number-based grouping. The sequence-numberization of dimension table primary key needs to be performed based on the lineitem.ctx in Q3 and the supplier.ctx in Q2.

In addition, it also needs to copy the supplier_2.ctx that has performed sequence-numberization in Q2 to the main directory of this query.

Code for data conversion:

Calculation code:

The groups@n in A6 refers to the sequence-number-based grouping mentioned in the previous article.

Test result:

4. Column-wise computing

Calculation code:

Test result: