解锁ChatGPT新玩法，挑战阿里云瑶池数据库SQL挑战赛

原创 SQLplusDB 2023-06-18

1730

解锁ChatGPT新玩法，挑战阿里云瑶池数据库SQL挑战赛

执行结果截图

赛题 1：找出各项考试中的佼佼者
赛题 2：
赛题 3：计算三角形面积

开通RDS MySQL Serverless 免费试用资源
赛题 1：找出各项考试中的佼佼者

Prompt指令1
SQL输出结果1
Prompt指令2（纠正不足）
SQL输出结果2（纠正后）
SQL执行验证

赛题 2：游戏游玩情况

Prompt指令1
SQL输出结果1
Prompt指令2（纠正不足）
SQL输出结果2（纠正后）
Prompt指令3（纠正不足）
SQL输出结果3（纠正后）
SQL执行验证

赛题 3：计算三角形面积

Prompt指令1
SQL输出结果1
Prompt指令2
输出结果2
Prompt指令3
输出结果3

解锁ChatGPT新玩法，挑战阿里云瑶池数据库SQL挑战赛

刚刚看到墨天轮社区联合阿里云瑶池数据库举办了SQL 挑战赛活动，下面我们就通过ChatGPT等大语言模型来挑战一下。

阿里云瑶池数据库SQL挑战赛来袭！Beats耳机等好礼等你赢！
https://www.modb.pro/db/636449

执行结果截图

首先先提交一下执行结果截图(貌似有些错误，且来看一看)。

赛题 1：找出各项考试中的佼佼者

SQL语句

SELECT t.name AS '考试名', s.name AS '学生姓名', ta.score AS '分数'
FROM TestAttempt ta
JOIN Test t ON ta.testId = t.id
JOIN Student s ON ta.studentId = s.id
WHERE (
    SELECT COUNT(DISTINCT studentId) 
    FROM TestAttempt 
    WHERE testId = ta.testId AND score > ta.score
) < 3
AND (
    SELECT MAX(score) 
    FROM TestAttempt 
    WHERE studentId = ta.studentId AND testId = ta.testId
) = ta.score
ORDER BY t.name, ta.score DESC;

在这里插入图片描述

赛题 2：

SELECT ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(event_date, first_game_played) <= 7 and  DATEDIFF(event_date, first_game_played) >0 THEN first_game.player_id END)/COUNT(DISTINCT Activity.player_id) , 2) AS retention_rate
FROM (
  SELECT player_id, MIN(event_date) AS first_game_played FROM Activity
  GROUP BY player_id
) AS first_game
JOIN Activity ON first_game.player_id = Activity.player_id

在这里插入图片描述

SELECT ROUND(SUM(CASE WHEN login_count >= 2 THEN 1 ELSE 0 END)/COUNT(DISTINCT player_id), 2) AS player_ratio
FROM (
    SELECT player_id, COUNT(DISTINCT event_date) AS login_count
    FROM Activity
    WHERE event_date <= DATE_ADD((SELECT MIN(event_date) FROM Activity AS A WHERE A.player_id = Activity.player_id), INTERVAL 7 DAY)
    GROUP BY player_id
) AS login_counts;

在这里插入图片描述

赛题 3：计算三角形面积

SELECT t.id, ROUND(0.5 * SQRT(POW((p2.x-p1.x)*(p3.y-p1.y)-(p3.x-p1.x)*POW((p2.y-p1.y), 2), 2) + POW((p2.y-p1.y)*POW((p3.z-p1.z), 2)-(p3.y-p1.y)*POW((p2.z-p1.z), 2), 2) + POW((p2.z-p1.z)*POW((p3.x-p1.x), 2)-(p3.z-p1.z)*POW((p2.x-p1.x), 2), 2)), 2) AS area
FROM Triangle t
JOIN Point p1 ON t.pointId1 = p1.id
JOIN Point p2 ON t.pointId2 = p2.id
JOIN Point p3 ON t.pointId3 = p3.id;

在这里插入图片描述

开通RDS MySQL Serverless 免费试用资源

阿里云试用资源开通地址：
https://free.aliyun.com/?pipCode=mysql&utm_content=m_1000372358
在这里插入图片描述
试用教程可以参考如下内容：
快速使用RDS MySQL Serverless实例

赛题 1：找出各项考试中的佼佼者

下面通过ChatGPT帮助我们编写SQL

Prompt指令1

你是一名MySQL数据库专家，请根据如下要求写SQL：
------------------------
表: Student的定义如下：

Column Name	Type
id	int
name	varchar
Id是该表的主键列。
该表的每一行都表示学生的ID和学生姓名。

表: Test的定义如下：
Column Name	Type
id	int
name	varchar
Id是该表的主键列。
该表的每一行表示考试ID和考试名。


表: TestAttempt的定义如下：

Column Name | Type
---|---
id | int
studentId | int
testId | int
score | int

Id是该表的主键列。
studentId是学生ID，
testId是考试的id，
score表示分数。
studentId和testId分别是Student表和Test表中id的外键。
该表的每一行表示一次考试记录，并包含了学生和考试的id。

一个学生可以参加任意考试，不限次数。
现在我们关注的是每门考试有哪些顶尖的学生。一门考试的 “顶尖学生” 是指一个学生的分数在参加该考试的 “不同” 学生中 得分排名前三 。
编写一个SQL查询，找出每个考试中“得分最高” 的的考生。
若同一个考生有多条考试记录，则取最高分。
如果存在并列，将并列的考生都列举出来，直到列举的考生达到或超过三人。
以每门考试考试分数从高到低的顺序 返回结果表(考试名，学生姓名，分数)。
------------------------

在这里插入图片描述

SQL输出结果1

以下是SQL查询语句：

SELECT t.name AS '考试名', s.name AS '学生姓名', ta.score AS '分数'
FROM TestAttempt ta
JOIN Test t ON ta.testId = t.id
JOIN Student s ON ta.studentId = s.id
WHERE (
    SELECT COUNT(DISTINCT score) 
    FROM TestAttempt 
    WHERE testId = ta.testId AND score > ta.score
) < 3
AND (
    SELECT MAX(score) 
    FROM TestAttempt 
    WHERE studentId = ta.studentId AND testId = ta.testId
) = ta.score
ORDER BY t.name, ta.score DESC;

解释：

首先使用JOIN将三个表连接起来，以便查询考试名、学生姓名和分数。
使用子查询来计算每个学生在该门考试中的排名，如果排名小于3，则该学生是顶尖学生之一。
使用子查询来找出每个学生在该门考试中的最高分数，如果该分数等于该考试记录的分数，则该学生是该门考试的得分最高者之一。
最后按照考试名和分数降序排序，以便得到每门考试的得分最高者和顶尖学生。

Prompt指令2（纠正不足）

我们可以看到对于SQL中【2. 使用子查询来计算每个学生在该门考试中的排名，如果排名小于3，则该学生是顶尖学生之一。】的内容而言，用于判断的条件是【 “ SELECT COUNT(DISTINCT score) …WHERE score > ta.score”的不同分数个数< 3】，事实上由于同一个考生有多条考试记录的情况，所以用不同学生ID的个数判断更好。所以我们再对ChatGPT进行指示修改。

Prompt指令

由于同一个考生有多条考试记录的情况，所以应该用不同学生ID的个数来判断排名前三，请修改上面的SQL。

在这里插入图片描述

SQL输出结果2（纠正后）

感谢指出问题，以下是修改后的SQL查询语句：

SELECT t.name AS '考试名', s.name AS '学生姓名', ta.score AS '分数'
FROM TestAttempt ta
JOIN Test t ON ta.testId = t.id
JOIN Student s ON ta.studentId = s.id
WHERE (
    SELECT COUNT(DISTINCT studentId) 
    FROM TestAttempt 
    WHERE testId = ta.testId AND score > ta.score
) < 3
AND (
    SELECT MAX(score) 
    FROM TestAttempt 
    WHERE studentId = ta.studentId AND testId = ta.testId
) = ta.score
ORDER BY t.name, ta.score DESC;

解释：

使用JOIN将三个表连接起来，以便查询考试名、学生姓名和分数。
使用子查询来计算每个学生在该门考试中的排名，如果排名小于3，则该学生是顶尖学生之一。
使用子查询来找出每个学生在该门考试中的最高分数，如果该分数等于该考试记录的分数，则该学生是该门考试的得分最高者之一。
最后按照考试名和分数降序排序，以便得到每门考试的得分最高者和顶尖学生。

SQL执行验证

通过mysql客户端验证执行结果，我们可以看到结果和预期的相同。
不得不说ChatGPT确实很强大。

mysql> SELECT t.name AS '考试名', s.name AS '学生姓名', ta.score AS '分数'
    -> FROM TestAttempt ta
    -> JOIN Test t ON ta.testId = t.id
    -> JOIN Student s ON ta.studentId = s.id
    -> WHERE (
    ->     SELECT COUNT(DISTINCT studentId)
    ->     FROM TestAttempt
    ->     WHERE testId = ta.testId AND score > ta.score
    -> ) < 3
    -> AND (
    ->     SELECT MAX(score)
    ->     FROM TestAttempt
    ->     WHERE studentId = ta.studentId AND testId = ta.testId
    -> ) = ta.score
    -> ORDER BY t.name, ta.score DESC
    -> \G
*************************** 1. row ***************************
  考试名: GRE
学生姓名: Joe
    分数: 330
*************************** 2. row ***************************
  考试名: GRE
学生姓名: Sam
    分数: 319
*************************** 3. row ***************************
  考试名: GRE
学生姓名: Max
    分数: 300
*************************** 4. row ***************************
  考试名: GRE
学生姓名: Will
    分数: 300
*************************** 5. row ***************************
  考试名: TOEFL
学生姓名: Janet
    分数: 112
*************************** 6. row ***************************
  考试名: TOEFL
学生姓名: Randy
    分数: 100
*************************** 7. row ***************************
  考试名: TOEFL
学生姓名: Joe
    分数: 98
7 rows in set (0.00 sec)

mysql>

Test	Student	Score
GRE	Joe	330
GRE	Sam	319
GRE	Max	300
GRE	Will	300
TOEFL	Henry	112
TOEFL	Randy	100
TOEFL	Joe	98

赛题 2：游戏游玩情况

Prompt指令1

你是一名MySQL数据库专家，请根据如下要求写SQL：
------------------------
Table: Activity
Column Name	Type
player_id	int
device_id	int
event_date	date
games_played	int

（player_id，event_date）是此表的主键。

这张表显示了某些的玩家的活动情况。
每一行是一个玩家在指定日期的游玩记录，包含了设备信息，以及总共玩了多少款游戏。

编写一个 SQL 查询，报告在首次游玩后的一周内至少再有一次游玩的玩家的比例，也就是注册首周内至少有两次登录的玩家占总玩家的比例，四舍五入到小数点后两位（无需百分数表示）。
---------

在这里插入图片描述

SQL输出结果1

可以使用子查询和聚合函数来实现此查询：

SELECT ROUND(COUNT(DISTINCT player_id)/(SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS player_ratio
FROM Activity
WHERE event_date <= DATE_ADD((SELECT MIN(event_date) FROM Activity), INTERVAL 7 DAY)
GROUP BY player_id
HAVING COUNT(DISTINCT event_date) >= 2;

解释一下这个查询的思路：

子查询 (SELECT MIN(event_date) FROM Activity) 找到了所有玩家的首次游玩日期，然后使用 DATE_ADD 函数加上 7 天，得到了首周结束日期。
在 WHERE 子句中，筛选出所有在首周内游玩过的记录。
使用 GROUP BY 子句按照 player_id 分组，然后使用 HAVING 子句筛选出至少有两次游玩记录的玩家。
最后使用 COUNT 函数和 DISTINCT 关键字计算出符合条件的玩家数量，除以总玩家数量，得到比例，并使用 ROUND 函数四舍五入到小数点后两位。

注意，这个查询中使用了 DISTINCT 关键字，因为一个玩家可能在首周内玩了多次游戏，但我们只需要计算他们为一位玩家。

Prompt指令2（纠正不足）

我们看到SQL中【WHERE event_date <= DATE_ADD((SELECT MIN(event_date) FROM Activity), INTERVAL 7 DAY)】表示和所有玩家的首次游玩日期的比较看是否是一个星期内再次玩过，事实上各个玩家应该和自己首次游玩日期进行比较。

Prompt指令

筛选至少有两次登录的玩家时，不应该以所有玩家的首次游玩日期为条件，而是各个玩家的首次游玩日期。

在这里插入图片描述

SQL输出结果2（纠正后）

非常感谢您的指正，您是正确的。在筛选至少有两次登录的玩家时，应该以各个玩家的首次游玩日期为条件，而不是所有玩家的首次游玩日期。以下是修改后的SQL查询语句：

SELECT ROUND(COUNT(DISTINCT player_id)/(SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS player_ratio
FROM Activity
WHERE event_date <= DATE_ADD((SELECT MIN(event_date) FROM Activity AS A WHERE A.player_id = Activity.player_id), INTERVAL 7 DAY)
GROUP BY player_id
HAVING COUNT(DISTINCT event_date) >= 2;

在这个查询语句中，我们使用了子查询来找到每个玩家的首次游玩日期。具体来说，我们在WHERE子句中使用了子查询来限制游玩日期在首次游玩日期后一周内，并且在子查询中使用了WHERE子句来限制只考虑当前玩家的首次游玩日期。这样就可以确保我们筛选出的是每个玩家在其自己的首次游玩日期后一周内的游玩记录。

感谢您的指正，希望这个修改后的查询语句能够更好地解决问题。

Prompt指令3（纠正不足）

上面的SQL执行结果由于Group By分组会导致结果输出多行。进一步修正。

Prompt指令

上面的SQL会输出多行结果，我们应该汇总成一个值。

在这里插入图片描述

SQL输出结果3（纠正后）

非常感谢您的指正，您是正确的。上面的SQL查询语句会输出多行结果，我们需要将这些结果汇总成一个值。以下是修改后的SQL查询语句：

SELECT ROUND(SUM(CASE WHEN login_count >= 2 THEN 1 ELSE 0 END)/COUNT(DISTINCT player_id), 2) AS player_ratio
FROM (
    SELECT player_id, COUNT(DISTINCT event_date) AS login_count
    FROM Activity
    WHERE event_date <= DATE_ADD((SELECT MIN(event_date) FROM Activity AS A WHERE A.player_id = Activity.player_id), INTERVAL 7 DAY)
    GROUP BY player_id
) AS login_counts;

在这个查询语句中，我们使用了一个子查询来计算每个玩家在其自己的首次游玩日期后一周内的游玩次数，并将结果按照玩家ID分组。然后，我们在外部查询中使用SUM函数和CASE表达式来计算至少有两次登录的玩家数量，并将其除以总玩家数量，得到所需的比例。最后，我们使用ROUND函数将结果四舍五入到小数点后两位。

感谢您的指正，希望这个修改后的查询语句能够更好地解决问题。

SQL执行验证

这次貌似靠谱一些，通过mysql客户端验证执行结果。

mysql> SELECT ROUND(SUM(CASE WHEN login_count >= 2 THEN 1 ELSE 0 END)/COUNT(DISTINCT player_id), 2) AS player_ratio
    -> FROM (
    ->     SELECT player_id, COUNT(DISTINCT event_date) AS login_count
    ->     FROM Activity
    ->     WHERE event_date <= DATE_ADD((SELECT MIN(event_date) FROM Activity AS A WHERE A.player_id = Activity.player_id), INTERVAL 7 DAY)
    ->     GROUP BY player_id
    -> ) AS login_counts;
+--------------+
| player_ratio |
+--------------+
|         0.33 |
+--------------+
1 row in set (0.00 sec)

mysql>

赛题 3：计算三角形面积

Prompt指令1

根据例子我们可以看到，要计算三角新的面积，所以我们先了解一下如何计算三角新的面积。

Prompt指令：

在SQL中如何计算三角形的面积？

在这里插入图片描述

Prompt指令：

如何根据三维空间坐标，计算出三角形的面积

在这里插入图片描述

SQL输出结果1

在SQL中计算三角形的面积需要知道三角形的底和高。假设我们有一个三角形，其底为b，高为h，则其面积为：

面积 = 1/2 * b * h

在SQL中，可以使用以下语句计算三角形的面积：

SELECT 0.5 * base * height AS area FROM triangle;

其中，triangle是包含三角形底和高的表，base和height是表中的列名。

在三维空间中，计算三角形的面积需要使用向量叉积的方法。假设我们有三个点A(x1, y1, z1)，B(x2, y2, z2)，C(x3, y3, z3)，则三角形ABC的面积为：

面积 = 1/2 * |AB × AC|

其中，|AB × AC|表示向量AB和向量AC的叉积的模长。

在SQL中，可以使用以下语句计算三角形的面积：

SELECT 0.5 * SQRT(POW((x2-x1)(y3-y1)-(x3-x1)(y2-y1), 2) + POW((y2-y1)(z3-z1)-(y3-y1)(z2-z1), 2) + POW((z2-z1)(x3-x1)-(z3-z1)(x2-x1), 2)) AS area FROM triangle;

其中，triangle是包含三个点坐标的表，x1、y1、z1、x2、y2、z2、x3、y3、z3是表中的列名。

Prompt指令2

你是一名MySQL数据库专家，请根据如下要求写SQL：
------------------------
表: Triangle

Column Name	Type
id	int
pointId1	int
pointId2	int
pointId3	int
id是该表的主键列。
该表的每一行包含三角形三个顶点的信息，pointId1、pointId2、pointId3均指向Point表的id字段。

表：Point

Column Name	Type
id	int
x	double
y	double
z	double
id是该表的主键列。

该表的每一行包含一个点的三维空间坐标。
写一个SQL查询，计算每个三角形的面积，保留两位小数。
------------------------

在这里插入图片描述

输出结果2

可以使用以下SQL查询计算每个三角形的面积：

SELECT t.id, ROUND(0.5 * SQRT(POW((p2.x-p1.x)(p3.y-p1.y)-(p3.x-p1.x)(p2.y-p1.y), 2) + POW((p2.y-p1.y)(p3.z-p1.z)-(p3.y-p1.y)(p2.z-p1.z), 2) + POW((p2.z-p1.z)(p3.x-p1.x)-(p3.z-p1.z)(p2.x-p1.x), 2)), 2) AS area
FROM Triangle t
JOIN Point p1 ON t.pointId1 = p1.id
JOIN Point p2 ON t.pointId2 = p2.id
JOIN Point p3 ON t.pointId3 = p3.id;

其中，使用JOIN将Triangle表和Point表连接起来，通过三个点的坐标计算三角形的面积。ROUND函数用于保留两位小数。

Prompt指令3

对于三维空间坐标计算三角形的面积，小编确实知识匮乏，所以相信ChatGPT，尝试执行一下。
发现如下错误。

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(p3.y-p1.y)-(p3.x-p1.x)(p2.y-p1.y), 2) + POW((p2.y-p1.y)(p3.z-p1.z)-(p3.y-p1.y)(' at line 1

为了解决错误，直接将错误抛给ChatGPT。

Prompt指令：

Error Code: 1064. You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use
 near '(p3.y-p1.y)-(p3.x-p1.x)(p2.y-p1.y), 2) + POW((p2.y-p1.y)(p3.z-p1.z)-(p3.y-p1.y)(' at line 1

在这里插入图片描述

输出结果3

这个错误提示是因为SQL语句中的乘号(*)被误认为是语法错误，需要使用正确的语法来表示乘法运算。

在SQL中，可以使用以下两种方式来表示乘法运算：

使用星号(*)表示乘法运算，但需要注意星号两侧不能有空格，否则会被解析为语法错误。
使用函数来表示乘法运算，例如使用函数POW(x, y)来计算x的y次方。

因此，在SQL查询中，可以将乘号(*)替换为函数POW(x, y)来表示乘法运算，例如：

SELECT t.id, ROUND(0.5 * SQRT(POW((p2.x-p1.x)*(p3.y-p1.y)-(p3.x-p1.x)*POW((p2.y-p1.y), 2), 2) + POW((p2.y-p1.y)*POW((p3.z-p1.z), 2)-(p3.y-p1.y)*POW((p2.z-p1.z), 2), 2) + POW((p2.z-p1.z)*POW((p3.x-p1.x), 2)-(p3.z-p1.z)*POW((p2.x-p1.x), 2), 2)), 2) AS area
FROM Triangle t
JOIN Point p1 ON t.pointId1 = p1.id
JOIN Point p2 ON t.pointId2 = p2.id
JOIN Point p3 ON t.pointId3 = p3.id;

这样就可以避免乘号(*)被误认为是语法错误的问题。

阿里云sql挑战赛

最后修改时间：2023-06-19 22:09:10

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

解锁ChatGPT新玩法，挑战阿里云瑶池数据库SQL挑战赛

Table of Contents

解锁ChatGPT新玩法，挑战阿里云瑶池数据库SQL挑战赛

执行结果截图

赛题 1：找出各项考试中的佼佼者

赛题 2：

赛题 3：计算三角形面积

开通RDS MySQL Serverless 免费试用资源

赛题 1：找出各项考试中的佼佼者

Prompt指令1

SQL输出结果1

Prompt指令2（纠正不足）

SQL输出结果2（纠正后）

SQL执行验证

赛题 2：游戏游玩情况

Prompt指令1

SQL输出结果1

Prompt指令2（纠正不足）

SQL输出结果2（纠正后）

Prompt指令3（纠正不足）

SQL输出结果3（纠正后）

SQL执行验证

赛题 3：计算三角形面积

Prompt指令1

SQL输出结果1

Prompt指令2

输出结果2

Prompt指令3

输出结果3

评论