用纯 SQL 硬刚 Advent of Code？ClickHouse 把「不可能」变成了 12 天的现实
Zach Naimon约 43 分钟本文字数：39754；估计阅读时间：100 分钟
作者：Zach Naimon
每年十二月，编程社区都会迎来一场集体仪式：Advent of Code（https://adventofcode.com/）。这个活动由 Eric Wastl 创建，是一个由小型编程谜题组成的降临节日历，从 12 月 1 日开始到 12 日结束，每天都会发布一个全新的挑战。
这些任务并不是常见的“修复一个 bug”或“构建一个 API”。它们是偏算法方向的挑战，涉及复杂的数据结构、图遍历、三维几何、元胞自动机模拟以及路径查找算法。因此，开发者通常会选择 Python、Rust、Go 或 C++ 等通用编程语言来完成这些题目。
为什么不是 SQL？
让数据库来解决这类问题，通常被认为是一个错误的选择。标准 SQL 是一种声明式语言，主要用于关系型数据的检索与聚合，而并非为命令式的游戏循环或复杂的状态管理而设计。它缺乏这些谜题常用的数据结构支持（例如栈、队列或树），而试图通过标准的 JOIN 操作来实现，往往会引发严重的性能问题，甚至直接导致语法错误。简而言之，用 SQL 来完成 Advent of Code 被广泛视为“不可行”的——至少也是极其痛苦的。
ClickHouse 的方法
在 ClickHouse，我们并不喜欢“不可行”这个词。我们坚信，只要工具合适，一切问题本质上都是数据问题。ClickHouse 不只是一个高速的 OLAP 数据库，它还是一个向量化查询引擎，内置了大量分析函数库，这些能力经过巧妙组合后，甚至可以用来解决通用计算问题。
为了验证这一点，我们决定用一种非常规的方式来完成 2025 年的 Advent of Code：完全使用 ClickHouse SQL。
规则
为了确保整个过程不存在任何取巧行为，我们为解决方案制定了三条严格的规则：
仅使用纯 ClickHouse SQL：我们完全不允许使用任何用户自定义函数（User Defined Functions，UDFs），尤其是可执行的 UDF，因为那样可以通过调用 Python 或 Bash 来“作弊”。如果查询引擎本身无法原生完成某件事，我们就不能使用任何变通手段。
只接受原始输入：在 Advent of Code 中，输入通常是杂乱的文本文件，有时是数字列表，有时是 ASCII 艺术地图，或者是一段晦涩的指令文本。我们不允许对输入进行任何预处理。解决方案查询必须直接接收 AoC 提供的原始输入字符串，并在查询内部完成解析。
“单一查询”约束：这是最具挑战性的一条规则。我们不能创建表、物化视图或临时表来保存中间状态。整个谜题流程——从输入解析，到解决第 1 部分，再到解决通常复杂得多的第 2 部分——都必须在一个单一、原子的查询中完成。这迫使我们大量依赖 CTE，在一次连续的执行过程中将所有逻辑串联起来。
下面展示的是 Advent of Code 2025 全部 12 天的解决方案，展示了我们如何将被认为“不可行”的算法挑战，转化为纯粹的 ClickHouse SQL 查询。
注意：为了遵守 Advent of Code 的分发政策，下面的查询通过一个封装的 URL() 表来获取原始谜题输入，而不会直接暴露输入内容。支持直接字符串输入的原始查询版本，可以在我们的 ClickHouse/TreeHouse 仓库中找到。
谜题：精灵们用一个带有旋转拨盘的保险装置锁住了他们的秘密入口。这个谜题需要根据一系列指令来模拟一个标记为 0–99 的拨盘的移动，例如 L68（向左转动 68 个刻度）或 R48（向右转动 48 个刻度）。
第 1 部分要求在初始位置为 50 的情况下，计算所有旋转指令执行完毕后拨盘的最终位置。
第 2 部分则需要进行更复杂的模拟：精确统计在整个过程中拨盘指向 0 的次数，包括在旋转过程中多次经过 0 的所有中间刻度。
我们是如何用 ClickHouse SQL 解决这个问题的：我们没有将其实现为过程式的循环，而是把整个过程视为一个流处理问题。由于拨盘的状态完全由历史移动决定，我们可以一次性计算出每一条指令对应的累计位置。具体做法是将方向解析为正数（向右）和负数（向左）的整数，然后使用窗口函数计算步数的累计和。在第 2 部分中，为了检测是否“经过 0”，我们比较当前累计值与上一行的累计值，从而判断拨盘是否跨过了 0。
实现细节：
1. sum() OVER (...): 我们使用标准的 SQL 窗口函数来维护拨盘位置的“累计值”。通过将左右方向统一归一化为正值和负值，我们可以在一次扫描中得到每一行对应的累计位置。
sum(normalized_steps) OVER (ORDER BY instruction_id) AS raw_position2. lagInFrame：为了统计经过 0 的次数，我们需要知道当前旋转开始前拨盘的位置。我们使用 lagInFrame 查看上一行的累计位置，从而比较一次旋转的起点和终点，并通过数学方式判断 0 是否位于两者之间。
完整解决方案：
WITH--Fetch puzzle inputinput_wrapper AS (SELECT raw_blob AS input FROM aoc.input1),-- Parse the input string into individual instructionsparsed_instructions AS (-- Initial placeholder rowSELECT0 AS instruction_id,'R50' AS raw_instruction,'R' AS direction,50::Int16 AS stepsUNION ALL-- Parse each line from inputSELECTrowNumberInAllBlocks() + 1 AS instruction_id,raw AS raw_instruction,substring(raw, 1, 1) AS direction,substring(raw, 2)::Int16 AS stepsFROM format(TSV, 'raw String', (SELECT input FROM input_wrapper))),-- Part 1: Calculate positions with simple modulo wrappingpart1_positions AS (SELECTinstruction_id,raw_instruction,direction,steps,-- Normalize direction: positive for R, negative for Lif(direction = 'R', steps % 100, -1 * (steps % 100)) AS normalized_steps,-- Calculate cumulative positionsum(normalized_steps) OVER (ORDER BY instruction_id) AS raw_position,-- Wrap position to 0-99 range((raw_position % 100) + 100) % 100 AS positionFROM parsed_instructions),-- Part 2: Calculate positions with full movement trackingposition_calculations AS (SELECTinstruction_id,raw_instruction,direction,steps,-- Normalize direction (no modulo yet)if(direction = 'R', steps, -1 * steps) AS normalized_steps,-- Calculate cumulative raw positionsum(normalized_steps) OVER (ORDER BY instruction_id ASC) AS raw_position,-- Wrap to 0-99 range((raw_position % 100) + 100) % 100 AS positionFROM parsed_instructions),-- Track turn counts based on position changesturn_tracking AS (SELECT*,-- Get previous position for comparisonlagInFrame(position) OVER (ORDER BY instruction_id ASCROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS previous_position,-- Calculate turns for this instructionif(instruction_id = 0,0,-- Base turns from full rotationsfloor(steps / 100) +-- Additional turn if we wrapped aroundif(direction = 'R',(position != 0 AND previous_position != 0 AND position < previous_position) ? 1 : 0,(position != 0 AND previous_position != 0 AND position > previous_position) ? 1 : 0)) +-- Extra turn if we land exactly on position 0if(instruction_id != 0 AND position = 0, 1, 0) AS turn_countFROM position_calculations),-- Calculate cumulative turn countspart2_turn_counts AS (SELECTinstruction_id,raw_instruction,direction,steps,position,turn_count,-- Running sum of turnssum(turn_count) OVER (ORDER BY instruction_id ASCROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_turnsFROM turn_tracking)-- Final results for both partsSELECT'Part 1' AS part,countIf(position = 0) AS solution -- Should be 1100 with my inputFROM part1_positionsUNION ALLSELECT'Part 2' AS part,max(cumulative_turns)::UInt64 AS solution -- Should be 6358 with my inputFROM part2_turn_counts;
查看完整谜题描述：https://adventofcode.com/2025/day/1
谜题：你正在帮助清理一个礼品店数据库，其中充满了无效的产品 ID。输入数据是一系列 ID 范围（例如 11-22、95-115）。
第 1 部分将无效 ID 定义为由某个序列恰好重复两次组成的数字（例如 1212 或 55）。
第 2 部分将这一规则扩展为任意序列至少重复两次（例如 123123123 或 11111）。目标是在给定的范围内找出所有无效 ID，并对它们求和。
我们是如何用 ClickHouse SQL 解决这个问题的：我们没有编写循环逐个遍历数字，而是利用了 ClickHouse 将数据“展开”为多行的能力。我们把紧凑的输入范围（例如 11-22）直接扩展成大量独立的行——范围内的每一个整数都会生成一行数据。在为每一个潜在 ID 创建行之后，我们将其转换为字符串，并并行地使用数组函数来检测是否存在重复模式。
实现细节：
1. arrayJoin：这是我们生成行时最常用的函数。我们使用 range(start, end) 为每一行输入生成一个整数数组，再通过 arrayJoin 将数组展开为独立的多行数据。这样一来，筛选无效 ID 就只需要一个简单的 WHERE 条件。
SELECT arrayJoin(range(bounds[1], bounds[2] + 1)) AS number2. arrayExists：在第 2 部分中，我们需要检查任意子串长度（从 1 到字符串长度）是否构成了重复模式。为此，我们使用带有 lambda 函数的 arrayExists 来遍历所有可能的子串长度。如果对任意长度返回 1，该 ID 就会被判定为无效。
arrayExists(x -> (string_length % x = 0) AND (repeat(substring(..., x), ...) = number_string),range(1, string_length))
完整解决方案：
-- Define puzzle inputWITH input_wrapper AS (SELECT trimRight(raw_blob,'\n') AS input FROM aoc.input2),-- Parse range bounds from input stringrange_bounds AS (SELECT arrayMap(x -> x::UInt64,splitByChar('-', arrayJoin(splitByChar(',', (SELECT input FROM input_wrapper)::String)))) AS bounds),-- Expand ranges into individual numbersexpanded_numbers AS (SELECTarrayJoin(range(bounds[1], bounds[2] + 1)) AS number,toString(number) AS number_string,length(number_string) AS string_lengthFROM range_bounds),-- Analyze each number for repeating patternsrepeating_analysis AS (SELECTnumber_string,toUInt64(number_string) AS number,-- Part 2: Check if string is made of any repeating pattern-- (e.g., "123123" = "123" repeated, "1111" = "1" repeated)arrayExists(x -> (string_length % x = 0)AND (repeat(substring(number_string, 1, x),(string_length / x)::UInt32) = number_string),range(1, string_length)) AS has_pattern_repeat,-- Part 1: Check if second half equals first half-- (e.g., "1212" -> "12" = "12", "123123" -> "123" = "123")if(string_length % 2 = 0AND substring(number_string, (string_length / 2) + 1, string_length / 2)= substring(number_string, 1, string_length / 2),1,0) AS has_half_repeatFROM expanded_numbersWHEREhas_pattern_repeat != 0OR has_half_repeat != 0ORDER BY number ASC)-- Calculate final solutionsSELECTsumIf(number, has_half_repeat = 1) AS part_1_solution, -- Should be 24043483400 with my inputsumIf(number, has_pattern_repeat = 1) AS part_2_solution -- Should be 38262920235 with my inputFROM repeating_analysis
查看完整谜题描述：https://adventofcode.com/2025/day/2
谜题：你需要使用多组电池来应急启动一部自动扶梯，每一组电池都表示为一个数字字符串（例如 987654321）。
第 1 部分要求你恰好选取两块电池（数字），在保持它们原始相对顺序不变的前提下，组成能够得到的最大两位数。
第 2 部分将这一问题扩展到更大的规模：需要恰好选取 12 块电池，组成最大的 12 位数。这就变成了一个典型的贪心优化问题——在每一步中，你都希望选择当前能选到的最大数字，同时又必须保证在它之后还剩下足够的数字，用来完成整个序列。
我们是如何用 ClickHouse SQL 解决这个问题的：第 1 部分只涉及简单的字符串操作，而第 2 部分则要求在遍历数字时持续维护状态。我们需要同时跟踪还剩多少个数字需要选择，以及当前在字符串中的位置，从而避免打乱原有顺序。为此，我们直接在 SQL 中使用 arrayFold 实现了这一贪心算法，它允许我们在遍历数组的同时，携带一个包含约束条件的累加器元组。
实现细节：
1. arrayFold：我们使用这个高阶函数来实现类似 reduce() 的逻辑。累加器中保存了一个元组：(digits_remaining, current_position, accumulated_value)。在 fold 的每一步中，我们都会计算当前可以选择的最优合法数字，并据此更新整个状态元组。
arrayFold((accumulator, current_element) -> ( ... ), -- Update logicdigits,(num_digits_needed, 0, 0) -- Initial state)
2. ngrams：为了把数字字符串当作数组来处理，我们使用了 ngrams(string, 1)。这个函数通常用于文本分析，但在这里，它恰好可以把字符串拆分成由单个字符组成的数
完整解决方案：
-- Define puzzle inputWITH input_wrapper AS (SELECT trimBoth(raw_blob,'\n') AS input FROM aoc.input3),-- Convert input to array of digit values for Part 2digit_array AS (SELECTarrayMap(x -> toUInt8(x),ngrams(arrayJoin(splitByChar('\n', (SELECT input FROM input_wrapper)::String)), 1)) AS digits,length(digits) AS total_digits),-- Constants12 AS num_digits_needed,-- Part 1: Find largest two-digit number from each linepart1_largest_pairs AS (SELECTngrams(arrayJoin(splitByChar('\n', (SELECT input FROM input_wrapper)::String)), 1) AS chars,arraySlice(chars, 1, length(chars) - 1) AS chars_without_last,-- Find first max digit, then find max digit after itconcat(arrayMax(chars_without_last),arrayMax(arraySlice(chars,arrayFirstIndex(x -> x = arrayMax(chars_without_last),chars) + 1)))::Int16 AS largest_two_digit),-- Part 2: Build largest N-digit number by greedily selecting max digitspart2_greedy_selection AS (SELECTdigits,-- Iteratively build number by selecting maximum available digitarrayFold((accumulator, current_element) -> (-- Decrement remaining digits countergreatest(accumulator.1 - 1, 0)::Int64,-- Update position: find where max digit is in remaining sliceaccumulator.2 + (arrayFirstIndex(x -> x = arrayMax(arraySlice(digits,accumulator.2 + 1,total_digits - accumulator.1 - accumulator.2 + 1)),arraySlice(digits,accumulator.2 + 1,total_digits - accumulator.1 - accumulator.2 + 1)))::UInt64,-- Accumulate joltage: add max digit * 10^(remaining-1)accumulator.3 + if(accumulator.1 = 0,0::UInt64,arrayMax(arraySlice(digits,accumulator.2 + 1,total_digits - accumulator.1 - accumulator.2 + 1)) * intExp10(greatest(0, accumulator.1 - 1)))),digits,-- Initial accumulator state:-- (digits_remaining, current_position, accumulated_value)(num_digits_needed::Int64, 0::UInt64, 0::UInt64)).3 AS joltage -- Extract the accumulated value (3rd element)FROM digit_array)-- Combine results from both partsSELECT'Part 1' AS part,sum(largest_two_digit)::UInt64 AS solution -- Should be 17263 with my inputFROM part1_largest_pairsUNION ALLSELECT'Part 2' AS part,sum(joltage) AS solution -- Should be 170731717900423 with my inputFROM part2_greedy_selection
查看完整谜题描述：https://adventofcode.com/2025/day/3
谜题：精灵们需要突破一面由纸卷堆叠而成的墙。这个谜题是 Conway 的生命游戏的一种变体。你会得到一个网格，其中 @ 表示一个纸卷的位置。
第 1 部分定义了一条规则：如果一个纸卷周围的邻居数量少于 4 个，它就可以被“移除”。你需要统计在初始状态下满足这一条件的纸卷数量。
第 2 部分要求递归地模拟这一过程。移除一个纸卷，可能会让原本无法移除的其他纸卷变得可移除。你需要不断执行这一过程，直到整个系统稳定下来，最后统计被移除的纸卷总数。
我们是如何用 ClickHouse SQL 解决这个问题的：由于这是一个每一步都依赖前一步结果的迭代模拟问题，我们采用了递归 CTE。我们将网格表示为一组 (x, y) 坐标点。在每一次递归中，通过对这些点进行自连接来计算每个点的邻居数量。随后，只保留那些仍然“存活”的点（邻居数量 >= 4），其余点则被隐式地移除。我们持续执行递归，直到点的数量不再发生变化。
实现细节：
1. WITH RECURSIVE：我们使用标准 SQL 的递归 CTE 来完成这一基于邻接关系的遍历过程。初始步骤选出所有纸卷的位置，递归步骤则根据邻居数量对结果集进行不断收缩。
WITH RECURSIVE recursive_convergence AS (-- Base case: all pointsUNION ALL-- Recursive step: keep points with >= 4 neighborsSELECT ... HAVING countIf(...) >= 4)
2. argMin：为了准确判断模拟过程在哪一刻达到稳定状态，我们在每一层递归深度上记录点的数量，并使用 argMin(point_count, depth)
完整解决方案：
WITH RECURSIVE-- Define puzzle input (grid with '@' symbols)input_wrapper AS (SELECT raw_blob AS input FROM aoc.input4),-- Split input into linesinput_lines AS (SELECT splitByChar('\n', (SELECT input FROM input_wrapper)::String) AS lines),-- Find all '@' symbol positions in the gridgrid_points AS (SELECT arrayJoin(arrayFlatten(arrayMap(line_tuple ->arrayMap(x_pos -> (x_pos, line_tuple.2),arrayFilter((pos, val) -> val = '@',arrayEnumerate(line_tuple.1),line_tuple.1)),arrayMap((line, line_num) -> (ngrams(line, 1), line_num),lines,range(1, length(lines) + 1))))::Array(Tuple(UInt8, UInt8))) AS pointFROM input_lines),-- Expand points into separate columnsinitial_points AS (SELECTpoint.1 AS x,point.2 AS yFROM grid_points),-- Recursive CTE: Keep only points with 4+ neighbors at each iterationrecursive_convergence AS (-- Base case: all initial points at depth 1SELECTx,y,1 AS depthFROM initial_pointsUNION ALL-- Recursive case: keep points with at least 4 neighborsSELECTp.x,p.y,depth + 1 AS depthFROM recursive_convergence AS pCROSS JOIN recursive_convergence AS qWHERE depth < 256 -- Maximum recursion depthGROUP BY p.x, p.y, depthHAVING countIf(q.x BETWEEN p.x - 1 AND p.x + 1AND q.y BETWEEN p.y - 1 AND p.y + 1AND NOT (p.x = q.x AND p.y = q.y)) >= 4),-- Track point counts at each depth leveldepth_statistics AS (SELECTdepth,count() AS point_count,lagInFrame(point_count, 1) OVER (ORDER BY depth) AS previous_countFROM recursive_convergenceGROUP BY depthORDER BY depth),-- Find the depth where the count stabilizes (no more points removed)stabilization_analysis AS (SELECTmin(depth) AS stabilization_depth,argMin(point_count, depth) AS stabilized_countFROM depth_statisticsWHERE point_count = previous_countAND point_count > 0),-- Part 1: Points removed after first iteration (depth 2)part1_solution AS (SELECT(SELECT count() FROM initial_points) -(SELECT point_count FROM depth_statistics WHERE depth = 2 LIMIT 1) AS solution),-- Part 2: Points removed when convergence stabilizespart2_solution AS (SELECT(SELECT count() FROM initial_points) - stabilized_count AS solutionFROM stabilization_analysis),-- Combine results from both parts (necessary to prevent a bug with recursive CTE/external UNIONs)combined_solutions AS (SELECT'Part 1' AS part,solution -- Should be 1604 with my inputFROM part1_solutionUNION ALLSELECT'Part 2' AS part,solution -- Should be 9397 with my inputFROM part2_solution)select * from combined_solutions settings use_query_cache=true, query_cache_share_between_users = 1, query_cache_nondeterministic_function_handling = 'save', query_cache_ttl = 80000000, result_overflow_mode = 'throw', read_overflow_mode = 'throw'
查看完整谜题描述：https://adventofcode.com/2025/day/4
谜题：精灵们在管理库存时遇到了麻烦，问题涉及一组被标记为“新鲜”的 ID 范围（例如 3-5、10-14）。
第 1 部分要求统计有多少个指定的物品 ID 落在任意一个新鲜范围之内。
第 2 部分则要求计算这些新鲜范围一共覆盖了多少个唯一的整数，也就是所有区间的并集。例如，当范围为 1-5 和 3-7 时，它们的并集是 1-7（大小为 7），而不是简单相加得到的 10。
我们是如何用 ClickHouse SQL 解决这个问题的：这是一个典型的区间运算问题。第 1 部分只需要做简单的范围判断过滤，而第 2 部分则需要将存在重叠的区间合并起来。手动实现区间合并逻辑往往相当复杂，但我们直接使用了 ClickHouse 提供的一个专用聚合函数，将原本复杂的算法问题压缩成了一行查询。
实现细节：
1. intervalLengthSum：我们使用这个专用聚合函数来计算区间并集的总长度。它能够自动处理区间之间的重叠和嵌套关系，使我们无需自行编写复杂的合并逻辑。
SELECT intervalLengthSum(range_tuple.1, range_tuple.2) AS solution2. arrayExists：在第 1 部分中，我们使用 arrayExists 来判断某个指定的 ID 是否落在数组中的任意一个有效区间内。这样就可以在不将区间展开为海量行数据的情况下，高效完成判断。
完整解决方案：
-- Define puzzle inputWITH input_wrapper AS (SELECT trimRight(raw_blob,'\n') AS input FROM aoc.input5),-- Split input into two sectionsinput_sections AS (SELECTsplitByString('\n\n', (SELECT input FROM input_wrapper)::String)[1] AS ranges_section,splitByString('\n\n', (SELECT input FROM input_wrapper)::String)[2] AS ids_section),-- Parse ranges from first section (format: "min-max" per line)parsed_ranges AS (SELECT arrayMap(x -> (toUInt64(splitByChar('-', x)[1]),toUInt64(splitByChar('-', x)[2]) + 1 -- Make max half-open),splitByChar('\n', ranges_section)) AS rangesFROM input_sections),-- Parse IDs from second section (one ID per line)parsed_ids AS (SELECT arrayMap(x -> toUInt64(x),splitByChar('\n', ids_section)) AS idsFROM input_sections),-- Explode ranges into individual rows for Part 2 interval calculationexploded_ranges AS (SELECT arrayJoin(ranges) AS range_tupleFROM parsed_ranges),-- Part 1: Count how many IDs fall within any rangepart1_solution AS (SELECTlength(arrayFilter(id -> arrayExists(range -> id BETWEEN range.1 AND range.2,ranges),ids)) AS solutionFROM parsed_ranges, parsed_ids),-- Part 2: Calculate total interval length (union of all ranges)part2_solution AS (SELECTintervalLengthSum(range_tuple.1, range_tuple.2) AS solutionFROM exploded_ranges)-- Combine results from both partsSELECT'Part 1' AS part,solution -- Should be 707 with my inputFROM part1_solutionUNION ALLSELECT'Part 2' AS part,solution -- Should be 361615643045059 with my inputFROM part2_solution;
查看完整谜题描述：https://adventofcode.com/2025/day/5
谜题：你发现了一张数学练习表，上面的计算题按列排列。
第 1 部分将输入理解为由空格分隔的数字列。你需要根据底部给出的运算符，对每一列的数字执行求和或相乘操作。
第 2 部分则揭示，这些输入是以“从右向左”的方式按列书写的，一个数字的各个数位被垂直堆叠在一起。你需要重新解析整个网格，将这些数位重新组合成数字，再按空白列进行分组，并应用对应的运算符。
我们是如何用 ClickHouse SQL 解决这个问题的：这一谜题的关键在于文本解析和数组操作。我们将输入文本当作一个二维字符矩阵来处理。为了从按行书写的文本形式切换到按列组织的数学题，我们本质上执行了一次“矩阵转置”。具体来说，我们先将每一行文本转换为字符数组，再对其进行“旋转”以按列处理，最后使用数组函数重建数字并完成运算。
实现细节：
1. splitByWhitespace：在第 1 部分中，我们使用该函数解析“横向”的表示方式。它能够自动处理列之间不固定的空格数量，这是简单字符串拆分难以正确处理的。
2. arrayProduct：由于 ClickHouse 并没有提供标准的 product() 聚合函数，我们将每一列映射为整数数组，并通过 arrayProduct 来完成乘法计算。
toInt64(arrayProduct(arrayMap(x -> toInt64(x), arraySlice(column, 1, length(column) - 1))))
3. arraySplit：在第 2 部分中，在提取出所有原始数字之后，我们需要将它们拆分成独立的数学表达式。我们使用 arraySplit 在遇到运算符列时对大数组进行切分，从而有效地区分不同的计算问题。
完整解决方案：
-- Define puzzle inputWITH input_wrapper AS (SELECT trimRight(raw_blob,'\n') AS input FROM aoc.input6),-- Part 1: Parse input into columns and apply operationspart1_parsed_rows AS (SELECT arrayMap(x -> splitByWhitespace(x),splitByChar('\n', (SELECT input FROM input_wrapper)::String)) AS rows),part1_columns AS (SELECT arrayMap(column_index -> arrayMap(row -> row[column_index],rows),range(1, length(rows[1]) + 1)) AS columnsFROM part1_parsed_rows),part1_solution AS (SELECT arraySum(arrayMap(column -> if(-- Check if last element is multiplication operatorarrayLast(x -> 1, column) = '*',-- Multiply all numbers in columntoInt64(arrayProduct(arrayMap(x -> toInt64(x),arraySlice(column, 1, length(column) - 1)))),-- Otherwise, add all numbers in columntoInt64(arraySum(arrayMap(x -> toInt64(x),arraySlice(column, 1, length(column) - 1))))),columns)) AS solutionFROM part1_columns),-- Part 2: Parse with character-level precision to handle multi-digit numberspart2_parsed_chars AS (SELECT arrayMap(x -> ngrams(x, 1),splitByChar('\n', (SELECT input FROM input_wrapper)::String)) AS char_rows),part2_columns_raw AS (SELECT arrayMap(column_index -> arrayMap(row -> row[column_index],char_rows),range(1, length(char_rows[1]) + 1)) AS columnsFROM part2_parsed_chars),part2_columns_filtered AS (SELECT arrayFilter(x -> NOT arrayAll(y -> y = ' ', x),columns) AS non_empty_columnsFROM part2_columns_raw),part2_numbers_extracted AS (SELECT arrayMap(column -> replaceAll(arrayStringConcat(arraySlice(column, 1, length(column) - 1)),' ',''),non_empty_columns) AS number_stringsFROM part2_columns_filtered),part2_numbers_grouped AS (SELECTnumber_strings,non_empty_columns,-- Split numbers by operator positionsarraySplit((number_str, has_operator) -> has_operator,number_strings,arrayMap(column -> hasAny(column, ['+', '*']),non_empty_columns)) AS number_groupsFROM part2_numbers_extracted, part2_columns_filtered),part2_operations AS (SELECT arrayZip(-- Extract operators from columnsarrayFilter(x -> has(['+', '*'], x),arrayFlatten(non_empty_columns)),-- Pair with corresponding number groupsnumber_groups) AS operations_with_numbersFROM part2_numbers_grouped),part2_solution AS (SELECT arraySum(arrayMap(operation -> if(-- Check operator typeoperation.1 = '*',-- Multiply all numbers in grouptoInt64(arrayProduct(arrayMap(x -> toInt64(x),operation.2))),-- Otherwise, add all numbers in grouptoInt64(arraySum(arrayMap(x -> toInt64(x),operation.2)))),operations_with_numbers)) AS solutionFROM part2_operations)-- Combine results from both partsSELECT'Part 1' AS part,solution -- 5782351442566 with my inputFROM part1_solutionUNION ALLSELECT'Part 2' AS part,solution -- 10194584711842 with my inputFROM part2_solution;
查看完整谜题描述：https://adventofcode.com/2025/day/6
谜题：你正在分析一个网格中的快子束传播过程。
第 1 部分模拟一束光线向下传播。当它遇到分裂器 ^ 时，会分裂成两束，分别向左和向右延伸。你需要统计整个过程中发生的分裂次数。
第 2 部分引入了“量子多世界”的设定：这一次，不是光束在分裂，而是宇宙本身发生分裂。你需要计算在网格底部最终存在的所有活跃“时间线”（路径）的数量。
我们是如何用 ClickHouse SQL 解决这个问题的：如果对每一条路径进行显式模拟，计算量会呈指数级增长。相反，我们将问题建模为一种波传播过程，类似于帕斯卡三角形的计算方式。我们使用 arrayFold 按行处理整个网格，在每一行中维护一个映射，记录每一列位置上的“世界”数量，并根据分裂器的规则，计算这些数量如何传递到下一行。
实现细节：
1. arrayFold：我们使用 arrayFold 来实现按行推进的模拟状态机。在这个过程中，我们携带一个复杂的状态对象——(left_boundary, right_boundary, worlds_map, part1_counter)，并在处理网格的每一行时持续更新它。
2. sumMap：为了处理多条光束在同一位置汇合的情况（例如左分支和右分支最终落在同一坐标），我们使用 sumMap 对 world 映射中相同键对应的值进行聚合，从而自然地合并汇聚到同一位置的“时间线”数量。
arrayReduce('sumMap', arrayMap(position -> map(...), ...))完整解决方案：
-- Define puzzle inputWITH input_wrapper AS (SELECT raw_blob AS input FROM aoc.input7),-- Parse input into character gridparsed_grid AS (SELECT arrayMap(x -> ngrams(x, 1),splitByChar('\n', (SELECT input FROM input_wrapper)::String)) AS rows),-- Find starting position in first rowinitial_state AS (SELECTarrayFirstIndex(x -> x = 'S', rows[1])::UInt8 AS start_position,map(arrayFirstIndex(x -> x = 'S', rows[1])::UInt8,1::UInt64)::Map(UInt8, UInt64) AS initial_worldsFROM parsed_grid),-- Filter to only rows with '^' markers (active rows)active_rows AS (SELECT arrayFilter(x -> has(x, '^'),rows) AS filtered_rowsFROM parsed_grid),-- Main iteration: propagate world counts through rowsworld_propagation AS (SELECTstart_position,initial_worlds,filtered_rows,-- Fold through each row, updating statearrayFold((accumulator, current_row) -> (-- Update left boundary (shrink inward)(accumulator.1 - 1)::UInt8,-- Update right boundary (shrink inward)(accumulator.2 + 1)::UInt8,-- Update world map: propagate counts based on '^' positionsmapSort((key, value) -> key,mapUpdate(accumulator.3,arrayReduce('sumMap',arrayMap(position -> if(-- Check if position has '^' and exists in current worldscurrent_row[position] = '^'AND mapContains(accumulator.3, position),-- Propagate world count to adjacent positionsmap(-- Left neighbor gets count (unless blocked by another '^')(position - 1)::UInt8,(accumulator.3[position] + if(current_row[greatest(0, position - 2)] = '^',0,accumulator.3[position - 1]))::UInt64,-- Current position resets to 0(position)::UInt8,0::UInt64,-- Right neighbor gets count(position + 1)::UInt8,(accumulator.3[position + 1] + accumulator.3[position])::UInt64),-- No propagation if conditions not metmap()::Map(UInt8, UInt64)),-- Only process positions within current boundariesarraySlice(arrayEnumerate(current_row),accumulator.1,(accumulator.2 - accumulator.1) + 1))))),-- Part 1 counter: count '^' positions with non-zero worldsaccumulator.4 + arrayCount(position ->current_row[position] = '^'AND mapContains(accumulator.3, position)AND accumulator.3[position] > 0,arraySlice(arrayEnumerate(current_row),accumulator.1,(accumulator.2 - accumulator.1) + 1))),filtered_rows,-- Initial accumulator state:-- (left_boundary, right_boundary, worlds_map, part1_counter)(start_position,start_position,initial_worlds,0::UInt64)) AS final_stateFROM initial_state, active_rows),-- Part 1: Count of '^' positions encountered with non-zero worldspart1_solution AS (SELECT final_state.4 AS solutionFROM world_propagation),-- Part 2: Sum of all world counts across all positionspart2_solution AS (SELECT arraySum(mapValues(final_state.3)) AS solutionFROM world_propagation)-- Combine results from both partsSELECT'Part 1' AS part,solution -- 1633 with my inputFROM part1_solutionUNION ALLSELECT'Part 2' AS part,solution -- 34339203133559 with my inputFROM part2_solution;
查看完整谜题描述：https://adventofcode.com/2025/day/7
谜题：精灵们正在连接三维空间中的电气接线盒。
第 1 部分要求找出距离最近的 1000 对点并将它们连接起来，然后分析由这些连接形成的电路规模，也就是各个连通分量的大小。
第 2 部分则要求不断连接当前距离最近的点，直到所有接线盒最终连成一个整体的大电路。这在算法上对应的是一个最小生成树问题。
我们是如何用 ClickHouse SQL 解决这个问题的：这是一个典型的图论问题，需要使用并查集（disjoint-set / union-find）的思路。我们首先生成点之间所有可能的边，并按距离从小到大排序。接着，使用 arrayFold 依次遍历这些边，只要一条边连接了两个原本不在同一集合中的点，就将这两个集合合并，从而逐步构建连通分量。
实现细节：
1. L2Distance：我们使用 ClickHouse 原生提供的 L2Distance 函数，高效地计算三维坐标 [x, y, z] 之间的欧几里得距离，从而可以按连接长度对所有候选边进行排序。
2. runningAccumulate：在第 2 部分中，我们需要判断何时已经包含了足够多的不同点，从而形成一个单一的电路。与其在每一行上反复执行代价高昂的 DISTINCT 统计，我们使用 uniqCombinedState 构建唯一元素的紧凑状态表示，再通过 runningAccumulate 按行合并这些状态，高效地得到唯一点数量的累计结果。
runningAccumulate(points_state) AS unique_points_seen完整解决方案：
-- Define puzzle inputWITH input_wrapper AS (SELECT raw_blob AS input FROM aoc.input8),-- Parse 3D coordinate pointsparsed_points AS (SELECT (x, y, z) AS pointFROM format('CSV', 'x UInt32, y UInt32, z UInt32', (SELECT input FROM input_wrapper)::String)),-- Generate all point pairs with L2 distances, sorted by distancepoint_pairs_by_distance AS (SELECTt1.point AS point1,t2.point AS point2,L2Distance([point1.1, point1.2, point1.3],[point2.1, point2.2, point2.3]) AS distanceFROM parsed_points AS t1CROSS JOIN parsed_points AS t2WHERE point1 < point2ORDER BY distance ASC),-- Take the 1000 closest pairsclosest_pairs AS (SELECT groupArray([point1, point2]) AS pairsFROM (SELECT point1, point2FROM point_pairs_by_distanceORDER BY distance ASCLIMIT 1000)),-- Part 1: Build connected components from closest pairsconnected_components AS (SELECTpairs,-- Fold through pairs to merge into connected componentsarrayFold((accumulator, pair) -> if(-- Check if any existing components contain points from current pairlength(arrayFilter(component -> hasAny(component, pair),accumulator)) > 0,-- Merge matching components with current pairarrayConcat(-- Keep non-matching components unchangedarrayFilter(component -> NOT hasAny(component, pair),accumulator),-- Add merged component[arrayDistinct(arrayFlatten(arrayConcat(arrayFilter(component -> hasAny(component, pair),accumulator),[pair])))]),-- No matches found, add pair as new componentarrayConcat(accumulator, [pair])),pairs,[]::Array(Array(Tuple(UInt32, UInt32, UInt32)))) AS componentsFROM closest_pairs),component_analysis AS (SELECTcomponents,arrayMap(x -> length(x), components) AS component_sizesFROM connected_components),part1_solution AS (SELECT arrayProduct(arraySlice(arrayReverseSort(component_sizes),1,3)) AS solutionFROM component_analysis),-- Part 2: Find first pair where 1000 unique points have been seenpoint_pair_states AS (SELECTpoint1,point2,distance,arrayReduce('uniqCombinedState', [point1, point2]) AS points_stateFROM point_pairs_by_distance),part2_solution AS (SELECTpoint1,point2,distance,runningAccumulate(points_state) AS unique_points_seen,point1.1 * point2.1 AS solutionFROM point_pair_statesWHERE unique_points_seen >= 1000ORDER BY distance ASCLIMIT 1)-- Combine results from both partsSELECT'Part 1' AS part,solution::UInt64 AS solution -- 135169 with my inputFROM part1_solutionUNION ALLSELECT'Part 2' AS part,solution::UInt64 AS solution -- 302133440 with my inputFROM part2_solutionSETTINGS allow_deprecated_error_prone_window_functions = 1;
查看完整谜题描述：https://adventofcode.com/2025/day/8
谜题：电影院的地板可以表示为一个网格，其中分布着一些红色瓷砖。
第 1 部分要求找出以任意两块红色瓷砖作为对角点时，所能形成的最大面积矩形。
第 2 部分在此基础上增加了约束：这个矩形必须完全位于由所有红色和绿色瓷砖围成的闭合环路内部。
我们是如何用 ClickHouse SQL 解决这个问题的：我们没有把它当作一个逐格搜索的问题，而是建模为几何计算问题。我们构造了表示候选矩形以及边界环路的多边形对象。通过将矩形边界转换为“环”结构，就可以直接使用 ClickHouse 原生的几何函数来计算面积并判断包含关系。
实现细节：
1. polygonAreaCartesian：我们通过为候选矩形构造对应的多边形对象，并直接使用 polygonAreaCartesian 计算面积，从而避免了手动计算宽度和高度。
2. polygonsWithinCartesian：为了判断矩形是否完全位于环路内部，我们使用了这个几何包含函数。这里采用了一个技巧：由于几何计算在处理恰好落在边界上的点时容易出现问题，我们将候选矩形略微向内缩小（缩小 0.01 个单位），从而确保包含性检查严格成立，避免因边界重合带来的误判。
-- Create slightly inset test bounds (0.01 units inside)(least(x1, x2) + 0.01, least(y1, y2) + 0.01) AS bottom_left, ...polygonsWithinCartesian(test_bounds, all_points_ring)
完整解决方案：
-- Define puzzle inputWITH input_wrapper AS (SELECT raw_blob AS input FROM aoc.input9),-- Parse 2D coordinate pointsparsed_points AS (SELECT *FROM format('CSV', 'x Float64, y Float64', (SELECT input FROM input_wrapper)::String)),-- Generate all unique pairs of pointspoint_pairs AS (SELECTc1.x AS x1,c1.y AS y1,c2.x AS x2,c2.y AS y2FROM parsed_points AS c1CROSS JOIN parsed_points AS c2WHERE (c1.x, c1.y) < (c2.x, c2.y)),-- Create bounding box polygons for each pairbounding_boxes AS (SELECTx1,y1,x2,y2,-- Exact bounding box (corners at point coordinates)[(least(x1, x2), least(y1, y2)), -- bottom-left(least(x1, x2), greatest(y1, y2)), -- top-left(greatest(x1, x2), greatest(y1, y2)), -- top-right(greatest(x1, x2), least(y1, y2)), -- bottom-right(least(x1, x2), least(y1, y2)) -- close the ring]::Ring AS exact_bounds,-- Expanded bounding box (extends 0.5 units beyond points)[(least(x1, x2) - 0.5, least(y1, y2) - 0.5), -- bottom-left(least(x1, x2) - 0.5, greatest(y1, y2) + 0.5), -- top-left(greatest(x1, x2) + 0.5, greatest(y1, y2) + 0.5), -- top-right(greatest(x1, x2) + 0.5, least(y1, y2) - 0.5), -- bottom-right(least(x1, x2) - 0.5, least(y1, y2) - 0.5) -- close the ring]::Ring AS expanded_boundsFROM point_pairs),-- Create polygon containing all points (for Part 2 containment test)all_points_array AS (SELECT groupArray((x, y)) AS points_arrayFROM parsed_points),all_points_polygon AS (SELECT arrayPushBack(points_array, points_array[1])::Ring AS ringFROM all_points_array),-- Part 1: Find largest bounding box by areapart1_candidates AS (SELECTx1,y1,x2,y2,exact_bounds,expanded_bounds,polygonAreaCartesian(expanded_bounds) AS areaFROM bounding_boxesORDER BY area DESCLIMIT 1),part1_solution AS (SELECT area AS solutionFROM part1_candidates),-- Part 2: Find largest bounding box that contains all pointspart2_candidates AS (SELECTbb.x1,bb.y1,bb.x2,bb.y2,-- Create slightly inset test bounds (0.01 units inside)(least(x1, x2) + 0.01, least(y1, y2) + 0.01) AS bottom_left,(least(x1, x2) + 0.01, greatest(y1, y2) - 0.01) AS top_left,(greatest(x1, x2) - 0.01, greatest(y1, y2) - 0.01) AS top_right,(greatest(x1, x2) - 0.01, least(y1, y2) + 0.01) AS bottom_right,-- Create test bounds polygon[bottom_left,top_left,top_right,bottom_right,bottom_left]::Ring AS test_bounds,-- Check if all points are within test boundspolygonsWithinCartesian(test_bounds, app.ring) AS all_points_contained,polygonAreaCartesian(bb.expanded_bounds) AS areaFROM bounding_boxes AS bbCROSS JOIN all_points_polygon AS appWHERE all_points_contained != 0ORDER BY area DESCLIMIT 1),part2_solution AS (SELECT area AS solutionFROM part2_candidates)-- Combine results from both partsSELECT'Part 1' AS part,solution AS area -- 4739623064 with my inputFROM part1_solutionUNION ALLSELECT'Part 2' AS part,solution AS area -- 1654141440 with my inputFROM part2_solution;
查看完整谜题描述：https://adventofcode.com/2025/day/9
谜题：你需要通过按下按钮来配置工厂中的机器。
第 1 部分涉及通过切换灯光状态（XOR 逻辑）来匹配指定的目标模式。
第 2 部分则需要通过递增“电压值”计数器，用尽可能少的按键次数达到一个非常大的目标整数。
我们是如何用 ClickHouse SQL 解决这个问题的：在第 1 部分中，由于搜索空间相对较小，我们可以直接使用暴力枚举的方法，生成所有可能的按钮组合，并通过位掩码进行验证。第 2 部分则需要更高效的策略。我们在 SQL 中实现了一个自定义的递归折半算法，通过反复减去按钮的影响，并对剩余的目标值进行“折半”处理，逐步将巨大的目标数缩减到零。
实现细节：
1. bitTest 和 bitCount：我们将按钮组合表示为二进制整数。bitTest 用于判断某个组合中是否按下了指定按钮，而 bitCount 则用于统计该组合对应的总按键次数，也就是整体代价。
2. ARRAY JOIN：为了构造第 1 部分的完整搜索空间，我们生成了一个整数范围（从 0 到 2^N），并使用 ARRAY JOIN 将其展开为多行数据，从而为每一种可能的按钮按压组合生成一行记录。
ARRAY JOIN range(0, toUInt32(pow(2, num_buttons)))完整解决方案：
WITH RECURSIVE-- Define puzzle inputinput_wrapper AS (SELECT raw_blob AS input FROM aoc.input10),-- Parse raw input into structured formatraw_split AS (SELECTrow_number() OVER () AS puzzle_id,splitByChar(' ', raw) AS componentsFROM format('TSVRaw', 'raw String', (SELECT input FROM input_wrapper)::String)),parsed_puzzles AS (SELECTpuzzle_id,-- Parse diagram: '#' becomes 1, '.' becomes 0arrayMap(x -> if(x = '#', 1, 0),ngrams(replaceRegexpAll(components[1], '[\\[\\]]', ''), 1)) AS target_diagram,-- Parse buttons: each button affects specific positionsarrayMap(button_str -> arrayMap(pos_str -> (toUInt16(pos_str) + 1),splitByChar(',', replaceRegexpAll(button_str, '[\\(\\)]', ''))),arraySlice(components, 2, length(components) - 2)) AS button_effects,-- Parse joltages: target values for Part 2arrayMap(x -> toUInt32(x),splitByChar(',', replaceRegexpAll(components[-1], '[\\{\\}]', ''))) AS target_joltagesFROM raw_split),puzzle_metadata AS (SELECTpuzzle_id,target_diagram,button_effects,target_joltages,length(button_effects) AS num_buttons,length(target_joltages) AS num_positionsFROM parsed_puzzles),-- PART 1: Brute force - enumerate all button combinationspart1_button_combinations AS (SELECTp.puzzle_id,p.target_diagram,p.button_effects,p.num_buttons,p.num_positions,combination_id,toUInt32(bitCount(combination_id)) AS button_presses,-- Calculate resulting diagram from this combinationarrayMap(position -> toUInt8(modulo(arrayReduce('sum',arrayMap(button_index -> if(bitTest(combination_id, button_index)AND has(button_effects[button_index + 1], position),1,0),range(0, num_buttons))),2)),range(1, num_positions + 1)) AS resulting_diagramFROM puzzle_metadata pARRAY JOIN range(0, toUInt32(pow(2, num_buttons))) AS combination_id),part1_minimum_solutions AS (SELECTpuzzle_id,min(button_presses) AS minimum_pressesFROM part1_button_combinationsWHERE target_diagram = resulting_diagramGROUP BY puzzle_id),-- PART 2: Pre-compute button combination patterns for recursive algorithmbutton_combination_patterns AS (SELECTp.puzzle_id,p.button_effects,p.num_buttons,p.num_positions,combination_id,toUInt32(bitCount(combination_id)) AS pattern_cost,-- Pattern: numeric effect on each positionarrayMap(position -> toUInt32(arrayReduce('sum',arrayMap(button_index -> if(bitTest(combination_id, button_index)AND has(button_effects[button_index + 1], position),1,0),range(0, num_buttons)))),range(1, num_positions + 1)) AS effect_pattern,-- Parity pattern: XOR constraint (mod 2)arrayMap(position -> toUInt8(modulo(arrayReduce('sum',arrayMap(button_index -> if(bitTest(combination_id, button_index)AND has(button_effects[button_index + 1], position),1,0),range(0, num_buttons))),2)),range(1, num_positions + 1)) AS parity_patternFROM puzzle_metadata pARRAY JOIN range(0, toUInt32(pow(2, num_buttons))) AS combination_id),-- Group patterns by parity for efficient lookuppatterns_grouped_by_parity AS (SELECTpuzzle_id,button_effects,num_buttons,num_positions,parity_pattern,groupArray(tuple(effect_pattern, pattern_cost)) AS available_patternsFROM button_combination_patternsGROUP BY puzzle_id, button_effects, num_buttons, num_positions, parity_pattern),-- Recursive halving algorithm: iteratively reduce joltages to zerorecursive_halving_solver AS (-- Base case: start with target joltagesSELECTpuzzle_id,button_effects,num_buttons,num_positions,target_joltages AS current_goal,toUInt64(0) AS accumulated_cost,0 AS recursion_depthFROM puzzle_metadataUNION ALL-- Recursive case: apply pattern, subtract, halve, and continueSELECTpuzzle_id,button_effects,num_buttons,num_positions,current_goal,min(accumulated_cost) AS accumulated_cost,min(recursion_depth) AS recursion_depthFROM (SELECTsolver.puzzle_id,solver.button_effects,solver.num_buttons,solver.num_positions,-- New goal: (current - pattern) / 2arrayMap(i -> intDiv(solver.current_goal[i] - pattern_tuple.1[i],2),range(1, solver.num_positions + 1)) AS current_goal,-- Accumulate cost: pattern_cost * 2^depthsolver.accumulated_cost +toUInt64(pattern_tuple.2) * toUInt64(pow(2, solver.recursion_depth)) AS accumulated_cost,solver.recursion_depth + 1 AS recursion_depthFROM recursive_halving_solver solverINNER JOIN patterns_grouped_by_parity patternsON patterns.puzzle_id = solver.puzzle_idAND patterns.parity_pattern = arrayMap(x -> if(x % 2 = 0, toUInt8(0), toUInt8(1)),solver.current_goal)ARRAY JOIN patterns.available_patterns AS pattern_tupleWHEREsolver.recursion_depth < 100AND NOT arrayAll(x -> x = 0, solver.current_goal)-- Ensure pattern doesn't overshoot (feasibility constraint)AND arrayAll(i -> pattern_tuple.1[i] <= solver.current_goal[i],range(1, solver.num_positions + 1)))GROUP BY puzzle_id, button_effects, num_buttons, num_positions, current_goal),part2_minimum_solutions AS (SELECTpuzzle_id,min(accumulated_cost) AS minimum_costFROM recursive_halving_solverWHERE arrayAll(x -> x = 0, current_goal)GROUP BY puzzle_id),-- Aggregate final solutionscombined_solutions AS (SELECT 'Part 1' AS part, sum(minimum_presses) AS solution -- 527 with my inputFROM part1_minimum_solutionsUNION ALLSELECT 'Part 2' AS part, sum(minimum_cost) AS solution -- 19810 with my inputFROM part2_minimum_solutions)-- Combine results from both partsSELECT * FROM combined_solutions settings use_query_cache=true, query_cache_share_between_users = 1, query_cache_nondeterministic_function_handling = 'save', query_cache_ttl = 80000000, result_overflow_mode = 'throw', read_overflow_mode = 'throw';
查看完整谜题描述：https://adventofcode.com/2025/day/10
谜题：你正在调试一个反应堆的控制图结构。
第 1 部分要求统计从你当前所在的节点到 out 节点之间所有不同路径的数量。
第 2 部分则要求统计从 svr 到 out 的路径数量，并且必须满足一个额外条件：路径需要同时经过中间节点 dac 和 fft。
我们是如何用 ClickHouse SQL 解决这个问题的：我们使用递归 CTE 对这张图进行遍历。为了在第 2 部分中满足路径约束条件，我们在递归过程中携带了“是否访问过”的状态标志。在遍历过程中，只要遇到对应的关键节点，就更新相应的布尔标志。最终，只需筛选出两个标志都为 true 的路径即可。
实现细节：
1. cityHash64：在大规模递归 JOIN 中，字符串比较往往代价较高。为此，我们使用 cityHash64 将节点名称（例如 svr、dac）转换为确定性的 64 位整数，从而显著提升 JOIN 的执行速度，并降低整体内存占用。
cityHash64('svr') AS svr_node2. 状态跟踪：我们在递归结果表中额外添加了布尔字段，用于跟踪路径状态。这使我们能够在一次遍历中完成“必须同时访问 X 和 Y”这样的约束判断，而无需额外的复杂后处理逻辑。
paths.visited_dac OR (edges.to_node = kn.dac_node) AS visited_dac完整解决方案：
WITH RECURSIVE-- Define puzzle inputinput_wrapper AS (SELECT raw_blob AS input FROM aoc.input11),-- Define key node identifierskey_nodes AS (SELECTcityHash64('svr') AS svr_node,cityHash64('you') AS you_node,cityHash64('dac') AS dac_node,cityHash64('fft') AS fft_node,cityHash64('out') AS out_node),-- Parse input connectionsraw_connections AS (SELECT splitByString(': ', raw) AS parsed_partsFROM format('TSV', 'raw String', (SELECT input FROM input_wrapper)::String)),parsed_connections AS (SELECTparsed_parts[1] AS input_node,splitByWhitespace(parsed_parts[2]) AS output_nodesFROM raw_connections),-- Create graph edges with hashed node IDsgraph_edges AS (SELECTcityHash64(input_node) AS from_node,cityHash64(arrayJoin(output_nodes)) AS to_nodeFROM parsed_connections),-- Part 2: Count paths from 'svr' to 'out' that visit both 'dac' and 'fft'paths_from_svr AS (-- Base case: start at 'svr' nodeSELECT0 AS generation,svr_node AS current_node,0::UInt8 AS visited_dac,0::UInt8 AS visited_fft,1::UInt64 AS paths_countFROM key_nodesUNION ALL-- Recursive case: traverse edges and track checkpoint visitsSELECTgeneration,current_node,visited_dac,visited_fft,sum(paths_count) AS paths_countFROM (SELECTpaths.generation + 1 AS generation,edges.to_node AS current_node,paths.visited_dac OR (edges.to_node = kn.dac_node) AS visited_dac,paths.visited_fft OR (edges.to_node = kn.fft_node) AS visited_fft,paths.paths_count AS paths_countFROM paths_from_svr pathsJOIN graph_edges edges ON edges.from_node = paths.current_nodeCROSS JOIN key_nodes knWHEREedges.to_node != kn.out_nodeAND paths.generation < 628)GROUP BY generation, current_node, visited_dac, visited_fft),-- Part 1: Count all paths from 'you' to 'out'paths_from_you AS (-- Base case: start at 'you' nodeSELECT0 AS generation,you_node AS current_node,1::UInt64 AS paths_countFROM key_nodesUNION ALL-- Recursive case: traverse edgesSELECTgeneration,current_node,sum(paths_count) AS paths_countFROM (SELECTpaths.generation + 1 AS generation,edges.to_node AS current_node,paths.paths_count AS paths_countFROM paths_from_you pathsJOIN graph_edges edges ON edges.from_node = paths.current_nodeCROSS JOIN key_nodes knWHEREedges.to_node != kn.out_nodeAND paths.generation < 628)GROUP BY generation, current_node),-- Part 1 solution: paths from 'you' to 'out'part1_solution AS (SELECT sum(paths.paths_count) AS solutionFROM paths_from_you pathsJOIN graph_edges edges ON edges.from_node = paths.current_nodeCROSS JOIN key_nodes knWHERE edges.to_node = kn.out_node),-- Part 2 solution: paths from 'svr' to 'out' visiting both checkpointspart2_solution AS (SELECT sum(paths.paths_count) AS solutionFROM paths_from_svr pathsJOIN graph_edges edges ON edges.from_node = paths.current_nodeCROSS JOIN key_nodes knWHEREedges.to_node = kn.out_nodeAND paths.visited_dac = 1AND paths.visited_fft = 1),solutions_combined as (SELECT'Part 1' AS part,(SELECT solution FROM part1_solution) AS solution -- 724 with my inputUNION ALLSELECT'Part 2' AS part,(SELECT solution FROM part2_solution) AS solution -- 473930047491888 with my input)SELECT * FROM solutions_combined;
查看完整谜题描述：https://adventofcode.com/2025/day/11
谜题：精灵们需要将形状不规则的礼物（由 # 组成的网格表示）打包进矩形区域中。乍一看，这像是一个复杂的二维装箱问题。不过，谜题的输入条件允许使用一种启发式的简化方法：只要礼物的总面积不超过区域的总面积，就可以认为是可行的。
我们是如何用 ClickHouse SQL 解决这个问题的：由于只需要进行面积层面的判断，解决方案的重点放在了解析上。我们将 ASCII 图案表示的礼物形状转换为二进制网格（由 1 和 0 组成的数组），并计算每种形状的面积（也就是 1 的数量）。接着，将每种礼物的需求数量与其面积相乘并求和，再与目标区域的总面积进行比较。
实现细节：
1. replaceRegexpAll：我们通过正则表达式替换，将可视化的 # 字符替换为 1，将 . 替换为 0。这样一来，原本用于展示的“图案”就变成了可以直接计算的二进制字符串，并进一步解析为数组。
2. arraySum：我们使用了带 lambda 的 arraySum 来完成类似“点积”的计算。通过将每种礼物的数量与其面积相乘，并在同一个表达式中求和，使实现保持简洁清晰。
arraySum((volume, area) -> volume * area,requested_shape_volumes,areas_per_shape)
完整解决方案：
-- Define puzzle inputWITH input_wrapper AS (SELECT trimRight(raw_blob,'\n') AS input FROM aoc.input12),-- Split input into sectionsinput_sections AS (SELECT arrayMap(section -> splitByChar('\n', section),splitByString('\n\n', (SELECT input FROM input_wrapper)::String)) AS sections),-- Extract regions section (last section)regions_section AS (SELECT sections[-1] AS region_linesFROM input_sections),-- Extract shape sections (all except last)shapes_sections AS (SELECT arrayJoin(arraySlice(sections, 1, length(sections) - 1)) AS shape_linesFROM input_sections),-- Parse shape dataparsed_shapes AS (SELECTshape_lines,-- Transform shape lines: first line is name, rest is patternarrayMap(line_index -> if(line_index = 1,-- First line: remove ':' from namereplaceAll(shape_lines[line_index], ':', ''),-- Other lines: convert '#' to 1, '.' to 0replaceRegexpAll(replaceRegexpAll(shape_lines[line_index], '#', '1'),'\\.','0')),arrayEnumerate(shape_lines)) AS transformed_linesFROM shapes_sections),-- Convert shape patterns to binary arraysshape_patterns AS (SELECTtransformed_lines,arrayMap(line -> arrayMap(char -> toUInt8(char),ngrams(line, 1)),arraySlice(transformed_lines, 2)) AS shape_gridFROM parsed_shapes),-- Calculate area needed for each shapeshape_areas AS (SELECT groupArray(arrayCount(cell -> cell = 1,arrayFlatten(shape_grid))) AS areas_per_shapeFROM shape_patterns),-- Parse region specificationsparsed_regions AS (SELECTarrayJoin(arrayMap(line -> splitByString(': ', line),region_lines)) AS region_partsFROM regions_section),-- Calculate region dimensions and requested volumesregion_specifications AS (SELECTregion_parts,-- Calculate total region area (product of dimensions)arrayProduct(arrayMap(dim -> toUInt32(dim),splitByChar('x', region_parts[1]))) AS total_region_area,-- Extract requested volumes for each shapearrayMap(vol -> toUInt8(vol),splitByChar(' ', region_parts[2])) AS requested_shape_volumesFROM parsed_regions),-- Check if each region can fit the requested shapesregion_fit_analysis AS (SELECTtotal_region_area,requested_shape_volumes,areas_per_shape,-- Calculate total area needed: sum of (volume * area) for each shapearraySum((volume, area) -> volume * area,requested_shape_volumes,areas_per_shape) AS total_area_needed,-- Check if shapes fit in regiontotal_area_needed <= total_region_area AS shapes_fitFROM region_specificationsCROSS JOIN shape_areas),-- Count regions where shapes fitsolution AS (SELECT countIf(shapes_fit) AS solutionFROM region_fit_analysis)-- Return final answerSELECT solution -- 463 with my inputFROM solution
查看完整谜题描述：https://adventofcode.com/2025/day/12
我们成功地仅使用纯 ClickHouse SQL 解决了全部 12 个谜题，这充分展示了 ClickHouse 查询引擎的灵活性与表达能力。这一切得益于 ClickHouse 内置的庞大函数库，它在很大程度上弥合了 SQL 与通用编程语言之间的差距。在整个挑战过程中，我们使用了十多种字符串函数，并配合 format 表函数，将杂乱无章的输入整理成可处理的数据集。我们大量依赖了 arrayMap、arrayProduct 等数组函数，而真正发挥关键作用的是 arrayReduce 和 arrayFold，它们让我们能够实现复杂的函数式逻辑，并在多次迭代中持续维护状态。再结合用于路径查找的原生递归 CTE、用于三维几何计算的距离函数、用于空间分析的多边形函数，以及用于逻辑运算的位运算，ClickHouse 的表现更像是一个高性能的向量计算引擎，而不仅仅是传统意义上的数据库。
由于我们人为设定的限制条件，这些 SQL 方案在计算效率上可能不及使用 Rust 或 Python 等通用编程语言实现的版本，但它们依然能够产生完全一致的结果。这个实验表明，只要工具足够强大，那些在 SQL 中看似“不可能”的问题，对 ClickHouse 来说不过是另一类有趣的数据挑战。完整的解决方案查询可以在我们的 ClickHouse/TreeHouse 仓库中查看。
注意：Thomas Neumann 在 2024 年使用 Umbra DB 完成了全部 Advent of Code 谜题，他的相关成果可以在对应的仓库中找到。
/END/
试用阿里云 ClickHouse企业版
轻松节省30%云资源成本？阿里云数据库ClickHouse 云原生架构全新升级，首次购买ClickHouse企业版计算和存储资源组合，首月消费不超过99.58元（包含最大16CCU+450G OSS用量）了解详情：https://t.aliyun.com/Kz5Z0q9G
征稿启示
面向社区长期正文，文章内容包括但不限于关于 ClickHouse 的技术研究、项目实践和创新做法等。建议行文风格干货输出&图文并茂。质量合格的文章将会发布在本公众号，优秀者也有机会推荐到 ClickHouse 官网。请将文章稿件的 WORD 版本发邮件至：[email protected]