性能优化案例课程 TPCH-Q22
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;
主查询外层是个常规的分组聚合，内层有两个用于条件的子查询。
两个子查询分别对应满足条件的 customer 记录集合，后一个子查询是 not exist，最后结果是两个集合的差集。
1. 数据存储
数据表没有特别的有序要求，都按照主键有序存储。
直接使用 Q3 中的 orders.ctx、customer.ctx，复制到本题的主目录中。
2. 一般实现
计算代码：
A |
|
1 |
=now() |
2 |
=["11","14","15","19","20","21","23"] |
3 |
=file("customer.ctx").open().cursor@m(C_CUSTKEY,C_PHONE,C_ACCTBAL;C_ACCTBAL>0.0 && A2.contain(left(C_PHONE,2))).fetch() |
4 |
=A3.avg(C_ACCTBAL) |
5 |
=A3.select@m(C_ACCTBAL>A4).derive@o().keys@im(C_CUSTKEY) |
6 |
=file("orders.ctx").open().cursor@m(O_CUSTKEY;O_CUSTKEY:A5) |
7 |
=A6.run(O_CUSTKEY.C_CUSTKEY=null).skip() |
8 |
=A5.select@m(C_CUSTKEY) |
9 |
=A8.groups(left(C_PHONE,2):cntrycode;count(1):numcust, sum(C_ACCTBAL):totacctbal) |
10 |
=interval@ms(A1,now()) |
A5 相当于选出满足第一个子查询条件的记录，A6 中与 A5 关联后选出第二个子查询的记录，A7 中令这些记录的 C_CUSTKEY 为 null，意即从 A5 中删除它们，相当于实现第二个子查询的条件 not exists，A8 中选出的即是满足两个子查询的记录。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
6 |
3. 数据转换
本题中要使用前面题目介绍的维表主键序号化方法，把 Q3 的 orders_5.ctx、customer_5.ctx 复制到本题主目录。
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
=["11","14","15","19","20","21","23"] |
|
3 |
=file("customer_5.ctx").open() |
=A3.cursor@m().skip().(null) |
4 |
=A3.cursor@m(C_CUSTKEY,C_ACCTBAL,C_PHONE;C_ACCTBAL>0.0 && A2.contain(left(C_PHONE,2))).fetch().@m(B3(C_CUSTKEY)=~) |
|
5 |
=B3.avg(C_ACCTBAL) |
|
6 |
=B3.@m(if(C_ACCTBAL>A5,~,null)) |
|
7 |
=file("orders_5.ctx").open().cursor@m(O_CUSTKEY;A6(O_CUSTKEY)).run(A6(O_CUSTKEY)=null).skip() |
|
8 |
=A6.select@m(~).groups@m(left(C_PHONE,2):cntrycode;count(1):numcust, sum(C_ACCTBAL):totacctbal) |
|
9 |
=interval@ms(A1,now()) |
|
B3、A6 是对位序列，值是 null 或者 customer 表的记录。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
6 |
数据转换 |
2 |
4. 列式计算
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
=["11","14","15","19","20","21","23"] |
|
3 |
=file("customer_5.ctx").open() |
=A3.cursor@m().skip().(null) |
4 |
=A3.cursor@mv(C_CUSTKEY,C_ACCTBAL,C_PHONE;C_ACCTBAL>0.0 && A2.contain(left(C_PHONE,2))).fetch().@m(B3(C_CUSTKEY)=~) |
|
5 |
=B3.avg(C_ACCTBAL) |
|
6 |
=B3.@m(if(C_ACCTBAL>A5,~,null)) |
|
7 |
=file("orders_5.ctx").open().cursor@mv(O_CUSTKEY;A6(O_CUSTKEY)).run(A6(O_CUSTKEY)=null).skip() |
|
8 |
=A6.select(~).groups@m(left(C_PHONE,2):cntrycode;count(1):numcust, sum(C_ACCTBAL):totacctbal) |
|
9 |
=interval@ms(A1,now()) |
|
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
6 |
数据转换 |
2 |
列式计算 |
1 |
英文版