Part One
推箱子的游戏，而且一次可以推动多个箱子，求最后箱子的分布情况
推箱子，很像之前2023 Day 14的题目，同样是一次可以推动多个箱子。
首先将迷宫保存到表中，后续更新表中的记录
drop table matrix;
create table matrix as
with origin as (
SELECT row_number() over () as rn, line
FROM lance_input
)
SELECT rn as _row, idx as _col, pos
FROM origin, regexp_split_to_table(line, '') with ordinality as x(pos, idx)
WHERE rn < (select rn from origin where line is null);
create index idx_row on matrix(_row);
create index idx_col on matrix(_col);
create index idx_pos on matrix(pos);
先判断机器人的下一步位置，是否可以继续前进。
如果前方空闲，则直接前进；如果前方是墙，则无法前进；如果前方是箱子，则需要判断需要一次推动几个箱子，这里会使用正则来匹配可以连续推动的箱子。
postgres=# select (regexp_matches('OOO.#', '^(O+\.)'))[1];
regexp_matches
----------------
OOO.
(1 row)
完整的推箱子函数如下
CREATE OR REPLACE FUNCTION move_robot(direction VARCHAR)
RETURNS VOID AS $$
DECLARE
robot_row INTEGER;
robot_col INTEGER;
target_row INTEGER;
target_col INTEGER;
empty_pos INTEGER;
next_pos VARCHAR;
move_pos VARCHAR;
BEGIN
SELECT _row, _col INTO robot_row, robot_col FROM matrix WHERE pos = '@';
target_row := robot_row;
target_col := robot_col;
IF direction = '>' THEN
target_col := robot_col + 1;
END IF;
IF direction = '<' THEN
target_col := robot_col - 1;
END IF;
IF direction = '^' THEN
target_row := robot_row - 1;
END IF;
IF direction = 'v' THEN
target_row := robot_row + 1;
END IF;
empty_pos := (select count(*) from matrix where _row = target_row AND _col = target_col AND pos = '.');
IF empty_pos = 1 THEN
IF direction = '>' THEN
UPDATE matrix SET _col = (CASE WHEN _col = robot_col THEN _col + 1 ELSE _col - 1 END)
WHERE _row = robot_row AND _col IN (robot_col, robot_col + 1);
END IF;
IF direction = '<' THEN
UPDATE matrix SET _col = (CASE WHEN _col = robot_col THEN _col - 1 ELSE _col + 1 END)
WHERE _row = robot_row AND _col IN (robot_col, robot_col - 1);
END IF;
IF direction = '^' THEN
UPDATE matrix SET _row = (CASE WHEN _row = robot_row THEN _row - 1 ELSE _row + 1 END)
WHERE _row IN (robot_row, robot_row - 1) AND _col = robot_col;
END IF;
IF direction = 'v' THEN
UPDATE matrix SET _row = (CASE WHEN _row = robot_row THEN _row + 1 ELSE _row - 1 END)
WHERE _row IN (robot_row, robot_row + 1) AND _col = robot_col;
END IF;
ELSE
IF direction = '>' THEN
next_pos := (select substring(string_agg(pos, '' order by _col), robot_col + 1) from matrix where _row = robot_row);
END IF;
IF direction = '<' THEN
next_pos := (select reverse(substring(string_agg(pos, '' order by _col), 1, robot_col - 1)) from matrix where _row = robot_row);
END IF;
IF direction = '^' THEN
next_pos := (select reverse(substring(string_agg(pos, '' order by _row), 1, robot_row - 1)) from matrix where _col = robot_col);
END IF;
IF direction = 'v' THEN
next_pos := (select substring(string_agg(pos, '' order by _row), robot_row + 1) from matrix where _col = robot_col);
END IF;
move_pos := (select (regexp_matches(next_pos, '^(O+\.)'))[1]);
IF move_pos IS NULL THEN
RETURN;
END IF;
IF direction = '>' THEN
UPDATE matrix set _col = (case when _col = robot_col + length(move_pos) THEN robot_col ELSE _col + 1 END)
WHERE _row = robot_row
AND _col between robot_col AND robot_col + length(move_pos);
END IF;
IF direction = '<' THEN
UPDATE matrix set _col = (case when _col = robot_col - length(move_pos) THEN robot_col ELSE _col - 1 END)
WHERE _row = robot_row
AND _col between robot_col - length(move_pos) AND robot_col;
END IF;
IF direction = '^' THEN
UPDATE matrix set _row = (case when _row = robot_row - length(move_pos) THEN robot_row ELSE _row - 1 END)
WHERE _col = robot_col
AND _row between robot_row - length(move_pos) AND robot_row;
END IF;
IF direction = 'v' THEN
UPDATE matrix set _row = (case when _row = robot_row + length(move_pos) THEN robot_row ELSE _row + 1 END)
WHERE _col = robot_col
AND _row between robot_row AND robot_row + length(move_pos);
END IF;
END IF;
END;
$$ LANGUAGE plpgsql;
Day Two
箱子从O变成了占用两个位置的[]，且一个箱子可能推动两个箱子，从而继续推动更多的箱子。
这部分最大的困难点，在于箱子在水平方向上变成了两个位置的[]。
对于左右方向上的移动，还是同样的一个高度，其实难度不大。可以将[]替换为O，然后按照第一部分的方式计算好后，再替换回去
next_pos := (select replace(substring(string_agg(pos, '' order by _col), robot_col + 1), '[]', 'O') from matrix where _row = robot_row);
move_pos := (select (regexp_matches(next_pos, '^(O+\.)'))[1]);
move_pos := replace(move_pos, 'O', '[]');
但是对于上下方向上的，难度就大了，主要在于水平方向宽度增加，一个箱子可以推动两个箱子，这两个箱子再推动了更多的箱子，如下图所示。
....[][][][].....
.....[]..[]......
......[][].......
.......[]........
所以，判断上下方向上是否前进是比较困难的，需要通过递归查询来查找这个方向上最后是否可以前进一步
每次找到一个上游后，对这个上游节点打标，0代表前方有墙不可移动，1代表前方还是箱子，2代表前方无障碍，可以前进。
flag=1的时候就需要继续查询了，而只要有一次遇到0，则整体都不可移动。
SELECT _row, _col,
CASE WHEN up_pos like '%#%' THEN 0
WHEN up_pos like '%[%' OR up_pos like '%]%' THEN 1
ELSE 2
END as flag
FROM (
SELECT _row, _col,
(select string_agg(pos, '' order by _col) from matrix where _row = t._row - 1 and _col in (t._col, t._col + 1)) as up_pos
FROM (
SELECT _row, case when pos = '[' then _col else _col - 1 end as _col
FROM matrix
WHERE _row = robot_row - 1
AND _col = robot_col
) t
) t
UNION ALL
SELECT _row, _col,
CASE WHEN up_pos like '%#%' THEN 0
WHEN up_pos like '%[%' OR up_pos like '%]%' THEN 1
ELSE 2
END as flag
FROM (
SELECT b._row, b._col,
(select string_agg(pos, '' order by _col) from matrix where _row = b._row - 1 and _col in (b._col, b._col + 1)) as up_pos
FROM boxes a
JOIN matrix b
ON b._row = a._row - 1
AND b.pos = '['
AND (b._col = a._col OR b._col = a._col - 1 OR b._col = a._col + 1)
AND flag = 1
) t
那么找到所有需要移动的位置后，则通过一次update，将这些位置进行更新
UPDATE matrix set pos = t.pos
FROM (
with recursive boxes AS (
SELECT _row, _col,
CASE WHEN up_pos like '%#%' THEN 0
WHEN up_pos like '%[%' OR up_pos like '%]%' THEN 1
ELSE 2
END as flag
FROM (
SELECT _row, _col,
(select string_agg(pos, '' order by _col) from matrix where _row = t._row - 1 and _col in (t._col, t._col + 1)) as up_pos
FROM (
SELECT _row, case when pos = '[' then _col else _col - 1 end as _col
FROM matrix
WHERE _row = robot_row - 1
AND _col = robot_col
) t
) t
UNION ALL
SELECT _row, _col,
CASE WHEN up_pos like '%#%' THEN 0
WHEN up_pos like '%[%' OR up_pos like '%]%' THEN 1
ELSE 2
END as flag
FROM (
SELECT b._row, b._col,
(select string_agg(pos, '' order by _col) from matrix where _row = b._row - 1 and _col in (b._col, b._col + 1)) as up_pos
FROM boxes a
JOIN matrix b
ON b._row = a._row - 1
AND b.pos = '['
AND (b._col = a._col OR b._col = a._col - 1 OR b._col = a._col + 1)
AND flag = 1
) t
), moved_boxes AS (
SELECT _row, _col, '[' as pos
FROM boxes b
WHERE not exists (select * From boxes where flag = 0)
UNION ALL
SELECT _row, _col + 1, ']' as pos
FROM boxes b
WHERE not exists (select * From boxes where flag = 0)
UNION ALL
SELECT robot_row, robot_col, '@' as pos
WHERE not exists (select * From boxes where flag = 0)
)
SELECT coalesce(a._row, b._row) as _row,
coalesce(a._col, b._col) as _col,
coalesce(a.pos, '.') as pos
FROM (select _row - 1 as _row, _col, pos from moved_boxes) a
FULL OUTER JOIN moved_boxes b
ON a._row = b._row
AND a._col = b._col
) t
WHERE matrix._row = t._row
AND matrix._col = t._col;