"select nation, o_year, sum(amount) as sum_profit from ( select n_name as nation, extract(year fr .."

terminator 北京
陈虎。真心想用集算器这样的工具降低程序员们的劳动强度，真心想成为高性能复杂计算、大数据计算编程难题的终结者。
190 浏览 • 1 个月前

性能优化案例课程 TPCH-Q9

计算＆AI

集算器(479)

select
    nation,
    o_year,
    sum(amount) as sum_profit
from
    (
        select
            n_name as nation,
            extract(year from o_orderdate) as o_year,
            l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
        from
            part,
            supplier,
            lineitem,
            partsupp,
            orders,
            nation
        where
            s_suppkey = l_suppkey
            and ps_suppkey = l_suppkey
            and ps_partkey = l_partkey
            and p_partkey = l_partkey
            and o_orderkey = l_orderkey
            and s_nationkey = n_nationkey
            and p_name like '%chocolate%'
    ) profit
group by
    nation,
    o_year
order by
    nation,
o_year desc;

这是多表关联过滤后的分组聚合运算，其中包括主子表 orders 和 lineitem 关联以及与双主键外键表 partsupp 的关联。

1. 数据存储

大表 orders、lineitem 按照主键 orderkey 有序存储，连接的时候可以有序归并。其他表没有特殊的排序要求，就按照主键有序存储。

这样，就可以继续使用题目 Q3 中的 orders.ctx、lineitem.ctx、customer.ctx。

part、supplier、partsupp 表直接使用题目 Q2 的 part.ctx、supplier.ctx、partsupp.ctx。

region、nation 表直接使用题目 Q2 的 regin.btx，nation.btx。

将这些表复制到本题的主目录中。

2. 一般实现

	A
1	=now()
2	>name="chocolate"
3	=file("nation.btx").import@b().keys@i(N_NATIONKEY)
4	=file("part.ctx").open().cursor@m(P_PARTKEY;pos(P_NAME, name)).fetch().keys@im(P_PARTKEY)
5	=file("supplier.ctx").open().cursor@m(S_SUPPKEY,S_NATIONKEY).fetch().keys@im(S_SUPPKEY)
6	=file("partsupp.ctx").open().cursor@m(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;A4.find(PS_PARTKEY)).fetch().keys@im(PS_PARTKEY,PS_SUPPKEY)
7	=file("lineitem.ctx").open().cursor@m(L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT;A4.find(L_PARTKEY))
8	=file("orders.ctx").open().new@r(A7,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,O_ORDERDATE)
9	=A8.join@i(L_PARTKEY:L_SUPPKEY,A6,PS_SUPPLYCOST).switch(L_SUPPKEY,A5)
10	=A9.groups(L_SUPPKEY.S_NATIONKEY:nationName,year(O_ORDERDATE):o_year;sum(L_EXTENDEDPRICE(1-L_DISCOUNT)-PS_SUPPLYCOSTL_QUANTITY):profit)
11	=A10.run(nationName=A3.find(nationName).N_NAME).sort(nationName,-o_year)
12	=interval@ms(A1,now())

本例中 orders 无过滤，而 lineitem 有过滤，过滤后的数据行数远小于 orders 的行数，所以在主子表关联时与前面的例子不同。此时在 A7 先构造 lineitem 游标，A8 中用它去过滤主表 orders，从而大大减少读取的数据行数，提高运行效率。

A9 中用 join@i 方法把外键表的字段 PS_SUPPLYCOST 拼到游标上，这里是个双字段主键的外键表，不能再使用在游标建立时匹配并转换的方法。

A10 用 NATIONKEY 代替字符串 NAME 分组，A11 分组结果再转换成字符串。

测试项目	执行时间（秒）
一般实现	37

3. 数据变换

数据变换要采用前面题目提到的维表主键序号化、日期整数化手段。

region、nation、supplier、part 表，可以直接使用以前的题目 Q2 转换的 region_2.btx，nation_2.btx、supplier_2.ctx、part_2.ctx。

part 表中的枚举型字符串字段 type 要完成数字化，可以用 Q2 的 p_type.btx。

customer 使用 Q5 中的 customer_3.ctx。

orders 表使用 Q3 中的 orders_5.ctx。

lineitem 表使用 Q8 中的 lineitem_4.ctx。

将这些表复制到本题的主目录中。

计算代码：

	A	B
1	=now()
2	>name="chocolate"
3	=file("nation_2.btx").import@b()
4	= file("part_2.ctx").open()	=A4.cursor@m().skip().(false)
5	=A4.cursor@m(P_PARTKEY;pos(P_NAME, name)).fetch().(B4(P_PARTKEY)=true)
6	=file("supplier_2.ctx").open()
7	=A6.cursor@m(S_NATIONKEY).fetch().(S_NATIONKEY)
8	=file("partsupp_2.ctx").open().cursor@m(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;B4(PS_PARTKEY)).fetch().keys@im(PS_PARTKEY,PS_SUPPKEY)
9	=file("lineitem_4.ctx").open().cursor@m(L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT;B4(L_PARTKEY))
10	=file("orders_5.ctx").open().new@r(A9,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,O_ORDERDATE)
11	=A10.select(L_PARTKEY=A8.find(L_PARTKEY,L_SUPPKEY).PS_SUPPLYCOST).switch(L_SUPPKEY,A7:#)
12	=A3.len()+1
13	=A11.groups(year(O_ORDERDATE)A12+L_SUPPKEY:gk;sum(L_EXTENDEDPRICE(1-L_DISCOUNT)-L_PARTKEY*L_QUANTITY):profit)
14	=A13.new(A3(gk%A12).N_NAME:nationName,gk\A12:o_year,profit).sort(nationName,-o_year)
15	=interval@ms(A1,now())

A11 中用 L_PARTKEY 和 L_SUPPKEY 在 A5 中按主键查找记录，找到后把 PS_SUPPLYCOST 赋值给 L_PARTKEY，如果没找到对应的记录则此值为 null，就会被 select 函数过滤掉。这个方法与前面介绍的 join@i 运算性能相当。

A13 中使用了 Q1 介绍的分组键技巧，把按 2 个字段分组变成按一个字段 gk 分组。

测试结果：

测试项目	执行时间（秒）
一般实现	37
数据变换	29

4. 列式计算

	A	B
1	=now()
2	>name="chocolate"
3	=file("nation_2.btx").import@b()
4	=file("part_2.ctx").open()	=A4.cursor@m().skip().(false)
5	=A4.cursor@mv(P_PARTKEY;pos(P_NAME, name)).fetch().(B4(P_PARTKEY)=true)
6	=file("supplier_2.ctx").open()
7	=A6.cursor@m(S_NATIONKEY).fetch()
8	=file("partsupp_2.ctx").open().cursor@mv(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;B4(PS_PARTKEY)).fetch().keys@im(PS_PARTKEY,PS_SUPPKEY)
9	=file("lineitem_4.ctx").open().cursor@mv(L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT;B4(L_PARTKEY))
10	=file("orders_5.ctx").open().new@r(A9,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,O_ORDERDATE)
11	=A10.join@i(L_PARTKEY:L_SUPPKEY,A8,PS_SUPPLYCOST;L_SUPPKEY,A7:#,S_NATIONKEY)
12	=A3.len()+1
13	=A11.groups(year(O_ORDERDATE)A12+S_NATIONKEY:gk;sum(L_EXTENDEDPRICE(1-L_DISCOUNT)-PS_SUPPLYCOST*L_QUANTITY):profit)
14	=A13.new(A3(gk%A12).N_NAME:nationName,gk\A12:o_year,profit).sort(nationName,-o_year)
15	=interval@ms(A1,now())
	=now()

在列式计算中，要尽量避免 switch 操作，A11 中使用 join 拼接新列，而不再使用 switch 给原有列赋值。

测试结果：

测试项目	执行时间（秒）
一般实现	37
数据变换	29
列式计算	12

集算器(479)