欢迎来到雲闪世界测试哪个 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
和MaxMovie
CTE 是多余的。请参阅下面 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.movie_id = m.movie_id
WHERE
mr.created_at BETWEEN '2020-02-01' AND '2020-02-29'
GROUP BY
m.movie_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.movie_id) DESC, name
LIMIT 1)
UNION ALL
(SELECT title AS results
FROM Movies m
JOIN MovieRating mr ON m.movie_id = mr.movie_id
WHERE EXTRACT(YEAR_MONTH FROM mr.created_at) = 202002
GROUP BY m.movie_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_results
movie_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.movie_id = m.movie_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:它给出了错误的答案。它的PreviousMonthUsers
CTE 和RetainedUsers
CTE 做同样的事情……我指出了它的错误两次,才纠正了它。这里 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)