仓库源文，站点原文

title: "LLM-based Text2SQL" categories:

Gao, D., Wang, H., Li, Y., Sun, X., Qian, Y., Ding, B., & Zhou, J. (2023). Text-to-sql empowered by large language models: A benchmark evaluation. arXiv preprint arXiv:2308.15363.

个人总结: 一篇 LLM 在 Text2SQL 数据集上的 prompt engineering 的实验报告. 在文中评测的两个数据集中效果是开源方案中最好的. 提出的 prompt 方案 DAIL-SQL 融合了现有的几种 RAG 方法.

<!-- more -->

数据集

实际上看给出的 Data Examples, 即使是 EXTRA HARD 的样例, 涉及的数据库和 SQL 相比实际都相当简单.

[Extra Hard] What is the average life expectancy in the countries where English is not the official language?

SELECT AVG(life_expectancy)
FROM country
WHERE name NOT IN 
   (SELECT T1.name
    FROM country AS T1 JOIN
    country_language AS T2
    ON T1.code = T2.country_code
    WHERE T2.language = "English"
      AND T2.is_official = "T")

评价指标

Prompts

Question Representation

Table continents, columns = [ContId, Continent]
Table countries, columns = [CountryId, CountryName, Continent]
Q: How many continents are there?
A: SELECT

Given the following database schema:
continents: ContId, Continent
countries: CountryId, CountryName, Continent

Answer the following: How many continents are there?
SELECT

### Complete sqlite SQL query only and with no explanation
### SQLite SQL tables, with their properties:
#
# continents (ContId, Continent)
# countries (CountryId, CountryName, Continent)
#
### How many continents are there?
SELECT

/* Given the following database schema: */
CREATE TABLE continents (
    ContId int primary key,
    Continent text,
    foreign key (ContId) references countries (Continent)
);

CREATE TABLE countries (
    CountryId int primary key,
    CountryName text,
    Continent int,
    foreign key (Continent) references continents (ContId)
);

/* Answer the following: How many continents are there? */
SELECT

Below is an instruction that describes a task, paired
with an input that provides further context. Write a
response that appropriately completes the request.

### Instruction:
Write a SQL query to answer the question "How many continents are there?"

### Input:
continents (ContId, Continent)
countries (CountryId, CountryName, Continent)

### Response:
SELECT

In-Context Learning

考虑 k-shot: 从训练集 (question-sql pairs) 中选 k 个放入 prompt.

Example Organization

/* Given the following database schema: */
${DATABASE_SCHEMA}
/* Answer the following: How many authors are there? */
SELECT COUNT(*) FROM authors

/* Given the following database schema: */
${DATABASE_SCHEMA}
/* Answer the following: How many farms are there? */
SELECT COUNT(*) FROM farm

${TARGET_QUESTION}

/* Some SQL examples are provided based on similar problems: */
SELECT COUNT(*) FROM authors

SELECT COUNT(*) FROM farm

${TARGET_QUESTION}

DAIL-SQL

这篇论文提出的方法, 缝合了上述所有方法.

/* Some example questions and corresponding SQL queries are provided based on similar problems: */

/* Answer the following: How many authors are there? */
SELECT COUNT(*) FROM authors

/* Answer the following: How many farms are there? */
SELECT COUNT(*) FROM farm

${TARGET_QUESTION}

最后是用这套 prompt 在 GPT-4 上达到 sota. 微调则是对开源小 LLM 进行, 因为没钱调 GPT-4.

用 zero-shot prompt 微调, 发现微调后的 zero-shot 效果远远好于微调前的 few-shots 效果, 但是微调后用 few-shots 并没有提升还可能下降效果.

其他