Ⅰ SQL code and analysis

Below is the SQL query statement:

select
    cntrycode,
    count(*) as numcust,
    sum(c_acctbal) as totacctbal
from (
    select
        substr(c_phone,1,2) as cntrycode,
        c_acctbal
    from
        customer
    where
        substr(c_phone,1,2) in ('11', '14', '15', '19', '20', '21', '23')
        and c_acctbal > (
            select
                avg(c_acctbal)
            from
                customer
            where
                c_acctbal > 0.00
                and substr(c_phone,1,2) in    ('11', '14', '15', '19', '20', '21', '23')
        )
        and not exists (
            select
                *
            from
                orders
            where
                o_custkey = c_custkey
        )
    ) custsale
group by
    cntrycode
order by
    cntrycode;

The main query’s outer layer is a regular grouping and aggregation operation, and its inner layer contains two conditional subqueries.

Ⅱ SPL solution

The two subqueries, where the second one is preceded by not exists, correspond to certain customer records meeting their respective condition. The final result should be the difference set of customer record sets corresponding to the two subqueries.

A5 selects records meeting condition specified in the first subquery. A6 associates with A5 and selects target records for the second subquery. A7 resets C_CUSTKEY values of all these selected records as null, which means deleting them from A5, in order to achieve the not exists condition on the second subquery. A8 selects records satisfying the two conditional subqueries.

Ⅲ Further optimization

1. Optimization method

In this example, we will use dimension table primary key numberization method explained in Q2 – customer table’s C_CUSTKEY field and orders table’s O_CUSTKEY field have been converted in the previous examples.

2. Code for data conversion

Copy customer_18.ctx and orders_21.ctx, and rename them customer_22.ctx and orders_22.ctx respectively.

3. Code after data conversion

First, we need to preload the dimension table. Below is preloading code:

Before performing the query, we need to first run the preloading code to load the small dimension table into memory.

Computing code:

Ⅳ Using enterprise edition’s column-wise computation

1. Original data

2. Optimized data

First, we need to preload the dimension table. Below is preloading code:

Before performing the query, we need to first run the preloading code to load the small dimension table into memory.

Computing code:

Ⅴ Test result

Unit: Second