利用DuckDB宏(macro)巧妙发现字符串弱相关

本期播客

利用DuckDB宏(macro)巧妙发现字符串弱相关

这篇duckdb的blog介绍了利用宏(macro)来巧妙查找字符串弱相关的内容.

什么是字符串弱相关?

两个字符串包含相同的字母, 但是这些字母的排序可能是不一样的, 也可能有些字母是重复的. 例如 abc 和 bbacabb .

下面的内容翻译自: https://duckdb.org/2025/09/11/solving-letter-scramble-puzzles.html

请重点关注macro的用法. 如果你熟悉PostgreSQL, 你肯定知道split函数, 以及intarray插件的sort和uniq函数, 把数组排序后去重, 可达到与之类似的效果.

• https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP
• https://www.postgresql.org/docs/current/intarray.html

另外扩展一个小知识, 还有字符串的相似度计算(非语义的相似度), 例如postgresql, p0stgresql, post greSql. 相差了少量字母, 使用PostgreSQL pg_trgm插件可计算其相似度.

• https://www.postgresql.org/docs/current/pgtrgm.html

使用 DuckDB 解决字母混乱难题

TL;DR：在这篇轻松的文章中，我们解决了荷兰火车上展示的一种谜题类型。

荷兰国家铁路公司 (NS)每周都会发布一款“字母混乱”式的谜题，题目会给出一个词条，其字母可以在荷兰火车站的名称中找到。为了匹配，它不必是完美的字母重排——例如，Amsterdam(9 个字母) 可以匹配mastered(8 个字母) 和Dream Master(11 个字母)，因为这三个词条包含相同的字母，只是重复次数不同。我们称之为“弱字母重排”。

九月第一周的谜题是Clumsy Rental Red。让我们尝试使用 DuckDB 来寻找解决方案！

字母规整 macro

首先，让我们创建一个宏，将字符串转换为唯一字符的有序列表：

CREATE MACRO order_letters(s) AS   
    lower(s)                  -- convert all characters to lowercase  
    .regexp_replace(  
'[^\p{L}]', '', 'g'
    )                         -- 正则表达式, remove all non-Unicode letters  
    .string_to_array('')      -- turn the string into a list  
    .list_distinct()          -- eliminate duplicate elements from the list  
    .list_sort();             -- sort the list  

我们可以用它来判断以下字符串1与2、3是否是弱字谜：

SELECT  
    order_letters('Amsterdam') AS letters_1,  
    order_letters('mastered') AS letters_2,  
    order_letters('Dream Master') AS letters_3,  
    letters_1 = letters_2 AS matches_1,  
    letters_1 = letters_3 AS matches_2;  

事实上，这两个表达都是Amsterdam的弱字谜。

与车站名称匹配

为了解答这个难题，我们需要一个火车站列表。幸运的是，DuckDB 的常用数据集之一是荷兰铁路数据集，其中包括其列车服务和火车站。我们可以创建一个包含车站名称的表：

CREATE TABLE stations AS  
    FROM 'https://blobs.duckdb.org/nl-railway/stations-2023-09.csv';  

然后，我们可以选出谜题中弱字母重排的车站名称：

SELECT name_long  
FROM stations  
WHERE order_letters(name_long) = order_letters('Clumsy Rental Red');  

用于查找弱字母重排的 table 宏

为了找到与字符串弱字母重排的车站名称，我们可以使用table宏：

CREATE MACRO find_weak_anagram(s) AS TABLE  
    SELECT name_long  
    FROM stations  
    WHERE order_letters(name_long) = order_letters(s);  

然后，我们可以使用一个简单的 SQL 语句来找到解决方案：

FROM find_weak_anagram('Clumsy Rental Red');  

弱字谜站对

我们很好奇：有没有两个车站的名字是弱字母重排的？我们可以用这两个车站的名字创建一个笛卡尔积，然后比较它们的字母顺序来找出答案：

SELECT s1.name_long AS station_1, s2.name_long AS station_2  
FROM stations s1, stations s2  
WHERE s1.name_long.order_letters() = s2.name_long.order_letters()  
  -- ensure symmetry-breaking  
  AND s1.name_long < s2.name_long  
  -- make sure the station names don't contain each other  
  AND NOT s1.name_long.contains(s2.name_long)  
  AND NOT s2.name_long.contains(s1.name_long);  

事实上，有三对车站的名称是彼此弱的字母重排：

清理宏

大多数情况下，运行简单的 DuckDB 脚本后无需清理：只需关闭内存数据库会话即可完成清理。但是，需要指出的是，DuckDB 中的宏是持久化的，这可能会造成一些问题  ——  例如，将数据库复制到DuckLake时：

ATTACH 'ducklake:metadata.ducklake' AS my_ducklake;  
COPY FROM DATABASE memory TO my_ducklake;  

DuckLake 不支持宏（函数），因此会抛出以下错误：

Not implemented Error:  
DuckLake does not support functions

有两种方法可以解决这个问题。

如果您需要保留宏，并且使用 DuckDB 作为 DuckLake 的目录(catalog/元数据)数据库，则可以使用DuckDB 到 DuckLake 的迁移脚本。这会将宏迁移到您的 DuckLake 目录(catalog/元数据)中。

如果您不需要宏或者目标目录数据库不支持它们，则可以使用以下命令删除它们：

DROP MACRO order_letters;  
DROP MACRO TABLE find_weak_anagram;  

没有宏，复制到 DuckLake 调用就会成功。

参考

https://duckdb.org/2025/09/11/solving-letter-scramble-puzzles.html

https://duckdb.org/docs/stable/sql/statements/create_macro.html

https://www.postgresql.org/docs/current/intarray.html

《DuckDB 语法糖macro,columns表达式,list lambda等结合例子: 动态聚合分析》

《DuckDB 宏(MACRO) 的使用》