性能优化案例课程 TPCH-Q15
create view revenue (supplier_no, total_revenue) as
select
l_suppkey,
sum(l_extendedprice * (1 - l_discount))
from
lineitem
where
l_shipdate >= date '1995-04-01'
and l_shipdate < date '1995-04-01' + interval '3' month
group by
l_suppkey;
select
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
from
supplier,
revenue
where
s_suppkey = supplier_no
and total_revenue = (
select
max(total_revenue)
from
revenue
)
order by
s_suppkey;
drop view revenue;
生成视图 revenue，与 supplier 关联后再选出含有最大值的记录。
1. 数据存储
lineitem 和 supplier 表没有什么特殊要求，按照主键有序存储。
继续使用题目 Q3 中的 lineitem.ctx 和 Q2 的 supplier.ctx。
将这些表复制到本题的主目录中。
2. 一般实现
这次查询分为两个阶段，先计算出视图 revenue，然后再找出 revenue 中 total_revenue 达到最大值的记录。前者是个常规的分组汇总，利用并行提高性能。
SPL 提供了 A.maxp 方法，可以直接返回最大值所在记录。最大值对应记录数不会太多，可以采用前面题目讲过的大维表查找方法。
A |
|
1 |
=now() |
2 |
1995-4-1 |
3 |
=elapse@m(A2,3) |
4 |
=file("lineitem.ctx").open().cursor@m(L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT;L_SHIPDATE>=A2 && L_SHIPDATE<A3) |
5 |
=A4.groups@u(L_SUPPKEY:supplier_no;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):total_revenue) |
6 |
=A5.maxp@a(total_revenue) |
7 |
=file("supplier.ctx").open() |
8 |
=A6.joinx@q(supplier_no,A7:S_SUPPKEY,S_NAME,S_ADDRESS,S_PHONE) |
9 |
=interval@ms(A1,now()) |
代码中用到了前面讲到过的维表属性化、多线程并行等优化方法。
A8 中采用大维表查找的方法实现 A6 和大维表 supplier 的连接。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
6 |
3. 数据转换
Lineitem 表中的 L_SHIPDATE 要进行前面介绍的日期整数化。Lineitem 的维表 supplier 要做维表主键序号化，以便采用序号分组。在 Q3 的 lineitem.ctx 和 Q2 的 supplier.ctx 基础上完成维表主键序号化。
还要将 Q2 已经序号化的 supplier_2.ctx 复制到本题的主目录。
数据转换代码：
A |
|
1 |
=file("supplier.ctx").open().import(S_SUPPKEY).keys@im(S_SUPPKEY) |
2 |
=file("lineitem.ctx").open().cursor() |
3 |
=A2.run(L_SUPPKEY=A1.pfind(L_SUPPKEY),L_SHIPDATE=days@o(L_SHIPDATE)) |
4 |
=file("lineitem_15_3.ctx").create@p(#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) |
5 |
>A4.append(A3) |
计算代码：
A |
|
1 |
=now() |
2 |
1995-4-1 |
3 |
=days@o(elapse@m(A2,3)) |
4 |
=days@o(A2) |
5 |
=file("lineitem_15_3.ctx").open().cursor@m(L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT;L_SHIPDATE>=A4 && L_SHIPDATE<A3) |
6 |
=A5.groups@n(L_SUPPKEY:supplier_no;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):total_revenue) |
7 |
=A6.maxp@a(total_revenue) |
8 |
=file("supplier_2.ctx").open() |
9 |
=A7.joinx@q(supplier_no,A8:S_SUPPKEY,S_NAME,S_ADDRESS,S_PHONE) |
10 |
=interval@ms(A1,now()) |
A6 中 groups@n 是前面介绍过的序号分组。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
6 |
数据变换 |
5 |
4. 列式计算
计算代码：
A |
|
1 |
=now() |
2 |
1995-4-1 |
3 |
=days@o(elapse@m(A2,3)) |
4 |
=days@o(A2) |
5 |
=file("lineitem_15_3.ctx").open().cursor@mv(L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT;L_SHIPDATE>=A4 && L_SHIPDATE<A3) |
6 |
=A5.groups@u(L_SUPPKEY:supplier_no;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):total_revenue) |
7 |
=A6.maxp@a(total_revenue) |
8 |
=file("supplier_2.ctx").open() |
9 |
=A7.joinx@q(supplier_no,A8:S_SUPPKEY,S_NAME,S_ADDRESS,S_PHONE) |
10 |
=interval@ms(A1,now()) |
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
6 |
数据转换 |
5 |
列式计算 |
2 |
英文版