利用Duckdb求解Advent of Code 2021第4题宾果游戏

DuckDB求解宾果游戏胜局

原创已于 2025-11-27 20:34:30 修改 · 740 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

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

于 2025-11-26 17:56:41 首次发布

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

273 篇文章

订阅专栏

数据库

184 篇文章

订阅专栏

数学

49 篇文章

订阅专栏

原题地址 https://adventofcode.com/2021/day/4

第 4 天：巨型乌贼

你已经到达海面下近 1.5 公里（近一英里）的深度，这里深得已经看不到任何阳光。然而，你能看到的是，一只巨型乌贼附着在你的潜水艇外面。

也许它想玩宾果游戏？

宾果游戏是在一组棋盘上进行的，每个棋盘由一个 5x5 的数字网格组成。数字被随机抽取，抽到的数字会在所有出现该数字的棋盘上被标记。（数字可能不会出现在所有棋盘上。）如果一个棋盘的任意一行或任意一列中的所有数字都被标记了，则该棋盘获胜。（对角线不算。）

潜水艇有一个宾果子系统，可以帮助乘客（目前是你和巨型乌贼）消磨时间。它会自动生成一个抽取数字的随机顺序和一个随机的棋盘集合（你的谜题输入）。例如：

7,4,9,5,11,17,23,2,0,14,21,24,10,16,13,6,15,25,12,22,18,20,8,19,3,26,1

22 13 17 11  0
 8  2 23  4 24
21  9 14 16  7
 6 10  3 18  5
 1 12 20 15 19

 3 15  0  2 22
 9 18 13 17  5
19  8  7 25 23
20 11 10 24  4
14 21 16 12  6

14 21 17 24  4
10 16 15  9 19
18  8 23 26 20
22 11 13  6  5
 2  0 12  3  7

在前五个数字（7, 4, 9, 5, 11）被抽出后，还没有赢家，但棋盘被标记如下（为节省空间并排显示）：

22 13 17 11  0         3 15  0  2 22        14 21 17 24  4
 8  2 23  4 24         9 18 13 17  5        10 16 15  9 19
21  9 14 16  7        19  8  7 25 23        18  8 23 26 20
 6 10  3 18  5        20 11 10 24  4        22 11 13  6  5
 1 12 20 15 19        14 21 16 12  6         2  0 12  3  7

在接下来的六个数字（17, 23, 2, 0, 14, 21）被抽出后，仍然没有赢家：

22 13 17 11  0         3 15  0  2 22        14 21 17 24  4
 8  2 23  4 24         9 18 13 17  5        10 16 15  9 19
21  9 14 16  7        19  8  7 25 23        18  8 23 26 20
 6 10  3 18  5        20 11 10 24  4        22 11 13  6  5
 1 12 20 15 19        14 21 16 12  6         2  0 12  3  7

最后，数字 24 被抽出：

22 13 17 11  0         3 15  0  2 22        14 21 17 24  4
 8  2 23  4 24         9 18 13 17  5        10 16 15  9 19
21  9 14 16  7        19  8  7 25 23        18  8 23 26 20
 6 10  3 18  5        20 11 10 24  4        22 11 13  6  5
 1 12 20 15 19        14 21 16 12  6         2  0 12  3  7

此时，第三个棋盘获胜，因为它至少有一整行或一整列的数字被标记（在这个例子中，整个顶行被标记：14 21 17 24 4）。

现在可以计算获胜棋盘的得分。首先找出该棋盘上所有未标记数字的总和；在这个例子中，总和是 188。然后，将这个总和乘以棋盘获胜时刚刚被抽出的数字 24，得到最终得分 188 * 24 = 4512。

为了确保战胜巨型乌贼，请找出哪个棋盘会最先获胜。如果你选择那个棋盘，你的最终得分是多少？

我的SQL如下

with recursive a as(select '7,4,9,5,11,17,23,2,0,14,21,24,10,16,13,6,15,25,12,22,18,20,8,19,3,26,1

22 13 17 11  0
 8  2 23  4 24
21  9 14 16  7
 6 10  3 18  5
 1 12 20 15 19

 3 15  0  2 22
 9 18 13 17  5
19  8  7 25 23
20 11 10 24  4
14 21 16 12  6

14 21 17 24  4
10 16 15  9 19
18  8 23 26 20
22 11 13  6  5
 2  0 12  3  7
' t),
n as(select row_number()over() nn,n from (select unnest(string_split(substr(t,1,instr(t,chr(10))-1),','))n from a)),
b as(select unnest(string_split(substr(t,instr(t,chr(10)||chr(10))+1),chr(10)||chr(10)))b from a),
line as(select row_number()over() rn,l from(select string_split(trim(replace(replace(b,chr(10),' '),'  ',' ')),' ')l from b)),
t as(
select 1 lv,rn,l,n,list_position(l,n)pos, 0 bm,l l2 ,false found from n,line where  nn=1
union all
select lv+1,rn,l,n.n,list_position(l,n.n)pos2, case when pos is not null then (1<<(pos-1)) else 0 end +bm,
[case when i=pos then '*' else l2[i] end for i in range(1,26)],
(select count(*)from t)<(select count(*)from line) from n,t where  nn=lv+1 and lv<99 
  and not exists(select 1 from range(5)t(i) where (bm>>i*5)&31=31) --有一整行被标记
  and not exists(select 1 from range(5)t(i) where (bm>>i)&1082401=1082401)--有一整列被标记
  and not found
)
--from t where lv>=20;
--select rn,max(lv)mlv from t group by rn;
,s as(select * from(select rn,mlv,row_number()over(order by mlv) rk from (select rn,max(lv)mlv from t group by rn)) where rk=1) 
select su::int*prevn::int score from(
SELECT list_reduce(
        list_filter(l2, lambda x: x <> '*'),
        lambda acc, x: acc::int + x::int
    )su,(select n from n where nn=lv-1)prevn from t where (rn,lv) in (select rn,mlv from s));

相应的查询结果显示，第3个棋盘在出第13个数时获胜，是最先获胜的。

│  lv   │  rn   │                                              l                                              │    n    │  pos  │    bm    │
├───────┼───────┼─────────────────────────────────────────────────────────────────────────────────────────────┼─────────┼───────┼──────────┤
│    12 │     2 │ [3, 15, 0, 2, 22, 9, 18, 13, 17, 5, 19, 8, 7, 25, 23, 20, 11, 10, 24, 4, 14, 21, 16, 12, 6] │ 24      │    19 │  3760940 │
│    12 │     3 │ [14, 21, 17, 24, 4, 10, 16, 15, 9, 19, 18, 8, 23, 26, 20, 22, 11, 13, 6, 5, 2, 0, 12, 3, 7] │ 24      │     4 │ 37294359 │
│    12 │     1 │ [22, 13, 17, 11, 0, 8, 2, 23, 4, 24, 21, 9, 14, 16, 7, 6, 10, 3, 18, 5, 1, 12, 20, 15, 19]  │ 24      │    10 │   564700 │
│    13 │     3 │ [14, 21, 17, 24, 4, 10, 16, 15, 9, 19, 18, 8, 23, 26, 20, 22, 11, 13, 6, 5, 2, 0, 12, 3, 7] │ 10      │     6 │ 37294367 │
│    13 │     1 │ [22, 13, 17, 11, 0, 8, 2, 23, 4, 24, 21, 9, 14, 16, 7, 6, 10, 3, 18, 5, 1, 12, 20, 15, 19]  │ 10      │    17 │   565212 │
│    13 │     2 │ [3, 15, 0, 2, 22, 9, 18, 13, 17, 5, 19, 8, 7, 25, 23, 20, 11, 10, 24, 4, 14, 21, 16, 12, 6] │ 10      │    18 │  4023084 │
│    14 │     1 │ [22, 13, 17, 11, 0, 8, 2, 23, 4, 24, 21, 9, 14, 16, 7, 6, 10, 3, 18, 5, 1, 12, 20, 15, 19]  │ 16      │    14 │   630748 │
│    14 │     2 │ [3, 15, 0, 2, 22, 9, 18, 13, 17, 5, 19, 8, 7, 25, 23, 20, 11, 10, 24, 4, 14, 21, 16, 12, 6] │ 16      │    23 │  4154156 │

.read 2104code.txt
┌───────┬─────────┐
│  rn   │ max(lv) │
│ int64 │  int32  │
├───────┼─────────┤
│     2 │      14 │
│     1 │      14 │
│     3 │      13 │
└───────┴─────────┘
┌───────┬───────┬───────┐
│  rn   │  mlv  │  rk   │
│ int64 │ int32 │ int64 │
├───────┼───────┼───────┤
│     3 │    13 │     1 │
└───────┴───────┴───────┘

select 1+(1<<5)+(1<<10)+(1<<15)+(1<<20) x;
┌────────────────┐
│       x        │
│     int32      │
├────────────────┤
│    1082401     │
│ (1.08 million) │
└────────────────┘
┌───────┐
│ score │
│ int32 │
├───────┤
│  4512 │
└───────┘

思路：

为了避免笛卡尔积，将每轮选出的数字不用列表存储，而是用unnest转为行，将一个棋盘转成一行数组，然后查找每轮数字在其中的位置序号，将相应的二进制位置1。如果要进一步减少重复存储，也可以对每个棋盘做unnest操作，利用位图跟踪进度即可。将位图从5的整数倍开始的5个位与5个位全都置1的二进制数（十进制32）做与操作，可以判断被检查的位是否全为1，如果全1，那么有一行被标记，同理可检查是否有一列被标记，1082401是1+(1<<5)+(1<<10)+(1<<15)+(1<<20)的结果，用以标记从首行某列开始连续相隔5位的同一列是否都置1。found标记用来表示已经找到获胜棋盘，提前结束迭代。
最后的分数计算费劲一些，棋盘要从最后一层取，利用数组reduce函数计算剩余数字总和，最后选的数字却要从上一层取。

第二部分

另一方面，尝试一个不同的策略可能是明智的：让巨型乌贼赢。

你不确定一只巨型乌贼一次能玩多少个宾果棋盘，所以与其浪费时间数它的触手，更稳妥的做法是找出哪个棋盘会最后拼成，并选择那个。这样，无论它选择剩下的哪个棋盘，它都肯定会赢。

在上面的例子中，第二个棋盘是最后拼成的，这是在数字 13 最终被叫出并且它的中间一列被完全标记后发生的。如果你继续玩到这个时候，第二个棋盘上未标记数字的总和将是 148，最终得分为 148 * 13 = 1924。

找出哪个棋盘将最后获胜。一旦它获胜，它的最终得分是多少？

这可能是最简单的第二部分，不是找出一个拼成的棋盘就结束，而是一直找到所有拼成的棋盘，然后找出其中最后拼成那个，只要把第一部分的SQL做两处修改，1.删除found停止迭代条件; 2.row_number分析函数排序加上desc表示倒序。

我的SQL如下

with recursive a as(select '7,4,9,5,11,17,23,2,0,14,21,24,10,16,13,6,15,25,12,22,18,20,8,19,3,26,1

22 13 17 11  0
 8  2 23  4 24
21  9 14 16  7
 6 10  3 18  5
 1 12 20 15 19

 3 15  0  2 22
 9 18 13 17  5
19  8  7 25 23
20 11 10 24  4
14 21 16 12  6

14 21 17 24  4
10 16 15  9 19
18  8 23 26 20
22 11 13  6  5
 2  0 12  3  7
' t),
n as(select row_number()over() nn,n from (select unnest(string_split(substr(t,1,instr(t,chr(10))-1),','))n from a)),
b as(select unnest(string_split(substr(t,instr(t,chr(10)||chr(10))+1),chr(10)||chr(10)))b from a),
line as(select row_number()over() rn,l from(select string_split(trim(replace(replace(b,chr(10),' '),'  ',' ')),' ')l from b)),
t as(
select 1 lv,rn,l,n,list_position(l,n)pos, 0 bm,l l2 ,false found from n,line where  nn=1
union all
select lv+1,rn,l,n.n,list_position(l,n.n)pos2, case when pos is not null then (1<<(pos-1)) else 0 end +bm,
[case when i=pos then '*' else l2[i] end for i in range(1,26)],
(select count(*)from t)<(select count(*)from line) from n,t where  nn=lv+1 and lv<99 
  and not exists(select 1 from range(5)t(i) where (bm>>i*5)&31=31) --有一整行被标记
  and not exists(select 1 from range(5)t(i) where (bm>>i)&1082401=1082401)--有一整列被标记
  --and not found
)
,s as(select * from(select rn,mlv,row_number()over(order by mlv desc) rk from (select rn,max(lv)mlv from t group by rn)) where rk=1) 
select su::int*prevn::int score from(
SELECT list_reduce(
        list_filter(l2, lambda x: x <> '*'),
        lambda acc, x: acc::int + x::int
    )su,(select n from n where nn=lv-1)prevn from t where (rn,lv) in (select rn,mlv from s));