SQL 挑战赛
本次墨天轮社区的 SQL 挑战赛已接近尾声（before 6/18），趁着活动尚未结束抓紧时间上车。
总共三道题目，需要先前往阿里云领取资源 – 云数据库 RDS MySQL Serverless
这里我选择的是 阿里云 RDS MySQL Serverless (MySQL 8)
导入数据集
在墨天轮文档下载数据集。
并导入阿里云 RDS，这里 DMS 提供了数据文件导入功能，可快速导入 csv 格式的数据文件。
赛题 1： 找出各项考试中的佼佼者
题
一个学生可以参加任意考试，不限次数。
现在我们关注的是每门考试有哪些顶尖的学生。一门考试的 顶尖学生 是指一个学生的分数在参加该考试的 不同 学生中 得分排名前三 。
编写一个SQL查询，找出每个考试中 得分最高的的考生 。
若同一个考生有多条考试记录，则取最高分。
如果存在并列，将并列的考生都列举出来，直到列举的考生达到或超过三人。
以 每门考试考试分数从高到低的顺序 返回结果表
解
使用窗口函数 ROW_NUMBER() 来为每个学生的得分排名，使用 PARTITION BY 将学生的得分排名限制为每个考试内部，然后使用 ORDER BY 按照得分从高到低进行排序。
使用 COUNT(*) OVER 窗口函数，用于计算每个考试中每个得分的出现次数。
使用 MAX(tt.score) OVER 窗口函数，用于获取每个考生在该考试中的最高分数。
如果使用 DENSE_RANK 则会出现75条结果，不符合预期，如下结果：
| Test 1 | Student 32 | 400 |
| Test 1 | Student 80 | 400 |
| Test 1 | Student 19 | 400 |
| Test 1 | Student 24 | 400 |
| Test 1 | Student 36 | 400 |
| Test 1 | Student 61 | 400 |
| Test 1 | Student 3 | 400 |
| Test 1 | Student 8 | 399 |
| Test 1 | Student 29 | 398 |
| Test 1 | Student 26 | 398 |
题目要求：
如果存在并列，将并列的考生都列举出来，直到列举的考生达到或超过三人。
所以 75 条记录的结果是错误的，
这里应该使用 RANK，得到的结果集为 44 条记录。
答
- 方案一（118ms）
SELECT t.name AS testName, t2.StuName, t2.score
FROM (
SELECT t1.testId, t1.StuName, t1.score, t1.rn, t1.cnt
, t1.max_score, RANK() OVER (PARTITION BY t1.testId ORDER BY t1.score DESC) AS rank_num
FROM (
SELECT tt.testId, tt.studentId, tt.score, s.name AS StuName
, ROW_NUMBER() OVER (PARTITION BY tt.testId ORDER BY tt.score DESC) AS rn
, COUNT(*) OVER (PARTITION BY tt.studentId, tt.score ) AS cnt
, MAX(tt.score) OVER (PARTITION BY tt.studentId, tt.testId ) AS max_score
FROM TestAttempt tt
JOIN Student s ON tt.studentId = s.id
) t1
WHERE t1.score = t1.max_score
ORDER BY t1.testId, t1.score DESC
) t2
JOIN Test t ON t2.testId = t.id
WHERE t2.rank_num <= 3;
- 方案二（71ms）
WITH t1 AS (
SELECT tt.testId, tt.studentId, tt.score
, MAX(tt.score) OVER (PARTITION BY tt.studentId, tt.testId ) AS max_score
, RANK() OVER (PARTITION BY tt.testId ORDER BY tt.score DESC) AS rank_num
FROM TestAttempt tt
),
t2 AS (
SELECT t1.testId, t1.studentId, t1.score
FROM t1
WHERE t1.score = t1.max_score
AND t1.rank_num <= 3
)
SELECT t.name AS testName, s.name AS StuName, t2.score
FROM t2
JOIN Test t ON t2.testId = t.id
JOIN Student s ON t2.studentId = s.id
;
截图
-
方案一（118ms）
-
方案二（71ms）
赛题 2： 游戏游玩情况
题
编写一个 SQL 查询，报告在首次游玩后的一周内至少再有一次游玩的玩家的比例，也就是注册首周内至少有两次登录的玩家占总玩家的比例，四舍五入到小数点后两位。
解
这张表 Activity 包含四个字段，每一行是一个玩家 (player_id) 在指定日期 (event_date) 的游玩记录，包含了设备信息 (device_id)，以及总共玩了多少款游戏 (games_played)。这套题目的关键在于如何利用 (player_id, event_date) 这两个字段计算出题目所需的结果。
首次游玩后的一周内至少再有一次游玩的玩家
需要先找到有哪些玩家在注册首周内再次登录，假设注册日期为 first_played_date ，则在 [first_played_date, first_played_date + 6 day] 的区间内还有游玩记录。这里用到了窗口函数 over (partition by .. order by .. frame) 的高级语法。具体参考 Window Function Frame Specification
按 player_id 分组，确认玩家首次游玩时间，即 event_date 的最小值。
按玩家分组，按游玩时间排序，并标记序号，每位玩家只取前两天游玩记录。
依据此中间结果，计算每位玩家的第二条记录的日期是否在首次游玩日期的一周内。
依据上面的统计结果，即可计算出题目要求的玩家比例。
答
- 方法一
WITH t AS (
SELECT player_id, event_date,
MIN(event_date) OVER (PARTITION BY player_id ) AS first_played_date,
ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS rn
FROM Activity
),
t2 AS (
SELECT t.player_id, t.event_date, t.first_played_date, t.rn,
COUNT(t.rn) OVER (PARTITION BY t.first_played_date ORDER BY t.event_date) AS cnt
FROM t
WHERE t.rn <= 2
AND t.event_date BETWEEN t.first_played_date AND DATE_ADD(t.first_played_date, INTERVAL 6 DAY)
AND t.event_date != t.first_played_date
)
SELECT ROUND(COUNT(t2.player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS pct
FROM t2
WHERE t2.cnt > 0
;
- 方法二：
SELECT ROUND(COUNT(t.player_id) / (
SELECT COUNT(DISTINCT player_id)
FROM Activity
), 2) AS pct
FROM (
SELECT player_id, event_date, MIN(event_date) OVER (PARTITION BY player_id ) AS 1st_date
, nth_value(event_date, 2) OVER (PARTITION BY player_id ORDER BY event_date) AS 2nd_date
, ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS rn
FROM Activity
) t
WHERE t.2nd_date IS NOT NULL
AND t.rn = 2
AND datediff(2nd_date, 1st_date) < 7;
共计14个玩家，在首次游玩后的一周内至少再有一次游玩的记录，百分比为 0.01 。
截图
-
方法一（80ms）
-
方法二（116ms）
RDS MySQL bug?
在测试过程中，因为用到了高级语法 RANGE BETWEEN CURRENT ROW AND INTERVAL 6 DAY FOLLOWING 导致 RDS 遇到了展示不出结果的情况，并且返回结果：【影响行数为-1】，在本地 MySQL 8.0.28 环境中，这个 SQL 是没有问题的。
WITH t AS (
SELECT player_id, event_date,
MIN(event_date) OVER (PARTITION BY player_id ) AS first_played_date,
ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS rn
FROM Activity
),
t2 AS (
SELECT t.player_id, t.event_date, t.first_played_date, t.rn,
COUNT(t.rn) OVER (PARTITION BY t.first_played_date ORDER BY t.event_date RANGE BETWEEN CURRENT ROW AND INTERVAL 6 DAY FOLLOWING) AS cnt
FROM t
WHERE t.rn <= 2
)
SELECT ROUND(COUNT(t2.player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS pct
FROM t2
WHERE t2.cnt > 0
;
赛题 3： 计算三角形面积
题
写一个SQL查询，计算每个三角形的面积，保留两位小数。
解
空间三角形的面积解法较多，比如用两条边的长度和夹角进行计算，或者海伦公式，或者Bretschneider公式，或者高斯斯特林公式。
这里用到的是向量计算法。
假设三角形的三个顶点坐标分别为 A(x1, y1, z1)、B(x2, y2, z2) 和 C(x3, y3, z3)。则该三角形的面积为：
经过初步验算，得到如下公式。
答
SELECT
t.id, ROUND(0.5 * SQRT(pow(a, 2) + pow(b, 2) + pow(c, 2)), 2) AS area
FROM (
SELECT triangle.id
, (p2.y - p1.y) * (p3.z - p1.z) - (p3.y - p1.y) * (p2.z - p1.z) AS a
, (p2.z - p1.z) * (p3.x - p1.x) - (p2.x - p1.x) * (p3.z - p1.z) AS b
, (p2.x - p1.x) * (p3.y - p1.y) - (p3.x - p1.x) * (p2.y - p1.y) AS c
FROM triangle
JOIN point p1 ON pointid1 = p1.id
JOIN point p2 ON pointid2 = p2.id
JOIN point p3 ON pointid3 = p3.id
) t
ORDER BY t.id;
这里用到了 MySQL 中的数学函数 Mathematical Functions：
SQRT: 返回非负数X的平方根。如果X为NULL，则该函数返回NULL。
POW: 返回X的Y次方的值，如果X或Y为NULL则返回NULL。
截图
总结
三道题目各有侧重，题目都很有趣，对于不常写 SQL 的人来说很有难度，对于 MySQL 或者 AliSQL 不熟的人来说，也有一定的挑战。
