为非技术分析师设计自定义查询语言

# 为非技术人员分析师设计自定义查询语言 来源: https://nchammas.com/writing/custom-query-language-design 最近，我有幸设计并实现了一种自定义查询语言¹ (<https://nchammas.com/writing/custom-query-language-design#fn:dsl>)，以及一个集成的执行环境。这是我第一次从事这类工作，它成为了我的一个 passion project（激情项目），我投入了许多个夜晚和周末。在大约 18 个月的时间里，我断断续续地从事这个副业项目，从中学到的东西比我过去八年在主业工作中学到的还要多。由于该项目服务于我当时雇主的一个团队，我无法分享确切的项目细节。但在这篇文章中，我将通过一个广泛等效的假设场景，回顾我所使用的设计过程：梳理问题、尝试现有解决方案，最后设计我自己的查询语言。 ## 理解需求 首先，为什么要设计一种自定义查询语言？原因有很多；就我的情况而言，承担这项任务的决策源于对以下三点的理解： 1. 我试图帮助的**用户群体**。 2. 这些用户想要查询的**数据**。 3. 这些用户的**使用场景**。 我认为，这种因素的组合是推动大多数查询语言诞生的驱动力。 ### 用户群体：分析师 我们的目标用户群体是专注于车辆维护数据的非技术和半技术分析师。非技术分析师可能有使用 Excel 等工具的经验，可能知道如何为该系统编写基本公式，但他们不熟悉 SQL 或其他编程方式。半技术分析师懂不少 SQL，但不是专业的软件开发者。也就是说，他们知道如何编写涉及连接或聚合的基本查询，但对于窗口函数或相关子查询等更高级的技巧则不那么熟练。 ### 数据：车辆维护日志 这些用户感兴趣的数据是流式传输到中央数据库的车辆维护数据。数据每天更新，跨度超过 10 年，涵盖美国各地的 1 亿多辆车辆。虽然数据具体存储方式² (<https://nchammas.com/writing/custom-query-language-design#fn:stored>)的实施细节——格式、存储系统、分区方案等——很有趣，但对我们而言，最重要的是数据的逻辑或抽象模式。 这些数据可以抽象描述为具有以下模式的两张表： ```text vehicle vehicle_id make model year maintenance_history vehicle_id time work_done detail ``` 每个表的示例“行”如下： `vehicle`: | vehicle_id | make | model | year | | :--------- | :------ | :----- | :--- | | 224 | Volvo | XC60 | 2017 | | 7889 | Mazda | Mazda3 | 2009 | | 8031 | Toyota | Tacoma | 2007 | `maintenance_history`: | vehicle_id | time | work_done | detail | | :--------- | :------------------ | :------------------------- | :----- | | 224 | 2023-10-13 14:33:17 | oil change | 0W-20 | | 224 | 2023-10-13 14:50:09 | oil filter change | *NULL* | | 7889 | 2010-01-03 09:11:42 | timing belt replacement | *NULL* | | 8031 | 2015-08-30 12:03:31 | diagnostic | P1155 | ### 使用场景：定义车辆群体 我们的目标用户群体有兴趣寻找符合特定特征和维护历史的车辆，主要是为了向这些车辆的车主营销产品或服务。行业术语称之为**车辆群体** (<https://www.nhtsa.gov/search?q=vehicle+population>)。以下是一些示例车辆群体： 1. 所有进行过进气升级、排气升级，并且重新配置了燃油/空气比率的车辆（即所谓的 [rice rockets](https://www.youtube.com/watch?v=f9x74SlY1ik)）。 2. 曾出现过三次或更多 P1155 诊断代码，但尚未更换氧传感器的丰田车。 3. 曾更换变速箱油，并在六个月内进行了完整变速箱重建的车辆。 我为目标用户群体构思了许多其他使用场景。然而，为了约束并聚焦我的设计，我将它们精简为上述少量示例用例，我认为这些用例最能捕捉用户的需求。这些是我用来指导设计过程的**参考查询**。我不确定这是否是查询语言设计的典型方式。但我可以说，在設計语言之前，对用户进行画像并将其需求提炼为一组集中的查询，是我所做的最重要的练习。它使我的工作有了根基，并为我提供了一个测试设计想法的对象。 ## 是否有现有的解决方案？ 这不可能不是第一次有人遇到这样的问题。也许有什么现成的东西我们可以直接使用。 ### 第一站：SQL 我自然而然——而且在我看来是正确的——冲动是首先考虑是否可以直接使用 SQL。数据已经结构化为表格。SQL 是一种强大、灵活的查询语言，被广泛使用且经受了时间的考验。也许我只需要创建几个视图，使某些类型的查询更容易表达，这样就足够了。 因此，我进行了另一个练习，我发现这对我的设计过程至关重要，就像之前的练习一样：我用 SQL 表达我的参考查询，看看它们会是什么样子。这些查询是否有可复用的模式？查询本身是否足够简单，以便我们的半技术和非技术用户能够编写？最终，我真的需要费心开发自定义查询语言吗？ 考虑我们之前记录的第一个参考查询： > 所有进行过进气升级、排气升级，并且重新配置了燃油/空气比率的车辆。 这是一个查询任何类型车辆的查询，这些车辆在维护历史中有三个独立的事件。事件可以以任何顺序出现；我们只是想看到对于给定车辆，这三个事件都存在，以便将其包含在此查询的结果集中。我的用户的大量查询——大约一半——大致属于这种性质，因此我想出的任何解决方案都需要很好地适用于这个简单的用例。 我尝试用 SQL 编写这个查询的第一次尝试看起来像这样： ```sql SELECT vehicle_id FROM maintenance_history WHERE work_done = 'intake upgrade' AND work_done = 'exhaust upgrade' AND work_done = 'fuel/air ratio reconfig'; ``` 这个查询是错误的，但这是我*希望*能够用来满足此用例的查询类型。它是错误的，因为我们正在寻找三个*不同*的行，具有相同的 `vehicle_id`，且 `work_done` 具有这三个不同的值，而上述查询试图找到一个*单个*行匹配所有值，这显然行不通。将 `AND` 改为 `OR` 也没有帮助，因为那样会找到维护历史中有任何*一个*这些事件的车辆，而我们想要的是拥有*所有*这些事件的车辆。 这种摸索我*感觉*自己想写的代码的过程，结果成为帮助我设计更好解决方案的另一个关键点。我们稍后会回到这一点。 对此用例的正确 SQL 查询如下： ```sql SELECT vehicle_id FROM maintenance_history WHERE work_done IN ( 'intake upgrade', 'exhaust upgrade', 'fuel/air ratio reconfig' ) GROUP BY vehicle_id HAVING COUNT(DISTINCT work_done) = 3 ``` 这虽然可行，但缺乏灵活性。如果我们稍微更改查询，以搜索升级了*任一*进气或排气（或两者都升级），并且重新配置了燃油/空气比率的汽车，我们就不能再使用这种查询模式了。这是因为这里的聚合破坏了关于在车辆上具体做了什么工作的信息。我们希望聚合每辆车的维护历史，以便一次性跨整个历史进行查询，而不丢失使我们能够表达更精细条件（如“做过这个*或*那个”或“做过这个*然后*那个”）的详细信息。 经过一些实验，我想出了以下查询模式： ```sql WITH history AS ( SELECT *, work_done = 'intake upgrade' AS has_intake_upgrade, work_done = 'exhaust upgrade' AS has_exhaust_upgrade, work_done = 'fuel/air ratio reconfig' AS has_ratio_reconfig FROM maintenance_history -- 这里的过滤器对性能很重要，但不是正确结果所必需的。 WHERE work_done IN ( 'intake upgrade', 'exhaust upgrade', 'fuel/air ratio reconfig' ) ) SELECT vehicle_id, BOOL_OR(has_intake_upgrade) AS has_intake_upgrade, BOOL_OR(has_exhaust_upgrade) AS has_exhaust_upgrade, BOOL_OR(has_ratio_reconfig) AS has_ratio_reconfig FROM history GROUP BY vehicle_id HAVING has_intake_upgrade AND has_exhaust_upgrade AND has_ratio_reconfig ``` 显然，这个解决方案比前一个更长。但作为一种查询模式，它将我们的查询分解为两个逻辑阶段：首先，我们提取每辆车历史的关键属性（各种 `BOOL_OR(...)` 部分），然后在这些属性上进行过滤（在 `HAVING` 子句中）。这种分离使模式非常灵活。例如，让我们重新考虑我们之前考虑的第一个查询的变体： > 升级了进气或排气（或两者都升级），并且重新配置了燃油/空气比率的汽车 这可以用与上面大致相同的查询来表达。我们只需要更改 `HAVING` 子句： ```sql HAVING (has_intake_upgrade OR has_exhaust_upgrade) AND has_ratio_reconfig ``` 我们只处理了第一个参考查询，但与我们目标车辆群体的纯英语描述相比，我们的 SQL 解决方案已经显得冗长且笨拙。其他查询可能有不适合我们提出模式的方面，更复杂的查询当然需要更长的 SQL 来表达。虽然在我探索问题的过程中，我尝试将大约六个查询翻译为 SQL，但我不会在这里逐一介绍。但我的结论是，我看不出如何简化这会让那些觉得这种 SQL 量令人望而生畏或根本无法接近的用户。每个查询都需要从维护历史中提取不同的属性，因此以一组视图重组数据以简化查询似乎不是一个可行的方法。 ### 为什么不使用 LLM？ SQL 很长。那又怎样？LLM（大型语言模型）非常擅长为这类受限问题编写代码。我们只需给 LLM 提供我们想要的车辆群体的纯英语描述，让它自动生成 SQL。这是一个诱人的想法。对一些人来说，LLM 加上一个简单的界面足以替代自定义查询语言。然而，对于这种情况，LLM 不能作为完整的解决方案。 自然英语没有编程语言的精确性，因此 LLM 首先必须解释可能含糊不清的描述。它是否正确解释了描述？必须有人验证生成的 SQL。正如我们在第一个参考查询的逐步操作中看到的那样，SQL 比它所捕获的英语描述冗长得多。而且许多查询，如我们另外两个参考查询，将更加复杂。 在一年时间里，这些分析师编写数百——甚至数千——个描述车辆群体的查询。这些群体是基于分析师的商业知识和与客户的对话设计的。分析师需要理解并捍卫他们构建的每个群体的细节。如果他们无法理解生成的 SQL，他们该如何做到这一点？即使是懂一些 SQL 的半技术分析师，也会因日复一日地审查如此多的生成 SQL 而迅速感到疲劳。 换句话说，问题主要在于**问责制** (<https://x.com/bumblebike/status/832394003492564993/photo/1>)和**可访问性**。通过一种针对手头业务问题的、易于访问的查询语言，这些分析师可以拥有他们的工作。自定义语言也将使 LLM 对他们更有用，而不是与 SQL 一起工作。他们将能够生成和调试他们实际上可以理解的查询。并且，与 SQL 相比，LLM 更有可能在一次尝试中正确完成与英语之间的翻译。LLM 并没有改变这个基本事实：编程就是思考，而当你使用正确的抽象时，清晰的思考要容易得多。我们的分析师，无论他们是否这样认为，都在进行一种形式的编程。 ### 其他先前的艺术 我进行了两轮先前艺术（prior art）研究。第一轮发生在我 2024 年底开始设计工作之前。当时我不确定要寻找什么，或者如何以与车辆维护主题无关的领域无关方式解释问题。我已经熟悉 [Cypher](https://neo4j.com/docs/cypher-manual/current/introduction/cypher-overview/) 和 [GraphFrames](https://graphframes.io/)。虽然我的问题不是图问题，但我确实从它们的语法中汲取了灵感，即使我没有直接使用其中任何一个。我记得搜索了“时间序列分析”和“时间序列查询语言”，因为我以为这是最接近我正在做的事情的描述。但结果似乎侧重于统计分析，而不是模式匹配，后来我意识到后者才是对我想要的更好的描述。 这一轮搜索中的一个有趣发现是 [Kusto](https://learn.microsoft.com/en-us/kusto/query/)，其一等公民的流水线让我想起了 [PRQL](https://prql-lang.org/)。 在撰写本文的过程中，我进行了另一轮先前艺术研究，这次是在我设计和实现新语言的经验的指导下，并借助 LLM 的协助。这次我找到了更多相关的结果，首先是术语 [Complex Event Processing](https://en.wikipedia.org/wiki/Complex_event_processing) (CEP)，这似乎是一个最能描述我们问题领域的行业术语。市面上有很多 CEP 引擎。其中一些是专有的；大多数扩展了 SQL 或提供了非常类似 SQL 的 DSL。在这个领域的一个令人惊讶的发现是 SQL 的 [`MATCH_RECOGNIZE`](https://learn.microsoft.com/en-us/stream-analytics-query/match-recognize-stream-analytics)，此前我不知何故漏掉了它。Oracle 首次在 2013 年发布了此功能，后来作为 SQL:2016 的一部分进行了标准化。这似乎是 SQL 对事件模式匹配问题的解决方案。截至 2026 年初，它并不广泛支持（PostgreSQL 和 Apache Spark 都不支持），而且最重要的是，它并没有足够简化我们参考查询的表达，使其对我的用户可访问。但这仍然是一个有趣的发展！ 我在研究 CEP 引擎时发现的有趣事物是 Elasticsearch 的 [Event Query Language (EQL)](https://www.elastic.co/docs/explore-analyze/query-filter/languages/eql)。它主要用于威胁检测，但对于我们的一些参考查询，它似乎非常适合这个问题。如果我的公司已经在使用 Elasticsearch，值得深入挖掘 EQL 作为解决方案，或者至少作为一个起点。 最后一点我想指出的先前艺术是 [Logica](https://logica.dev/)。它不是 CEP 引擎，而是一种 Datalog 家族的逻辑编程语言。Datalog 总是让我印象深刻的是，它如何优雅地表达那些在 SQL 中需要更多文字才能复制的思想。而 Logica 特别之处在于，它将程序编译成可以在大规模上运行的 SQL（尽管 Apache Spark 目前不是其支持的方言之一）。如果今天我重新开始，我会考虑围绕 Logica 构建一个薄薄的包装，作为我的用户的 DSL。 ## 提炼问题的本质 在寻找针对我试图为用户解决的问题的现有解决方案时，我从正确的地方开始了：SQL，*de facto* 的通用查询语言。