阿里云瑶池数据库SQL挑战赛答题

前言

看到墨天轮上阿里云瑶池数据库SQL挑战赛的报名通知(官网链接)，一共3道SQL题目。找了点空闲时间，尝试一下不一定正确😎

第一题

编写一个SQL查询，找出每个考试中 得分最高的的考生 。

这是一道非常经典的Top N题目，一般都可以用窗口函数来解决。

解题思路

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

用MAX()函数加上GROUP BY来过滤。

2.如果存在并列，将并列的考生都列举出来，直到列举的考生达到或超过三人。以每门考试考试分数从高到低的顺序，返回结果表。

用DENSE_RANK() OVER (PARTITION BY ORDER BY )来解决。

SQL源码

WITH
  t2 AS (
    SELECT
      studentId,
      testId,
      max_score,
      DENSE_RANK() OVER (
        PARTITION BY
          testId
        ORDER BY
          max_score DESC
      ) AS dense_rank_num
    FROM
      (
        SELECT
          studentId,
          testId,
          MAX(score) max_score
        FROM
          testattempt
        GROUP BY
          studentId,
          testId
      ) t1
  )
SELECT
  test.name Test,
  student.name Student,
  t2.max_score Score
FROM
  t2
  LEFT JOIN test ON t2.testId = test.id
  LEFT JOIN student ON t2.studentId = student.id
WHERE
  t2.dense_rank_num <= 3;

DMS截图

第二题

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

这题可以用窗口函数、也可以用子查询比较存在的方式完成，下面使用窗口函数方式。

解题思路

1.首先明确本题只和玩家ID、登录时间有关，和设备信息、共玩多少款游戏无关。

2.其次要求出同玩家相邻两次登录的时间间隔和登录的次数。

用LAG( , 1 ) OVER (PARTITION BY ORDER BY )来返回出位于当前行的前1行登录时间，然后用DATEDIFF()求出相邻两次登录的时间间隔，用ROW_NUMBER() OVER (PARTITION BY ORDER BY )返回登录次数

3.最后求出总共玩家人数，再和注册首周内有两次登录的玩家总数做比例。

用COUNT(DISTINCT )函数求总玩家人数，条件过滤首周内有两次登录的玩家总数，两者求比例保留小数点后两位。

SQL源码

WITH
  t1 AS (
    SELECT
      player_id,
      DATEDIFF(
        event_date,
        LAG(event_date, 1) OVER (
          PARTITION BY
            player_id
          ORDER BY
            event_date
        )
      ) diffdays,
      ROW_NUMBER() OVER (
        PARTITION BY
          player_id
        ORDER BY
          event_date
      ) rn
    FROM
      activity
  )
SELECT
  ROUND(
    COUNT(player_id) / (
      SELECT
        COUNT(DISTINCT player_id)
      FROM
        activity
    ),
    2
  ) fraction
FROM
  t1
WHERE
  rn = 2
  AND diffdays <= 7;

DMS截图

第三题

写一个SQL查询，计算每个三角形的面积，保留两位小数。

这题要理解三维空间中已知三角形顶点，求三角形面积的公式

解题思路

1.三个顶点坐标对应关系

通过triangle表和point表LEFT JOIN关联求出三角形的3个点的坐标（x1,y1,z1）,(x2,y2,z2) ,(x3,y3,z3)

2.三角形面积公式

先用两点间的距离公式算出三边长a，b，c，再用海伦公式计算。

$a= \sqrt{(x1-x2)^2+(y1-y2)^2+(z1-z2)^2}$

$b=\sqrt{(x3-x2)^2+(y3-y2)^2+(z3-z2)^2 )}$

$c=\sqrt{(x3-x1)^2+(y3-y1)^2+(z3-z1)^2 )}$

$p=(a+b+c)/2$

$S=\sqrt{(p*(p-a)*(p-b)*(p-c))}$

SQL源码

WITH
  triangle_area AS (
    SELECT
      t.id TriangleId,
      SQRT(
        POW(t1.x - t2.x, 2) + POW(t1.y - t2.y, 2) + POW(t1.z - t2.z, 2)
      ) a,
      SQRT(
        POW(t3.x - t2.x, 2) + POW(t3.y - t2.y, 2) + POW(t3.z - t2.z, 2)
      ) b,
      SQRT(
        POW(t3.x - t1.x, 2) + POW(t3.y - t1.y, 2) + POW(t3.z - t1.z, 2)
      ) c,
      (
        SQRT(
          POW(t1.x - t2.x, 2) + POW(t1.y - t2.y, 2) + POW(t1.z - t2.z, 2)
        ) + SQRT(
          POW(t3.x - t2.x, 2) + POW(t3.y - t2.y, 2) + POW(t3.z - t2.z, 2)
        ) + SQRT(
          POW(t3.x - t1.x, 2) + POW(t3.y - t1.y, 2) + POW(t3.z - t1.z, 2)
        )
      ) / 2 p
    FROM
      triangle t
      LEFT JOIN point t1 on t.pointId1 = t1.id
      LEFT JOIN point t2 on t.pointId2 = t2.id
      LEFT JOIN point t3 on t.pointId3 = t3.id
  )
SELECT
  TriangleId,
  Round(SQRT((p * (p - a) * (p - b) * (p - c))), 2) Area
FROM
  triangle_area;

DMS截图

总结

题目有用到窗口函数、子查询、有高中数学公式等知识点，挺有意思的😀