性能优化案例课程 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 |
英文版