[hive] 经典sql题及答案(二)

推荐:

经典sql题及答案(一)

经典sql题及答案(三)

题目部分

4 、编写连续7 天登录的总人数：
数据:
t1表
Uid dt login_status(1登录成功,0异常)
1 2019-07-11 1
1 2019-07-12 1
1 2019-07-13 1
1 2019-07-14 1
1 2019-07-15 1
1 2019-07-16 1
1 2019-07-17 1
1 2019-07-18 1
2 2019-07-11 1
2 2019-07-12 1
2 2019-07-13 0
2 2019-07-14 1
2 2019-07-15 1
2 2019-07-16 0
2 2019-07-17 1
2 2019-07-18 0
3 2019-07-11 1
3 2019-07-12 1
3 2019-07-13 1
3 2019-07-14 1
3 2019-07-15 1
3 2019-07-16 1
3 2019-07-17 1
3 2019-07-18 1

编写sql实现

6 、编写sql 语句实现每班前三名，分数一样并列，同时求出前三名按名次排序的一次的分差：
数据：
stu表
Stu_no class score
1 1901 90
2 1901 90
3 1901 83
4 1901 60
5 1902 66
6 1902 23
7 1902 99
8 1902 67
9 1902 87
编写sql 实现，结果如下：
结果数据：
班级 stu_no score rn rn1 rn_diff
1901 1 90 1 1 90
1901 2 90 1 1 0
1901 3 83 3 1 -7
1902 7 99 1 1 99
1902 9 87 2 2 -12
1902 8 67 3 3 -20

8 、编写sql 实现行列互换：
数据如下：

编写sql 实现，得到结果如下：

9 、编写sql 实现如下：
数据：
t1表
uid tags
1 1,2,3
2 2,3
3 1,2
编写sql实现如下结果：
uid tag
1 1
1 2
1 3
2 2
2 3
3 1
3 2

10 、行转列
数据：
T1表:
Tags
1,2,3
1,2
2,3
T2表:
Id lab
1 A
2 B
3 C
根据T1和T2表的数据，编写sql实现如下结果：
ids tags
1,2,3 A,B,C
1,2 A,B
2,3 B,C

11 、行转列
数据：
t1表：
id tag flag
a b 2
a b 1
a b 3
c d 6
c d 8
c d 8
编写sql实现如下结果：
id tag flag
a b 1|2|3
c d 6|8

16 、时间格式转换：yyyyMMdd -> yyyy-MM-dd
数据:
t1表
20171205
编写sql实现如下的结果：
2017-12-05

答案部分

4
1	2019-07-11	1
1	2019-07-12	1
1	2019-07-13	1
1	2019-07-14	1
1	2019-07-15	1
1	2019-07-16	1
1	2019-07-17	1
1	2019-07-18	1
2	2019-07-11	1
2	2019-07-12	1
2	2019-07-13	0
2	2019-07-14	1
2	2019-07-15	1
2	2019-07-16	0
2	2019-07-17	1
2	2019-07-18	0
3	2019-07-11	1
3	2019-07-12	1
3	2019-07-13	1
3	2019-07-14	1
3	2019-07-15	1
3	2019-07-16	1
3	2019-07-17	1
3	2019-07-18	1

create table sql004
(
udi int,
dt string,
login_status int
)
row format delimited
fields terminated by '\t';
load data local inpath '/root/in/sql004' into table sql004;

按用户分组,过滤掉登陆异常的记录并加一行等差数列
select
udi,
dt,
login_status,
row_number() over(partition by udi order by dt) `rank`
from
sql004
where login_status=1;t1
dt与等差数列做差
select
udi,
dt,
login_status,
`rank`,
date_sub(dt,`rank`) sub
from
(