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

2023-11-11 05:10

本文主要是介绍[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	1create 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
(select
udi,
dt,
login_status,
row_number() over(partition by udi order by dt) `rank`
from
sql004
where login_status=1)t1;t2
按sub字段做聚集
select
udi,
dt,
login_status,
`rank`,
sub,
count(sub) over(partition by udi,sub) acount
from
(select
udi,
dt,
login_status,
`rank`,
date_sub(dt,`rank`) sub
from
(select
udi,
dt,
login_status,
row_number() over(partition by udi order by dt) `rank`
from
sql004
where login_status=1)t1)t2;t3
得到数量大于7的记录
select
udi,
dt,
login_status
from
(select
udi,
dt,
login_status,
`rank`,
sub,
count(sub) over(partition by udi,sub) acount
from
(select
udi,
dt,
login_status,
`rank`,
date_sub(dt,`rank`) sub
from
(select
udi,
dt,
login_status,
row_number() over(partition by udi order by dt) `rank`
from
sql004
where login_status=1)t1)t2)t3
where acount>6;6
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	87create table sql006
(
stu_no int,
class int,
score int
)
row format delimited
fields terminated by '\t';
load data local inpath '/root/in/sql006'into table sql006;获得排名,再加一列上一名次学生的成绩
select
stu_no,
class,
score,
rank() over(partition by class order by score desc) `rank`,
lag(score,1,0) over(partition by class order by score desc) preced
from
sql006;t1
过滤出前三名的学生,做前后两名学生成绩的差
select
class,
stu_no,
score,
`rank`,
score-preced rn_diff
from
(select
stu_no,
class,
score,
rank() over(partition by class order by score desc) `rank`,
lag(score,1,0) over(partition by class order by score desc) preced
from
sql006)t1
where 
`rank`<4;8
1	001	语文	90
2	001	数学	92
3	001	英语	80
4	002	语文	88
5	002	数学	90
6	002	英语	75.5
7	003	语文	70
8	003	数学	85
9	003	英语	90
10	003	政治	82create table sql008
(
id int,
userid string,
subject string,
score int
)
row format delimited
fields terminated by '\t';
load data local inpath '/root/in/sql008' into table sql008;先用if()做初步的列转行
select
userid,
if(subject='语文',score,0) yuwen1,
if(subject='数学',score,0) shuxue1,
if(subject='英语',score,0) yingyu1,
if(subject='政治',score,0) zhengzhi1
from
sql008;t1
按userid做聚合,取有意义的值
select
userid,
max(yuwen1) yuwen,
max(shuxue1) shuxue,
max(yingyu1) yingyu,
max(zhengzhi1) zhengzhi
from
(select
userid,
if(subject='语文',score,0) yuwen1,
if(subject='数学',score,0) shuxue1,
if(subject='英语',score,0) yingyu1,
if(subject='政治',score,0) zhengzhi1
from
sql008)t1
group by
userid;t2
加一列求和
select
userid,
yuwen,
shuxue,
yingyu,
zhengzhi,
yuwen+shuxue+yingyu+zhengzhi total
from
(select
userid,
max(yuwen1) yuwen,
max(shuxue1) shuxue,
max(yingyu1) yingyu,
max(zhengzhi1) zhengzhi
from
(select
userid,
if(subject='语文',score,0) yuwen1,
if(subject='数学',score,0) shuxue1,
if(subject='英语',score,0) yingyu1,
if(subject='政治',score,0) zhengzhi1
from
sql008)t1
group by
userid)t2;t3
再加一行total
select
'total' userid,
sum(yuwen),
sum(shuxue), 
sum(yingyu),
sum(zhengzhi),
sum(total) 
from
(select
userid,
yuwen,
shuxue,
yingyu,
zhengzhi,
yuwen+shuxue+yingyu+zhengzhi total
from
(select
userid,
max(yuwen1) yuwen,
max(shuxue1) shuxue,
max(yingyu1) yingyu,
max(zhengzhi1) zhengzhi
from
(select
userid,
if(subject='语文',score,0) yuwen1,
if(subject='数学',score,0) shuxue1,
if(subject='英语',score,0) yingyu1,
if(subject='政治',score,0) zhengzhi1
from
sql008)t1
group by
userid)t2)t3;t4
将t3视图和t4视图连接起来的到最后的结果(t4加的这行total完全没意义啊)
select
userid,
yuwen,
shuxue,
yingyu,
zhengzhi,
yuwen+shuxue+yingyu+zhengzhi total
from
(select
userid,
max(yuwen1) yuwen,
max(shuxue1) shuxue,
max(yingyu1) yingyu,
max(zhengzhi1) zhengzhi
from
(select
userid,
if(subject='语文',score,0) yuwen1,
if(subject='数学',score,0) shuxue1,
if(subject='英语',score,0) yingyu1,
if(subject='政治',score,0) zhengzhi1
from
sql008)t1
group by
userid)t2
union
select
'total' userid,
sum(yuwen),
sum(shuxue), 
sum(yingyu),
sum(zhengzhi),
sum(total) 
from
(select
userid,
yuwen,
shuxue,
yingyu,
zhengzhi,
yuwen+shuxue+yingyu+zhengzhi total
from
(select
userid,
max(yuwen1) yuwen,
max(shuxue1) shuxue,
max(yingyu1) yingyu,
max(zhengzhi1) zhengzhi
from
(select
userid,
if(subject='语文',score,0) yuwen1,
if(subject='数学',score,0) shuxue1,
if(subject='英语',score,0) yingyu1,
if(subject='政治',score,0) zhengzhi1
from
sql008)t1
group by
userid)t2)t3;9
1	1,2,3
2	2,3
3	1,2create table sql009
(
uid int,
tags array<string>
)
row format delimited
fields terminated by '\t'
collection items terminated by ',';
load data local inpath '/root/in/sql009' into table sql009;
利用udtf函数explode()
select
uid,
tag
from
sql009
lateral view explode(tags)t as tag;10
T1表:
Tags
1,2,3
1,2
2,3T2表:
Id	lab
1	A
2	B
3	Ccreate table sql010t1
(
tags array<string>
)
row format delimited
fields terminated by '\t'
collection items terminated by ',';create table sql010t2
(
id int,
lab string
)
row format delimited
fields terminated by '\t';load data local inpath '/root/in/sql010t1' into table sql010t1;
load data local inpath '/root/in/sql010t2' into table sql010t2;为sql010t1加上行标识
select
row_number() over() id,
tags
from
sql010t1;t1
将数组拆开
select
id,
tag
from
(select
row_number() over() id,
tags
from
sql010t1)t1
lateral view explode(tags)t as tag;t1
t1和sql010t2两表关联查询
select
t1.id,
tag,
lab
from
(select
id,
tag
from
(select
row_number() over() id,
tags
from
sql010t1)t1
lateral view explode(tags)t as tag)t1
join
sql010t2
on 
t1.tag=sql010t2.id;t2
根据行标识聚合,获得结果
selectconcat_ws(',',collect_set(tag)) ids,concat_ws(',',collect_set(lab)) tags
from(selectt1.id,tag,labfrom(selectid,tagfrom(selectrow_number() over() id,tagsfromsql010t1)t1lateral view explode(tags)t as tag)t1joinsql010t2on t1.tag=sql010t2.id)t2
group byid;11
a	b	2
a	b	1
a	b	3
c	d	6
c	d	8
c	d	8create table sql011
(
id string,
tag string,
flag string
)
row format delimited
fields terminated by '\t';
load data local inpath '/root/in/sql011' into table sql011;select
id,
tag,
concat_ws('|',collect_set(flag)) flag
from sql011
group by
id,tag;16
select from_unixtime(unix_timestamp('20171205','yyyymmdd'),'yyyy-mm-dd') from dual;

这篇关于[hive] 经典sql题及答案(二)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/387748

相关文章

MySQL 中的 CAST 函数详解及常见用法

《MySQL中的CAST函数详解及常见用法》CAST函数是MySQL中用于数据类型转换的重要函数,它允许你将一个值从一种数据类型转换为另一种数据类型,本文给大家介绍MySQL中的CAST... 目录mysql 中的 CAST 函数详解一、基本语法二、支持的数据类型三、常见用法示例1. 字符串转数字2. 数字

Mysql实现范围分区表(新增、删除、重组、查看)

《Mysql实现范围分区表(新增、删除、重组、查看)》MySQL分区表的四种类型(范围、哈希、列表、键值),主要介绍了范围分区的创建、查询、添加、删除及重组织操作,具有一定的参考价值,感兴趣的可以了解... 目录一、mysql分区表分类二、范围分区(Range Partitioning1、新建分区表:2、分

MySQL 定时新增分区的实现示例

《MySQL定时新增分区的实现示例》本文主要介绍了通过存储过程和定时任务实现MySQL分区的自动创建,解决大数据量下手动维护的繁琐问题,具有一定的参考价值,感兴趣的可以了解一下... mysql创建好分区之后,有时候会需要自动创建分区。比如,一些表数据量非常大,有些数据是热点数据,按照日期分区MululbU

SQL Server配置管理器无法打开的四种解决方法

《SQLServer配置管理器无法打开的四种解决方法》本文总结了SQLServer配置管理器无法打开的四种解决方法,文中通过图文示例介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的... 目录方法一:桌面图标进入方法二:运行窗口进入检查版本号对照表php方法三:查找文件路径方法四:检查 S

MySQL 删除数据详解(最新整理)

《MySQL删除数据详解(最新整理)》:本文主要介绍MySQL删除数据的相关知识,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录一、前言二、mysql 中的三种删除方式1.DELETE语句✅ 基本语法: 示例:2.TRUNCATE语句✅ 基本语

MySQL中查找重复值的实现

《MySQL中查找重复值的实现》查找重复值是一项常见需求,比如在数据清理、数据分析、数据质量检查等场景下,我们常常需要找出表中某列或多列的重复值,具有一定的参考价值,感兴趣的可以了解一下... 目录技术背景实现步骤方法一:使用GROUP BY和HAVING子句方法二:仅返回重复值方法三:返回完整记录方法四:

从入门到精通MySQL联合查询

《从入门到精通MySQL联合查询》:本文主要介绍从入门到精通MySQL联合查询,本文通过实例代码给大家介绍的非常详细,需要的朋友可以参考下... 目录摘要1. 多表联合查询时mysql内部原理2. 内连接3. 外连接4. 自连接5. 子查询6. 合并查询7. 插入查询结果摘要前面我们学习了数据库设计时要满

MySQL查询JSON数组字段包含特定字符串的方法

《MySQL查询JSON数组字段包含特定字符串的方法》在MySQL数据库中,当某个字段存储的是JSON数组,需要查询数组中包含特定字符串的记录时传统的LIKE语句无法直接使用,下面小编就为大家介绍两种... 目录问题背景解决方案对比1. 精确匹配方案(推荐)2. 模糊匹配方案参数化查询示例使用场景建议性能优

mysql表操作与查询功能详解

《mysql表操作与查询功能详解》本文系统讲解MySQL表操作与查询,涵盖创建、修改、复制表语法,基本查询结构及WHERE、GROUPBY等子句,本文结合实例代码给大家介绍的非常详细,感兴趣的朋友跟随... 目录01.表的操作1.1表操作概览1.2创建表1.3修改表1.4复制表02.基本查询操作2.1 SE

MySQL中的锁机制详解之全局锁,表级锁,行级锁

《MySQL中的锁机制详解之全局锁,表级锁,行级锁》MySQL锁机制通过全局、表级、行级锁控制并发,保障数据一致性与隔离性,全局锁适用于全库备份,表级锁适合读多写少场景,行级锁(InnoDB)实现高并... 目录一、锁机制基础:从并发问题到锁分类1.1 并发访问的三大问题1.2 锁的核心作用1.3 锁粒度分