DuckDB递归CTE解AoC二进制筛选
第二部分每次筛选的条件都和前一次相关，递归CTE在这里最合适，一开始我把条件s >=c-s写成了s>=c//2, 貌似等价，其实不然，当c为奇数，除法截断取整有误差，比如s=6，c=13，s >=c-s不成立，而s>=c//2成立，导致错误，结果用了很多时间调试，这警示我们要严格按需求编写。
with recursive t as(select '00100
11110
10110
10111
10101
01111
00111
11100
10000
11001
00010
01010' t),
a as(select unnest(string_split(t,chr(10)))a from t),
b as(select 1 lv,a,(select case when s >=c-s then 1 else 0 end from( select sum(substr(a,1,1)::int)s,count(*)c from a))b from a
union all
select lv+1,a, (select case when s >=c-s then 1 else 0 end from( select sum(substr(a,lv+1-case when lv=5 then 1 else 0 end,1)::int)s,count(*)c from b where substr(a,lv,1)=b)) from b where lv<5 and substr(a,lv,1)=b),
bb as(select * from b where lv=(select max(lv)from b)and substr(a,lv,1)=b),
b2 as(select 1 lv,a,(select case when s <c-s then 1 else 0 end from( select lv,sum(substr(a,1,1)::int)s,count(*)c from a))b from a
union all
select lv+1,a, (select case when s <c-s then 1 else 0 end from( select sum(substr(a,lv+1-case when lv=5 then 1 else 0 end,1)::int)s,count(*)c from b2 where substr(a,lv,1)=b)) from b2 where lv<5 and substr(a,lv,1)=b),
bb2 as(select * from b2 where lv=(select max(lv)from b2)and (substr(a,lv,1)=b or 1=(select count(*) from b2 where lv=(select max(lv)from b2)) ))
select bb.a, bb2.a, bb.a::BITSTRING::int * bb2.a::BITSTRING::int from bb, bb2
;
总体思路是用一个b列保存当前层次对应的子字符串的出现最多次、最少次的值，首次就用全表统计，然后在迭代中用上次的结果过滤，同时生成下次要用的值。最后一步筛选无法在递归中完成，单独用子查询bb、bb2得到。
上述代码适用于示例的五位数，对于真实的输入，把代码中的5替换成12即可。
DuckDB允许在递归CTE中使用子查询、聚合函数等，大大方便了实现。
以下是递归查询产生的b表和bb表的结果，可以看出逐步筛选的过程。
┌───────┬─────────┬───────┐
│ lv │ a │ b │
│ int32 │ varchar │ int32 │
├───────┼─────────┼───────┤
│ 1 │ 00100 │ 1 │
│ 1 │ 11110 │ 1 │
│ 1 │ 10110 │ 1 │
│ 1 │ 10111 │ 1 │
│ 1 │ 10101 │ 1 │
│ 1 │ 01111 │ 1 │
│ 1 │ 00111 │ 1 │
│ 1 │ 11100 │ 1 │
│ 1 │ 10000 │ 1 │
│ 1 │ 11001 │ 1 │
│ 1 │ 00010 │ 1 │
│ 1 │ 01010 │ 1 │
│ 2 │ 11110 │ 0 │
│ 2 │ 10110 │ 0 │
│ 2 │ 10111 │ 0 │
│ 2 │ 10101 │ 0 │
│ 2 │ 11100 │ 0 │
│ 2 │ 10000 │ 0 │
│ 2 │ 11001 │ 0 │
│ 3 │ 10110 │ 1 │
│ 3 │ 10111 │ 1 │
│ 3 │ 10101 │ 1 │
│ 3 │ 10000 │ 1 │
│ 4 │ 10110 │ 1 │
│ 4 │ 10111 │ 1 │
│ 4 │ 10101 │ 1 │
│ 5 │ 10110 │ 1 │
│ 5 │ 10111 │ 1 │
├───────┴─────────┴───────┤
│ 28 rows 3 columns │
└─────────────────────────┘
┌───────┬─────────┬───────┐
│ lv │ a │ b │
│ int32 │ varchar │ int32 │
├───────┼─────────┼───────┤
│ 5 │ 10111 │ 1 │
└───────┴─────────┴───────┘
┌───────┬─────────┬───────┐
│ lv │ a │ b │
│ int32 │ varchar │ int32 │
├───────┼─────────┼───────┤
│ 1 │ 00100 │ 0 │
│ 1 │ 11110 │ 0 │
│ 1 │ 10110 │ 0 │
│ 1 │ 10111 │ 0 │
│ 1 │ 10101 │ 0 │
│ 1 │ 01111 │ 0 │
│ 1 │ 00111 │ 0 │
│ 1 │ 11100 │ 0 │
│ 1 │ 10000 │ 0 │
│ 1 │ 11001 │ 0 │
│ 1 │ 00010 │ 0 │
│ 1 │ 01010 │ 0 │
│ 2 │ 00100 │ 1 │
│ 2 │ 01111 │ 1 │
│ 2 │ 00111 │ 1 │
│ 2 │ 00010 │ 1 │
│ 2 │ 01010 │ 1 │
│ 3 │ 01111 │ 0 │
│ 3 │ 01010 │ 0 │
│ 4 │ 01010 │ 0 │
├───────┴─────────┴───────┤
│ 20 rows 3 columns │
└─────────────────────────┘
memory D .read 2103p2.txt
┌───────┬─────────┬───────┐
│ lv │ a │ b │
│ int32 │ varchar │ int32 │
├───────┼─────────┼───────┤
│ 4 │ 01010 │ 0 │
└───────┴─────────┴───────┘
.read 2103p2t.txt
┌─────────┬─────────┬────────────────────────────────────────────────────────────────────────────┐
│ a │ a │ (CAST(CAST(bb.a AS BIT) AS INTEGER) * CAST(CAST(bb2.a AS BIT) AS INTEGER)) │
│ varchar │ varchar │ int32 │
├─────────┼─────────┼────────────────────────────────────────────────────────────────────────────┤
│ 10111 │ 01010 │ 230 │
└─────────┴─────────┴────────────────────────────────────────────────────────────────────────────┘
我们也可以不用递归，用手工方法列出卷积的分组结果，肉眼从中找出每层出现最多或最少的值。以下示例仅列出每轮出现最多次值的筛选过程。
select substr(a,1,1)a1,substr(a,2,1)a2,substr(a,3,1)a3,substr(a,4,1)a4,substr(a,5,1)a5,count(*)c from a
group by rollup(a1,a2,a3,a4,a5) order by a1,a2,a3,a4,a5;
┌─────────┬─────────┬─────────┬─────────┬─────────┬───────┐
│ a1 │ a2 │ a3 │ a4 │ a5 │ c │
│ varchar │ varchar │ varchar │ varchar │ varchar │ int64 │
├─────────┼─────────┼─────────┼─────────┼─────────┼───────┤
│ 0 │ 0 │ 0 │ 1 │ 0 │ 1 │
│ 0 │ 0 │ 0 │ 1 │ NULL │ 1 │
│ 0 │ 0 │ 0 │ NULL │ NULL │ 1 │
│ 0 │ 0 │ 1 │ 0 │ 0 │ 1 │
│ 0 │ 0 │ 1 │ 0 │ NULL │ 1 │
│ 0 │ 0 │ 1 │ 1 │ 1 │ 1 │
│ 0 │ 0 │ 1 │ 1 │ NULL │ 1 │
│ 0 │ 0 │ 1 │ NULL │ NULL │ 2 │
│ 0 │ 0 │ NULL │ NULL │ NULL │ 3 │
│ 0 │ 1 │ 0 │ 1 │ 0 │ 1 │
│ 0 │ 1 │ 0 │ 1 │ NULL │ 1 │
│ 0 │ 1 │ 0 │ NULL │ NULL │ 1 │
│ 0 │ 1 │ 1 │ 1 │ 1 │ 1 │
│ 0 │ 1 │ 1 │ 1 │ NULL │ 1 │
│ 0 │ 1 │ 1 │ NULL │ NULL │ 1 │
│ 0 │ 1 │ NULL │ NULL │ NULL │ 2 │
│ 0 │ NULL │ NULL │ NULL │ NULL │ 5 │
│ 1 │ 0 │ 0 │ 0 │ 0 │ 1 │
│ 1 │ 0 │ 0 │ 0 │ NULL │ 1 │
│ 1 │ 0 │ 0 │ NULL │ NULL │ 1 │
│ 1 │ 0 │ 1 │ 0 │ 1 │ 1 │
│ 1 │ 0 │ 1 │ 0 │ NULL │ 1 │
│ 1 │ 0 │ 1 │ 1 │ 0 │ 1 │
│ 1 │ 0 │ 1 │ 1 │ 1 │ 1 │<---第5步，1和0一样多，按题意选1
│ 1 │ 0 │ 1 │ 1 │ NULL │ 2 │<---第4步，1多
│ 1 │ 0 │ 1 │ NULL │ NULL │ 3 │<---第3步，1多
│ 1 │ 0 │ NULL │ NULL │ NULL │ 4 │<---第2步，0多
│ 1 │ 1 │ 0 │ 0 │ 1 │ 1 │
│ 1 │ 1 │ 0 │ 0 │ NULL │ 1 │
│ 1 │ 1 │ 0 │ NULL │ NULL │ 1 │
│ 1 │ 1 │ 1 │ 0 │ 0 │ 1 │
│ 1 │ 1 │ 1 │ 0 │ NULL │ 1 │
│ 1 │ 1 │ 1 │ 1 │ 0 │ 1 │
│ 1 │ 1 │ 1 │ 1 │ NULL │ 1 │
│ 1 │ 1 │ 1 │ NULL │ NULL │ 2 │
│ 1 │ 1 │ NULL │ NULL │ NULL │ 3 │
│ 1 │ NULL │ NULL │ NULL │ NULL │ 7 │<---第1步，1多
│ NULL │ NULL │ NULL │ NULL │ NULL │ 12 │
├─────────┴─────────┴─────────┴─────────┴─────────┴───────┤
│ 38 rows 6 columns │
└─────────────────────────────────────────────────────────┘
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
