性能优化案例课程 TPCH-Q7
select
supp_nation,
cust_nation,
l_year,
sum(volume) as revenue
from
(
select
n1.n_name as supp_nation,
n2.n_name as cust_nation,
extract(year from l_shipdate) as l_year,
l_extendedprice * (1 - l_discount) as volume
from
supplier,
lineitem,
orders,
customer,
nation n1,
nation n2
where
s_suppkey = l_suppkey
and o_orderkey = l_orderkey
and c_custkey = o_custkey
and s_nationkey = n1.n_nationkey
and c_nationkey = n2.n_nationkey
and (
(n1.n_name = 'CHINA' and n2.n_name = 'RUSSIA')
or (n1.n_name = 'RUSSIA' and n2.n_name = 'CHINA')
)
and l_shipdate between date '1995-01-01' and date '1996-12-31'
) shipping
group by
supp_nation,
cust_nation,
l_year
order by
supp_nation,
cust_nation,
l_year;
这就是多表关联过滤后的分组聚合运算，其中包括主子表 orders 和 lineitem 关联。
1. 数据存储
大表 orders、lineitem 按照主键 orderkey 有序存储，连接的时候可以有序归并。其他表没有特殊的排序要求，就按照主键有序存储。
这样，就可以继续使用题目 Q3 中的 orders.ctx、lineitem.ctx、customer.ctx。
nation、supplier 表直接使用题目 Q2 的 nation.btx 和 supplier.ctx。
将这些表复制到本题的主目录中。
2. 一般实现
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
1995-01-01 |
1996-12-31 |
3 |
>name1="CHINA" |
>name2="RUSSIA" |
4 |
=file("nation.btx").import@b().keys@i(N_NATIONKEY) |
|
5 |
>n1=A4.select@1(N_NAME==name1).N_NATIONKEY |
|
6 |
>n2=A4.select@1(N_NAME==name2).N_NATIONKEY |
|
7 |
=file("supplier.ctx").open().cursor@m(S_SUPPKEY,S_NATIONKEY;S_NATIONKEY==n1 || S_NATIONKEY==n2).fetch().keys@im(S_SUPPKEY) |
|
8 |
=file("customer.ctx").open().cursor@m(C_CUSTKEY,C_NATIONKEY;C_NATIONKEY==n1 || C_NATIONKEY==n2).fetch().keys@im(C_CUSTKEY) |
|
9 |
=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_CUSTKEY;O_CUSTKEY:A8) |
|
10 |
=file("lineitem.ctx").open().news(A9,L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT,L_SHIPDATE,O_CUSTKEY;L_SHIPDATE>=A2 && L_SHIPDATE <=B2,L_SUPPKEY:A7) |
|
11 |
=A10.select(O_CUSTKEY.C_NATIONKEY!=L_SUPPKEY.S_NATIONKEY) |
|
12 |
=A11.groups(L_SUPPKEY.S_NATIONKEY:supp_nation,O_CUSTKEY.C_NATIONKEY:cust_nation,year(L_SHIPDATE):l_year; sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)): volume) |
|
13 |
=A12.run(supp_nation=A4.find(supp_nation).N_NAME,cust_nation=A4.find(cust_nation).N_NAME) |
|
14 |
=interval@ms(A1,now()) |
|
这里使用了前面题目中说过的游标建立时过滤、主子表有序关联和将关联字段转换成外键表指针的技巧，在 A12 中也是类似 Q5 中用整数键值代替字符串分组，在 A13 中再替换回来。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
13 |
3. 数据变换
数据变换要采用前面题目提到的维表主键序号化、日期整数化手段。
region、nation、supplier 表，可以直接使用以前的题目 Q2 转换的 region_2.btx，nation_2.btx 和 supplier_2.ctx。
customer 使用 Q5 中的 customer_3.ctx。
orders、lineitem 表使用 Q3 中的 orders_5.ctx、lineitem_5.ctx。
将这些表复制到本题的主目录中。
计算代码：
A |
B |
||
1 |
=now() |
||
2 |
=days@o(date("1995-01-01")) |
=days@o(date("1996-12-31")) |
|
3 |
[CHINA,RUSSIA] |
||
4 |
=file("nation_2.btx").import@b().(A3.pos(N_NAME)) |
||
5 |
=file("supplier_2.ctx").open() |
=A5.cursor@m().skip().(null) |
|
6 |
=A5.cursor@m(S_SUPPKEY,S_NATIONKEY;A4(S_NATIONKEY)).fetch().(B5(S_SUPPKEY)=S_NATIONKEY) |
||
7 |
=file("customer_3.ctx").open() |
=A7.cursor@m().skip().(null) |
|
8 |
=A7.cursor@m(C_CUSTKEY,C_NATIONKEY;A4(C_NATIONKEY)).fetch().(B7(C_CUSTKEY)=C_NATIONKEY) |
||
9 |
=file("orders_5.ctx").open().cursor@m(O_ORDERKEY,O_CUSTKEY;O_CUSTKEY:B7:#) |
||
10 |
=file("lineitem_5.ctx").open().news(A9,L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT,L_SHIPDATE,O_CUSTKEY;L_SHIPDATE>=A2 && L_SHIPDATE <=B2,L_SUPPKEY:B5:#) |
||
11 |
=A10.select(O_CUSTKEY!=L_SUPPKEY).run(O_CUSTKEY=A4(O_CUSTKEY),L_SUPPKEY=A4(L_SUPPKEY)) |
||
12 |
=A11.groups@u(year(L_SHIPDATE)*9+L_SUPPKEY*3+O_CUSTKEY:gk; sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)): volume) |
||
13 |
=A12.new(A3(gk%9\3):supp_nation,A3(gk%3):cust_nation,gk\9:l_year,volume).sort(supp_nation,cust_nation,l_year) |
||
14 |
=interval@ms(A1,now()) |
||
A4、B5、B7 是对位序列。
A11 中把 O_CUSTKEY 和 L_SUPPKEY 替换成 A5 对位序列中的值，即 1 或 2，分别对应 CHINA 和 RUSSIA。A12 中使用 Q1 中介绍的分组键技巧，把按 3 个字段分组转变成按一个字段分组。
A12 中日期整数化以后仍然可以用 year 函数计算出它所属年份。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
13 |
数据变换 |
9 |
4. 列式计算
A |
B |
|
1 |
=now() |
|
2 |
=days@o(date("1995-01-01")) |
=days@o(date("1996-12-31")) |
3 |
[CHINA,RUSSIA] |
|
4 |
=file("nation_2.btx").import@b().(A3.pos(N_NAME)) |
|
5 |
=file("supplier_2.ctx").open() |
=A5.cursor@m().skip().(null) |
6 |
=A5.cursor@m(S_SUPPKEY,S_NATIONKEY;A4(S_NATIONKEY)).fetch().(B5(S_SUPPKEY)=S_NATIONKEY) |
|
7 |
=file("customer_3.ctx").open() |
=A7.cursor@m().skip().(null) |
8 |
=A7.cursor@m(C_CUSTKEY,C_NATIONKEY;A4(C_NATIONKEY)).fetch().(B7(C_CUSTKEY)=C_NATIONKEY) |
|
9 |
=file("orders_5.ctx").open().cursor@mv(O_ORDERKEY,O_CUSTKEY;B7(O_CUSTKEY)) |
|
10 |
=file("lineitem_5.ctx").open().news(A9,L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT,L_SHIPDATE,O_CUSTKEY;L_SHIPDATE>=A2 && L_SHIPDATE<=B2 && B5(L_SUPPKEY)) |
|
11 |
=A10.derive@o(A4(B5(L_SUPPKEY)):s,A4(B7(O_CUSTKEY)):c).select@v(s!=c) |
|
12 |
=A11.groups@u(year(L_SHIPDATE)*9+c*3+s:gk; sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):volume) |
|
13 |
=A12.new(A3(gk%3):supp_nation,A3(gk%9\3):cust_nation,gk\9:l_year,volume).sort(supp_nation,cust_nation,l_year) |
|
14 |
=interval@ms(A1,now()) |
|
在列式计算中，要尽量避免使用 switch 操作。A9 中使用 B7(O_CUSTKEY) 仅仅过滤数据，而不再使用 O_CUSTKEY:B7:# 这种关联与过滤同时进行的方法。
A11 中先用 O_CUSTKEY 在 B7 中的 customer 数据找到对应的 NATIONKEY，然后再取得 A4 的值。为了达到这个目的，在 B7 中计算的对位序列中，满足条件的是 NATIONKEY，不满足条件的是 null。
对于 lineitem 和 supplier 的关联，也是和 orders、customer 类似的处理。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
13 |
数据变换 |
9 |
列式计算 |
4 |
英文版