性能优化案例课程 TPCH-Q6
select
sum(l_extendedprice * l_discount) as revenue
from
lineitem
where
l_shipdate >= date '1995-01-01'
and l_shipdate < date '1995-01-01' + interval '1' year
and l_discount between 0.05 - 0.01 and 0.05 + 0.01
and l_quantity < 24;
这是一个单表过滤后的聚合运算。
1. 数据存储
排序没有什么特殊要求，lineitem 按照主键 orderkey 有序存储即可，继续使用题目 Q3 中的 lineitem.ctx，复制到本题的主目录中。
2. 一般实现
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
1995-1-1 |
=elapse@y(A2,1) |
3 |
=0.05-0.01 |
=0.05+0.01 |
4 |
>quantity=24 |
|
5 |
=file("lineitem.ctx").open().cursor@m(L_EXTENDEDPRICE,L_DISCOUNT;L_SHIPDATE>=A2 && L_SHIPDATE<B2 && L_DISCOUNT>=A3 && L_DISCOUNT<=B3 && L_QUANTITY<quantity) |
|
6 |
=A5.total(sum(L_EXTENDEDPRICE*L_DISCOUNT)) |
|
7 |
=interval@ms(A1,now()) |
|
注意 A5 的 cursor 函数不能直接写 L_EXTENDEDPRICE*L_DISCOUNT 这样的表达式，要写在 A6 中。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
6 |
3. 数据变换
数据变换要采用前面题目提到的日期整数化手段。
Q1 中对 lineitem 表中的 L_SHIPDATE 已经整数化了，可以直接使用 lineitem6.ctx，复制到本题的主目录中。
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
1995-1-1 |
|
3 |
=days@o(A2) |
=days@o(elapse@y(A2,1)) |
4 |
=0.05-0.01 |
=0.05+0.01 |
5 |
>quantity=24 |
|
6 |
=file("lineitem6.ctx").open().cursor@m(L_EXTENDEDPRICE,L_DISCOUNT;L_SHIPDATE>=A3 && L_SHIPDATE<B3 && L_DISCOUNT>=A4 && L_DISCOUNT<=B4 && L_QUANTITY<quantity) |
|
7 |
=A6.total(sum(L_EXTENDEDPRICE*L_DISCOUNT)) |
|
8 |
=interval@ms(A1,now()) |
|
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
6 |
数据变换 |
4 |
4. 列式计算
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
1995-1-1 |
|
3 |
=days@o(A2) |
=days@o(elapse@y(A2,1)) |
4 |
=0.05-0.01 |
=0.05+0.01 |
5 |
>quantity=24 |
|
6 |
=file("lineitem6.ctx").open().cursor@m(L_EXTENDEDPRICE,L_DISCOUNT;L_SHIPDATE>=A3 && L_SHIPDATE<B3 && L_DISCOUNT>=A4 && L_DISCOUNT<=B4 && L_QUANTITY<quantity) |
|
7 |
=A6.total(sum(L_EXTENDEDPRICE*L_DISCOUNT)) |
|
8 |
=interval@ms(A1,now()) |
|
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
6 |
数据变换 |
4 |
列式计算 |
2 |
英文版