Qwen：Qwen3，R1 在 Text2SQL 效果评估

发布于：2025-05-13 ⋅ 阅读:(14) ⋅ 点赞:(0)

【对比模型】

  • Qwen3 235B-A22B（2350亿总参数，220亿激活参数），32B，30B-A3B；
  • QwQ 32B（推理模型）
  • DeepSeek-R1 671B（满血版）（推理模型）

1，跑分对比

【官方对比】

指标/模型 Qwen3 235B-A22B Qwen3-32B Qwen3-30B-A3B QwQ-32B DeepSeek-R1 671B-A37B
AIME 24 85.7 81.4 80.4 79.5 79.8
AIME 25 81.5 72.9 70.9 69.5 70.0
LiveCodeBench 70.7 65.7 62.6 62.7/63.4 64.3/65.9
LiveBench 77.7 74.9 74.3 72.0/73.1 71.6
BFCL 70.8 70.3 69.1 66.4 56.9/60.3
ArenaHard 95.6 93.8 91.0 89.5 93.2
CodeForces 2056 1977 1974 1982 2029
Aider 61.8 50.2 - - 56.9
MultiIF 70.8 70.3 72.2 68.3 56.9
GPQA - - 65.8 65.6 -
IFEval - - - 83.9 83.3

PS：表中多个数据：62.7/63.4。左出自 Qwen3 博客，右出自 QwQ-32B 论文。

【结论总结】

  • Qwen3 235B-A22B 为全方位领先模型，在所有公开指标中几乎都是最高分，尤其在数学、代码、对话与逻辑推理方面，具备显著优势。

  • Qwen3-32B 和 30B-A3B 是成本更低但保持较好性能的替代选项，适合资源有限的场景。

  • QwQ 32B 和 DeepSeek-R1 在部分指标表现尚可（如IFEval、CodeForces），但总体偏弱，且不稳定。

  • DeepSeek-R1 的表现不及其模型规模（671B），说明其效率或能力未充分发挥。

【数据集介绍】

  • AIME 24 / 25：数学竞赛题，考查数学推理能力。

  • LiveCodeBench：代码生成任务。

  • LiveBench：多任务真实世界评测。

  • BFCL、ArenaHard：多为复杂推理或语言理解任务。

  • CodeForces：程序竞赛分数（越高越好）。

  • Aider、MultiIF、GPQA、IFEval：代码辅助、复杂推理、多跳问答等任务。

2，模型架构

指标/模型 Qwen3 235B-A22B Qwen3-32B Qwen3-30B-A3B QwQ-32B DeepSeek-R1 671B-A37B
种类 MoE Dense MoE Dense MoE
总参数 235B 32B 30B 32B 671B
网络层数 94 64 48 64 62
注意力头数 64 / 4 64 / 8 32 / 4 40 / 8 -
上下文长度 128K 128K 128K 128K 128K
  • 235B：模型总参数；A22B：MoE架构下的激活参数；

【MoE & Dense】

  • Dense（稠密）模型是指：每个输入都激活模型中所有的参数，所有的神经网络层和单元都会参与到每一次前向传播计算中。

  • MoE 模型是指：为每个输入动态选择一部分“专家子网络”进行激活和计算，而不是激活全部参数。比如，一个 MoE 层里有 64 个专家网络，但每次输入只选其中 2~4 个进行前向传播。

特性 Dense 模型 MoE 模型
每次激活参数量 全部参数 部分专家（如 2/64）
参数规模 通常较小（如70B） 可超大（如1T），但每次推理只用一小部分
推理成本 高 相对低
性能提升潜力 线性增长 潜在指数增长
实现复杂度 简单 高，需要路由和负载均衡机制

3，语义SQL解析

任务描述：

你的目标是将给定的自然语言查询（NLQ）转换为相应的SQL查询语句。我们希望通过测试不同的LLM模型，在语义理解和生成SQL语句的准确性方面，找到它们之间的差异。

请根据以下自然语言查询，生成对应的SQL查询语句。

自然语言查询示例：

  1. 查询1: "获取所有销售额大于1000的订单的订单号、客户姓名和订单日期。"

  2. 查询2: "找到所有在2023年1月1日到2023年12月31日之间下单的客户的名称和他们的总购买金额。"

  3. 查询3: "返回所有购买了'电子产品'类别商品的客户信息，以及他们购买的商品的名称。"

  4. 查询4: "获取2022年第二季度内，每个地区的平均销售额和总订单数。"

  5. 查询5: "列出所有库存低于50的产品信息，包括产品名称、库存数量和价格。"

数据库表结构（假设）：

  • orders (订单表): order_id, customer_id, order_date, total_amount, region

  • customers (客户表): customer_id, customer_name

  • products (产品表): product_id, product_name, category, stock_quantity, price

  • order_items (订单项表): order_id, product_id, quantity, price

输出：
对于每个查询，请生成一个相应的SQL查询语句。

【测试结果】Qwen3 235B-A22B & DeepSeek-R1 671B-A37B 胜出

指标/模型 Qwen3 235B-A22B Qwen3-32B Qwen3-30B-A3B QwQ-32B DeepSeek-R1 671B-A37B
查询1 √ √ √ √ √
查询2 ⭐⭐⭐ ⭐⭐ ⭐⭐ ⭐⭐ ⭐⭐⭐
查询3 √ √ √ √ √
查询4 √ √ √ √ √
查询5 √ √ √ √ √
  • 查询1: "获取所有销售额大于1000的订单的订单号、客户姓名和订单日期。"
# deepseek-r1
SELECT o.order_id, c.customer_name, o.order_date FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.total_amount > 1000;
# qwen3-235b-a22b
SELECT o.order_id, c.customer_name, o.order_date FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.total_amount > 1000;
# qwen3-32b
SELECT o.order_id, c.customer_name, o.order_date FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.total_amount > 1000;
# qwen3-30b-a3b
SELECT orders.order_id, customers.customer_name, orders.order_date FROM orders JOIN customers ON orders.customer_id = customers.customer_id WHERE orders.total_amount > 1000;
# qwq:32b
SELECT o.order_id, c.customer_name, o.order_date FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.total_amount > 1000;
  • 查询2: "找到所有在2023年1月1日到2023年12月31日之间下单的客户的名称和他们的总购买金额。"
# deepseek-r1
SELECT c.customer_name, SUM(o.total_amount) AS total_purchase FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY c.customer_id;
# qwen3-235b-a22b
SELECT c.customer_name, SUM(o.total_amount) AS total_purchase_amount FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY c.customer_name;
# qwen3-32b
SELECT c.customer_name, SUM(o.total_amount) AS total_purchase FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY c.customer_id, c.customer_name;
# qwen3-30b-a3b
SELECT customers.customer_name, SUM(orders.total_amount) AS total_purchase FROM orders JOIN customers ON orders.customer_id = customers.customer_id WHERE orders.order_date BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY customers.customer_id, customers.customer_name;
# qwq:32b
SELECT c.customer_name, SUM(o.total_amount) AS total_purchase_amount FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY c.customer_id, c.customer_name;
  • 查询3: "返回所有购买了'电子产品'类别商品的客户信息，以及他们购买的商品的名称。"
# deepseek-r1
SELECT DISTINCT c.*, p.product_name FROM customers c JOIN orders o ON c.customer_id = o.customer_id JOIN order_items i ON o.order_id = i.order_id JOIN products p ON i.product_id = p.product_id WHERE p.category = '电子产品';
# qwen3-235b-a22b
SELECT DISTINCT c.*, p.product_name FROM customers c JOIN orders o ON c.customer_id = o.customer_id JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id WHERE p.category = '电子产品';
# qwen3-32b
SELECT c.customer_name, p.product_name FROM customers c JOIN orders o ON c.customer_id = o.customer_id JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id WHERE p.category = '电子产品';
# qwen3-30b-a3b
SELECT customers.customer_name, products.product_name FROM customers JOIN orders ON customers.customer_id = orders.customer_id JOIN order_items ON orders.order_id = order_items.order_id JOIN products ON order_items.product_id = products.product_id WHERE products.category = '电子产品';
# qwq:32b
SELECT DISTINCT c.*, p.product_name FROM customers c JOIN orders o ON c.customer_id = o.customer_id JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id WHERE p.category = '电子产品';
  • 查询4: "获取2022年第二季度内，每个地区的平均销售额和总订单数。"
# deepseek-r1
SELECT region, AVG(total_amount) AS avg_sales, COUNT(order_id) AS total_orders FROM orders WHERE order_date BETWEEN '2022-04-01' AND '2022-06-30' GROUP BY region;
# qwen3-235b-a22b
SELECT region, AVG(total_amount) AS avg_sales, COUNT(*) AS total_orders FROM orders WHERE order_date BETWEEN '2022-04-01' AND '2022-06-30' GROUP BY region;
# qwen3-32b
SELECT o.region, AVG(o.total_amount) AS avg_sales, COUNT(o.order_id) AS total_orders FROM orders o WHERE o.order_date BETWEEN '2022-04-01' AND '2022-06-30' GROUP BY o.region;
# qwen3-30b-a3b
SELECT region, AVG(total_amount) AS avg_sales, COUNT(order_id) AS total_orders FROM orders WHERE order_date BETWEEN '2022-04-01' AND '2022-06-30' GROUP BY region;
# qwq:32b
SELECT region, AVG(total_amount) AS average_sales, COUNT(order_id) AS total_orders FROM orders WHERE order_date BETWEEN '2022-04-01' AND '2022-06-30' GROUP BY region;
  • 查询5: "列出所有库存低于50的产品信息，包括产品名称、库存数量和价格。"
# deepseek-r1
SELECT product_name, stock_quantity, price FROM products WHERE stock_quantity < 50;
# qwen3-235b-a22b
SELECT product_name, stock_quantity, price FROM products WHERE stock_quantity < 50;
# qwen3-32b
SELECT product_name, stock_quantity, price FROM products WHERE stock_quantity < 50;
# qwen3-30b-a3b
SELECT product_name, stock_quantity, price FROM products WHERE stock_quantity < 50;
# qwq:32b
SELECT product_name, stock_quantity, price FROM products WHERE stock_quantity < 50;

4，复杂语义SQL解析

【测试1】

你是一个资深的数据分析师，负责为公司的业务部门从数据库中提取关键数据。请根据以下复杂的自然语言业务需求，编写一条等价的 SQL 查询语句，并确保语义准确、逻辑严谨、结果符合业务预期。

【业务需求】找出在 2023 年第二季度内，购买了 "旗舰X200" 产品的客户中，总消费金额排名前 10 的客户姓名和他们所在的城市，要求这些客户的订单中至少包含一次“线上”支付，并且不包含任何“退款”记录。

【数据库表结构说明】
1. customers(id, name, city)
2. orders(id, customer_id, order_date, payment_method, status)
3. order_items(id, order_id, product_name, quantity, price)

说明：
- 一个 customer 可以有多个 orders；
- 一个 order 包含多个 order_items；
- payment_method 可能的值有 '线上', '线下'；
- status 可能的值有 '已完成', '退款', '取消'；
- order_date 是日期格式。

【额外说明】
- 请仅返回 SQL 语句，不要附带解释；
- 若需使用子查询、联结或聚合，请合理安排；
- 所有字段和表名都来自上述数据库结构；
- 尽可能使用标准 SQL 语法，确保在 MySQL 或 PostgreSQL 上兼容。

【返回格式】
```sql
-- SQL 查询语句
SELECT ...

【测试结果】Qwen3 235B-A22B & DeepSeek-R1 671B-A37B 胜出

  • deepseek-r1：7，有效筛选客户，但金额统计和子条件时间范围不严谨
  • qwen3-32b：9，少量语义模糊
  • qwq:32b：10
  • qwen3-30b-a3b：6，子查询没有限定时间
  • qwen3-235b-a22b：10
# deepseek-r1
SELECT c.name, c.city, SUM(oi.quantity * oi.price) AS total_amount
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
WHERE c.id IN (
    SELECT DISTINCT o.customer_id
    FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    WHERE oi.product_name = '旗舰X200'
    AND o.order_date BETWEEN '2023-04-01' AND '2023-06-30'
)
AND EXISTS (
    SELECT 1
    FROM orders o2
    WHERE o2.customer_id = c.id
    AND o2.payment_method = '线上'
)
AND NOT EXISTS (
    SELECT 1
    FROM orders o3
    WHERE o3.customer_id = c.id
    AND o3.status = '退款'
)
GROUP BY c.id, c.name, c.city
ORDER BY total_amount DESC
LIMIT 10;
# qwen3-32b
SELECT 
    c.name, 
    c.city, 
    SUM(CASE WHEN o.status = '已完成' THEN oi.quantity * oi.price ELSE 0 END) AS total_spent
FROM 
    customers c
JOIN 
    orders o ON c.id = o.customer_id
JOIN 
    order_items oi ON o.id = oi.order_id
WHERE 
    oi.product_name = '旗舰X200'
    AND o.order_date BETWEEN '2023-04-01' AND '2023-06-30'
GROUP BY 
    c.id, c.name, c.city
HAVING 
    SUM(CASE WHEN o.status = '退款' THEN 1 ELSE 0 END) = 0
    AND SUM(CASE WHEN o.payment_method = '线上' AND o.status = '已完成' THEN 1 ELSE 0 END) >= 1
ORDER BY 
    total_spent DESC
LIMIT 10;
# qwq:32b
SELECT c.name, c.city, SUM(oi.quantity * oi.price) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.order_date BETWEEN '2023-04-01' AND '2023-06-30'
AND c.id IN (
    SELECT DISTINCT o2.customer_id
    FROM orders o2
    JOIN order_items oi2 ON o2.id = oi2.order_id
    WHERE o2.order_date BETWEEN '2023-04-01' AND '2023-06-30'
    AND oi2.product_name = '旗舰X200'
)
GROUP BY c.id, c.name, c.city
HAVING 
    EXISTS (
        SELECT 1
        FROM orders o3
        WHERE o3.customer_id = c.id
        AND o3.payment_method = '线上'
        AND o3.order_date BETWEEN '2023-04-01' AND '2023-06-30'
    )
    AND NOT EXISTS (
        SELECT 1
        FROM orders o4
        WHERE o4.customer_id = c.id
        AND o4.status = '退款'
        AND o4.order_date BETWEEN '2023-04-01' AND '2023-06-30'
    )
ORDER BY total_spent DESC
LIMIT 10;
# qwen3-30b-a3b
SELECT c.name, c.city, SUM(oi.quantity * oi.price) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.order_date BETWEEN '2023-04-01' AND '2023-06-30'
  AND oi.product_name = '旗舰X200'
  AND o.status != '退款'
  AND c.id NOT IN (SELECT customer_id FROM orders WHERE status = '退款')
GROUP BY c.id, c.name, c.city
HAVING SUM(CASE WHEN o.payment_method = '线上' THEN 1 ELSE 0 END) >= 1
ORDER BY total_spent DESC
LIMIT 10;
# qwen3-235b-a22b
SELECT c.name, c.city, SUM(oi.quantity * oi.price) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.order_date BETWEEN '2023-04-01' AND '2023-06-30'
  AND NOT EXISTS (
    SELECT 1
    FROM orders o2
    WHERE o2.customer_id = c.id
      AND o2.order_date BETWEEN '2023-04-01' AND '2023-06-30'
      AND o2.status = '退款'
  )
  AND EXISTS (
    SELECT 1
    FROM orders o3
    JOIN order_items oi2 ON o3.id = oi2.order_id
    WHERE o3.customer_id = c.id
      AND o3.order_date BETWEEN '2023-04-01' AND '2023-06-30'
      AND oi2.product_name = '旗舰X200'
  )
GROUP BY c.id, c.name, c.city
HAVING SUM(CASE WHEN o.payment_method = '线上' THEN 1 ELSE 0 END) >= 1
ORDER BY total_spent DESC
LIMIT 10;

5，SQL 语句理解

你是一个数据库专家，请仔细阅读以下表结构和 SQL 查询，只用一句话描述出该SQL查询的作用。

表结构：

-- 用户表
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    name VARCHAR(50),
    signup_date DATETIME,
    referrer_id INT
);

-- 订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    product_id INT,
    order_date DATETIME,
    amount DECIMAL(10,2),
    status ENUM('pending', 'completed', 'canceled')
);

-- 产品表
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    category_id INT,
    price DECIMAL(10,2),
    name VARCHAR(100)
);

-- 类别表
CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(50)
);

-- 售后表
CREATE TABLE refunds (
    refund_id INT PRIMARY KEY,
    order_id INT,
    refund_amount DECIMAL(10,2),
    refund_date DATETIME
);

SQL 查询：

SELECT 
    u.name AS user_name,
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(CASE 
        WHEN o.status = 'completed' THEN o.amount 
        ELSE 0 END) AS completed_amount,
    (
        SELECT COUNT(*) 
        FROM orders o2 
        WHERE o2.user_id = u.user_id 
          AND o2.status = 'canceled' 
          AND EXISTS (
              SELECT 1 
              FROM refunds r 
              WHERE r.order_id = o2.order_id 
                AND r.refund_amount > (
                    SELECT AVG(p.price) 
                    FROM products p 
                    WHERE p.category_id IN (
                        SELECT category_id 
                        FROM products 
                        WHERE product_id = o2.product_id
                    )
                )
          )
    ) AS suspicious_cancellations,
    (
        SELECT GROUP_CONCAT(DISTINCT c.category_name)
        FROM categories c
        WHERE c.category_id IN (
            SELECT p.category_id
            FROM products p
            WHERE p.product_id IN (
                SELECT o3.product_id
                FROM orders o3
                WHERE o3.user_id = u.user_id
                  AND o3.order_date BETWEEN 
                      DATE_SUB(CURDATE(), INTERVAL 6 MONTH) AND CURDATE()
            )
        )
    ) AS recent_categories
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.signup_date < DATE_SUB(NOW(), INTERVAL 1 YEAR)
  AND EXISTS (
      SELECT 1 
      FROM users u2 
      WHERE u2.user_id = u.referrer_id 
        AND EXISTS (
            SELECT 1 
            FROM orders o4 
            WHERE o4.user_id = u2.user_id 
              AND o4.status = 'completed'
              AND o4.amount > 1000
        )
  )
GROUP BY u.user_id, u.name
HAVING completed_amount > 500
   AND suspicious_cancellations > 1
ORDER BY completed_amount DESC
LIMIT 10;

【测试结果】正确答案：找出注册时间超过一年的活跃高消费用户（Top 10），并标记他们是否有未解决的投诉。

  • deepseek-r1：9
  • qwen3-32b：10
  • qwq:32b：6
  • qwen3-30b-a3b：7
  • qwen3-235b-a22b：9.5
# deepseek-r1
该SQL查询的作用是：筛选出注册超过一年、推荐人有大额成交订单的用户中，完成订单金额超过500且存在超过1次退款金额高于同类产品均价的异常取消订单的用户，统计其总订单数、完成金额、可疑取消次数及最近半年的购买品类，并按完成金额降序取前10名。

# qwen3-32b
该SQL查询旨在找出注册超过一年且由推荐人（推荐人至少有一个金额超过1000的已完成订单）引入的用户，统计他们的总订单数、已完成订单金额、可疑取消订单数（取消订单的退款金额超过对应产品类别的平均价格）及最近6个月购买的产品类别，筛选出已完成金额超500且可疑取消订单数超1的用户，按已完成金额降序返回前10名。

# qwq:32b
该SQL查询用于筛选出注册超过1年的用户中，由成功推荐人推荐、且在过去6个月有可疑退款行为（退款金额超过产品所属类别均价的取消订单超过1次）、年度完成订单金额超500元的用户，并统计其订单数、完成金额、可疑取消次数及近期购买品类，最终按消费金额降序取前10名。

# qwen3-30b-a3b
该SQL查询用于识别注册超过一年、其推荐人有大额订单的用户，统计其总订单数、完成金额，并筛选出存在多个可疑取消订单（退款金额高于所购产品类别的平均价格）及近期购买多类商品的高价值用户，按完成金额降序排列前10名。

# qwen3-235b-a22b
该SQL查询用于筛选出注册时间超过一年、其推荐人有过单笔订单金额超1000元的用户中，自身有效订单总金额超过500元且存在两个以上可疑取消订单（取消后退款金额高于同类商品均价）的用户，并展示其最近六个月购买过的商品类别，按有效订单金额降序返回前十名用户信息。

6，SQL 补全改写

你正在维护一个销售分析系统。现在有一个 SQL 片段用于分析客户的购买行为，但由于开发中断，该 SQL 残缺不全。请你根据提供的语义和表结构，尝试补写和改全这个 SQL，使其逻辑完整并可以在 MySQL 中正确执行，只需要输出缺失部分即可。

数据库结构（部分）：

-- 用户表
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  phone VARCHAR(20)
);

-- 订单表
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  amount DECIMAL(10,2),
  order_time DATETIME,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

任务目标语义：我们希望找出在过去3个月中，连续两次下单金额均高于该用户平均订单金额的用户姓名、联系方式、以及他们最近一次下单的时间。

初步 SQL 查询语句：

SELECT u.name, u.phone, latest_order_time
FROM users u
JOIN (
  SELECT user_id, MAX(order_time) AS latest_order_time
  FROM orders
  WHERE order_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
  GROUP BY user_id
) t ON u.id = t.user_id
WHERE u.id IN (
  -- 请补全这个子查询

【测试结果】

  • deepseek-r1：8.0，使用窗口函数但平均值时间范围错误
  • qwen3-32b：7.0，不保证连续性
  • qwq:32b：5.0，差异过大
  • qwen3-30b-a3b：9.0
  • qwen3-235b-a22b：9.5
# deepseek-r1
SELECT DISTINCT user_id
FROM (
  SELECT 
    o.user_id,
    o.amount,
    ua.avg_amount,
    LEAD(o.amount) OVER (PARTITION BY o.user_id ORDER BY o.order_time) AS next_amount
  FROM orders o
  JOIN (
    SELECT user_id, AVG(amount) AS avg_amount
    FROM orders
    GROUP BY user_id
  ) ua ON o.user_id = ua.user_id
  WHERE o.order_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
) t
WHERE t.amount > t.avg_amount AND t.next_amount > t.avg_amount
)

# qwen3-32b
SELECT user_id
FROM (
    SELECT 
        o.user_id,
        o.amount,
        ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.order_time DESC) AS rn,
        (SELECT AVG(amount) FROM orders o2 
         WHERE o2.user_id = o.user_id 
           AND o2.order_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH)) AS avg_amount
    FROM orders o
    WHERE o.order_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
) ranked
WHERE rn <= 2 AND amount > avg_amount
GROUP BY user_id
HAVING COUNT(*) = 2

# qwq:32b
SELECT user_id FROM orders o JOIN ( SELECT user_id, AVG(amount) AS avg_amt FROM orders GROUP BY user_id ) a ON o.user_id = a.user_id WHERE o.order_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH) AND o.amount > a.avg_amt GROUP BY o.user_id HAVING COUNT(*) >= 2

# qwen3-30b-a3b
SELECT user_id
FROM (
    SELECT 
        user_id,
        amount,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time DESC) AS rn
    FROM orders
    WHERE order_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
) AS ranked
WHERE rn <= 2
GROUP BY user_id
HAVING 
    MAX(CASE WHEN rn = 1 THEN amount END) > (SELECT AVG(amount) FROM orders WHERE user_id = ranked.user_id)
    AND MAX(CASE WHEN rn = 2 THEN amount END) > (SELECT AVG(amount) FROM orders WHERE user_id = ranked.user_id)

# qwen3-235b-a22b
SELECT user_id
FROM (
    SELECT 
        o.user_id,
        o.amount AS curr_amount,
        LAG(o.amount) OVER (PARTITION BY o.user_id ORDER BY o.order_time) AS prev_amount,
        ua.avg_amount
    FROM orders o
    INNER JOIN (
        SELECT user_id, AVG(amount) AS avg_amount
        FROM orders
        GROUP BY user_id
    ) ua ON o.user_id = ua.user_id
    WHERE o.order_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
) t
WHERE curr_amount > avg_amount AND prev_amount > avg_amount

7，SQL 修改纠正

任务：你将看到一条SQL查询语句及其语义描述。该查询语句存在某些错误，需要你帮助修正。请仔细检查并修复SQL中的错误。错误可能非常隐蔽，要求你深刻理解语法和语义，确保修复后的查询语句能够正确执行。只返回修改后的SQL语句即可。

给定 SQL 查询语句：

SELECT users.name, orders.product, payments.payment_amount 
FROM users
JOIN orders ON users.id = orders.user_id 
JOIN payments ON orders.order_id = payments.order_id 
WHERE users.age > 25 
  AND payments.payment_date > '2024-01-01' 
  AND orders.order_date = '2024-01-01'
  AND orders.amount > 100
ORDER BY payments.payment_amount DESC;

表结构：

  • users 表： id INT PRIMARY KEY name VARCHAR(100) email VARCHAR(100) age INT
  • orders 表： order_id INT PRIMARY KEY user_id INT product VARCHAR(100) order_date DATE amount DECIMAL(10, 2)
  • payments 表： payment_id INT PRIMARY KEY order_id INT payment_date DATE payment_amount DECIMAL(10, 2)

语义：从users表中查询所有年龄大于25的用户的名字，查询他们的订单信息（包括产品和支付金额）。只返回在2024年1月1日之后付款的订单，且订单金额大于100。

【测试结果】

  • deepseek-r1：10
  • qwen3-32b：10
  • qwq:32b：10
  • qwen3-30b-a3b：6
  • qwen3-235b-a22b：10
# deepseek-r1
SELECT users.name, orders.product, payments.payment_amount 
FROM users
JOIN orders ON users.id = orders.user_id 
JOIN payments ON orders.order_id = payments.order_id 
WHERE users.age > 25 
  AND payments.payment_date > '2024-01-01' 
  AND orders.amount > 100
ORDER BY payments.payment_amount DESC;

# qwen3-32b
SELECT users.name, orders.product, payments.payment_amount FROM users JOIN orders ON users.id = orders.user_id JOIN payments ON orders.order_id = payments.order_id WHERE users.age > 25 AND payments.payment_date > '2024-01-01' AND orders.amount > 100 ORDER BY payments.payment_amount DESC;

# qwq:32b
SELECT users.name, orders.product, payments.payment_amount FROM users JOIN orders ON users.id = orders.user_id JOIN payments ON orders.order_id = payments.order_id WHERE users.age > 25 AND payments.payment_date > '2024-01-01' AND orders.amount > 100 ORDER BY payments.payment_amount DESC;

# qwen3-30b-a3b
SELECT users.name, orders.product, payments.payment_amount FROM users JOIN orders ON users.id = orders.user_id JOIN payments ON orders.order_id = payments.order_id WHERE users.age > 25 AND payments.payment_date > '2024-01-01' AND orders.order_date = '2024-01-01' AND orders.amount > 100 ORDER BY payments.payment_amount DESC;

# qwen3-235b-a22b
SELECT users.name, orders.product, payments.payment_amount FROM users JOIN orders ON users.id = orders.user_id JOIN payments ON orders.order_id = payments.order_id WHERE users.age > 25 AND payments.payment_date > '2024-01-01' AND orders.amount > 100 ORDER BY payments.payment_amount DESC;

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布