乾学院 53 号会员
18 浏览  • 
     ...

CodeBuddy+SQLazy 盘活历史复杂 SQL 详解

 

数据库里总有一些历史 SQL，上百行、多层嵌套，写的人早已不在，注释也几乎没有，接手的人要先花不少时间读懂，才谈得上修改。让 AI 直接改也不可靠，它生成的新 SQL 很可能与原 SQL 对不上，重新审一遍的功夫不比重写少。把这类 SQL 翻译成 SQLazy 分步脚本，逻辑就清楚多了：每一步做什么一目了然，能单独运行，中间结果随时可看，哪一步不对就改哪一步，调试和审计都有据可查；脚本本身就是文档，交接、传承直接交脚本。验证通过后还能编译成其他数据库的 SQL，换库只是重新编译一次。
本文就按这个流程，完整走一遍翻译过程。

1. 安装配置 SQLazy

下载安装 SQLazy

进入官网https://www.raqsoft.com.cn/download-NaturalSPL，下载 SQLazy（NaturalSPL）

Picture1png
一键安装 SQLazy，可保留默认配置项。点击桌面图标 "NaturalSPL" 打开 IDE。

Picture2png

配置项目目录

在操作系统中，新建任意目录作为本项目根目录，比如 "d:\workbuddyNSPL"，在该目录下新建空目录 nspl，将来存放 CodeBuddy 生成的 nspl 代码文件。

打开 "SQLazy安装目录\LLM" 目录，找到 SQLazy 知识库，即该目录下的文件：SQL 转 sqlazy.md，以及 2 个目录：函数、功能。

SQL 转 sqlazy.md：自定义命令文件，定义了在 LLM 对话框调用该命令的格式、SQL 转换流程、SQLazy 格式规范、硬性约束，并声明了所有 SQLazy 函数 / 功能文档的加载目录。

函数：SQLazy 所有的函数定义文件。

功能：SQLazy 所有的功能定义文件。

将这些文件和目录复制到本项目根目录，形如：

Picture3png

配置 JDBC

将数据库对应的驱动放入 "SQLazy安装目录\..\common\jdbc"，这里以 mysql8 为例，将 mysql-connector-java-8.0.29.jar 放入该目录：

Picture4png
在 SQLazy IDE 中，用菜单 "工具 -> 数据连接" 新建数据源。新建 My_SQL 数据源，按 Java 规范填写，命名为 mysql8。点击连接按钮，如果数据源名变为粉色，则说明连接成功。

Picture5png

2. 安装配置 CodeBuddy

进入官网https://www.codebuddy.cn/ide/，下载 CodeBuddy IDE。

Picture6png
按缺省选项安装 CodeBuddy，安装后按官方要求登录。

Picture7png
用菜单 "文件 -> 打开文件夹" 打开本项目的根目录，CodeBuddy 会自动索引上下文，并自动以 Craft 类型打开右下角的 LLM 对话框。如果没有自动打开，请点击右上角的按钮展开 LLM 对话框。

Picture8png

3. 基本翻译步骤

拿到一个复杂 SQL 后，先在 CodeBuddy 中把这个 SQL 翻译成 nspl，同时分析出详细的计算要求；然后在 SQLazy IDE 中打开、运行、调试 nspl；最后把 nspl 翻译成各种方言 SQL，进行 SQL 的常规维护。

确认表数据

本步骤可选。拿到一个复杂 SQL 时，不必读懂 SQL 的计算过程，但通常可以确认 SQL 所用的表是否存在于数据库，比如，例子 SQL 使用 MySQL 8 的表 overlapping_intervals，数据如下。

account_id,start_date,end_date
A,2019-06-20,2019-06-29
A,2019-06-25,2019-07-25
A,2019-07-20,2019-08-26
A,2019-12-25,2020-01-25
A,2021-04-27,2021-07-27
A,2021-06-25,2021-07-14
A,2021-07-10,2021-08-14
A,2021-09-10,2021-11-12
B,2019-07-13,2020-07-14
B,2019-06-25,2019-08-26

生成 nspl 文件

在 CodeBuddy 的对话框中输入下面指令，第一行是固定的 skills 命令，后续行是具体 SQL：

/SQL转sqlazy
WITH t2 AS (
		SELECT account_id, start_date, end_date
			, MAX(end_date) OVER (PARTITION BY account_id ORDER BY CASE
				WHEN account_id IS NULL THEN 1
				ELSE 0
			END, account_id ASC, CASE
				WHEN start_date IS NULL THEN 1
				ELSE 0
			END, start_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_max
		FROM overlapping_intervals
	)
SELECT MAX(end_date) AS end_date, MIN(start_date) AS start_date, account_id
FROM (
	SELECT account_id, start_date, end_date, prev_max
		, 1 + SUM(CASE
			WHEN start_date > prev_max THEN 1
			ELSE 0
		END) OVER (PARTITION BY account_id ORDER BY CASE
			WHEN account_id IS NULL THEN 1
			ELSE 0
		END, account_id ASC, CASE
			WHEN start_date IS NULL THEN 1
			ELSE 0
		END, start_date ASC) AS gid
	FROM t2
) t_4
GROUP BY account_id, gid
ORDER BY account_id, gid

直接回车或点击发送按钮，CodeBuddy 将进入执行阶段。

Picture9png
执行完成后会输出多项内容，重要的有两项：任务描述、nspl 文件。任务描述是辅助内容，可以帮助我们快速理解该 SQL，包括题目、模拟数据、计算要求等。类似下面（由于 LLM 的不确定性，实际格式会有所不同）。

Picture10png
核心是 nspl 文件，打开 "项目根目录 \nspl" 可以看到该文件。

Picture11png

用内存表运行 nspl

在 SQLazy IDE 中打开 "项目根目录 \nspl" 中的 nspl 文件。注意，实际代码可能与图片中有偏差，甚至每次生成的 nspl 文件都有少许偏差，这是由 LLM 的不确定性决定的。

Picture12png
点击菜单 "工具 -> 内存表" 打开配置内存表的界面。

Picture13png
点击 "导入数据库表"，将列出数据库所有的表（当前 schema）。勾选需要用的表 overlapping_intervals，并点击 "确定"。

Picture14png
注意，CodeBuddy 会根据 SQL 的表名生成 nspl 中对应的锚点，不用人工干预。

Picture15png
在内存表界面中，overlapping_intervals 已出现在列表里，再次勾选该行的 "选中" 并关闭界面。（第一次 "导入数据库表" 是在 "选择入表" 对话框中勾选并点 "确定"，把表加入内存表列表；这次是在列表中勾选 "选中" 并关闭界面，两步操作针对同一张表。）

Picture16png
点击执行（或单步运行）按钮，如果右边显示正确计算结果，则说明 nspl 代码正常。

Picture17png
点击左边某行 nspl 代码时，右边会自动跳转到对应的中间计算结果，这在调试时非常方便。比如点击左边的 t6（导出字段）步骤，可以看到最终结果。

Picture18png

核对计算结果

将 nspl 的计算结果和原始 SQL 的计算结果进行比较，如果一样，则说明 nspl 无误。

Picture19png

移植 SQL

本步骤可选。有时候，我们需要把原 SQL 移植到其他类型的数据库，这种情况可以让 SQLazy 帮助实现。在 SQLazy IDE 中保持 nspl 打开，点击菜单 "程序 -> 编译"，在目标数据库中选择需要的类型（如 SQLServer），点击 "编译"，将自动生成对应数据库方言的 SQL。

Picture20png

4. 进阶：更多数据源

SQL数据源

上面解题步骤的数据源是库表生成的内存表，这里介绍 SQL 实时数据源。

在 nspl 代码的第一行插入新行，命名为 overlapping_intervals（必须与下一行的锚点同名），编写 SQL 功能用于取数：SQL "select * from overlapping_intervals"; 数据库 mysql8

其中，mysql8 是之前配置好的数据源名。

Picture21png
点击执行按钮，如果右边显示正确计算结果，则说明 SQL 数据源取数成功。

Picture22png

文本数据源

使用指定符号分隔的文本文件做数据源，可以脱离数据库运行 nspl。

将前面的逗号分隔的文本保存成文件 overlapping_intervals.csv。

将 nspl 代码中原来的第一行（命名为 overlapping_intervals 的行）替换为以下内容，用 nspl 的文件功能取数：文件 "d:\\overlapping_intervals.csv"; 逗号分隔; 标题

其中，"逗号分隔" 表示分隔符是逗号，可以指定其他符号；"标题" 表示文本的首行为字段名。

运行后如图所示。

Picture23png

5. 进阶：失败处理

LLM 存在幻觉风险，不能保证百分百成功，但有一些办法可以提高成功率。

补充任务描述

翻译失败主要原因是 SQL 难以解读，比如嵌套过多、结构混乱、命名不规范，如果在指令里补充任务描述，主要是目标、源数据、预期结果这三项，将显著提高成功率。比如下面是个 SQL，找出每天离特定时刻最近的记录，直接翻译容易失败，这时可以在 SQL 后面补上任务描述，完整指令如下：

Picture23png

/SQL转sqlazy
SELECT
    t,
    d
FROM
(
    SELECT
        CASE
            WHEN x = 1 THEN dt8
            ELSE dt20
        END AS t,
        CASE
            WHEN x = 1 THEN d8
            ELSE d20
        END AS d
    FROM
    (
        SELECT
            DATE(dt) grp,
            MAX(CASE WHEN rk8 = 1 THEN dt END) dt8,
            MAX(CASE WHEN rk8 = 1 THEN d END) d8,
            MAX(CASE WHEN rk20 = 1 THEN dt END) dt20,
            MAX(CASE WHEN rk20 = 1 THEN d END) d20
        FROM
        (
            SELECT
                STR_TO_DATE(t,'d.M.%Y %H:%i:%s') dt,
                t,
                d,
                RANK() OVER
                (
                    PARTITION BY DATE(STR_TO_DATE(t,'d.M.%Y %H:%i:%s'))
                    ORDER BY
                    ABS
                    (
                        TIMESTAMPDIFF
                        (
                            SECOND,
                            STR_TO_DATE(t,'d.M.%Y %H:%i:%s'),
                            STR_TO_DATE
                            (
                                CONCAT
                                (
                                    YEAR(STR_TO_DATE(t,'d.M.%Y %H:%i:%s')),
                                    '-',
                                    MONTH(STR_TO_DATE(t,'d.M.%Y %H:%i:%s')),
                                    '-',
                                    DAY(STR_TO_DATE(t,'d.M.%Y %H:%i:%s')),
                                    ' 08:00:00'
                                ),
                                '%Y-%m-%d %H:%i:%s'
                            )
                        )
                    )
                ) rk8,
                RANK() OVER
                (
                    PARTITION BY DATE(STR_TO_DATE(t,'d.M.%Y %H:%i:%s'))
                    ORDER BY
                    ABS
                    (
                        TIMESTAMPDIFF
                        (
                            SECOND,
                            STR_TO_DATE(t,'d.M.%Y %H:%i:%s'),
                            STR_TO_DATE
                            (
                                CONCAT
                                (
                                    YEAR(STR_TO_DATE(t,'d.M.%Y %H:%i:%s')),
                                    '-',
                                    MONTH(STR_TO_DATE(t,'d.M.%Y %H:%i:%s')),
                                    '-',
                                    DAY(STR_TO_DATE(t,'d.M.%Y %H:%i:%s')),
                                    ' 20:00:00'
                                ),
                                '%Y-%m-%d %H:%i:%s'
                            )
                        )
                    )
                ) rk20
            FROM tb_seq
        ) t
        GROUP BY DATE(dt)
    ) t
    JOIN
    (
        SELECT 1 x
        UNION ALL
        SELECT 2
    ) t
) t;

1.目标：找出每天离早8点和晚8点最近的记录

2.源数据：
数据库表tb_seq的t列是日期时间类型，每天对应多条数据：
t	d
1.1.2024 08:08:08	1
1.1.2024 10:10:10	2
1.1.2024 15:15:15	3
1.1.2024 20:20:20	4
2.1.2024 09:09:09	5
2.1.2024 12:12:12	6
2.1.2024 16:16:16	7
12.12.2024 16:16:16	8

3.预期结果：
t	d
1.1.2024 08:08:08	1
1.1.2024 20:20:20	4
2.1.2024 09:09:09	5
2.1.2024 16:16:16	7
12.12.2024 16:16:16	8
12.12.2024 16:16:16	8

注意：12.12.2024 16:16:16 当天只有这一条记录，它同时是距早 8 点和距晚 8 点最近的记录，因此按两个时间点各输出一次，预期结果中该行出现两次。
计算结果如下。

Picture24png

反馈错误日志

如果 SQLazy IDE 执行 nspl 时报错，则可以点击菜单 "工具 -> 控制台"，将错误日志复制出来。

Picture25png
全部粘贴到 CodeBuddy 中，并简单说明这是错误日志。LLM 会分析日志并重新生成 nspl 文件。

Picture26png

如此反复，通常可以解决问题。

使用高性能模型

使用性能一般的模型时可能失败率偏高，可尝试在 CodeBuddy 中切换到高性能模型后重试。

Picture27png