AI_TO_SQL (Lakehouse v1)

使用最新模型 text-davinci-003 将自然语言指令转换为 SQL 查询。

PlaidCloud Lakehouse 通过结合 OLAP 与 AI，提供了一种高效构建 SQL 查询的方案。借助此函数，可以将用自然语言编写的指令转换为与表结构相符的 SQL 查询语句。例如，向该函数输入“Get all items that cost 10 dollars or less”这样的句子，即可生成对应的 SQL 查询“SELECT * FROM items WHERE price <= 10”作为输出。

请参阅上游实现。

Analyze 语法

func.ai_to_sql('<natural-language-instruction>')

Analyze 示例

在此示例中，使用 AI_TO_SQL 函数根据指令生成 SQL 查询语句，然后执行生成的语句以获取查询结果。

func.ai_to_sql('List the total amount spent by users from the USA who are older than 30 years, grouped by their names, along with the number of orders they made in 2022')

函数生成的 SQL 语句作为输出：

*************************** 1. row ***************************
     database: openai
generated_sql: SELECT name, SUM(price) AS total_spent, COUNT(order_id) AS total_orders
               FROM users
                        JOIN orders ON users.id = orders.user_id
               WHERE country = 'USA' AND age > 30 AND order_date BETWEEN '2022-01-01' AND '2022-12-31'
               GROUP BY name;

SQL 语法

USE <your-database>;
SELECT * FROM ai_to_sql('<natural-language-instruction>');

SQL 示例

在此示例中，使用 AI_TO_SQL 函数根据指令生成 SQL 查询语句，然后执行生成的语句以获取查询结果。

准备数据。

CREATE DATABASE IF NOT EXISTS openai;
USE openai;

CREATE TABLE users(
    id INT,
    name VARCHAR,
    age INT,
    country VARCHAR
);

CREATE TABLE orders(
    order_id INT,
    user_id INT,
    product_name VARCHAR,
    price DECIMAL(10,2),
    order_date DATE
);

-- Insert sample data into the users table
INSERT INTO users VALUES (1, 'Alice', 31, 'USA'),
                         (2, 'Bob', 32, 'USA'),
                         (3, 'Charlie', 45, 'USA'),
                         (4, 'Diana', 29, 'USA'),
                         (5, 'Eva', 35, 'Canada');

-- Insert sample data into the orders table
INSERT INTO orders VALUES (1, 1, 'iPhone', 1000.00, '2022-03-05'),
                          (2, 1, 'OpenAI Plus', 20.00, '2022-03-06'),
                          (3, 2, 'OpenAI Plus', 20.00, '2022-03-07'),
                          (4, 2, 'MacBook Pro', 2000.00, '2022-03-10'),
                          (5, 3, 'iPad', 500.00, '2022-03-12'),
                          (6, 3, 'AirPods', 200.00, '2022-03-14');

以英文编写的指令作为输入，运行 AI_TO_SQL 函数。

SELECT * FROM ai_to_sql(
    'List the total amount spent by users from the USA who are older than 30 years, grouped by their names, along with the number of orders they made in 2022');

函数生成的 SQL 语句作为输出：

*************************** 1. row ***************************
     database: openai
generated_sql: SELECT name, SUM(price) AS total_spent, COUNT(order_id) AS total_orders
               FROM users
                        JOIN orders ON users.id = orders.user_id
               WHERE country = 'USA' AND age > 30 AND order_date BETWEEN '2022-01-01' AND '2022-12-31'
               GROUP BY name;

运行生成的 SQL 语句以获取查询结果。

┌─────────┬─────────────┬─────────────┐
│ name    │ order_count │ total_spent │
├─────────┼─────────────┼─────────────┤
│ Bob     │           2 │     2020.00 │
│ Alice   │           2 │     1020.00 │
│ Charlie │           2 │      700.00 │
└─────────┴─────────────┴─────────────┘