sql练习专场（一） 1-5
这是总结的一些sql题目，共25道题，每个博客会写5道题
第一题
这道题需要找出连续活跃3天以上的用户，其中每个用户每天可以连续登录多次。
create table sql1_1(
uid string,
dt string
);
insert into sql1_1
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');思路
由于其中的数据并没有重复的，就不再写distinct了。可以先使用row_number函数对每个用户的登录时间进行一个排名，然后使用日期减去这个排名，若得到的日期相同，那么可以说明，这些时间就是连续登录的了。
结果
with t1 as
( select *,date_sub(dt,row_number() over (partition by uid order by dt)) tempdt from sql1_1 )
select uid from t1 group by uid,tempdt having count(*) >=3;
第二题
这道题是用于统计每月会员数量的，只给了起始日期和结束日期，需要hive的自定义函数。
create table sql1_2(
consumerid string,
startdate string,
enddate string
)
row format delimited
fields terminated by ',';
load data local inpath '/home/homedata/sql_1/sql1_2.txt' into table sql1_2;
1,2021-01-01,2022-01-01
2,2021-02-02,2022-02-02
3,2021-03-03,2022-03-03hive自定义函数
1、创建一个maven项目，添加以下依赖
<dependencies>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>3.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
<version>3.3.1</version>
</dependency>
</dependencies>2、创建一个类继承GenericUDTF，实现其中的方法
public class ExplodeYear extends GenericUDTF {
@Override
public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
// 返回值的数据类型和名称
//1.定义输出数据的列名和类型
List<String> fieldNames = new ArrayList<String>();
List<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>();
//2.添加输出数据的列名和类型
fieldNames.add("mt");
fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
}
@Override
public void process(Object[] objects) throws HiveException {
String beginDate = objects[0].toString();
String endDate = objects[1].toString();
ArrayList<String> list = DateUtil.dateExplode(beginDate, endDate);
for (String s : list) {
forward(new String[]{s});
}
}
@Override
public void close() throws HiveException {
}
}
3、编写日期工具类
public class DateUtil {
public static ArrayList<String> dateExplode(String beginDate,String endDate){
ArrayList<String> list= new ArrayList<>();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM");
try {
Date dateFrom = simpleDateFormat.parse(beginDate);
Date dateTo = simpleDateFormat.parse(endDate);
Calendar calendar = Calendar.getInstance();
calendar.setTime(dateFrom);
while (calendar.getTime().before(dateTo)){
String before = simpleDateFormat.format(calendar.getTime());
list.add(before);
calendar.add(Calendar.MONTH,1);
}
} catch (ParseException e) {
throw new RuntimeException(e);
}
return list;
}
}4、将maven项目打包，放到hive的lib下，在hive中添加该jar包，创建一个临时函数
add jar /opt/installs/hive/lib/MyFunction-1.0-SNAPSHOT.jar;
create temporary function k1 as 'com.bigdata.customfunctions.ExplodeYear';5、sql实现
select mt,count(*) count from sql1_2 lateral view k1(startdate,enddate) t as mt group by mt ;6、结果截图
第三题
统计每个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 成全加载数据
create table sql1_3(
u_id int,
mf_id int,
mf_name string,
m_id int,
m_name string
)
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties(
'input.regex'='(\\d+)\\s+(\\d+)\\s+(.+?)\\s+(\\d+)\\s+(.+?)'
);
load data local inpath '/home/homedata/sql_1/sql1_3.txt' into table sql1_3;结果
编写sql
with t1 as (
// 前三的歌单
select mf_id from sql1_3 group by mf_id order by count(*) desc limit 3
) ,t2 as(
// 根据歌单挑选出所有的歌曲记录数量
select mf_name ,m_name,count(*) count from t1 left join sql1_3 on t1.mf_id = sql1_3.mf_id group by mf_name ,m_name
),t3 as (
// 按照数量进行排序
select mf_name,m_name,rank() over (partition by mf_name order by count desc ) rankNum from t2
)
select * from t3 where rankNum <=3;
第四题
查询出每门课都大于80分的学生姓名
这道题就很简单了，要想查询出每门课都大于80分的学生姓名，只需要该学生的最低分大于80即可，而没有成绩的科目，就认为该学生没有报这门课，不需要考虑弃考的情况。
create table sql1_4(
name string,
course string,
grade int
);
INSERT INTO sql1_4 (name, course, grade) VALUES
('张三', '英语', 85),
('张三', '语文', 90),
('张三', '数学', 92),
('李四', '英语', 75),
('李四', '语文', 98),
('李四', '数学', 72),
('王五', '英语', 90),
('王五', '语文', 85),
('王五', '数学', 92),
('王五', '体育', 80);select name from sql1_4 group by name having min(grade) >=80;第五题
这道题是查询出至少连续出现3次的数字
create table sql1_5(
id int,
num int
)
row format delimited
fields terminated by ' ';
INSERT INTO sql1_5 (id, num) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 2),
(5, 1),
(6, 2),
(7, 2);sql
with t1 as (
select *,row_number() over (partition by num order by id) ,
id - row_number() over (partition by num order by id) jyl from sql1_5
)
select num from t1 group by jyl,num having count(*) >=3;