数据库-练习题

练习题

1. 力扣 1633. 各赛事的用户注册率 简单

用户表： Users

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| user_name   | varchar |
+-------------+---------+
user_id 是该表的主键(具有唯一值的列)。
该表中的每行包括用户 ID 和用户名。

注册表： Register

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| contest_id  | int     |
| user_id     | int     |
+-------------+---------+
(contest_id, user_id) 是该表的主键(具有唯一值的列的组合)。
该表中的每行包含用户的 ID 和他们注册的赛事。

编写解决方案统计出各赛事的用户注册百分率，保留两位小数。

返回的结果表按 percentage 的 降序 排序，若相同则按 contest_id 的 升序 排序。

返回结果如下示例所示。

示例 1：

输入：
Users 表：
+---------+-----------+
| user_id | user_name |
+---------+-----------+
| 6       | Alice     |
| 2       | Bob       |
| 7       | Alex      |
+---------+-----------+

Register 表：
+------------+---------+
| contest_id | user_id |
+------------+---------+
| 215        | 6       |
| 209        | 2       |
| 208        | 2       |
| 210        | 6       |
| 208        | 6       |
| 209        | 7       |
| 209        | 6       |
| 215        | 7       |
| 208        | 7       |
| 210        | 2       |
| 207        | 2       |
| 210        | 7       |
+------------+---------+
输出：
+------------+------------+
| contest_id | percentage |
+------------+------------+
| 208        | 100.0      |
| 209        | 100.0      |
| 210        | 100.0      |
| 215        | 66.67      |
| 207        | 33.33      |
+------------+------------+
解释：
所有用户都注册了 208、209 和 210 赛事，因此这些赛事的注册率为 100% ，我们按 contest_id 的降序排序加入结果表中。
Alice 和 Alex 注册了 215 赛事，注册率为 ((2/3) * 100) = 66.67%
Bob 注册了 207 赛事，注册率为 ((1/3) * 100) = 33.33%

题解:

表结构很简单, 都是只有两个属性

计算每个 contest_id 对应的注册率 , 我们只需要查询出每个 contest_id 对应的注册人数 , 有多少人 , 再除以 Users 中的用户人数即可

SELECT
    r.contest_id,
    ROUND(COUNT(r.user_id)/(SELECT COUNT(*) FROM Users) * 100,2) as percentage
FROM
    Register AS r 
GROUP BY
    r.contest_id
ORDER BY
    percentage DESC,
    contest_id ASC;

应该还是挺好理解的 , 查询两个数据 contest_id 和 percentage , group by 按 contest_id 分组 , 计算的每组 user_id 数量 , 这样就可以计算出注册率 , 最后按要求排一下序

2. 力扣 1661. 每台机器的进程平均运行时间 简单

表: Activity

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| machine_id     | int     |
| process_id     | int     |
| activity_type  | enum    |
| timestamp      | float   |
+----------------+---------+
该表展示了一家工厂网站的用户活动。
(machine_id, process_id, activity_type) 是当前表的主键（具有唯一值的列的组合）。
machine_id 是一台机器的ID号。
process_id 是运行在各机器上的进程ID号。
activity_type 是枚举类型 ('start', 'end')。
timestamp 是浮点类型,代表当前时间(以秒为单位)。
'start' 代表该进程在这台机器上的开始运行时间戳 , 'end' 代表该进程在这台机器上的终止运行时间戳。
同一台机器，同一个进程都有一对开始时间戳和结束时间戳，而且开始时间戳永远在结束时间戳前面。

现在有一个工厂网站由几台机器运行，每台机器上运行着 相同数量的进程 。编写解决方案，计算每台机器各自完成一个进程任务的平均耗时。

完成一个进程任务的时间指进程的'end' 时间戳 减去 'start' 时间戳。平均耗时通过计算每台机器上所有进程任务的总耗费时间除以机器上的总进程数量获得。

结果表必须包含machine_id（机器ID） 和对应的 average time（平均耗时） 别名 processing_time，且四舍五入保留3位小数。

以 任意顺序 返回表。

具体参考例子如下。

示例 1:

输入：
Activity table:
+------------+------------+---------------+-----------+
| machine_id | process_id | activity_type | timestamp |
+------------+------------+---------------+-----------+
| 0          | 0          | start         | 0.712     |
| 0          | 0          | end           | 1.520     |
| 0          | 1          | start         | 3.140     |
| 0          | 1          | end           | 4.120     |
| 1          | 0          | start         | 0.550     |
| 1          | 0          | end           | 1.550     |
| 1          | 1          | start         | 0.430     |
| 1          | 1          | end           | 1.420     |
| 2          | 0          | start         | 4.100     |
| 2          | 0          | end           | 4.512     |
| 2          | 1          | start         | 2.500     |
| 2          | 1          | end           | 5.000     |
+------------+------------+---------------+-----------+
输出：
+------------+-----------------+
| machine_id | processing_time |
+------------+-----------------+
| 0          | 0.894           |
| 1          | 0.995           |
| 2          | 1.456           |
+------------+-----------------+
解释：
一共有3台机器,每台机器运行着两个进程.
机器 0 的平均耗时: ((1.520 - 0.712) + (4.120 - 3.140)) / 2 = 0.894
机器 1 的平均耗时: ((1.550 - 0.550) + (1.420 - 0.430)) / 2 = 0.995
机器 2 的平均耗时: ((4.512 - 4.100) + (5.000 - 2.500)) / 2 = 1.456

题解:

select a1.machine_id , round((sum(a2.timestamp)-sum(a1.timestamp))/count(a1.machine_id),3) processing_time
from 
    Activity as a1 join Activity as a2 
    on a1.machine_id = a2.machine_id 
    and a1.process_id=a2.process_id 
    and a1.activity_type = 'start' 
    and a2.activity_type = 'end'
group by a1.machine_id;

举例:

| machine_id | process_id | activity_type | timestamp |
| ---------- | ---------- | ------------- | --------- |
| 0          | 0          | start         | 0.712     |
| 0          | 0          | end           | 1.52      |
| 0          | 1          | start         | 3.14      |
| 0          | 1          | end           | 4.12      |
| 1          | 0          | start         | 0.55      |
| 1          | 0          | end           | 1.55      |
| 1          | 1          | start         | 0.43      |
| 1          | 1          | end           | 1.42      |
| 2          | 0          | start         | 4.1       |
| 2          | 0          | end           | 4.512     |
| 2          | 1          | start         | 2.5       |
| 2          | 1          | end           | 5         |

调用如下 sql 代码

select a1.machine_id as machine_id , a1.process_id as a1process_id , a1.timestamp as start_time , a2.timestamp as end_time
from 
    Activity as a1 join Activity as a2 
    on a1.machine_id = a2.machine_id 
    and a1.process_id=a2.process_id 
    and a1.activity_type = 'start' 
    and a2.activity_type = 'end'

得到如下

| machine_id | a1process_id | start_time | end_time |
| ---------- | ------------ | ---------- | -------- |
| 0          | 0            | 0.712      | 1.52     |
| 0          | 1            | 3.14       | 4.12     |
| 1          | 0            | 0.55       | 1.55     |
| 1          | 1            | 0.43       | 1.42     |
| 2          | 0            | 4.1        | 4.512    |
| 2          | 1            | 2.5        | 5        |

所以 sum(a2.timestamp) - sum(a1.timestamp) / count(a1.machine_id) 就是每个机器平均每个进程的运行时间

当然也可以简化 avg(a2.timestamp - a1.timestamp)

Ps : 我是后来才之后有求平均数的函数的(╯°□°）╯︵┻━┻

3. 力扣 1667. 修复表中的名字 简单

表： Users

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| user_id        | int     |
| name           | varchar |
+----------------+---------+
user_id 是该表的主键(具有唯一值的列)。
该表包含用户的 ID 和名字。名字仅由小写和大写字符组成。

编写解决方案，修复名字，使得只有第一个字符是大写的，其余都是小写的。

返回按 user_id 排序的结果表。

返回结果格式示例如下。

示例 1：

输入：
Users table:
+---------+-------+
| user_id | name  |
+---------+-------+
| 1       | aLice |
| 2       | bOB   |
+---------+-------+
输出：
+---------+-------+
| user_id | name  |
+---------+-------+
| 1       | Alice |
| 2       | Bob   |
+---------+-------+

解答:

感觉这个题就是纯粹考函数

select user_id, concat(Upper(substring(name,1,1)),lower(substring(name,2))) as name
from Users
order by user_id;

concat 字符串连接

Upper 字母大写

Lower 字母小写

substring 截取字符串