SQL练习专场--01
在这个专场中，会陆续更新一些关于sql的面试题
-- 题目1：【*】找出连续活跃3天及以上的用户
建表语句：
create table t_useractive(
uid string,
dt string
);
insert into t_useractive
values('A','2023-10-01'),('A','2023-10-02'),('A','2023-10-03'),('A','2023-10-04'),
('B','2023-10-01'),('B','2023-10-03'),('B','2023-10-04'),('B','2023-10-05'),
('C','2023-10-01'),('C','2023-10-03'),('C','2023-10-05'),('C','2023-10-06'),
('D','2023-10-02'),('D','2023-10-03'),('D','2023-10-05'),('D','2023-10-06');
解题思路：
先使用排名函数row_number给dt的值日期进行排序，通过data_sub()方法相减，若得到的日期相同，那么可以说明，这些时间就是连续登录的了。
最后在根据pm的值进行count()，如果列数大于3，就证明是连续三天登录的
sql代码如下：
select * from t_useractive;
-- 第一步，先将字符串类型的dt转成日期类型(其实在这题中，不转换也可以)
select uid,date_format(dt,'yyyy-MM-dd') dt from t_useractive group by uid,dt;
-- 第二步，使用row_number函数对每个用户的登录时间进行一个排名，然后使用日期减去这个排名，若得到的日期相同，那么可以说明，这些时间就是连续登录的了。
with t as (
select uid,date_format(dt,'yyyy-MM-dd') dt from t_useractive group by uid,dt
),t2 as (
select *, date_sub(dt, row_number() over (partition by uid order by dt)) pm from t_useractive
) select uid from t2 group by uid, pm having count(1) >= 3;结果：
复习排名函数：
1、row_number()
row_number从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列
效果如下：
98 1
97 2
97 3
96 4
95 5
95 6
没有并列名次情况，顺序递增2、rank()
生成数据项在分组中的排名，排名相等会在名次中留下空位
效果如下：
98 1
97 2
97 2
96 4
95 5
95 5
94 7
有并列名次情况，顺序跳跃递增3、dense_rank()
生成数据项在分组中的排名，排名相等会在名次中不会留下空位
效果如下：
98 1
97 2
97 2
96 3
95 4
95 4
94 5
有并列名次情况，顺序递增题目2：统计每个Top3歌单以及Top3歌单下的Top3歌曲
表中数据如下：
1 1 经典老歌 1 月亮代表我的心
2 1 经典老歌 1 月亮代表我的心
3 1 经典老歌 3 夜来香
4 1 经典老歌 4 我只在乎你
5 1 经典老歌 5 千言万语
6 1 经典老歌 5 千言万语
7 2 流行金曲 7 突然好想你
8 2 流行金曲 8 后来
9 2 流行金曲 9 童话
10 2 流行金曲 10 晴天
11 2 流行金曲 7 突然好想你
12 2 流行金曲 7 突然好想你
13 3 纯音乐集 13 二泉映月
14 3 纯音乐集 14 琵琶语
15 3 纯音乐集 15 梦回还
16 4 欧美音乐 16 Shape of My Heart
17 4 欧美音乐 17 Just the Way You Are
18 4 欧美音乐 18 Hello
19 4 欧美音乐 19 A Thousand Years
20 4 欧美音乐 20 Thinking Out Loud
21 4 欧美音乐 20 Thinking Out Loud
22 4 欧美音乐 18 Hello
23 4 欧美音乐 18 Hello
24 5 民谣时光 24 易燃易爆炸
25 5 民谣时光 25 成全
26 5 民谣时光 25 成全
27 5 民谣时光 25 成全建表语句以及导入语句：
现在本地集群的 /home/sqltest 下创建一个est03.txt，将上面的数据放进去，再执行以下语句，完成数据导入
create table songs(
uid int,
lid int,
list_name string,
sid int,
song_name string
)
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties(
'input.regex'='([^\\s]+)\\s+([^\\s]+)\\s+([^\\s]+)\\s+([^\\s]+)\\s+(.*)'
,"output.format.string" = "%1$s %2$s %3$s %4$s %5$s"
);
load data local inpath '/home/sqltest/test03.txt' into table songs;sql如下：
-- 统计每个Top3歌单以及Top3歌单下的Top3歌曲
-- 思路：先统计top3歌单
select list_name,count(1) list_songsNum from songs group by list_name order by list_songsNum desc limit 3;
-- 再根据top3歌单挑选出所有的歌曲进行数量记录 （通过join）
with t as (
select list_name,count(1) list_songsNum from songs group by list_name order by list_songsNum desc limit 3
) select s.list_name,s.song_name,count(1) songsNum from t join songs s on t.list_name = s.list_name group by s.list_name,s.song_name;
-- 最后，使用排名函数排名，取排名前三名的即可
with t as (
select list_name,count(1) list_songsNum from songs group by list_name order by list_songsNum desc limit 3
) ,t2 as (
select s.list_name,s.song_name,count(1) songsNum from t join songs s on t.list_name = s.list_name group by s.list_name,s.song_name
) ,t3 as (
select list_name,song_name,rank() over (partition by list_name order by songsNum desc) pm from t2
)select * from t3 where pm <= 3
如果这里使用的是row_number() 排序函数的话，就会产生下面的结果，可能会丢失数据
（row_number()的值不会存在重复）
第三题总结：先根据表，求出top3的歌单，再使用join使临时表与原表关联，得出top3歌单下的所有歌曲信息，然后再使用聚合函数得出每个歌曲的数量多少，之后根据排名函数给歌曲排名，得到前三歌单的歌曲
题目3：【*】用一条sql语句查询出每门课都大于或等于80分的学生姓名
建表语句以及sql语句如下：
create table t1(
name string,
course string,
grade int
);
INSERT INTO t1 (name, course, grade) VALUES
('张三', '英语', 85),
('张三', '语文', 90),
('张三', '数学', 92),
('李四', '英语', 75),
('李四', '语文', 98),
('李四', '数学', 72),
('王五', '英语', 90),
('王五', '语文', 85),
('王五', '数学', 92),
('王五', '体育', 80);
select * from t1;
-- 用一条sql语句查询出每门课都大于或等于80分的学生姓名
select name,min(grade) minScore from t1 group by name having minScore > 80;
先更新这么几道，后续会接着更新更多