性能优化案例课程 TPCH-Q2
select * from (
select s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment
from part,supplier,partsupp,nation,region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 25
and p_type like '%COPPER'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and ps_supplycost = (
select
min(ps_supplycost)
from
partsupp,
supplier,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
)
order by s_acctbal desc,n_name,s_name,p_partkey
)
where rownum <= 100;
where 条件中的子查询
select min(ps_supplycost) from…
涉及的表是 partsupp，supplier，nation，region。
外层 SQL 语句
select s_acctbal,s_name,…
相比多了 part 表。这就可以看出，子查询中的 p_partkey = ps_partkey 表示与外层语句关联的字段是 p_partkey。
为了更容易理解这个语句，可以新建一个视图 V：
select *
from part,partsupp,supplier,nation,region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and p_size = 25
and p_type like '%COPPER'
则原语句可以改写成：
select s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment
from V
where
ps_supplycost = (
select
min(ps_supplycost)
from
V V1
where
V.p_partkey = V1.p_partkey
)
这就把原查询变成了一个单表查询，相当于找出 V 中这样一些记录，使得这些记录的 ps_supplycost 值在所有与该记录的 partkey 值相同的记录中取值最小。
这个运算的本质是对 V 按 partkey 分组后对每组聚合，计算出每组中 ps_supplycost 最小的那些记录。但是，SQL 不支持这种聚合运算，于是只能写成子查询的情况。
1. 数据存储
这一题是遍历计算，大表 part、partsupp 外存成列式组表。Supplier 相对较大，也存成组表。
part、partsupp 两个表是主子表，要按照关联主键 partkey 排序。后续计算有较大结果集的分组，分组键恰好也是 partkey。那么数据按 partkey 有序存储时，在解决关联的同时，还能让分组运算的复杂度降低。
组表有一些索引信息，总会有一个最低的空间占用，不适合存储小数据表。因此小数据表 nation，region 采用行式的集文件，可以占用更少的磁盘空间，计算时全部读入。
对于较小的集文件和 supplier 组表，并没有特别的排序要求，就缺省都按照主键有序。
数据转换代码：
A |
|
1 |
=file("region.tbl").import(; , "|").new(_1:R_REGIONKEY, _2:R_NAME, _3:R_COMMENT).sort(R_REGIONKEY) |
2 |
=file("region.btx").export@b(A1) |
3 |
=file("nation.tbl").import(; , "|").new(_1:N_NATIONKEY, _2:N_NAME, _3:N_REGIONKEY, _4:N_COMMENT).sort(N_NATIONKEY) |
4 |
=file("nation.btx").export@b(A3) |
5 |
=file("supplier.tbl").cursor(; , "|").new(_1:S_SUPPKEY, _2:S_NAME, _3:S_ADDRESS, _4:S_NATIONKEY, _5:S_PHONE, _6:S_ACCTBAL, _7:S_COMMENT).sortx(S_SUPPKEY) |
6 |
=file("supplier.ctx").create@y(#S_SUPPKEY, S_NAME, S_ADDRESS, S_NATIONKEY, S_PHONE, S_ACCTBAL, S_COMMENT) |
7 |
>A6.append(A5) |
8 |
=file("part.tbl").cursor(; , "|").new(_1:P_PARTKEY, _2:P_NAME, _3:P_MFGR, _4:P_BRAND, _5:P_TYPE, _6:P_SIZE, _7:P_CONTAINER, _8:P_RETAILPRICE, _9:P_COMMENT).sortx(P_PARTKEY) |
9 |
=file("part.ctx").create@y(#P_PARTKEY, P_NAME,P_MFGR, P_BRAND, P_TYPE, P_SIZE, P_CONTAINER, P_RETAILPRICE, P_COMMENT) |
10 |
>A9.append(A8) |
11 |
=file("partsupp.tbl").cursor(; , "|").new(_1:PS_PARTKEY, _2:PS_SUPPKEY, _3:PS_AVAILQTY, _4:PS_SUPPLYCOST, _5:PS_COMMENT).sortx(PS_PARTKEY,PS_SUPPKEY) |
12 |
=file("partsupp.ctx").create@py(#PS_PARTKEY, #PS_SUPPKEY, PS_AVAILQTY,PS_SUPPLYCOST,PS_COMMENT) |
13 |
>A12.append(A11) |
2. 关联解决
先解决表之间的关联，也就计算出前面说的视图 V。
小维表可以采用预先加载、预先关联的方式。
加载代码：
A |
B |
|
1 |
=file("region.btx").import@b().keys@i(R_REGIONKEY) |
|
2 |
=file("nation.btx").import@b().keys@i(N_NATIONKEY) |
|
3 |
=file("supplier.ctx").open().import().keys@i(S_SUPPKEY) |
|
4 |
=A2.switch(N_REGIONKEY,A1) |
=A3.switch(S_NATIONKEY,A2) |
5 |
>env(region,A1),env(nation,A2),env(supplier,A3) |
|
A1 到 A3 预加载 region、nation、supplier 三个维表，建立带索引的主键。
A4、B4 实现维表间的预关联。
A5 将维表复制给全局变量，准备在后续计算中使用。
大表 part 和 partsupp 之间两个表是主子关系，采用有序归并的方式实现连接。
计算代码：
A |
B |
C |
|
1 |
=now() |
||
2 |
>size=25 |
>type="COPPER" |
>name="ASIA" |
3 |
=file("part.ctx").open().cursor@m(P_PARTKEY,P_MFGR;P_SIZE==size && pos@zh(P_TYPE,type)) |
||
4 |
=file("partsupp.ctx").open().cursor(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;PS_SUPPKEY:supplier;A3) |
||
5 |
=A4.joinx@im(PS_PARTKEY,A3:P_PARTKEY,P_MFGR) |
||
6 |
=A5.select(PS_SUPPKEY.S_NATIONKEY.N_REGIONKEY.R_NAME==name) |
||
7 |
for A6,10000 |
||
8 |
=interval@ms(A1,now()) |
||
A3 用前面题目讲到过的游标前过滤，在游标建立时使用过滤条件。
A4 把维表 supplier 连接到事实表 partsupp 上，PS_SUPPKEY:supplier 相当于 switch@i。
A5 对 part 和 partsupp 有序归并，把主表的字段拼接到子表上。
A7 中循环计算游标，并取数，计算时间。
这道题目完整的解法，是包括维表加载的。为了观察全部执行时间，后面的介绍就不采用预加载机制了。
包含维表加载的计算代码：
A |
B |
C |
|
1 |
=now() |
||
2 |
>size=25 |
>type="COPPER" |
>name="ASIA" |
3 |
=file("region.btx").import@b(R_REGIONKEY,R_NAME).keys@i(R_REGIONKEY) |
||
4 |
=file("nation.btx").import@b(N_NATIONKEY,N_NAME,N_REGIONKEY).keys@i(N_NATIONKEY) |
||
5 |
=file("supplier.ctx").open().import(S_SUPPKEY,S_NAME,S_ADDRESS,S_NATIONKEY,S_PHONE,S_ACCTBAL,S_COMMENT).keys@i(S_SUPPKEY) |
||
6 |
=A4.switch(N_REGIONKEY,A3) |
=A5.switch(S_NATIONKEY,A4) |
|
7 |
=file("part.ctx").open().cursor@m(P_PARTKEY,P_MFGR;P_SIZE==size && pos@zh(P_TYPE,type)) |
||
8 |
=file("partsupp.ctx").open().cursor(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;PS_SUPPKEY:A5;A7) |
||
9 |
=A8.joinx@im(PS_PARTKEY,A7:P_PARTKEY,P_MFGR) |
||
10 |
=A9.select(PS_SUPPKEY.S_NATIONKEY.N_REGIONKEY.R_NAME==name) |
||
11 |
for A10,10000 |
||
12 |
=interval@ms(A1,now()) |
||
预加载的时候，结果存成全局变量，可能给多种计算使用，所以取出全部字段。这里 A3、A4、A5 只取出需要的字段。
不采用预加载的测试结果：
测试项目 |
执行时间（秒） |
关联解决 |
12 |
3. 维表过滤
上节的方法是先连接维表再过滤，因为多条事实表记录会对应相同的维表记录，导致维表条件可能会被反复计算。如果先过滤维表，只剩下需要的记录，连接事实表的时候，一方面可以不必重复计算维表上的过滤条件，另一方面不满足条件的事实表记录直接在游标中过滤掉，能减少硬盘数据读取量和对象产生时间。
计算代码：
A |
B |
C |
|
1 |
=now() |
||
2 |
>size=25 |
>type="COPPER" |
>name="ASIA" |
3 |
=file("region.btx").import@b(R_REGIONKEY,R_NAME).keys@i(R_REGIONKEY) |
||
4 |
=file("nation.btx").import@b(N_NATIONKEY,N_NAME,N_REGIONKEY) |
||
5 |
=A4.switch(N_REGIONKEY,A3) |
||
6 |
=A4.select(N_REGIONKEY.R_NAME==name).derive@o().keys@i(N_NATIONKEY) |
||
7 |
=file("supplier.ctx").open().cursor@m(S_SUPPKEY,S_NAME,S_ADDRESS,S_NATIONKEY,S_PHONE,S_ACCTBAL,S_COMMENT;S_NATIONKEY:A6).fetch().keys@i(S_SUPPKEY) |
||
8 |
=file("part.ctx").open().cursor@m(P_PARTKEY,P_MFGR;P_SIZE==size && pos@zh(P_TYPE,type)) |
||
9 |
=file("partsupp.ctx").open().cursor(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;PS_SUPPKEY:A7;A8) |
||
10 |
=A9.joinx@im(PS_PARTKEY,A8:P_PARTKEY,P_MFGR) |
||
11 |
for A10,10000 |
||
12 |
=interval@ms(A1,now()) |
||
A6 对 supplier 的维表 nation 过滤。
A7 用过滤后的 nation 表连接、过滤 supplier 表，可以避免读取 supplier 中不合适的记录。
A9 用过滤后的 supplier 连接、过滤 partsupp 表，可以避免读取 partsupp 中不合适的记录。
测试结果：
测试项目 |
执行时间（秒） |
关联解决 |
12 |
维表过滤 |
7 |
4. 维表序号化
如果维表的主键是从 1 开始的自然数 (也就是记录所在行号)，那么就可以用键值直接按行号定位维表记录，而不再需要计算和比对 HASH 值了，从而加快与维表关联的速度。
直接用序号定位，维表不需要建立索引，占用内存也会小很多。
转换代码：
A |
B |
|
1 |
=file("region.btx").import@b() |
=A1.new(#:R_REGIONKEY,R_NAME,R_COMMENT) |
2 |
=file("region_2.btx").export@b(B1) |
=file("nation.btx").import@b() |
3 |
=B2.new(#:N_NATIONKEY, A1.pselect(R_REGIONKEY==N_REGIONKEY):N_REGIONKEY,N_NAME,N_COMMENT) |
|
4 |
=file("nation_2.btx").export@b(A3) |
|
5 |
=file("supplier.ctx").open().import().keys@i(S_SUPPKEY) |
|
6 |
=A5.new(#:S_SUPPKEY,S_NAME,S_ADDRESS,B2.pselect(N_NATIONKEY==S_NATIONKEY):S_NATIONKEY,S_PHONE,S_ACCTBAL,S_COMMENT) |
|
7 |
=file("supplier_2.ctx").create@y(#S_SUPPKEY, S_NAME, S_ADDRESS, S_NATIONKEY, S_PHONE, S_ACCTBAL, S_COMMENT) |
|
8 |
>A7.append(A6.cursor()) |
|
9 |
=file("part.ctx").open() |
=A9.cursor().id(P_TYPE).sort() |
10 |
=file("p_type.btx").export@b(B9) |
=A9.cursor().run(P_TYPE=B9.pos@b(P_TYPE)) |
11 |
=file("part_2.ctx").create@y(#P_PARTKEY, P_NAME,P_MFGR, P_BRAND, P_TYPE, P_SIZE, P_CONTAINER, P_RETAILPRICE, P_COMMENT) |
|
12 |
>A11.append@i(B10.cursor()) |
|
13 |
=file("partsupp.ctx").open().cursor() |
=A13.run(PS_SUPPKEY=A5.pfind(PS_SUPPKEY)) |
14 |
=file("partsupp_2.ctx").create@py(#PS_PARTKEY, #PS_SUPPKEY,PS_AVAILQTY, PS_SUPPLYCOST,PS_COMMENT) |
|
15 |
>A14.append(B13) |
|
A1-A8，将维表 region、nation、supplier 的主键转为序号，相应的外键字段也转为序号。
B9、A10 将用 id 函数得到 P_TYPE 等的去重取值列表，再排序存入 btx 文件成为维表，完成字符串字段的序号化。
B10-A12 完成 part 表的外键序号化。
A12-A15 完成 partsupp 表的外键序号化。
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
>size=25 |
>type="COPPER" |
3 |
=file("region_2.btx").import@b(R_REGIONKEY,R_NAME) |
|
4 |
=file("nation_2.btx").import@b(N_NATIONKEY,N_NAME,N_REGIONKEY) |
|
5 |
=file("p_type.btx").import@b().(_1) |
=file("supplier_2.ctx").open().import() |
6 |
=A4.switch(N_REGIONKEY,A3:#) |
=B5.switch(S_NATIONKEY,A4:#) |
7 |
=file("part.ctx").open().cursor@m(P_PARTKEY,P_MFGR;P_SIZE==size && pos@zh(P_TYPE,type)) |
|
8 |
=file("partsupp_2.ctx").open().cursor(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;PS_SUPPKEY:B6:#;A7) |
|
9 |
=A8.joinx@im(PS_PARTKEY,A7:P_PARTKEY,P_MFGR) |
|
10 |
=A9.select(PS_SUPPKEY.S_NATIONKEY.N_REGIONKEY.R_NAME==name) |
|
11 |
for A10,10000 |
|
12 |
=interval@ms(A1,now()) |
|
A3、A4、A5 已经完成主键序号化的维表，加载时就不需再要建立索引了。
A8 中用维表主键的序号完成外键连接。
为了观察与“关联解决”计算的性能差别，这里还是采用先连接后过滤的方式。
测试结果：
测试项目 |
执行时间（秒） |
关联解决 |
12 |
维表序号化 |
10 |
5. 对位序列
对于已经序号化的外键，我们还可以使用对位序列来处理维表上的过滤。
计算代码：
A |
B |
C |
|
1 |
=now() |
||
2 |
>size=25 |
>type="COPPER" |
>name="ASIA" |
3 |
=file("region_2.btx").import@b(R_REGIONKEY,R_NAME) |
||
4 |
=file("nation_2.btx").import@b(N_NATIONKEY,N_NAME,N_REGIONKEY) |
||
5 |
=file("p_type.btx").import@b().(pos@zh(_1,type)) |
||
6 |
=A4.switch(N_REGIONKEY,A3:#) |
=A6.(if(N_REGIONKEY.R_NAME==name,~,null)) |
|
7 |
=file("supplier_2.ctx").open() |
=A7.cursor@m().skip().(null) |
|
8 |
=A7.cursor@m(;S_NATIONKEY:B6:#).fetch() |
=A8.(B7(S_SUPPKEY)=~) |
|
9 |
=file("part_2.ctx").open().cursor@m(P_PARTKEY,P_MFGR;P_SIZE==size && A5(P_TYPE)) |
||
10 |
=file("partsupp_2.ctx").open().cursor(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;PS_SUPPKEY:B7:#;A9) |
||
11 |
=A10.joinx@im(PS_PARTKEY,A9:P_PARTKEY,P_MFGR) |
||
12 |
for A11,10000 |
||
13 |
=interval@ms(A1,now()) |
||
A5 会产生一个与维表 p_type 同样长度的序列，其成员当满足过滤条件时为 p_type 的序号，不满足时为 null。part 表中的 P_TYPE 已经序号化处理过，它的值对应于 p_type 表中记录的行号，所以在 A9 就可以用 A5(P_TYPE) 来判断 part 中此行数据是否满足过滤条件。
B6、B7 也是对位序列，成员是当满足过滤条件时为原记录，不满足时为 null。
因为不需要做实质性的查找，对位序列的性能非常好，在处理维表过滤时很有效。
测试结果：
测试项目 |
执行时间（秒） |
关联解决 |
12 |
维表过滤 |
7 |
维表序号化 |
10 |
对位序列 |
4 |
6. 聚合理解
到这里，关联解决已经处理完了。下面，要对关联结果分组，每组计算出 ps_supplycost 最小值所在的记录。
计算代码：
A |
B |
C |
|
1 |
=now() |
||
2 |
>size=25 |
>type="COPPER" |
>name="ASIA" |
3 |
=file("region_2.btx").import@b(R_REGIONKEY,R_NAME) |
||
4 |
=file("nation_2.btx").import@b(N_NATIONKEY,N_NAME,N_REGIONKEY) |
||
5 |
=file("p_type.btx").import@b().(pos@zh(_1,type)) |
||
6 |
=A4.switch(N_REGIONKEY,A3:#) |
=A6.(if(N_REGIONKEY.R_NAME==name,~,null)) |
|
7 |
=file("supplier_2.ctx").open() |
=A7.cursor@m().skip().(null) |
|
8 |
=A7.cursor@m(;S_NATIONKEY:B6:#).fetch() |
=A8.(B7(S_SUPPKEY)=~) |
|
9 |
=file("part_2.ctx").open().cursor@m(P_PARTKEY,P_MFGR;P_SIZE==size && A5(P_TYPE)) |
||
10 |
=file("partsupp_2.ctx").open().cursor(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;PS_SUPPKEY:B7:#;A9) |
||
11 |
=A10.joinx@im(PS_PARTKEY,A9:P_PARTKEY,P_MFGR) |
||
12 |
=A11.groups(PS_PARTKEY;P_MFGR,minp@a(PS_SUPPLYCOST):m) |
||
13 |
=A12.news(m;PS_PARTKEY,m.PS_SUPPKEY.S_ACCTBAL,m.PS_SUPPKEY.S_NAME,m.PS_SUPPKEY.S_NATIONKEY.N_NAME, P_MFGR,m.PS_SUPPKEY.S_ADDRESS, m.PS_SUPPKEY.S_PHONE,m. PS_SUPPKEY.S_COMMENT) |
||
14 |
=A13.top(100;-S_ACCTBAL,N_NAME,S_NAME,PS_PARTKEY) |
||
15 |
=interval@ms(A1,now()) |
||
A12 按照 PS_PARTKEY 分组，每组用聚合函数 minp 找出最小值所在的记录，而不是最小值本身。分组结果并不大，这里的 groups 函数直接用累积方式计算出结果序表。
A14 中利用 top 函数取出前 100 名，top 也是聚合函数，不需要全排序就可以得到前 100 名。
测试项目 |
执行时间（秒） |
聚合理解 |
4.5 |
7. 有序分组
part 表和 partsupp 表有序归并的结果仍对主键有序，可以实现有序分组计算。有序分组可以返回游标，适用于结果集较大的情况。
计算代码：
A |
B |
C |
|
1 |
=now() |
||
2 |
>size=25 |
>type="COPPER" |
>name="ASIA" |
3 |
=file("region_2.btx").import@b(R_REGIONKEY,R_NAME) |
||
4 |
=file("nation_2.btx").import@b(N_NATIONKEY,N_NAME,N_REGIONKEY) |
||
5 |
=file("p_type.btx").import@b().(pos@zh(_1,type)) |
||
6 |
=A4.switch(N_REGIONKEY,A3:#) |
=A6.(if(N_REGIONKEY.R_NAME==name,~,null)) |
|
7 |
=file("supplier_2.ctx").open() |
=A7.cursor@m().skip().(null) |
|
8 |
=A7.cursor@m(;S_NATIONKEY:B6:#).fetch() |
=A8.(B7(S_SUPPKEY)=~) |
|
9 |
=file("part_2.ctx").open().cursor@m(P_PARTKEY,P_MFGR;P_SIZE==size && A5(P_TYPE)) |
||
10 |
=file("partsupp_2.ctx").open().cursor(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;PS_SUPPKEY:B7:#;A9) |
||
11 |
=A10.joinx@im(PS_PARTKEY,A9:P_PARTKEY,P_MFGR) |
||
12 |
=A11.group@s(PS_PARTKEY;P_MFGR,minp@a(PS_SUPPLYCOST):m) |
||
13 |
=A12.news(m;PS_PARTKEY,m.PS_SUPPKEY.S_ACCTBAL,m.PS_SUPPKEY.S_NAME,m.PS_SUPPKEY.S_NATIONKEY.N_NAME, P_MFGR,m.PS_SUPPKEY.S_ADDRESS, m.PS_SUPPKEY.S_PHONE,m. PS_SUPPKEY.S_COMMENT) |
||
14 |
=A13.total(top(100;-S_ACCTBAL,N_NAME,S_NAME,PS_PARTKEY)) |
||
15 |
=interval@ms(A1,now()) |
||
A12 使用有序分组 group 函数计算出游标形式的结果集，有序分组只要比较相邻的记录就可以了，占用的内存更少，并能避免 Hash 计算及比对。@s 表示用累积方式聚合。
group@s 返回的是游标，当结果集比较大的时候也不会出现内存溢出的情况。
由于 A12 中的 group 函数得到的是游标，A4 中就要用游标的 total 函数，再加上聚合函数 top 计算出结果。
测试结果：
测试项目 |
执行时间（秒） |
聚合理解 |
4.5 |
有序分组 |
4.5 |
因为数据实际上是按照分组字段有序的，所以累积分组计算时，有序分组效果不明显。
8. 有序分组 - 小结果集
在分组结果不大的时候，也可以用 groups@o 实现有序分组。
计算代码：
A |
B |
C |
|
1 |
=now() |
||
2 |
>size=25 |
>type="COPPER" |
>name="ASIA" |
3 |
=file("region_2.btx").import@b(R_REGIONKEY,R_NAME) |
||
4 |
=file("nation_2.btx").import@b(N_NATIONKEY,N_NAME,N_REGIONKEY) |
||
5 |
=file("p_type.btx").import@b().(pos@zh(_1,type)) |
||
6 |
=A4.switch(N_REGIONKEY,A3:#) |
=A6.(if(N_REGIONKEY.R_NAME==name,~,null)) |
|
7 |
=file("supplier_2.ctx").open() |
=A7.cursor@m().skip().(null) |
|
8 |
=A7.cursor@m(;S_NATIONKEY:B6:#).fetch() |
=A8.(B7(S_SUPPKEY)=~) |
|
9 |
=file("part_2.ctx").open().cursor@m(P_PARTKEY,P_MFGR;P_SIZE==size && A5(P_TYPE)) |
||
10 |
=file("partsupp_2.ctx").open().cursor(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;PS_SUPPKEY:B7:#;A9) |
||
11 |
=A10.joinx@im(PS_PARTKEY,A9:P_PARTKEY,P_MFGR) |
||
12 |
=A11.groups@o(PS_PARTKEY;P_MFGR,minp@a(PS_SUPPLYCOST):m) |
||
13 |
=A12.news(m;PS_PARTKEY,m.PS_SUPPKEY.S_ACCTBAL,m.PS_SUPPKEY.S_NAME,m.PS_SUPPKEY.S_NATIONKEY.N_NAME, P_MFGR,m.PS_SUPPKEY.S_ADDRESS, m.PS_SUPPKEY.S_PHONE,m. PS_SUPPKEY.S_COMMENT) |
||
14 |
=A13.top(100;-S_ACCTBAL,N_NAME,S_NAME,PS_PARTKEY) |
||
15 |
=interval@ms(A1,now()) |
||
A12 中用 groups@o，表示用有序分组的方法，只比较相邻的分组字段，直接计算出结果序表。
A14 中就用序表的 top 函数计算前 100 名了。
测试结果：
测试项目 |
执行时间（秒） |
聚合理解 |
4.5 |
有序分组 |
4.5 |
有序分组 - 小结果集 |
4 |
9. 关联定位
part 表和 partsupp 表之间是主键关联的，用被过滤后的表的主键去查找关联表的记录，可以避免全表遍历，获得更好的性能。先看主表关联定位子表的情况。
计算代码：
A |
B |
C |
|
1 |
=now() |
||
2 |
>size=25 |
>type="COPPER" |
>name="ASIA" |
3 |
=file("region_2.btx").import@b(R_REGIONKEY,R_NAME) |
||
4 |
=file("nation_2.btx").import@b(N_NATIONKEY,N_NAME,N_REGIONKEY) |
||
5 |
=file("p_type.btx").import@b().(pos@zh(_1,type)) |
||
6 |
=A4.switch(N_REGIONKEY,A3:#) |
=A6.(if(N_REGIONKEY.R_NAME==name,~,null)) |
|
7 |
=file("supplier_2.ctx").open() |
=A7.cursor@m().skip().(null) |
|
8 |
=A7.cursor@m(;S_NATIONKEY:B6:#).fetch() |
=A8.(B7(S_SUPPKEY)=~) |
|
9 |
=file("part_2.ctx").open().cursor@m(P_PARTKEY,P_MFGR;P_SIZE==size && A5(P_TYPE)) |
||
10 |
=file("partsupp_2.ctx").open().news(A9,P_PARTKEY,P_MFGR,PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;PS_SUPPKEY:B7:#) |
||
11 |
=A10.groups@o(PS_PARTKEY;P_MFGR,minp@a(PS_SUPPLYCOST):m) |
||
12 |
=A11.news(m;PS_PARTKEY,m.PS_SUPPKEY.S_ACCTBAL,m.PS_SUPPKEY.S_NAME,m.PS_SUPPKEY.S_NATIONKEY.N_NAME, P_MFGR,m.PS_SUPPKEY.S_ADDRESS, m.PS_SUPPKEY.S_PHONE,m. PS_SUPPKEY.S_COMMENT) |
||
13 |
=A12.top(100;-S_ACCTBAL,N_NAME,S_NAME,PS_PARTKEY) |
||
14 |
=interval@ms(A1,now()) |
||
A10 使用过滤后的主表 part 去取出关联的子表 partsupp 记录。因为主表对子表是一对多的关系，所以这里要使用 news，表示主表中取出的每条记录可能对应多条子表记录，此时会把主表字段按关联的子表记录数量复制，相当于做 joinx。
我们也可以用子表关联定位主表。
计算代码：
A |
B |
C |
|
1 |
=now() |
||
2 |
>size=25 |
>type="COPPER" |
>name="ASIA" |
3 |
=file("region_2.btx").import@b(R_REGIONKEY,R_NAME) |
||
4 |
=file("nation_2.btx").import@b(N_NATIONKEY,N_NAME,N_REGIONKEY) |
||
5 |
=file("p_type.btx").import@b().(pos@zh(_1,type)) |
||
6 |
=A4.switch(N_REGIONKEY,A3:#) |
=A6.(if(N_REGIONKEY.R_NAME==name,~,null)) |
|
7 |
=file("supplier_2.ctx").open() |
=A7.cursor@m().skip().(null) |
|
8 |
=A7.cursor(;S_NATIONKEY:B6:#).fetch() |
=A8.(B7(S_SUPPKEY)=~) |
|
9 |
=file("partsupp_2.ctx").open().cursor@m(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;PS_SUPPKEY:B7:#) |
||
10 |
=file("part_2.ctx").open().new@r(A9,P_PARTKEY,P_MFGR,PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;P_SIZE==size && A5(P_TYPE)) |
||
11 |
=A10.groups@o(PS_PARTKEY;minp@a(PS_SUPPLYCOST):m) |
||
12 |
=A11.news(m;PS_PARTKEY,m.PS_SUPPKEY.S_ACCTBAL,m.PS_SUPPKEY.S_NAME,m.PS_SUPPKEY.S_NATIONKEY.N_NAME, P_MFGR,m.PS_SUPPKEY.S_ADDRESS, m.PS_SUPPKEY.S_PHONE,m. PS_SUPPKEY.S_COMMENT) |
||
13 |
=A12.top(100;-S_ACCTBAL,N_NAME,S_NAME,PS_PARTKEY) |
||
14 |
=interval@ms(A1,now()) |
||
A10 中的 new 函数用过滤后的子表去取出关联的主表记录。@r 选项表示子表相同主键有多条记录时，复制主表的记录。
测试结果：
测试项目 |
执行时间（秒） |
聚合理解 |
4.5 |
有序分组 |
4.5 |
有序分组 - 累计 |
4 |
关联定位 - 主表定位子表 |
3.5 |
关联定位 - 子表定位主表 |
4 |
主表过滤条件去掉的记录比较多，子表过滤条件去掉的比较少，所以主表过滤子表的关联定位，性能更好一些。
10. 列式计算
前面题目提到的列式计算，也可以用于本题。
计算代码：
A |
B |
C |
|
1 |
=now() |
||
2 |
>size=25 |
>type="COPPER" |
>name="ASIA" |
3 |
=file("region_2.btx").import@b(R_REGIONKEY,R_NAME) |
||
4 |
=file("nation_2.btx").import@b(N_NATIONKEY,N_NAME,N_REGIONKEY) |
||
5 |
=file("p_type.btx").import@b().(pos@zh(_1,type)) |
||
6 |
=A4.switch(N_REGIONKEY,A3:#) |
=A6.(if(N_REGIONKEY.R_NAME==name,~,null)) |
|
7 |
=file("supplier_2.ctx").open() |
=A7.cursor@m().skip().(null) |
|
8 |
=A7.cursor(;S_NATIONKEY:B6:#).fetch() |
=A8.(B7(S_SUPPKEY)=~) |
|
9 |
=file("part_2.ctx").open().cursor@mv(P_PARTKEY,P_MFGR;P_SIZE==size && A5(P_TYPE)) |
||
10 |
=file("partsupp_2.ctx").open() |
||
11 |
=A10.news(A9,P_PARTKEY,P_MFGR,PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;B7(PS_SUPPKEY)) |
||
12 |
=A11.groups@o(PS_PARTKEY;minp@a(PS_SUPPLYCOST):m) |
||
13 |
=A12.(m).conj().derive@o(B7(PS_SUPPKEY):s) |
||
14 |
=A13.new(P_PARTKEY,s.S_ACCTBAL,s.S_NAME,s.S_NATIONKEY.N_NAME, P_MFGR,s.S_ADDRESS,s.S_PHONE,s.S_COMMENT) |
||
15 |
=A14.top(100;-S_ACCTBAL,N_NAME,S_NAME,P_PARTKEY) |
||
16 |
=interval@ms(A1,now()) |
||
在列式计算中，要尽量避免使用 switch 操作。A11 中使用 B7(PS_PARTKEY) 仅仅过滤数据，而不再使用 PS_SUPPKEY:B7:# 这种关联与过滤同时进行的方法。因为这里 PS_PARTKEY 是序号化的，可以直接在 supplier 表中通过行号获取记录而无需关联，可以更好地体现列式计算的优势。
A13 中先用 PS_SUPPKEY 在 supplier 表中找到对应的记录生成 s 列，然后再用 A14 中的 new 函数通过 s 列找到要显示的目标列数据。
测试结果：
测试项目 |
执行时间（秒） |
聚合理解 |
4.5 |
有序分组 |
4.5 |
有序分组 - 累计 |
4 |
关联定位 - 子表关联主表 |
3.5 |
关联定位 - 主表关联子表 |
4 |
列式计算 |
2.5 |
陈虎老师，文中"3.维表过滤"中的A4格中，pos@t ()这个选项t在官方文档中没有找到。
这里是不是表示 pos@tail 比较尾部的意思？
从 sql 语句中观察，p_type like “%COPPER” 这部分的意思应该是 COPPER 结尾的意思，对吧？
相当于 SPL 中的 like 函数 like@s(str,“%copper”)
如果是这样的话，那 pos 函数缺少了一个只比较尾部的选项 pos@tail，因为也确实存在 pos@head 只比较头部的选项 pos@h。
当然，pos@zh() 也表示比较尾部。
这是个笔误，pos 没有 t 选项。
应该是 pos@zh
懂了，谢谢陈虎老师🙏 🙏
英文版
请问 序表.select@o 选项 o 是新加的吗😄 结果返回成序表？这个好👍 省得 derive() 了。
以前有，后来取消了，我改一下。
😄 麻烦陈虎老师了🙏
原来 select@o 是之前的，怪不得函数文档中没有说明。但目前还能使用这个选项 typeof(序表.select@o()) 返回结果类型是 table，如果不加 o 选项，返回结果的类型是 sequence。以前老是把 select、group 之后的结果直接用表函数去处理，老是出错，所以必须要把排列先 derive 一下成序表，这点记得特别牢。😂
derive@o 您在 TPCH-Q1 中有讲过 "列式游标要避免在计算过程中产生新的变量，要使用 derive 方法将新变量变成新的列，选项 @o 表示新列增加到原序表上，不产生新序表"，这里改成这样写倒是能保持系列文章内容的一惯性。
感谢，跟着系列文章学习，百看不厌，涨知识👍 👍