使用DuckDB SQL求解Advent of Code 2024第7题

原创已于 2025-11-20 15:07:08 修改 · 316 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库 #DuckDB #Advent of Code #算法

于 2025-11-20 13:56:53 首次发布

编程语言软件工程同时被 2 个专栏收录

249 篇文章

订阅专栏

数据库

160 篇文章

订阅专栏

编程达人挑战赛·第4期 10w+人浏览 240人参与

题目原始地址 https://adventofcode.com/2024/day/7
要求在一些缺少运算符(+和*)的数列中找出能够计算出左侧的值的行，并把这些行左侧的值加起来。
示例数据：

190: 10 19
3267: 81 40 27
83: 17 5
156: 15 6
7290: 6 8 6 15
161011: 16 10 13
192: 17 8 14
21037: 9 7 18 13
292: 11 6 16 20

把以上内容存成文件2407-input.txt，希望用DuckDB的query表函数实现表达式计算。比如，

select case when 3=(from query('select 1+2 a')) then 1 end;

返回1。

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,listagg(c[i]||op[case when (b&(1<<i)=0)then 1 else 2 end] ,''order by i)ex from t,
    (select ['+','*'] op ) ,
    (select i from range(1,len(c)+1)t(i)),
    (select b from range(1,1<<(len(c)-1)+1)t(b))
    group by rn,s,c,b )
from s;
select (from query('select '||ex[:len(ex)-1]||s)) ev,* from s where rn<3;

报错了，因为query函数只支持字面量。

Table function "query" does not support lateral join column parameters - cannot use column "ex" in this context.
The function only supports literals as parameters.

用如下语句可以把数字和运算符合并为表达式

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,[c[i]||case when i=len(c) then '' when (b&(1<<(i-1))=0)then '+' else '*' end for i in range(1,len(c)+1)] ex from t,
      (select b from range(1,(1<<(len(c)-1))+1)t(b))
      )
select rn,s,c,b,array_to_string(ex,'')||'='||s exp from s group by rn,s,c,b,ex order by rn,s,c,b;


┌───────┬────────┬─────────────────┬───────┬─────────────────┐
│  rn   │   s    │        c        │   b   │       exp       │
│ int64 │ int64  │    varchar[]    │ int64 │     varchar     │
├───────┼────────┼─────────────────┼───────┼─────────────────┤
│     1 │    190 │ [10, 19]        │     1 │ 10*19=190       │
│     1 │    190 │ [10, 19]        │     2 │ 10+19=190       │
│     2 │   3267 │ [81, 40, 27]    │     1 │ 81*40+27=3267   │
│     2 │   3267 │ [81, 40, 27]    │     2 │ 81+40*27=3267   │
│     2 │   3267 │ [81, 40, 27]    │     3 │ 81*40*27=3267   │
│     2 │   3267 │ [81, 40, 27]    │     4 │ 81+40+27=3267   │

然后把多行条件表达式用or连接起来

D select s,group_concat(exp, ' or ') from tmp group by s;
┌────────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│   s    │                                                              group_concat(exp, ' or ')                                                               │
│ int64  │                                                                       varchar                                                                        │
├────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│   3267 │ 81*40+27=3267 or 81+40*27=3267 or 81*40*27=3267 or 81+40+27=3267                                                                                     │
│     83 │ 17*5=83 or 17+5=83                                                                                                                                   │
│   7290 │ 6*8+6+15=7290 or 6+8*6+15=7290 or 6*8*6+15=7290 or 6+8+6*15=7290 or 6*8+6*15=7290 or 6+8*6*15=7290 or 6*8*6*15=7290 or 6+8+6+15=7290                 │
│ 161011 │ 16*10+13=161011 or 16+10*13=161011 or 16*10*13=161011 or 16+10+13=161011                                                                             │
│    190 │ 10*19=190 or 10+19=190                                                                                                                               │
│  21037 │ 9*7+18+13=21037 or 9+7*18+13=21037 or 9*7*18+13=21037 or 9+7+18*13=21037 or 9*7+18*13=21037 or 9+7*18*13=21037 or 9*7*18*13=21037 or 9+7+18+13=21037 │
│    156 │ 15*6=156 or 15+6=156                                                                                                                                 │
│    192 │ 17*8+14=192 or 17+8*14=192 or 17*8*14=192 or 17+8+14=192                                                                                             │
│    292 │ 11*6+16+20=292 or 11+6*16+20=292 or 11*6*16+20=292 or 11+6+16*20=292 or 11*6+16*20=292 or 11+6*16*20=292 or 11*6*16*20=292 or 11+6+16+20=292         │
└────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

想要直接把字符串表达式转为逻辑值，行不通。

D select sum(s) from (select rn,s,group_concat(exp, ' or ')exp2 from tmp group by rn,s) where exp2::bool=True;
Conversion Error:
Could not convert string '10*19=190 or 10+19=190' to BOOL when casting from source column exp2

LINE 1: ..._concat(exp, ' or ')exp2 from tmp group by rn,s) where exp2::bool=True;

一个sql不够就再来一个，想到先把上述查询拼接为一个大SQL语句，然后执行

D select 'select sum(s) from(' union all select 'select '||s||' s where '||exp2||' union all' from (select rn,s,group_concat(exp, ' or ')exp2 from tmp group by rn,s) union all select 'select 0)';
select sum(s) from(
select 161011 s where 16*10+13=161011 or 16+10*13=161011 or 16*10*13=161011 or 16+10+13=161011 union all
select 3267 s where 81*40+27=3267 or 81+40*27=3267 or 81*40*27=3267 or 81+40+27=3267 union all
select 156 s where 15*6=156 or 15+6=156 union all
select 292 s where 11*6+16+20=292 or 11+6*16+20=292 or 11*6*16+20=292 or 11+6+16*20=292 or 11*6+16*20=292 or 11+6*16*20=292 or 11*6*16*20=292 or 11+6+16+20=292 union all
select 192 s where 17*8+14=192 or 17+8*14=192 or 17*8*14=192 or 17+8+14=192 union all
select 83 s where 17*5=83 or 17+5=83 union all
select 21037 s where 9*7+18+13=21037 or 9+7*18+13=21037 or 9*7*18+13=21037 or 9+7+18*13=21037 or 9*7+18*13=21037 or 9+7*18*13=21037 or 9*7*18*13=21037 or 9+7+18+13=21037 union all
select 190 s where 10*19=190 or 10+19=190 union all
select 7290 s where 6*8+6+15=7290 or 6+8*6+15=7290 or 6*8*6+15=7290 or 6+8+6*15=7290 or 6*8+6*15=7290 or 6+8*6*15=7290 or 6*8*6*15=7290 or 6+8+6+15=7290 union all
select 0)
D .output sql2407.txt
D select 'select sum(s) from(' union all select 'select '||s||' s where '||exp2||' union all' from (select rn,s,group_concat(exp, ' or ')exp2 from tmp group by rn,s) union all select 'select 0)';
D .output
D .read sql2407.txt
3457

算出的值有误，正确答案是3749，原因是题目要求从左到右计算，不遵守四则运算规则。

D select 161011 s where 16*10+13=161011 or 16+10*13=161011 or 16*10*13=161011 or 16+10+13=161011 union all
  select 3267 s where 81*40+27=3267 or 81+40*27=3267 or 81*40*27=3267 or 81+40+27=3267 union all
  select 156 s where 15*6=156 or 15+6=156 union all
  select 292 s where 11*6+16+20=292 or 11+6*16+20=292 or 11*6*16+20=292 or 11+6+16*20=292 or 11*6+16*20=292 or 11+6*16*20=292 or 11*6*16*20=292 or 11+6+16+20=292 union all
  select 192 s where 17*8+14=192 or 17+8*14=192 or 17*8*14=192 or 17+8+14=192 union all
  select 83 s where 17*5=83 or 17+5=83 union all
  select 21037 s where 9*7+18+13=21037 or 9+7*18+13=21037 or 9*7*18+13=21037 or 9+7+18*13=21037 or 9*7+18*13=21037 or 9+7*18*13=21037 or 9*7*18*13=21037 or 9+7+18+13=21037 union all
  select 190 s where 10*19=190 or 10+19=190 union all
  select 7290 s where 6*8+6+15=7290 or 6+8*6+15=7290 or 6*8*6+15=7290 or 6+8+6*15=7290 or 6*8+6*15=7290 or 6+8*6*15=7290 or 6*8*6*15=7290 or 6+8+6+15=7290 union all
  select 0;
3267
190
0

而上述表达式漏掉了292，它正好是先做加法能算出来，

D select (11 + 6) * 16 + 20;
292

用括号来改变优先级，这么写就算得了正确答案。

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,[case when i=1 then repeat('(',len(c))else''end|| c[i]||case when i=len(c) then ')' when (b&(1<<(i-1))=0)then ')+' else ')*' end for i in range(1,len(c)+1)] ex from t,
      (select b from range(1,(1<<(len(c)-1))+1)t(b))
      ),
e as(select rn,s,c,b,array_to_string(ex,'')||'='||s exp from s group by rn,s,c,b,ex order by rn,s,c,b)
select 'select sum(s) from(' union all select 'select '||s||' s where '||exp2||' union all' from (select rn,s,group_concat(exp, ' or ')exp2 from e group by rn,s) union all select 'select 0)';

select sum(s) from(
select 190 s where ((10)*19)=190 or ((10)+19)=190 union all
select 161011 s where (((16)*10)+13)=161011 or (((16)+10)*13)=161011 or (((16)*10)*13)=161011 or (((16)+10)+13)=161011 union all
select 83 s where ((17)*5)=83 or ((17)+5)=83 union all
select 156 s where ((15)*6)=156 or ((15)+6)=156 union all
select 192 s where (((17)*8)+14)=192 or (((17)+8)*14)=192 or (((17)*8)*14)=192 or (((17)+8)+14)=192 union all
select 7290 s where ((((6)*8)+6)+15)=7290 or ((((6)+8)*6)+15)=7290 or ((((6)*8)*6)+15)=7290 or ((((6)+8)+6)*15)=7290 or ((((6)*8)+6)*15)=7290 or ((((6)+8)*6)*15)=7290 or ((((6)*8)*6)*15)=7290 or ((((6)+8)+6)+15)=7290 union all
select 21037 s where ((((9)*7)+18)+13)=21037 or ((((9)+7)*18)+13)=21037 or ((((9)*7)*18)+13)=21037 or ((((9)+7)+18)*13)=21037 or ((((9)*7)+18)*13)=21037 or ((((9)+7)*18)*13)=21037 or ((((9)*7)*18)*13)=21037 or ((((9)+7)+18)+13)=21037 union all
select 3267 s where (((81)*40)+27)=3267 or (((81)+40)*27)=3267 or (((81)*40)*27)=3267 or (((81)+40)+27)=3267 union all
select 292 s where ((((11)*6)+16)+20)=292 or ((((11)+6)*16)+20)=292 or ((((11)*6)*16)+20)=292 or ((((11)+6)+16)*20)=292 or ((((11)*6)+16)*20)=292 or ((((11)+6)*16)*20)=292 or ((((11)*6)*16)*20)=292 or ((((11)+6)+16)+20)=292 union all
select 0)

D .output sql2407.txt
D 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,[case when i=1 then repeat('(',len(c))else''end|| c[i]||case when i=len(c) then ')' when (b&(1<<(i-1))=0)then ')+' else ')*' end for i in range(1,len(c)+1)] ex from t,
        (select b from range(1,(1<<(len(c)-1))+1)t(b))
        ),
  e as(select rn,s,c,b,array_to_string(ex,'')||'='||s exp from s group by rn,s,c,b,ex order by rn,s,c,b)
  select 'select sum(s) from(' union all select 'select '||s||' s where '||exp2||' union all' from (select rn,s,group_concat(exp, ' or ')exp2 from e group by rn,s) union all select 'select 0)';
D .output
D .read sql2407.txt
3749

既然是从左到右逐步计算，那么可以用递归子查询做到，一个SQL也可以。

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] s
  union
  select lv+1,c,case when (b&(1<<(lv-1))=0)then s::int+c[lv+1]::int else s::int*c[lv+1]::int end from x where lv<len(c))
  select s from x )
  from t,
         (select b from range(1,(1<<(len(c)-1))+1)t(b))
  
          )
  from s;
1|190|[10, 19]|2|29
2|3267|[81, 40, 27]|4|148
3|83|[17, 5]|2|22
4|156|[15, 6]|2|21
5|7290|[6, 8, 6, 15]|8|35
6|161011|[16, 10, 13]|4|39
7|192|[17, 8, 14]|4|39
8|21037|[9, 7, 18, 13]|8|47
9|292|[11, 6, 16, 20]|8|53
1|190|[10, 19]|1|190

最终计算的时候要注意对于像3267这种多行的排重，用distinct s解决。

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
union
select lv+1,c,case when (b&(1<<(lv-1))=0)then su::int+c[lv+1]::int else su::int*c[lv+1]::int end from x where lv<len(c))
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);

3749

第2部分加入了字符串合并操作，用二进制位不足以表示，用包含123的十进制数代替保存运算符，取数时用子字符串表示，如下所示

with recursive t as (select 1 a union select 2 union select 3),t1 as(select 1 lv,a b from t union all select lv+1, b*10+a from t,t1 where lv<3) from t1; 
┌────┬─────┐
│ lv │  b  │
├────┼─────┤
│ 1  │ 3   │
│ 1  │ 1   │
│ 1  │ 2   │
│ 2  │ 33  │
│ 2  │ 13  │
│ 2  │ 23  │
│ 2  │ 31  │
│ 2  │ 11  │
│ 2  │ 21  │
│ 2  │ 32  │
│ 2  │ 12  │
│ 2  │ 22  │
│ 3  │ 333 │
│ 3  │ 133 │

如下是完整实现，与示例中的结果相同

with recursive t0 as (select 1 a union select 2 union select 3),
t1 as(select 1 lv,a b from t0 union all select lv+1, b*10+a from t0,t1 where lv<3) ,

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
union
select lv+1,c,case substr(b,lv,1) when 1 then su::int+c[lv+1]::int when 2 then su::int*c[lv+1]::int else (su||c[lv+1])::int end  from x where lv<len(c))
select su from x )su
from t,
       (select b::text b from t1 where lv=len(c)-1)

        )
select sum(s) from(select sum(distinct s)s from s where s=su group by rn);

┌────────┐
│ sum(s) │
├────────┤
│ 11387  │
└────────┘