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

题目原始地址 https://adventofcode.com/2024/day/2

7 6 4 2 1
1 2 7 8 9
9 7 6 2 1
1 3 2 4 5
8 6 4 4 1
1 3 6 7 9

要在数列的行中找出安全行的数目，安全的标准是：

1. 数列全升或全降
2. 相邻两数的差距在1-3之间。

将以上示例数据存入2402-input.txt文件。
代码如下：

with a as(from read_csv('2402-input.txt',delim='',header=0)t(r)),
t as(select row_number()over()rn,string_split(trim(r),' ')c from a),
s as(select rn,generate_subscripts(c, 1)rn2,unnest(c) c from t),
g as(select *,lead(c)over(partition by rn order by rn2)::int-c::int g from s)
select count(rn) from t where not exists(select 1 from g where g.rn=t.rn and abs(g) not between 1 and 3)
        and not exists(select 1 from g where g.rn=t.rn having abs(sum(g.g))<>sum(abs(g.g)));

┌───────────┐
│ count(rn) │
├───────────┤
│ 2         │
└───────────┘

答案与题目给出的一致
思路是：

1. 用generate_subscripts跟踪数字在数列中的序号
2. 用分析函数lead-本数得到差距数。
3. 差距数绝对值在1-3之间，如果差距数全都同号，那么差距数的绝对值之和等于和的绝对值。

第2部分容忍在去掉一个数的情况下，新数列满足安全条件，就算原数列行安全，
思路是将每个数列依次分别去掉第一个到最后一个数，生成n-1个新数列，对新数列执行一样的判断标准。
然后将两个条件求出的数列行号去重合并，如果只算第二种，可能漏掉原来符合，新数列都不符合的情况。
但经过试验，只算第二种的结果和合并计算结果相同，那么第2部分就可以用简化的SQL答案。

with safe0 as
(with a as(from read_csv('2402-input.txt',delim='',header=0)t(r)),
t as(select row_number()over()rn,string_split(trim(r),' ')c from a),
s as(select rn,generate_subscripts(c, 1)rn2,unnest(c) c from t),
g as(select *,lead(c)over(partition by rn order by rn2)::int-c::int g from s)
select rn from t where not exists(select 1 from g where g.rn=t.rn and abs(g) not between 1 and 3)
        and not exists(select 1 from g where g.rn=t.rn having abs(sum(g.g))<>sum(abs(g.g)))), 
safe1 as
(with a as(from read_csv('2402-input.txt',delim='',header=0)t(r)),
t0 as(select row_number()over()rn,string_split(trim(r),' ')c from a),
t as(select rn,i,c[:i-1]||c[i+1:] c from t0,(select i from range(1, len(c)+1)t(i)) ) , 
s as(select rn,i, generate_subscripts(c, 1)rn2,unnest(c) c from t),
g as(select *,lead(c)over(partition by rn,i order by rn2)::int-c::int g from s)
select rn from t where not exists(select 1 from g where g.rn=t.rn and g.i=t.i and abs(g) not between 1 and 3)
        and not exists(select 1 from g where g.rn=t.rn and g.i=t.i having abs(sum(g.g))<>sum(abs(g.g))))
select count(rn) from(from safe0 union from safe1);

–简化版，只算第二种条件

with a as(from read_csv('2402-input.txt',delim='',header=0)t(r)),
t0 as(select row_number()over()rn,string_split(trim(r),' ')c from a),
t as(select rn,i,c[:i-1]||c[i+1:] c from t0,(select i from range(1, len(c)+1)t(i)) ) , 
s as(select rn,i, generate_subscripts(c, 1)rn2,unnest(c) c from t),
g as(select *,lead(c)over(partition by rn,i order by rn2)::int-c::int g from s)
select count(distinct rn) from t where not exists(select 1 from g where g.rn=t.rn and g.i=t.i and abs(g) not between 1 and 3)
        and not exists(select 1 from g where g.rn=t.rn and g.i=t.i having abs(sum(g.g))<>sum(abs(g.g)));

补记：
原数列安全时，它去掉第一个数的新数列也必然安全，所以不会出现我之前担心的遗漏情况。第2部分的简化版SQL在理论上成立。