select
    sum(l_extendedprice) / 7.0 as avg_yearly
from
    lineitem,part
where
    p_partkey = l_partkey
    and p_brand = 'Brand#33'
    and p_container = 'LG DRUM'
    and l_quantity < (
        select
            0.2 * avg(l_quantity)
        From
            Lineitem
        Where
            l_partkey = p_partkey
);

Such an association with the primary table according to the equivalence condition in the subquery can always be converted to a JOIN operation, and the SQL statement can be rewritten as:

select
    sum(l_extendedprice) / 7.0 as avg_yearly
from
    lineitem,part,
    (select
        l_partkey lp,
        0.2*avg(l_quantity) lq
from
        lineitem
    group by
        l_partkey
    ) lpq
where
    p_partkey = l_partkey
    and p_brand = 'Brand#33'
    and p_container = 'LG DRUM'
    and l_partkey = lp
    and l_quantity < lq;

The subquery lpq is a grouping & aggregation operation, the results of which can be regarded as a table with lp as the primary key. Thus, the main query is equivalent to an aggregation operation on the result set of associating the fact table lineitem with the dimension tables lpq and part.

1. Data storage

There is no special requirement for tables lineitem and part, store them in order by primary key.

Continue to use lineitem.ctx from Q3 and part.ctx from Q2. Copy them to the main directory of this query.

2. General method

Calculation code:

A5 and A7 adopt the pre-cursor filtering technology, with the filter condition being search by primary key.

Test result:

3. Data conversion

For this query, we need to use the dimension table primary key sequence-numberization method. Copy lineitem_4.ctx from Q8 and part_2.ctx from Q16 to the main directory of this query.

In the part_2.ctx of Q16, the enumeration string field p_brand has been converted to numbers. We need to convert the enumeration string field p_container to numbers.

Code for data conversion:

Calculation code:

A6 is an alignment sequence, and its value is true or false.

A9 updates the value of the alignment sequence A6 to false or avg*0.2. Thus, in A10, the L_PARTKEY of the record that meets the condition will be assigned a value of avg*0.2, allowing it to participate in subsequent calculations in A11.

Test result:

After data conversion, the speed is not improved obviously. The reason is that the filter condition of pre-cursor filtering technology used in previous section is a search calculation by primary key, the performance of which is not much different from that of the alignment sequence used in this section.

4. Column-wise computing

Calculation code:

Since the columnar cursor cannot use switch to assign values to fields, the filter condition in A10 is modified from L_PARTKEY:B5:# in the previous section as B5(L_PARTKEY), which can only implement filtering but not assignment. A11needstouseB5(L_PARTKEY) to calculate again.

Test result: