要在一些缺少运算符(+、*和||)的数列中找出能够计算出左侧的值的行，前文给出的方法在处理两种运算符时性能尚可，处理三种就不行了。我向DuckDB CTE功能的开发者kryonix
求助，他给出了如下参考链接。经过测试，它求解850行的输入，同时计算两种运算符和三种运算符，用时不到1秒，而我的方法只计算两种运算符也要2秒多，如下所示。
memory D .read day07_850.sql
┌──────────────────┬───────────────────┐
│ part1 │ part2 │
│ int128 │ int128 │
├──────────────────┼───────────────────┤
│ 663613490587 │ 110365987435001 │
│ (663.61 billion) │ (110.37 trillion) │
└──────────────────┴───────────────────┘
Run Time (s): real 0.812 user 1.906250 sys 0.359375
memory D .read 2407lt.txt
┌──────────────────┐
│ sum(s) │
│ int128 │
├──────────────────┤
│ 663613490587 │
│ (663.61 billion) │
└──────────────────┘
Run Time (s): real 2.581 user 3.015625 sys 0.281250
neumannt是怎么达到高效的？下面分析我们的代码区别。
先看我自己的
with a as(from read_csv('2407-input.txt',delim=':')t(s,r)), --将输入文件每行用“:”做分隔符拆分成左右两部分。
t as(select row_number()over()rn,s,string_split(trim(r),' ')c from a), --将前面的结果加上行号，并将右侧字符串用“:”做分隔符转化为数组。
s as(select rn,s,c,b,
(with recursive x using key(c)as (select 1 lv ,c ,c[1] su --将前面的结果与1到2的数组长度次幂的序列做笛卡尔积，利用相关子查询计算二进制数各位代表的运算符和数组中数字组成的算式的结果
union --递归CTE的迭代每次计算右侧数组中的前两个数字和候选运算符做为中间结果，下轮迭代用中间结果做左操作数、未用过的最前数字做右操作数。
select lv+1,c,case when (b&(1<<(lv-1))=0)then su::bigint+c[lv+1]::bigint else su::bigint*c[lv+1]::bigint end from x where lv<len(c) and su::bigint<s::bigint)
select su from x )su
from t,
(select b from range(1,(1<<(len(c)-1))+1)t(b)) --用二进制位表示每轮的两个运算符，所有二进制位组成的数就是所有运算符组合。
)
select sum(s) from(select sum(distinct s)s from s where s=su group by rn); --在计算结果等于左侧的算式中，去重复累加左侧值。
前两步经过测试，用时很短，可以忽略，因此不考虑优化。
从代码注释可知，上述算法穷举了每行数字的所有组合。
设第i行的右侧数字有a[i]个，那么它产生的数组长度就是a[i]个元素，从而做笛卡尔积的临时表有2(a[i]-1)行，两者相乘，结果数组的总长度就是a[i]*2(a[i]-1)，
按实际数据中某行6014: 9 8 4 80 572 2计算，它的长度6，存储空间大约是6*2^5=192个整数，然后CTE迭代6次，每次要复制数组一次，光是记录数组，就复制了1000多个整数。
每行的数组长度不同，复制个数也不同。
用如下sql计算长度和复制次数，
with a as(from read_csv('2407-input.txt',delim=':')t(s,r)),
t as(select row_number()over()rn,s,len(string_split(trim(r),' '))lenc from a),
s as(select lenc,count(*)cnt from t group by lenc)
--from s order by lenc;
select sum((lenc*lenc*(1<<(lenc-1)))*cnt) from s;
┌───────┬───────┐
│ lenc │ cnt │
│ int64 │ int64 │
├───────┼───────┤
│ 3 │ 21 │
│ 4 │ 30 │
│ 5 │ 223 │
│ 6 │ 124 │
│ 7 │ 104 │
│ 8 │ 81 │
│ 9 │ 78 │
│ 10 │ 64 │
│ 11 │ 69 │
│ 12 │ 56 │
├───────┴───────┤
│ 10 rows │
└───────────────┘
memory D .read 2407ana.sql
┌──────────────────────────────────────────────────┐
│ sum((((lenc * lenc) * (1 << (lenc - 1))) * cnt)) │
│ int128 │
├──────────────────────────────────────────────────┤
│ 31184996 │
│ (31.18 million) │
└──────────────────────────────────────────────────┘
可见运算两种运算符，程序要复制3千万个整数，还不算其他开销。比如由于涉及大数运算，整数要用8字节的bigint类型，带来的类型转换开销。
再看我们的CTE退出条件，只有迭代次数，而没有其他，所以上述整数复制次数一次也不能少。而实际有用的次数远远低于此数，因为我们的计算单调递增，当算出大于等于左侧结果就可以退出，这在乘法中更加突出。
如果是三种运算符，则把2的幂改为3的幂，复制21亿个整数，比两种运算符多了700倍，简单推算需要1500多秒，难怪算不出来了。
select sum((lenc*lenc*(power(3,(lenc-1))))*cnt) from s;
memory D .read 2407ana.sql
┌─────────────────────────────────────────────────────┐
│ sum((((lenc * lenc) * power(3, (lenc - 1))) * cnt)) │
│ double │
├─────────────────────────────────────────────────────┤
│ 2105540487.0 │
│ (2.11 billion) │
└─────────────────────────────────────────────────────┘
再看neumannt的程序
with recursive aoc7_input(i) as (select '
54753537: 2 35 2 5 5 9 1 17 367 73
4351178317: 51 646 47 281 6 91
452662455: 574 1 291 1 271 44
...
741157: 288 6 9 70 4 38 3 9 4 61
77760221: 27 600 48 86 121 7 7
1003: 3 66 92 700 7 8 1
'),
lines(y,line) as ( --整个文件分行
select 0, substr(i,1,position(E'\n' in i)-1), substr(i,position(E'\n' in i)+1)
from aoc7_input
union all
select y+1,substr(r,1,position(E'\n' in r)-1), substr(r,position(E'\n' in r)+1)
from lines l(y,l,r) where position(E'\n' in r)>0
),
targets(y,target) as ( --每行按:拆分成左值和右值
select y, substr(line,1,position(':' in line)-1)::bigint from lines where line like '%: %'
),
arguments(y,x,v) as ( --将右值按左值拆分成多行，每行有行号和数字，作为参数
select y, 0 as x, substr(line,1,position(' ' in line)-1)::bigint as v, substr(line,position(' ' in line)+1) as r
from (select y, substr(line,position(': ' in line)+2) as line from lines where line like '%: %') s
union all
select y, x+1, case when r like '% %' then substr(r,1,position(' ' in r)-1) else r end::bigint, case when r like '% %' then substr(r,position(' ' in r)+1) else '' end
from arguments where r<>''
),
expansions(y,x,target,c,cc) as ( --将参数和3种运算符笛卡尔积，迭代条件是按左值和行号关联，当前操作数小于或等于左值。
select y,0,target,v,false from targets natural join arguments where x=0
union all
select y,x,target,nv,cc
from (
select e.y,a.x as x,target,case when d=1 then c+v when d=2 then c*v else (c::text||v::text)::bigint end as nv, cc or d=3 as cc
from expansions e, arguments a, generate_series(1,3) s(d)
where e.y=a.y and e.x+1=a.x) s where nv<=target
),
successes(y,cc) as (
select y,cc
from expansions e
where target=c
and x=(select max(x) from arguments a where e.y=a.y)
)
select (select sum(target) from targets where y in (select y from successes where not cc)) as part1,
(select sum(target) from targets where y in (select y from successes)) as part2
观察可见，前两步操作差别不大，关键区别是，它没有用完整的参数数组做临时表的一列，而是拆分成了单独的参数，每轮迭代只取用到的参数。而且这种算法有机会提前剪枝，对于已达标的迭代及时结束。
下面计算它的空间和时间复杂度。暂不考虑剪枝，因为每次只读取一个参数，参数的复制次数就是参数个数，而不是参数个数平方。
select sum((lenc*(power(3,(lenc-1))))*cnt) from s;
memory D .read 2407ana.sql
┌────────────────────────────────────────────┐
│ sum(((lenc * power(3, (lenc - 1))) * cnt)) │
│ double │
├────────────────────────────────────────────┤
│ 183286719.0 │
│ (183.29 million) │
└────────────────────────────────────────────┘
在笛卡尔积不变的情况下，复制总次数已经降到了我的算法两种运算符6倍的水平。
select sum((lenc*(power(3,0.8*(lenc-1))))*cnt) from s;
memory D .read 2407ana.sql
┌────────────────────────────────────────────────────┐
│ sum(((lenc * power(3, (0.8 * (lenc - 1)))) * cnt)) │
│ double │
├────────────────────────────────────────────────────┤
│ 18681659.305710133 │
│ (18.68 million) │
└────────────────────────────────────────────────────┘
如果考虑剪枝，假定平均迭代到参数80%时退出，低于我的算法两种运算符的水平。这就解释了文章开头所示的同时计算两种和三种运算符比我只计算两种还快的事实。
neumannt的程序确实高明，值得学习，本例再次警示我们，设计算法要避免不必要的笛卡尔积。而且，剪枝要尽早。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
