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

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

有两列数，用三个空格隔开，第一部分要求将左右两列数按大小排序配对，找出每对数的距离（差的绝对值）之和。

第二部分要求，对左列的每个数，找出它在右列出现的次数，将左列数乘以次数，然后求和。

这是个简单题，排序聚合，最适合用SQL解决

--方法1, 分析函数
with t as (select row_number()over(order by a::int)rn, row_number()over(order by b::int)rn2 , * from read_csv('2401-input.txt',delim='   ', header=0)t(a, b))
select sum(abs(t.a::int-t1.b::int)) from t,t t1 where t.rn=t1.rn2;

--方法2，利用DuckDB特有的Positional Join
with t as (select a::int a, b::int b from read_csv('2401-input.txt',delim='   ', header=0)t(a, b))
select sum(abs(t.a-t1.b)) from (from t order by a)t POSITIONAL JOIN (from t order by b)t1;


--方法1, 标量子查询
with t as (select a::int a, b::int b from read_csv('2401-input.txt',delim='   ', header=0)t(a, b))
select sum(t.a*(select count(*)c from t t1 where t.a=t1.b)) from t;

--方法2，等值连接
with t as (select a::int a, b::int b from read_csv('2401-input.txt',delim='   ', header=0)t(a, b))
select sum(t.a*t1.c) from t, (select b, count(*)c from t  group by b)t1 where t.a=t1.b;