"一、 SQL 及分析查询SQL语句如下： create view revenue (supplier_no, total_revenue) as select l_suppkey, sum( .."

sjr 北京
乾学院 32 号会员
1 回帖 • 659 浏览 • 1 年前

用 TPCH 练习性能优化 Q15

计算＆AI

一、 SQL及分析

查询SQL语句如下：

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关联后再选出含有最大值的记录。

二、 SPL实现

这次查询分为两个阶段，先计算出视图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())

A5计算出视图revenue，A6用maxp@a一次遍历即返回total_revenue最大的那些记录，完成这个运算后再去到supplier表中查出其它字段，减少计算量。

三、进一步优化

1. 优化方法

本例中要使用Q1中介绍的日期整数化优化方法，lineitem中的L_SHIPDATE已在Q1中转换过了。还要使用Q2中介绍的维表主键序号化方法，supplier中的S_SUPPKEY、lineitem中的L_SUPPKEY都已在之前的例子中转换过了。

2. 数据转换代码

复制supplier_11.ctx、lineitem_14.ctx分别重命名为supplier_15.ctx、lineitem_15.ctx。

3. 数据转换后的计算代码

维表需要预加载，加载代码如下：

	A
1	>env(supplier, file("supplier_15.ctx").open().import())

在查询计算之前，需要先运行预加载代码，将小维表读入内存。

计算代码：

	A
1	=now()
2	1995-4-1
3	=days@o(elapse@m(A2,3))
4	=days@o(A2)
5	=file("lineitem_15.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	=A7.new(supplier_no,total_revenue,(s=supplier(supplier_no)).S_NAME,s.S_ADDRESS,s.S_PHONE)
9	=interval@ms(A1,now())

A6中使用groups@n进行分组，原理同Q13。A7中maxp得到最大值所在记录。

四、使用企业版列式计算

1. 原始数据

	A
1	=now()
2	1995-4-1
3	=elapse@m(A2,3)
4	=file("lineitem.ctx").open().cursor@mv(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())

2. 优化后数据