利用Duckdb求解Advent of Code 2022第7题 目录大小

原题地址：https://adventofcode.com/2022/day/7

第 7 天：设备上没有剩余空间

随着探险队行进，你能听到鸟儿鸣叫和雨滴打在树叶上的声音。偶尔，你甚至能听到远处更响亮的声音；这里的动物到底能长多大？

精灵们给你的设备除了通信系统外还有其他问题。你尝试运行一个系统更新：

$ system-update --please --pretty-please-with-sugar-on-top
Error: No space left on device

也许你可以删除一些文件来为更新腾出空间？

你浏览文件系统以评估情况，并保存了终端输出结果（你的谜题输入）。例如：

$ cd /
$ ls
dir a
14848514 b.txt
8504156 c.dat
dir d
$ cd a
$ ls
dir e
29116 f
2557 g
62596 h.lst
$ cd e
$ ls
584 i
$ cd ..
$ cd ..
$ cd d
$ ls
4060174 j
8033020 d.log
5626152 d.ext
7214296 k

文件系统由一棵文件（纯数据）和目录（可以包含其他目录或文件）的树组成。最外层的目录称为 /。你可以在文件系统中导航，进入或离开目录，并列出当前所在目录的内容。

在终端输出中，以 $ 开头的行是你执行的命令，非常像一些现代计算机：

• cd 表示更改目录。这会改变当前目录是哪个目录，但具体结果取决于参数：
  • cd x 向下一级：它在当前目录中查找名为 x 的目录并将其设为当前目录。
  • cd .. 向上一级：它找到包含当前目录的目录，然后将该目录设为当前目录。
  • cd / 将当前目录切换到最外层目录 /。
• ls 表示列表。它打印出当前目录直接包含的所有文件和目录：
  • 123 abc 表示当前目录包含一个名为 abc、大小为 123 的文件。
  • dir xyz 表示当前目录包含一个名为 xyz 的目录。

根据上面示例中的命令和输出，你可以确定文件系统在视觉上看起来像这样：

- / (dir)
  - a (dir)
    - e (dir)
      - i (file, size=584)
    - f (file, size=29116)
    - g (file, size=2557)
    - h.lst (file, size=62596)
  - b.txt (file, size=14848514)
  - c.dat (file, size=8504156)
  - d (dir)
    - j (file, size=4060174)
    - d.log (file, size=8033020)
    - d.ext (file, size=5626152)
    - k (file, size=7214296)

这里，有四个目录：/（最外层目录）、a 和 d（在 / 中）、以及 e（在 a 中）。这些目录还包含各种大小的文件。

由于磁盘已满，你的第一步可能是找到适合删除的目录。为此，你需要确定每个目录的总大小。一个目录的总大小是它直接或间接包含的文件的大小之和。（目录本身不计为有任何固有大小。）

上面目录的总大小可以如下找到：

• 目录 e 的总大小是 584，因为它包含一个大小为 584 的文件 i，没有其他目录。
• 目录 a 的总大小是 94853，因为它包含文件 f（大小 29116）、g（大小 2557）和 h.lst（大小 62596），再加上间接包含文件 i（a 包含 e，而 e 包含 i）。
• 目录 d 的总大小是 24933642。
• 作为最外层目录，/ 包含每个文件。它的总大小是 48381165，即每个文件大小的总和。

首先，找到所有总大小最多为 100000 的目录，然后计算它们总大小的总和。在上面的例子中，这些目录是 a 和 e；它们总大小的总和是 95437 (94853 + 584)。（如本例所示，此过程可能会多次计算文件！）

找到所有总大小最多为 100000 的目录。这些目录的总大小之和是多少？

解题思路：
用一个数组跟踪当前目录，先计算每个目录下文件的大小，进而根据子目录包含关系，计算包含子目录和文件的总大小，最后按条件筛选
当前目录利用$ cd开头的字符串中空格以后的内容确定，..回退一层，其余前进一层，文件大小根据不以$ cd开头的字符串中也不含dir来判断文件，取空格前的数字字符串转为整数。

with recursive t as(select '$ cd /
$ ls
dir a
14848514 b.txt
8504156 c.dat
dir d
$ cd a
$ ls
dir e
29116 f
2557 g
62596 h.lst
$ cd e
$ ls
584 i
$ cd ..
$ cd ..
$ cd d
$ ls
4060174 j
8033020 d.log
5626152 d.ext
7214296 k' t), 
b as(select row_number()over()rn,b from(select unnest(string_split(t,chr(10)))b from t)), 
p as(select 1 lv, 0 dirlv, ['/' ] pa, 0 siz
union all
select lv+1, case when instr(b,'$ cd ' )=1 and instr(b,'$ cd ..' )=0 then dirlv+1 when  instr(b,'$ cd ..' )=1 then dirlv-1 else dirlv end, 
case when instr(b,'$ cd ' )=1 and instr(b,'$ cd ..' )=0 then pa||[substr(b, 6)] when  instr(b,'$ cd ..' )=1 then pa[:len(pa)-1] else pa end, 
case when instr(b,'$' )=0 and instr(b,'dir ' )<>1 then substr(b, 1, instr(b,' ')-1)::int else 0 end
from p, b where rn=lv+1 and lv<(select max(rn)from b)
)
, s as(select pa, sum(siz)sumz from p group by pa order by pa)
, ta as(select pa, sum(sumz)sumz from (select pa[:i] pa , sumz from s, range(1, 14)t(i) where i<=len(pa)) group by pa order by pa)
select sum(sumz) from ta where sumz<=100000
;

这里硬编码的常数14需要改成实际数据最深目录深度。
上述ta还有如下两种等价写法，一种是张泽鹏先生提供的，不需要子查询，

, ta as(select par.pa, sum(s.sumz)sumz from s par,s where par.pa=s.pa[:len(par.pa)] group by par.pa order by par.pa)

另一种不需要常数。

, ta as(select pa, sum(sumz)sumz from (select pa[:i] pa , sumz from s, range(1, len(pa)+1)t(i)) group by pa order by pa)

奇怪的是，这两种都比原始写法慢一些。

第二部分要求在总空间70000000的盘上删除一个目录，使剩余空间至少30000000。找到最小能完成这个任务的目录。
那就在第一部分的基础上，先用70000000减去根目录大小，得到现有剩余空间，再找空间大于或等于（30000000-剩余空间）的目录的最小值就行了。
只要修改最后一句为：

select min(sumz) from ta where sumz>=30000000-(70000000-(select sumz from ta where pa=['/']))