2023瑶池大赛

2023-06-15 138

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL，集群系列 2核4GB

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

Redis 开源版，标准版 2GB

简介： 瑶池SQL大赛

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

解题思路

1、分数去重

一个学生可以参加任意考试，不限次数。

若同一个考生有多条考试记录，则取最高分。

根据上述题意，在分数 testattempt 表中按照考试类型获取每个学生的最高分数，以此来保证每个学生在每门考试中只存在一个最高分。由此，我们得到第一个子表 t 。

selectMAX(`score`) asscore,
`studentId`,
`testId`from`testattempt`GROUPBY`studentId`,
`testId`

2、进行分类排序

找出每个考试中得分最高的的考生。

如果存在并列，将并列的考生都列举出来，直到列举的考生达到或超过三人。

先对第一步表中的数据按照考试类型将分数从高到低排序，因此使用 order by t.testid,t.score desc ；
根据同考试类型对分数进行排名，也就是相同的分数也排名相同。这里有两种方法，一种是我们可借助变量赋值的方式进行排名处理，另一种是借助于窗口函数 DENSE_RANK() 来处理。（代码参考后续截图代码）

3、根据排名取出前三

基于第二步的排名通过判断排名小于等于3来筛选出前三的学生,为方便显示，可再进行一次分数排序

方法一

SELECTtest.`name`astest,
student.`name`asstudent,
rankresult.scoreasscoreFROM  (
SELECTt.*,
-- 排名代码，如果考试 testid 与上一个不同，那么排名从1开始
@rank :=IF(
@prev_test=`testid`,
-- 如果分数 score 与上一个相同则沿用上次的排名，否则排名+1CASEWHEN@score=t.scoreTHEN@rankWHEN@score :=t.scoreTHEN@rank :=@rank+1END,
1      ) AS`rank`,
@score :=t.score,
@prev_test :=`testid`FROM      (
selectMAX(`score`) asscore,
`studentId`,
`testId`from`testattempt`GROUPBY`studentId`,
`testId`      ) astORDERBYt.`testid`,
t.`score`DESC  ) asrankresultJOIN`test`onrankresult.testid=test.`id`join`student`onrankresult.studentid=`student`.`id`whererankresult.rank<=3orderbyrankresult.testid,
rankresult.scoredesc

方法二

SELECTtest.`name`astest,
student.`name`asstudent,
rankresult.scoreasscoreFROM  (
SELECTt.*,
-- 此处使用 dense_rank() 进行不跳号排序
      (DENSE_RANK() over ( PARTITIONby`testId`ORDERBYt.scoreDESC )) ASmyrankFROM      (
selectMAX(`score`) asscore,
`studentId`,
`testId`from`testattempt`GROUPBY`studentId`,
`testId`      ) ast  ) asrankresultJOIN`test`onrankresult.testid=test.`id`join`student`onrankresult.studentid=`student`.`id`whererankresult.myrank<=3orderbyrankresult.testid,
rankresult.scoredesc

赛题二：游戏游玩情况

解题思路

1、计算总用户数

因用户可在不同日期登录，所以在数据表中存在多条记录因此需要按照 player_id 做去重后在计算用户数

SELECTCOUNT(DISTINCT (`player_id`)) asuser_countfrom`activity`

2、获取用户首次登录后7天的日期范围

按照用户 player_id 做分组，取最小的登录日期，使用 date_add 计算该日期7天内的日期，或者通过使用datediff结合变量赋值计算出距离第一次登陆时间时间差，从而得到中间表（方法一的 t2 ，方法二的 t1）。

PS：因为本题中给定的时间是日期格式，所以如果需求不算登录当天的话，date_add 的数值应该为 6。比如：5月24日的7天内的截止日期为 5月30日，而不是 5月31日。本题结果使用7做计算。

3、获取首次登录7天后登录的用户数量

使用 activity 表与步骤2中的 t2 做 join 查询，设置activity 表中的登录时间在 t2 的区间范围内，利用 group by player_id having count(1) >1,由此得到满足条件的用户。再对结果集进行 count 即可得到首次登录后7天后的登录用户数量

4、计算百分比并四舍五入保留两位小数

使用 round() 函数对步骤3中的数据除以步骤1中的数据，得到四舍五入的结果。

方法一

SELECTround(
COUNT(t3.player_id) / (
SELECTCOUNT(DISTINCT (`player_id`)) asuser_countfrom`activity`    ),
2  ) asfractionfrom  (
SELECTt1.`player_id`,
COUNT(1) asnumFROM`activity`t1join (
SELECTmin(event_date) asstart_date,
date_add(min(event_date), interval7day)asend_date,
`player_id`FROM`activity`GROUPBY`player_id`      ) t2ont1.`player_id`=t2.player_idwheret1.`event_date`BETWEENt2.start_dateandt2.end_dateGROUPBYt1.`player_id`HAVINGCOUNT(1) >1  ) t3

方法二

selectround(
t2.player_count/ (
SELECTCOUNT(DISTINCT (`player_id`)) asuser_countfrom`activity`    ),
2  ) asfractionfrom  (
SELECtcount(DISTINCT(`player_id`)) asplayer_countfrom      (
SELECT`player_id`,
`event_date`,
casewhen@player_id=`player_id`thendatediff(`event_date`, @prev_date)
when@player_id :=`player_id`then0endasdays,
casewhen@player_id1=`player_id`then@prev_datewhen@player_id1 :=`player_id`then@prev_date :=`event_date`endasfirst_login_dateFROM`activity`orderby`player_id`,
`event_date`asc      ) t1wheret1.days<=7andt1.days>0  ) t2

赛题三：计算三角形面积

解题思路

1、确认三角形计算面积公式

此处使用向量叉积的方式计算三角形面积。假设三个点坐标如下 (x1,y1,z1)，(x2,y2,z2)，(x3,y3,z3)，那么该三角形组成的面积S的公式为：

2、获取三个点的具体坐标值

使用 join 方法分别将 triangle 表中三个点的具体坐标值，然后再使用公式计算即可。这里我们会需要用到求平方的函数 pow，以及求平方根的函数 sqrt。

结果

SELECTt.idasTriangleId,
round(
sqrt(
pow((y1-y2), 2) *pow((z1-z3), 2) +pow((z1-z2), 2) *pow((x1-x3), 2) +pow((x1-x2), 2) *pow((y1-y3), 2)
    ) *0.5,
2  ) asAreaFROM  (
SELECTt1.id,
t2.`x`asx1,
t2.`y`asy1,
t2.`z`asz1,
t3.`x`asx2,
t3.`y`asy2,
t3.`z`asz2,
t4.`x`asx3,
t4.`y`asy3,
t4.`z`asz3FROM`triangle`t1join`point`t2ont1.`pointId1`=t2.`id`join`point`t3ont1.`pointId2`=t3.`id`join`point`t4ont1.`pointId3`=t4.`id`  ) ast