ChatGPT、Claude 和 Gemini 在数据分析方面的比较（第一部分）

欢迎来到雲闪世界测试哪个 AI 助手能写出最好的 SQL 的十个问题

第一轮：问题解决（LeetCode SQL）

问题 1 — LeetCode SQL 262

      我之所以选择这道题，是因为它在 LeetCode 上被标记为“困难”，接受率低至 36.5%。它的复杂性来自于在不同的列上两次连接同一张表并解释多个过滤器。

GPT-4o：GPT 第一次尝试就提供了正确的解决方案，并提供了详细的解释。可获得满分。

Claude 3.5 Sonnet：Claude 提供了准确的代码和详细的解释。可获得满分。

<span style="color:rgba(0, 0, 0, 0.8)"><span style="background-color:#ffffff"><span style="background-color:#f9f9f9"><span style="color:#242424">- 参考 Claude 的答案
SELECT 
    t.request_at AS Day, 
    ROUND( 
        SUM(CASE 
            WHEN t.status = 'cancelled_by_driver' OR t.status = 'cancelled_by_client' 
            THEN 1 
            ELSE 0 
        END) / COUNT(*), 
        2 
    ) AS '取消率' 
FROM 
    Trips t 
JOIN 
    Users uc ON t.client_id = uc.users_id 
JOIN 
    Users ud ON t.driver_id = ud.users_id 
WHERE 
    uc.banned = 'No' 
    AND ud.banned = 'No' 
    AND t.request_at BETWEEN '2013-10-01' AND '2013-10-03' 
GROUP BY 
    t.request_at</span></span></span></span>

Gemini 高级版：Gemini 还给出了正确的问题并附上了解释，获得了满分。有趣的是，它在回答的最后附上了一个 GitHub 仓库。Gemini 似乎不仅根据其“知识”生成问题，还会搜索互联网。然而，它链接的仓库是一个随机的 Java LeetCode 练习仓库，其中甚至没有包含这个 SQL 问题……我认为链接搜索结果可以提高可信度。但如果结果不相关，嗯？🤔

Gemini 链接了一个随机的 GitHub 仓库（图片来自作者）

问题 2 — LeetCode SQL 185

这也是一个“困难”的 SQL 问题，需要一个窗口函数。

GPT-4o：GPT 使用窗口函数提供了正确的解决方案，并给出了清晰的解释。可获得满分。

<span style="color:rgba(0, 0, 0, 0.8)"><span style="background-color:#ffffff"><span style="background-color:#f9f9f9"><span style="color:#242424">-- 参考 GPT 的解决方案
WITH RankedSalaries AS ( 
    SELECT 
        e.id, 
        e.name, 
        e.salary, 
        e.departmentId, 
        d.name AS Department, 
        DENSE_RANK() OVER (PARTITION BY e.departmentId ORDER BY e.salary DESC) AS salary_rank 
    FROM 
        Employee e 
    JOIN 
        Department d ON e.departmentId = d.id 
) 
SELECT 
    Department, 
    Name AS Employee, 
    salary AS Salary 
FROM 
    RankedSalaries 
WHERE 
    salary_rank <= 3 
ORDER BY 
    Department, salary DESC, Name;</span></span></span></span>

Claude 3.5 Sonnet：Claude 也提供了类似的正确解决方案，并附有全面的解释。可获得满分。

Gemini 进阶版：Gemini 给出了正确的解决方案，获得了满分。这次，它既链接了包含 LeetCode SQL 解决方案的相关 GitHub 存储库，也链接了具体的 LeetCode 问题。我很高兴它没有犯同样的错误。

Gemini 链接了相关的 GitHub repo 和正确的 LeetCode 问题（图片来自作者）

问题 3 — LeetCode SQL 1341

我选的最后一个问题是“中等”难度的问题，但它的录取率很低，只有 40.1%。它需要结合两个聚合中的最佳结果。

GPT-4o：解决方案正确且解释得很好，尽管有点冗长且效率低下。MaxUser和MaxMovieCTE 是多余的。请参阅下面 Claude 的简洁解决方案。因此，我给它半分。

<span style="color:rgba(0, 0, 0, 0.8)"><span style="background-color:#ffffff"><span style="background-color:#f9f9f9"><span style="color:#242424">-- GPT 的解决方案
WITH UserRatings AS（
    SELECT 
        u.name AS user_name，
        COUNT(*) AS ratings_count 
    FROM 
        MovieRating mr 
    JOIN 
        Users u ON mr.user_id = u.user_id 
    GROUP BY 
        u.user_id 
），
MovieRatingsInFeb2020 AS（
    SELECT 
        m.title，
        AVG(mr.rating) AS avg_rating 
    FROM 
        MovieRating mr 
    JOIN 
        Movies m ON mr.movi​​e_id = m.movi​​e_id 
    WHERE 
        mr.created_at BETWEEN '2020-02-01' AND '2020-02-29' 
    GROUP BY 
        m.movi​​e_id 
），
MaxUser AS（
    SELECT 
        user_name 
    FROM 
        UserRatings 
    WHERE 
        ratings_count = (SELECT MAX(rating_count) FROM UserRatings) 
    ORDER BY 
        user_name 
    LIMIT 1 
），
MaxMovie AS（
    SELECT 
        title 
    FROM 
        MovieRatingsInFeb2020 
    WHERE 
        avg_rating = (SELECT MAX(avg_rating) FROM MovieRatingsInFeb2020) 
    ORDER BY 
        title 
    LIMIT 1 
) 
SELECT user_name AS results FROM MaxUser 
UNION ALL 
SELECT title AS results FROM MaxMovie;</span></span></span></span>

Claude 3.5 Sonnet：Claude 的解决方案简洁高效，并解释了两个子查询。可得满分。

<span style="color:rgba(0, 0, 0, 0.8)"><span style="background-color:#ffffff"><span style="background-color:#f9f9f9"><span style="color:#242424">--Claude 的解决方案
(SELECT name AS results 
FROM Users u 
JOIN MovieRating mr ON u.user_id = mr.user_id 
GROUP BY u.user_id 
ORDER BY COUNT(DISTINCT mr.movi​​e_id) DESC, name 
LIMIT 1) 

UNION ALL 

(SELECT title AS results 
FROM Movies m 
JOIN MovieRating mr ON m.movi​​e_id = mr.movi​​e_id 
WHERE EXTRACT(YEAR_MONTH FROM mr.created_at) = 202002 
GROUP BY m.movi​​e_id 
ORDER BY AVG(mr.rating) DESC, title 
LIMIT 1);</span></span></span></span>

Gemini Advanced ：不幸的是，Gemini 的解决方案是错误的。由于实际上没有列，因此过滤器WHERE user_rank=1 OR movie_rank=1抛出了语法错误。有趣的是，它链接到一个包含正确解决方案的相关 GitHub 存储库，但它并没有在自己的答案中利用这一点。（所以即使它搜索互联网，它也不会使用搜索结果中的解决方案？我很困惑……）combined_resultsmovie_rank

<span style="color:rgba(0, 0, 0, 0.8)"><span style="background-color:#ffffff"><span style="background-color:#f9f9f9"><span style="color:#242424">-- Gemini 的解决方案
WITH 
-- 1. 找到评分最高的用户
MostProlificUser AS （
    SELECT 
        u.name AS results, 
        RANK() OVER (ORDER BY COUNT(*) DESC, u.name ASC) AS user_rank 
    FROM MovieRating mr 
    JOIN Users u ON mr.user_id = u.user_id 
    GROUP BY u.name 
）, 

-- 2. 计算 2020 年 2 月电影的平均评分
MovieAvgRatings AS （
    SELECT 
        m.title AS results, 
        AVG(mr.rating) AS avg_rating, 
        RANK() OVER (ORDER BY AVG(mr.rating) DESC, m.title ASC) AS movie_rank 
    FROM MovieRating mr 
    JOIN Movies m ON mr.movi​​e_id = m.movi​​e_id 
    WHERE mr.created_at BETWEEN '2020-02-01' AND '2020-02-29' 
    GROUP BY m.title    

) 

-- 3. 合并结果并筛选出排名靠前的结果
SELECT results 
FROM ( 
    SELECT results, user_rank FROM MostProlificUser 
    UNION ALL 
    SELECT results, movie_rank FROM MovieAvgRatings 
) combined_results 
WHERE user_rank = 1 OR movie_rank = 1;</span></span></span></span>

第一轮回顾

在这一轮中，获胜者是Claude 3.5 Sonnet，它正确回答了所有三个问题，并给出了清晰的解释。GPT -4o得分 2.5 分，由于查询效率低下而丢掉了 0.5 分。Gemini Advanced因最后一个问题的语法错误而获得 2 分。它的“来源和相关内容”功能仍然不一致——有时不相关，有时与答案不一致。

第一轮成绩（图片由作者提供）

第二轮：业务逻辑

奖励：数据集准备

在这一轮中，我计划将虚假数据集上传到这三个 AI 工具，提供有限的描述，并让它们读取数据集来解释我的业务问题并编写 SQL 查询。但首先，我需要生成合成数据。

我还寻求法学硕士学位的帮助来完成这项任务。

我生成合成数据集的提示（图片来自作者）

ChatGPT-4o：它生成了四个带有下载链接的 CSV 文件。我印象非常深刻，因为 GPT 不仅创建了我指定的列，还添加了与业务环境非常契合的其他列。它使用该faker包生成看起来非常真实的虚假数据。

GPT 生成的合成数据集。别担心，付款信息都是假的。（图片来自作者）

ChatGPT 利用 faker 包生成合成数据集（图片来自作者）

Claude 3.5 Sonnet：Claude 提供了使用方法生成合成数据集的 Python 代码np.random。但是，它无法直接运行代码来提供 CSV 下载链接，并且它没有使用该faker包，因此生成的数据感觉不太真实。

Gemini Advanced：Gemini 在这项任务中表现不佳。它生成了四个我可以在 Google 电子表格中打开的表格，但这些只是表格描述的表格😂。经过后续澄清，它创建了每个只有 10 行的假表格，并拒绝提供更大的数据集。

Gemini 制作了表格描述表（图片来自作者）

加载数据集

准备好四个合成数据集后，我尝试将它们上传到三个 AI 工具（在新的对话线程中）。文件总大小为 920 KB，每个数据集的行数从 500 到 5,000 行不等。

GPT 生成的文件（图片来自作者）

ChatGPT-4o：ChatGPT 成功加载了四个数据集，并为每个表提供了预览。用户界面允许您展开表格以进行更仔细的检查。其文件上传限制为每个文件 512MB，一次对话最多可上传 10 个文件。

Claude 3.5 Sonnet：当我尝试上传这四个数据集时，Claude 返回了一个错误：“对话超出长度限制的 119% ”。这令人惊讶，因为 Claude 3.5 Sonnet 的上下文窗口比 ChatGPT-4o 更高。尽管他们声明的文件上传限制为“每个文件 30MB（最多 5 个文件） ”，但实际限制似乎更低，可能是因为需求量大。最后，我不得不删掉一半以上的行，以将总文件大小减少到 320 KB，而且成功了。

克劳德关于长度限制的错误信息（图片来自作者）

双子座高级版：双子座回答说：“我无法帮助你，因为我只是一个语言模型，没有理解和回应的能力。 ”🤖️因此，我不得不在后续问题中提供表格描述，而不是真实的数据集。

Gemini 文件上传时的错误信息（图片来自作者）

问题4：月销售额计算

提示：请帮我编写一个 SQL 查询来计算美国用户每月的订单总额。

GPT-4o：它提供了带有过滤器的正确答案country = 'United States'。它根据对数据集的理解，主动添加了订单状态过滤器order_status = 'Completed'。我会给它满分，并为此加 0.5 分。

Claude 3.5 Sonnet：它还提供了正确的查询。对于“美国用户”过滤器，它写道country = 'United States' OR countru = 'US'。我故意将一些“美国”替换为“美国”，以模拟现实世界的数据不一致。我很高兴看到 Claude 注意到了这个细节🎉。它也获得了满分和 0.5 分加分。

<span style="color:rgba(0, 0, 0, 0.8)"><span style="background-color:#ffffff"><span style="background-color:#f9f9f9"><span style="color:#242424">--- 参考 Claude 的 SQL 代码
SELECT 
    EXTRACT(YEAR FROM o.order_time) AS year, 
    EXTRACT(MONTH FROM o.order_time) AS month, 
    SUM(op.total_price) AS total_amount 
FROM 
    orders o 
JOIN 
    users u ON o.user_id = u.user_id 
JOIN 
    ordered_products op ON o.order_id = op.order_id 
WHERE 
    u.country = 'United States' OR u.country = 'US' 
GROUP BY 
    EXTRACT(YEAR FROM o.order_time), 
    EXTRACT(MONTH FROM o.order_time) 
ORDER BY 
    year, month;</span></span></span></span>

Gemini 高级：Gemini 使用过滤器生成了正确的查询country = 'United States'，获得满分。

问题 5：新用户数量

提示：请帮我写一个 SQL 查询来计算每个月的首次用户数量。首次用户是当月首次下单的用户。

GPT-4o、Claude 3.5 Sonnet和Gemini Advanced：三者均提出了正确的查询并获得了满分。

<span style="color:rgba(0, 0, 0, 0.8)"><span style="background-color:#ffffff"><span style="background-color:#f9f9f9"><span style="color:#242424">--- 参考 Gemini 的 SQL 代码
WITH first_orders AS ( 
    SELECT 
        user_id, 
        MIN(order_time) AS first_order_time 
    FROM orders 
    GROUP BY user_id 
) 
SELECT 
    DATE_TRUNC('month', first_order_time) AS first_order_month, 
    COUNT(DISTINCT user_id) AS num_first_time_users 
FROM first_orders 
GROUP BY first_order_month 
ORDER BY first_order_month;</span></span></span></span>

问题6：最畅销的产品类别

提示：请帮我编写一个 SQL 查询，以按每月订单总金额获取前 5 个最受欢迎的产品类别。

GPT-4o、Claude 3.5 Sonnet和Gemini Advanced：这三个模型都使用窗口函数提出了正确的查询，获得了满分。

<span style="color:rgba(0, 0, 0, 0.8)"><span style="background-color:#ffffff"><span style="background-color:#f9f9f9"><span style="color:#242424">--- 供参考的 Gemini 代码
WITH category_orders AS ( 
    SELECT 
        DATE_TRUNC('month', o.order_time) AS order_month, 
        p.product_category, 
        SUM(op.total_price) AS total_category_amount 
    FROM orders o 
    JOIN ordered_products op ON o.order_id = op.order_id 
    JOIN products p ON op.product_id = p.product_id 
    GROUP BY order_month, p.product_category 
), 

ranked_categories AS ( 
    SELECT 
        order_month, 
        product_category, 
        total_category_amount, 
        ROW_NUMBER() OVER (PARTITION BY order_month ORDER BY total_category_amount DESC) AS category_rank 
    FROM category_orders 
) 

SELECT 
    order_month, 
    product_category, 
    total_category_amount 
FROM ranked_categories 
WHERE category_rank <= 5 
ORDER BY order_month,类别排名；</span></span></span></span>

问题 7：留存率

提示：请帮我写一个 SQL 查询来计算每个月的用户留存率。X 月的留存率 = 在 X-1 个月下订单的用户中，% 也在 X 个月下订单。

GPT-4o：它给出了错误的答案。它的PreviousMonthUsersCTE 和RetainedUsersCTE 做同样的事情……我指出了它的错误两次，才纠正了它。这里 0 分。

<span style="color:rgba(0, 0, 0, 0.8)"><span style="background-color:#ffffff"><span style="background-color:#f9f9f9"><span style="color:#242424">--- GPT 的初始代码
WITH MonthlyOrders AS ( 
    SELECT 
        o.user_id, 
        DATE_FORMAT(STR_TO_DATE(o.order_time, '%m/%d/%y %H:%i'), '%Y-%m') AS order_month 
    FROM 
        orders o 
    WHERE 
        o.order_status = 'Completed' 
    GROUP BY 
        o.user_id, order_month 
), 
PreviousMonthUsers AS ( 
    SELECT 
        mo1.order_month AS current_month, 
        COUNT(DISTINCT mo1.user_id) AS previous_month_users 
    FROM 
        MonthlyOrders mo1 
    JOIN 
        MonthlyOrders mo2 ON mo1.user_id = mo2.user_id 
        AND DATE_FORMAT(DATE_SUB(STR_TO_DATE(mo1.order_month, '%Y-%m'), INTERVAL 1 MONTH), '%Y-%m') = mo2.order_month 
    GROUP按
        当前月
），
RetainedUsers AS（
    选择
        mo1.order_month 作为当前月，
        COUNT（DISTINCT mo1.user_id）作为 retained_users
    从
        MonthlyOrders mo1
    加入
        MonthlyOrders mo2 ON mo1.user_id = mo2.user_id
        和 DATE_FORMAT（DATE_SUB（STR_TO_DATE（mo1.order_month，'％Y-％m'），INTERVAL 1 MONTH），'％Y-％m'）= mo2.order_month 
    WHERE 
        mo1.order_month ！= mo2.order_month
    按
        当前月分组
）
选择
    pmu.current_month、
    pmu.previous_month_users、
    ru.retained_users，
    CASE 
        WHEN pmu.previous_month_users = 0 THEN 0 
        ELSE ROUND（ru.retained_users * 100.0 / pmu.previous_month_users，2）以    previousMonthUsers pmu为
    起始点，以retention_rate为结束，LEFT JOIN     RetainedUsers ru按pmu.current_month = ru.current_month排序    ；





</span></span></span></span>

Claude 3.5 Sonnet：它的初始解决方案也失败了，但比 GPT 更接近。它将分母计算total_users为当前月份活跃且之前有订单的用户。但是，我希望分母是 X-1 个月活跃的用户。Claude 在一次跟进后修正了其代码，获得了 0.5 分。

<span style="color:rgba(0, 0, 0, 0.8)"><span style="background-color:#ffffff"><span style="background-color:#f9f9f9"><span style="color:#242424">--- Claude 的初始代码
WITH monthly_active_users AS （
    SELECT DISTINCT 
        user_id，
        EXTRACT(YEAR FROM order_time) AS year，
        EXTRACT(MONTH FROM order_time) AS month 
    FROM orders 
），
user_activity AS （
    SELECT 
        user_id，
        year，
        month，
        LAG(year) OVER (PARTITION BY user_id ORDER BY year，month) AS prev_year，
        LAG(month) OVER (PARTITION BY user_id ORDER BY year，month) AS prev_month 
    FROM monthly_active_users 
），
retention_counts AS （
    SELECT 
        year，
        month，
        COUNT(DISTINCT CASE 
            WHEN (prev_year = year AND prev_month = month - 1) OR 
                 (prev_year = year - 1 AND prev_month = 12 AND month = 1) 
            THEN user_id 
        END) AS retained_users，
        COUNT(DISTINCT CASE 
            WHEN prev_year IS NOT NULL AND prev_month IS NOT NULL 
            THEN user_id 
        END) AS total_users 
    FROM user_activity 
    GROUP BY year, month 
) 
SELECT 
    year, 
    month, 
    CASE 
        WHEN total_users > 0 
        THEN ROUND(CAST(retained_users AS DECIMAL) / total_users * 100, 2) 
        ELSE 0 
    END AS reservation_rate 
FROM reservation_counts 
ORDER BY year, month;</span></span></span></span>

Gemini Advanced：第一次尝试也失败了。它没有按照我的要求计算留存率users ordered in month X and X-1 / users ordered in month X-1，而是计算了users ordered in month X and X+1 / users ordered in month X。经过一次跟进后，Gemini 修正了代码并获得了 0.5 分。

<span style="color:rgba(0, 0, 0, 0.8)"><span style="background-color:#ffffff"><span style="background-color:#f9f9f9"><span style="color:#242424">--- Gemini 的初始代码
WITH monthly_users AS ( 
    SELECT DISTINCT 
        user_id, 
        DATE_TRUNC('month', order_time) AS order_month 
    FROM orders 
), 
retained_users AS ( 
    SELECT 
        m1.order_month, 
        COUNT(DISTINCT m1.user_id) AS total_users_previous_month, 
        COUNT(DISTINCT m2.user_id) AS retained_users 
    FROM monthly_users m1 
    LEFT JOIN monthly_users m2 
        ON m1.user_id = m2.user_id 
        AND DATE_TRUNC('month', m2.order_month) = DATE_TRUNC('month', DATEADD(month, 1, m1.order_month)) 
    GROUP BY m1.order_month 
) 
SELECT 
    order_month, 
    total_users_previous_month, 
    retained_users, 
    (retained_users::decimal / total_users_previous_month) * 100 AS 保留率
FROM 保留用户
ORDER BY 订单月;</span></span></span></span>

第二轮回顾

在这一轮中，Claude 3.5 Sonnet以 3.5 分（满分 4 分）和 0.5 分（加分）领先。ChatGPT -4o以 3 分（满分 4 分）和 0.5 分（加分）的成绩领先，在创建和管理合成数据集方面表现出色。Gemini Advanced以 3.5 分（满分 4 分）的成绩领先，并且是唯一无法处理直接 CSV 上传的模型。

第二轮成绩（图片由作者提供）

第三轮：查询优化

在最后一轮中，我专注于查询优化技巧。我使用了SQL 优化文章中效率低下的代码示例。这些查询基于上述相同的四个虚假数据集。

问题 8：仅选择必要的列

<span style="color:rgba(0, 0, 0, 0.8)"><span style="background-color:#ffffff"><span style="background-color:#f9f9f9"><span style="color:#242424">-- 获取每个用户的第一笔订单金额
WITH first_order AS ( 
  SELECT * 
  FROM orders 
  QUALIFY ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_time) = 1 
  
) 

SELECT 
  first_order.user_id, first_order.order_id, sum(price) AS first_order_amount 
FROM first_order 
JOIN ordered_products 
  ON first_order.order_id = ordered_products.order_id 
GROUP BY 1,2 
;</span></span></span></span>

我的期望：最好在窗口函数中仅选择必要的列，而不是SELECT *。

GPT-4o：它通过仅选择相关列并提供清晰的解释来优化查询。它还建议确保适当的索引以增强窗口函数的性能。满分。

Claude 3.5 Sonnet：Claude 不仅选择了相关列，还处理了COALESCE(SUM(price), 0) AS first_order_amount潜在的空值情况。可获得满分。

Gemini 高级版：Gemini 还通过选择相关列并提供索引建议来优化查询。可获满分。

<span style="color:rgba(0, 0, 0, 0.8)"><span style="background-color:#ffffff"><span style="background-color:#f9f9f9"><span style="color:#242424">--- 参考 Claude 的代码
WITH first_order AS ( 
  SELECT user_id, order_id, order_time 
  FROM orders 
  QUALIFY ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_time) = 1 
) 

SELECT 
  fo.user_id, 
  fo.order_id, 
  COALESCE(SUM(op.price), 0) AS first_order_amount 
FROM first_order fo 
LEFT JOIN ordered_products op ON fo.order_id = op.order_id 
GROUP BY fo.user_id, fo.order_id</span></span></span></span>

问题 9：预聚合

<span style="color:rgba(0, 0, 0, 0.8)"><span style="background-color:#ffffff"><span style="background-color:#f9f9f9"><span style="color:#242424">-- 我想要创建一个宽表，
-- 它保留用户表中的所有列，
-- 但还包含我们上面计算的附加列 first_order_amount。WITH 
first_order AS ( 
  SELECT users.*, orders.order_id 
  FROM users 
  LEFT JOIN orders 
    ON users.user_id = orders.user_id 
  QUALIFY ROW_NUMBER() OVER(PARTITION BY users.user_id ORDER BY order_time) = 1 
  
), 

SELECT first_order.*, sum(price) AS first_order_amount 
FROM first_order 
LEFT JOIN ordered_products 
  ON first_order.order_id = ordered_products.order_id 
GROUP BY ALL 
;</span></span></span></span>

我的期望：给定users一个具有许多列的宽表，最好first_order_amount先进行聚合计算，然后加入到users表中以获取所有列。

GPT-4o：最初，GPT-4o 仅更新了格式并users明确列出了表格中的列。在提示用户表格较宽后，它建议先进行预聚合，获得 0.5 分。

Claude 3.5 Sonnet：Claude 建议进行预聚合，并建议将大表连接移至最后一步进行优化，并给出了清晰的解释。可获得满分。

<span style="color:rgba(0, 0, 0, 0.8)"><span style="background-color:#ffffff"><span style="background-color:#f9f9f9"><span style="color:#242424">-Claude 的代码
WITH first_order AS（
  SELECT 
    u.user_id，
    o.order_id，
    o.order_time 
  FROM users u 
  LEFT JOIN orders o ON u.user_id = o.user_id 
  QUALIFY ROW_NUMBER() OVER(PARTITION BY u.user_id ORDER BY o.order_time) = 1 
），
first_order_amount AS（
  SELECT 
    fo.user_id，
    COALESCE(SUM(op.price), 0) AS first_order_amount 
  FROM first_order fo 
  LEFT JOIN ordered_products op ON fo.order_id = op.order_id 
  GROUP BY fo.user_id 
）

SELECT 
  u.*，
  COALESCE(foa.first_order_amount, 0) AS first_order_amount 
FROM users u 
LEFT JOIN first_order_amount foa ON u.user_id = foa.用户 ID</span></span></span></span>

Gemini 高级版：Gemini 也建议进行预聚合，但查询存在语法问题，并且它坚持认为查询是正确的……我非常困惑，所以我自己在 Snowflake 中测试了一个类似的查询（它抛出了Syntax error: unexpected ‘GROUP’），并询问了 ChatGPT 和 Claude。我 95% 确定您不能以这种方式在同一个 CTE 中使用 QUALIFY 和 GROUP BY……鉴于这是一个语法错误，Gemini 获得 0 分。但如果这在其他一些数据仓库中有效，请告诉我。

<span style="color:rgba(0, 0, 0, 0.8)"><span style="background-color:#ffffff"><span style="background-color:#f9f9f9"><span style="color:#242424">-- Gemini 的代码
WITH first_order_details AS ( 
    SELECT 
        o.user_id, 
        o.order_id, 
        SUM(op.price) AS first_order_amount 
    FROM orders o 
    LEFT JOIN ordered_products op ON o.order_id = op.order_id 
    QUALIFY ROW_NUMBER() OVER(PARTITION BY o.user_id ORDER BY o.order_time) = 1 
    GROUP BY o.user_id, o.order_id -- 在 CTE 中预聚合
) 

SELECT 
    u.*, -- 包括来自用户
    fod.first_order_amount 
FROM users u 的所有列
LEFT JOIN first_order_details fod ON u.user_id = fod.user_id;</span></span></span></span>

问题 10：重复数据删除

<span style="color:rgba(0, 0, 0, 0.8)"><span style="background-color:#ffffff"><span style="background-color:#f9f9f9"><span style="color:#242424">-- 我想要一个包含每个用户的第一个和最后一个订单 ID 的表
SELECT DISTINCT user_id, order_id, 'first_order' as order_label 
FROM orders 
QUALIFY ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_time) = 1 

UNION 

SELECT DISTINCT user_id, order_id, 'last_order' as order_label 
FROM orders 
QUALIFY ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_time DESC) = 1 
;</span></span></span></span>

我的期望：此查询使用 DISTINCT、UNION 和 ROW_NUMBER() 进行重复数据删除。这三个操作都很昂贵，而且它们在这里做同样的事情，所以我们应该消除重复数据删除的工作。

GPT-4o：它优化了代码，但改变了输出表结构。好吧，我想我没有明确说我需要相同的输出结构……澄清后，它提供了具有正确列的优化代码。我在这里给它 0.5 分。

<span style="color:rgba(0, 0, 0, 0.8)"><span style="background-color:#ffffff"><span style="background-color:#f9f9f9"><span style="color:#242424">使用 first_order AS（
  选择
    user_id，
    order_id 作为 first_order_id
  从
    订单中
  限定
    ROW_NUMBER（）OVER（按 user_id 划分顺序按 order_time 划分）= 1 
），

last_order AS（
  选择
    user_id，
    order_id 作为 last_order_id
  从
    订单中
  限定
    ROW_NUMBER（）OVER（按 user_id 划分顺序按 order_time 划分）= 1 
）

选择
  fo.user_id，
  fo.first_order_id，
  lo.last_order_id
从
  first_order fo 
JOIN 
  last_order lo 
ON 
  fo.user_id = lo.user_id；</span></span></span></span>

Claude 3.5 Sonnet：Claude 提供了一种解决方案，将两个窗口函数 CTE 合并为一个，并使用CASE WHEN+WHERE rn_first=1 OR rn_last=1获得具有相同结构的输出。老实说，我甚至没有想到这种方法。它再次获得满分。

<span style="color:rgba(0, 0, 0, 0.8)"><span style="background-color:#ffffff"><span style="background-color:#f9f9f9"><span style="color:#242424">--- Claude 的代码
WITH user_orders AS ( 
  SELECT 
    user_id, 
    order_id, 
    order_time, 
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_time ASC) AS rn_first, 
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_time DESC) AS rn_last 
  FROM orders 
) 
SELECT 
  user_id, 
  order_id, 
  CASE 
    WHEN rn_first = 1 THEN 'first_order' 
    ELSE 'last_order' 
  END AS order_label 
FROM user_orders 
WHERE rn_first = 1 OR rn_last = 1 
ORDER BY user_id, order_label</span></span></span></span>

Gemini 高级版：Gemini 还将两个窗口函数合二为一，然后使用 DISTINCT 获取第一个和最后一个订单 ID。这需要两次表扫描，并且 DISTINCT 可能很昂贵，因此在我看来，它的效果不如 Claude 的解决方案。我会给它 0.5 分。

<span style="color:rgba(0, 0, 0, 0.8)"><span style="background-color:#ffffff"><span style="background-color:#f9f9f9"><span style="color:#242424">
--- Gemini 的代码
WITH first_and_last_orders AS（
    SELECT 
        user_id, 
        order_id, 
        FIRST_VALUE(order_id) OVER (PARTITION BY user_id ORDER BY order_time) AS first_order_id, 
        LAST_VALUE(order_id) OVER (PARTITION BY user_id ORDER BY order_time) AS last_order_id 
    FROM orders 
）

SELECT DISTINCT 
    user_id, 
    first_order_id AS order_id, 
    'first_order' AS order_label 
FROM first_and_last_orders 

UNION ALL 

SELECT DISTINCT 
    user_id, 
    last_order_id AS order_id, 
    'last_order' AS order_label 
FROM first_and_last_orders;</span></span></span></span>

第三轮回顾🥁

在这一轮查询优化中，Claude 3.5 Sonnet明显胜出，他用创新的解决方案答对了所有三个问题。ChatGPT -4o在两个问题上需要指导，但最终全部答对，获得 2 分。Gemini Advanced有一个语法错误，并且代码优化程度较低，因此只获得 1.5 分。

第三轮成绩（图片由作者提供）

概括

最终成绩（图片由作者提供）

🥇 Claude 3.5 十四行诗(10 分)

• Claude 在 SQL 生成和优化方面表现最佳，最初只答错一道题，但经过澄清后很快就改正了。如果您正在寻找 AI 来协助处理 SQL 查询，我百分百推荐 Claude。
• 我也更喜欢它们的用户界面，因为我可以格式化文本输入以提高可读性。
• 我欣赏的另一个功能是他们的“项目”功能——您可以设置自定义说明并在项目级别共享知识，方便团队使用。
• 但是，如果您想与其共享真实数据集，其较低的文件上传限制可能是一个挑战。他们接受的实际文件大小似乎比他们声称的和 ChatGPT 允许的要低得多，这可能是由于需求量很大。希望 Claude 能尽快改进此功能。

克劳德项目（作者提供图片）

🥈 GPT-4o（8 分）

• GPT-4o 能够编写语法正确的 SQL。它在业务逻辑方面表现出色，但在查询优化方面落后。
• GPT-4o 的一个显著优势是能够加载数据集并直接在 UI 中与其交互，从而帮助理解和探索数据——你可以在我的文章中阅读有关此功能的更多信息。
• 它生成带有下载链接的合成数据集的能力也非常令人印象深刻。

🥉双子座高级版（7 分）

• 与 Claude 和 ChatGPT 相比，Gemini 在这三个类别中的表现都较差，但它仍然能够正确回答 70% 的问题。
• 能够搜索网站并提供参考链接可能是其竞争优势。但是，根据我上述的经验，这些链接有时不相关或与响应不匹配，从而损害了可信度。
• Gemini 的另一个优点是它与其他 Google Suite 产品集成。例如，您可以在 Google Spreadsheet 中打开它生成的数据集。

感谢关注雲闪世界。（Aws解决方案架构师vs开发人员&GCP解决方案架构师vs开发人员）

 订阅频道(https://t.me/awsgoogvps_Host)
 TG交流群(t.me/awsgoogvpsHost)