select * from (
    select
        c_custkey,c_name,
        sum(l_extendedprice * (1 - l_discount)) as revenue,
        c_acctbal,n_name,c_address,c_phone,c_comment
    from
        customer,orders,lineitem,nation
    where
        c_custkey = o_custkey
        and l_orderkey = o_orderkey
        and o_orderdate >= date '1993-05-01'
        and o_orderdate < date '1993-05-01' + interval '3' month
        and l_returnflag = 'R'
        and c_nationkey = n_nationkey
    group by
        c_custkey,
        c_name,
        c_acctbal,
        c_phone,
        n_name,
        c_address,
        c_comment
    order by
        revenue desc
) where rownum <=20;

这是多表关联（其中包括主子表 orders 和 lineitem 关联）过滤后的分组聚合运算，然后按分组聚合值排序后取前 20 条记录。

1. 数据存储

大表 orders、lineitem 按照主键 orderkey 有序存储，连接的时候可以有序归并。其他表没有特殊的排序要求，就按照主键有序存储。

这样，就可以继续使用题目 Q3 中的 orders.ctx、lineitem.ctx、customer.ctx。

nation 表直接使用题目 Q2 的 nation.btx。

将这些表复制到本题的主目录中。

2. 一般实现

	A
1	=now()
2	1993-5-1
3	=elapse@m(A2,3)
4	=file("nation.btx").import@b().keys@i(N_NATIONKEY)
5	=file("customer.ctx").open().cursor@m(C_CUSTKEY,C_NAME,C_ADDRESS,C_NATIONKEY,C_PHONE,C_ACCTBAL,C_COMMENT;C_NATIONKEY:A4).fetch().keys@i(C_CUSTKEY)
6	=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_CUSTKEY;O_ORDERDATE>=A2 && O_ORDERDATE<A3,O_CUSTKEY:A5)
7	=file("lineitem.ctx").open().news@r(A6,sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):dp,O_CUSTKEY;L_RETURNFLAG=="R")
8	=A7.groups@u(O_CUSTKEY:c_custkey;sum(dp):revenue)
9	=A8.top(-20;revenue)
10	=A9.new(c_custkey.C_CUSTKEY,c_custkey.C_NAME,revenue,c_custkey.C_ACCTBAL,c_custkey.C_NATIONKEY.N_NAME,c_custkey.C_ADDRESS,c_custkey.C_PHONE,c_custkey.C_COMMENT)
11	=A10.sort@z(revenue)
12	=interval@ms(A1,now())

代码中用到了前面讲到过的游标前过滤、主子表有序归并、外键字段属性化等优化方法。

测试结果：

3. 大维表查找

我们发现，这个运算可以改造成两步，第一步把 orders 和 lineitem 表关联，完成针对 custkey 的分组汇总运算，然后再基于这个结果集继续和维表 customer 做关联运算。用 SQL 大致可以改写成这样：

select c_custkey,c_name,revenue,c_acctbal,n_name,c_address,c_phone,c_comment
from (
    select o_custkey, revenue
    from (
    	select o_custkey,
       	    sum(l_extendedprice * (1 - l_discount)) as revenue
    	from
            orders,lineitem
    	where
       	    l_orderkey = o_orderkey
        	    and o_orderdate >= date '1993-05-01'
        	    and o_orderdate < date '1993-05-01' + interval '3' month
        	    and l_returnflag = 'R'
            group by o_custkey
            order by revenue desc
    ) orders_lineitem 
    where rownum <=20
) orders_lineitem20,customer,nation
where
    c_custkey = o_custkey
    and c_nationkey = n_nationkey
order by revenue desc;

因为最终结果只要取 20 条记录，子查询 orders_lineitem20 也只要返回 20 条记录，然后用这 20 条记录和 cutstomer 表做后续的关联计算即可，而原来的方法相当于子查询 orders_lineitem 的每一条记录都要和 customer 表关联。

这是个小事实表 orders_lineitem20 和大维表 customer 连接的场景，可以采用大维表查找的方法，将连接转换为对 customer 表主键的批量查找。customer 表对主键 c_custkey 有序，可以快速的完成查找计算。

计算代码：

	A
1	=now()
2	1993-5-1
3	=elapse@m(A2,3)
4	=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_CUSTKEY;O_ORDERDATE>=A2 && O_ORDERDATE<A3)
5	=file("lineitem.ctx").open().news@r(A4,sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):dp,O_CUSTKEY;L_RETURNFLAG=="R")
6	=A5.groups@u(O_CUSTKEY:c_custkey;sum(dp):revenue)
7	=A6.top(-20;revenue)
8	=file("nation.btx").import@b().keys@i(N_NATIONKEY)
9	=file("customer.ctx").open()
10	=A7.joinx@q(c_custkey,A9:C_CUSTKEY,C_NAME,C_ADDRESS,C_NATIONKEY,C_PHONE,C_ACCTBAL,C_COMMENT)
11	=A10.switch(C_NATIONKEY,A8:N_NATIONKEY)
12	=A11.new(c_custkey:C_CUSTKEY,C_NAME,revenue,C_ACCTBAL,C_NATIONKEY.N_NAME,C_ADDRESS,C_PHONE,C_COMMENT)
13	=A12.sort@z(revenue)
14	=interval@ms(A1,now())

A7 算出来之后，用 joinx@q 从 customer 表中有序匹配快速地把相关记录取出来，不必将 customer 装入内存，也不必用 orders 表中的大量数据去连接 customer 表，减少数据读取量、计算量。

测试结果：

4. 数据变换

数据变换要采用前面题目提到的枚举型字符串字段数字化、维表主键序号化、日期整数化手段。

nation 表，可以直接使用以前的题目 Q2 转换的 nation_2.btx。

customer 使用 Q5 中的 customer_3.ctx。

orders 表使用 Q3 中的 orders_5.ctx。

将这些表复制到本题的主目录中。

Q1 虽然将 lineitem 中的 L_RETURNFLAG 转化成数字了，但 lineitem.ctx 没有主键，不满足有序归并的要求。需要在 Q3 的 lineitem.ctx 基础上做枚举字段数字化转换。

数据转换代码：

	A
1	=file("lineitem.ctx").open()
2	=A1.cursor(L_RETURNFLAG).id(L_RETURNFLAG).sort()
3	=file("l_returnflag.btx").export@b(A2)
4	=A1.cursor().run(L_RETURNFLAG =A2.pos@b(L_RETURNFLAG))
5	=file("lineitem4.ctx").create(#L_ORDERKEY,#L_LINENUMBER,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_RETURNFLAG,L_LINESTATUS,L_SHIPDATE,L_COMMITDATE,L_RECEIPTDATE,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT)
6	>A5.append(A4)

计算代码：

	A
1	=now()
2	1993-5-1
3	=days@o(elapse@m(A2,3))
4	=days@o(A2)
5	=file("nation_2.btx").import@b()
6	=file("l_returnflag.btx").import@b().(_1=="R")
7	=file("orders_5.ctx").open().cursor@m(O_ORDERKEY,O_CUSTKEY;O_ORDERDATE>=A4 && O_ORDERDATE<A3)
8	=file("lineitem4.ctx").open().news@r(A7,sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):dp,O_CUSTKEY;A6(L_RETURNFLAG))
9	=A8.groups@u(O_CUSTKEY:c_custkey;sum(dp):revenue)
10	=A9.top(-20;revenue)
11	=file("customer.ctx").open()
12	=A10.joinx@q(c_custkey,A11:C_CUSTKEY,C_NAME,C_ADDRESS,C_NATIONKEY,C_PHONE,C_ACCTBAL,C_COMMENT)
13	=A12.new(c_custkey:C_CUSTKEY,C_NAME,revenue,C_ACCTBAL,A5(C_NATIONKEY).N_NAME,C_ADDRESS,C_PHONE,C_COMMENT)
14	=A13.sort@z(revenue)
15	=interval@ms(A1,now())

A3、A4 是日期数字化，A6 是对位序列。

A13 的 nation 表使用了外键字段序号化。

测试结果：

5. 列式计算

	A
1	=now()
2	1993-5-1
3	=days@o(elapse@m(A2,3))
4	=days@o(A2)
5	=file("nation_2.btx").import@b()
6	=file("l_returnflag.btx").import@b().(_1=="R")
7	=file("orders_5.ctx").open().cursor@mv(O_ORDERKEY,O_CUSTKEY;O_ORDERDATE>=A4 && O_ORDERDATE<A3)
8	=file("lineitem4.ctx").open().news@r(A7,sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):dp,O_CUSTKEY;A6(L_RETURNFLAG))
9	=A8.groups@u(O_CUSTKEY:c_custkey;sum(dp):revenue)
10	=A9.top(-20;revenue)
11	=file("customer.ctx").open()
12	=A10.joinx@q(c_custkey,A11:C_CUSTKEY,C_NAME,C_ADDRESS,C_NATIONKEY,C_PHONE,C_ACCTBAL,C_COMMENT)
13	=A12.new(c_custkey:C_CUSTKEY,C_NAME,revenue,C_ACCTBAL,A5(C_NATIONKEY).N_NAME,C_ADDRESS,C_PHONE,C_COMMENT)
14	=A13.sort@z(revenue)
15	=interval@ms(A1,now())

测试结果：