目录
一,原题力扣链接
二,题干
用户表:
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%
三,建表语句
Create table If Not Exists Users (user_id int, user_name varchar(20));
Create table If Not Exists Register (contest_id int, user_id int);
Truncate table Users;
insert into Users (user_id, user_name) values ('6', 'Alice');
insert into Users (user_id, user_name) values ('2', 'Bob');
insert into Users (user_id, user_name) values ('7', 'Alex');
Truncate table Register;
insert into Register (contest_id, user_id) values ('215', '6');
insert into Register (contest_id, user_id) values ('209', '2');
insert into Register (contest_id, user_id) values ('208', '2');
insert into Register (contest_id, user_id) values ('210', '6');
insert into Register (contest_id, user_id) values ('208', '6');
insert into Register (contest_id, user_id) values ('209', '7');
insert into Register (contest_id, user_id) values ('209', '6');
insert into Register (contest_id, user_id) values ('215', '7');
insert into Register (contest_id, user_id) values ('208', '7');
insert into Register (contest_id, user_id) values ('210', '2');
insert into Register (contest_id, user_id) values ('207', '2');
insert into Register (contest_id, user_id) values ('210', '7');
四,分析
题解: 第一个表是 用户表 记录的用户id和用户名称 第二个表是 注册表 记录的赛事id 和注册的用户id 题目 要求统计不同赛事的用户注册率 保留两位小数 并且排序 分析: 拼接2个表 不同赛事 以赛事分组 用户的注册率 赛事注册的用户数/ 用户的总数 *100 +% 保留2位小数 round函数 排序 以注册率升序 以id降序 向desc 后默认asc 实现: 第一步 拼接2个表 第二步 以赛事分组 以注册人数id count 拿到该赛事的人数 第三步 查询学生表 拿到全部的学生人数 第四步 以该赛事人数 / 全部的人数 *100 + concat拼接上 % 第五步 round函数 四舍五入 第六步 排序
五,SQL解答
方式一,常规解答:
-- 解法一
with t1 as (
select count(user_id) as c_uid from users
), t2 as (
select contest_id,count(r1.user_id) as c_r_uid from users u1,register r1
where u1.user_id=r1.user_id group by contest_id
)
select t2.contest_id as contest_id,round( (t2.c_r_uid/t1.c_uid)*100,2) as percentage from t2,t1
order by percentage desc ,contest_id ;
方式二,开窗函数解答
select distinct r1.contest_id as contest_id,
round((count(r1.user_id) over (partition by r1.contest_id ) )/(select count(user_id) from users)*100,2) percentage
from users u1,register r1
where u1.user_id=r1.user_id
order by percentage desc ,contest_id ;
六,验证
方式一 常规步骤验证:
方式一 开窗验证:
七,知识点总结
知识点总结 1,内链接拼接2个表; 2,分组 求聚合 count 以赛事分组 求该赛季的注册人数总数; 3,查询学生表 聚合学生总人数 count学生id; 4,以赛事注册人数/总人数 *100 得到百分率 5,round 四舍五入 6,多列排序 orderby 内连接的运用 分组,聚合的运用 分组聚合开窗的运用 round 四舍五入的运用 子查询在字段的运用 whth的运用 排序 order by 多条件的运用 降序 desc 升序默认