使用DuckDB SQL求解Advent of Code 2023第1题

原题地址 https://adventofcode.com/2023/day/1

第一部分要求把文件中每行字符串最左的数字和最右的数字合并成两位数再累计求和，

第二部分要求把文件中每行字符串中包含的数字英文单词转成数字后，再按第一部分要求执行。

难点是数字可能是1~9，事先不知道最左是哪一个，如果对每个数字逐个instr判断位置，再比较那个最左，语句势必很冗长，怎么做方便？
我的思路是利用translate函数把所有数字都替换成0，那么最左那个也被替换成0，然后用instr判断0的位置，再将那个位置的字符直接读出来，即为所求。
至于最右那个，先将整个字符串翻转，剩下就能照搬最左的处理方式读出数字，连翻转回去都不需要。

第一部分代码如下：

with t as (select a,reverse(a)ra,translate(a, '0123456789', '0000000000')ta, translate(ra, '0123456789', '0000000000')tra 
from read_csv('2301-input.txt',delim=',', header=0)t(a))
select sum(substr(a, instr(ta, '0'), 1)::int *10 +substr(ra, instr(tra, '0'), 1)::int) from t;

成功通过了。

第二部分的9个英文单词长度不一没法用简单方法统一处理，首先想到是嵌套replace，如下所示

with t0 as(select replace(replace(replace(replace(replace(replace(replace(replace(replace
(a, 'one','1'), 'two','2'), 'three','3'), 'four','4'), 'five','5'), 'six','6'), 'seven','7'), 'eight','8'), 'nine','9') a 
from read_csv('2301-input.txt',delim=',', header=0)t(a)) 
,t as (select a,reverse(a)ra,translate(a, '0123456789', '0000000000')ta, translate(ra, '0123456789', '0000000000')tra from t0)
select sum(substr(a, instr(ta, '0'), 1)::int *10 +substr(ra, instr(tra, '0'), 1)::int) from t;

执行没问题，但答案错误。
仔细看题中的示例数据，发现了出错原因。
如果一行中有多个数字的英文单词，上述写法是按one、two、three…顺序替换的，而题目要求是先替换最左的，像twone这种包含两个单词，两个单词又分享同一个字母的情况，应该替换成2ne，而上述代码替换成tw1。
现在问题就变成了与第一部分类似，显然不能先instr、找出最左再替换，那样太繁琐，这就要利用ASCII码数字都小于小写字母这个事实，如果将字符串分别用one、two、three…替换，比较替换后的字符串，最小的那个就是替换了最左的那个单词后的。
比如上面的2ne就比tw1要小。而多行比较最小，用min函数即可，所以有了以下代码：

with t(c,d)as(values('one','1'),('two','2'),('three','3'),('four','4'),('five','5'),('six','6'),('seven','7'),('eight','8'),('nine','9')),
a as(from read_csv('2301-input.txt',delim=',', header=0)t(a)) 
,b as(select min(replace(a,c,d ))m,a from a, t group by a )
--将替换了最左英文单词的字符串翻转后，查找翻转的英文字母替换数字，再翻转回去
,d as(select reverse(min(replace(reverse(m),reverse(c),d )))a,m from b, t group by m )
,tr as (select a,reverse(a)ra,translate(a, '0123456789', '0000000000')ta, translate(ra, '0123456789', '0000000000')tra from d)
select sum(substr(a, instr(ta, '0'), 1)::int *10 +substr(ra, instr(tra, '0'), 1)::int) from tr;

结果对了。