1. 首页
  2. 文章中心
  3. sql

HQL笔试题总结（持续更新中）日期：202111162021111720211118

84 阅读 0 评论
我是靠谱客的博主 野性灯泡，最近开发中收集的这篇文章主要介绍HQL笔试题总结（持续更新中）日期：202111162021111720211118，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1、获取第二高的薪水

  • 表(emp) & 列 （empno,salary）
select nvl(salary,null) 
from（
	select  e.salary
				desc_rank()over(order by e.salary desc) as rank
	from emp e
）t1
where t1.rank = 2

2、分数排名（名词之间不能有间隔）

  • 表（scores） & 列 （id，score）
select score,desc_rank(over score desc) as rank
from  Scores
order by rank

3、查询表中连续至少连续登录三天的用户

  • 表（player） & 列 （id，last_day）
    （1）分析函数将登陆日期排序
select id，last_dat,row_number()over(partition by id over by last_day) as rank_num
from palyer

（2）计算用户不同登录日期之间的时间差

select id，;last_day，date_sub(last_day - rank_num) as last_num_day
from (
	select id，last_dat,row_number()over(partition by id over by last_day) as rank_num
	from palyer
) t1

（3）对id，last_num_id进行分组获取连续登陆天数

select id,last_num_by,count(1) as days 
from (
	select id，;last_day，date_sub(last_day - rank_num) as last_num_day
	from (
		select id，last_dat,row_number()over(partition by id over by last_day) as rank_num
		from palyer
	) t1
) tt1
group by id,last_num_by
时间排名
2021-11-111
2021-11-122
2021-11-133
2021-12-114
2021-12-155

（4）进行筛选过滤，获取连续登录三天的用户id

select id
from (
	select id,last_num_by,count(1) as days 
	from (
		select id，;last_day，date_sub(last_day - rank_num) as last_num_day
		from (
			select id，last_dat,row_number()over(partition by id over by last_day) as rank_num
			from palyer
		) t1
	) tt1
	group by id,last_num_by
)
where days = 3

4、获取每一个学生中分数最高的分数及科目

  • 表（score）& 列（id，course，score）

（1）分组获取学生成绩

select id, max(score) as max_score
from Scores
group by id

（2）通过表连接获取学生科目

select 
	id,course,score
from Scores s1
left join (
	select id, max(score) as max_score
	from Scores
	group by id
) s2
on s1.id =  s2.id and s1.score = s2.score

日期：20211116

5、部门工资最高的员工

  • 表（emp） | 列 （id，name，salary，departmentid）

在这里插入图片描述

select name
from
	(
	select name,rank()over(partition by departmentid over salary desc) as rank
	from emp) t1
where t1.rank = 1

6、部门前三高的所有员工信息

  • 表（emp） | 列（id，name，Salary，Departmentid）
    在这里插入图片描述
select * 
from emp tt1 left join 
(select id
from(
	select id,desc_rank()over(partition by departmentid order by salary desc) as rank
	from emp ) t1
where t1.rank <= 3) tt2
on tt1.id = tt2.id

7、hive行转列

  • 源表
    在这里插入图片描述

  • 目标表
    在这里插入图片描述

  • 代码实战

（1）将省份和城市连接起来，作为临时表

select name,concat(provice,city) as address
from user_info

（2）使用子查询，查询第一步的结果，并按照连接后的省份城市字段进行group by排序，然后查出的多个结果使用collect_set进行去重汇总，并使用concat_ws按照“｜”进行连接，把查询结果输出即可

select
    tui1.place place,
    concat_ws('|', collect_set(tui1.user_name)) users
from
    (select
        user_name,
        concat(province, ",", city) place
    from
        user_info) tui1
group by
    tui1.place;
  • COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。
--需求:统计每个城市有哪些人

select city,collect_set(user_name) from user_info group by city;

在这里插入图片描述

8、列转行

  • 源表
    在这里插入图片描述
  • 目标表
    在这里插入图片描述
select
    user_name,
    hobby
from 
    user_hobby_info
    lateral view explode(hobbies) hobby_tmp as hobby;
  • 具体详解

（1）explode(hobbies)：把user_hobby_info表里定义的array类型的hobbies字段进行拆分；

（2）lateral view explode(hobbies) hobby_tmp：将拆分后的字段定义为临时表，即视图

（3）lateral view explode(hobbies) hobby_tmp as hobby：把虚拟表里的字段名定义为hobby；

（4）两表做笛卡尔积连接

from
    user_hobby_info
    lateral view explode(hobbies) hobby_tmp as hobby

（5）查询连接结果

select
    user_name,
    hobby
from 
    user_hobby_info
    lateral view explode(hobbies) hobby_tmp as hobby;

20211117

9、取出最近三次的价格并单独做拉链

（1）需求分析

  • 源表如下

在这里插入图片描述

  • 目标表如下
    在这里插入图片描述
  • 建表语句如下
create table Price(
name     string
,buy_date    string
,price   int
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
;


INSERT INTO Price VALUES
('A','2021/11/1',100),
('A','2021/10/1',120),
('A','2021/12/1',115),
('B','2021/11/1',99),
('B','2021/9/1',88)

（2）代码如下

①单独生成一列排名，作为子查询

select 
name
,price
,row_number()over(partition by name order by buy_date) as rank
from Price

②分别查询出前三并单独命名做另外一列

select name
,case when rank = 1 then price end as price
,case when rank = 2 then price end as price1
,case when rank = 3 then price end as price2
from (select 
name
,price
,row_number()over(partition by name order by buy_date) as rank
from Price) t1

在这里插入图片描述

③根据name进行分组，取每一列的最大值命名

select name
,max(price) as price
,max(price1) as price1
,max(price2) as price2
from(
select name
,case when rank = 1 then price end as price
,case when rank = 2 then price end as price1
,case when rank = 3 then price end as price2
from (select 
name
,price
,row_number()over(partition by name order by buy_date) as rank
from Price) t1) tt1
group by name

20211118

10、多表联合主键连接 & 判空

（1）需求

  • 三张源表（T1110A，T1110B，T1110C）

在这里插入图片描述

  • 目标表

在这里插入图片描述

  • 建表语句
CREATE TABLE T1110A
(WAREHOUSE string,
 ITEM string,
 QTY int)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
 
INSERT INTO T1110A VALUES ('A','P001',100),('A','P002',200),('B','P001',120 );
 
CREATE TABLE T1110B
(WAREHOUSE string,
 ITEM string,
 QTY int
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
 
INSERT INTO T1110B VALUES ('A','P001',50),('B','P001',30);

CREATE TABLE T1110C
(WAREHOUSE string,
 ITEM string,
 QTY int
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
 
INSERT INTO T1110C VALUES ('A','P001',10),('A','P002',20),('C','P001',15),('C','P003',10);

（2）代码如下

①首先进行多表连接

select t1.WAREHOUSE as t1w,t2.WAREHOUSE as t2w,t3.WAREHOUSE as t3w,t1.QTY as t1Q,t2.QTY as T2Q,t3.QTY as T3Q from T1110A t1 full join T1110B t2 on t1.WAREHOUSE = t2.WAREHOUSE and t1.item = t2.item
full join T1110C t3 on t1.WAREHOUSE = t3.WAREHOUSE and t1.item = t3.item
  • 运行结果

在这里插入图片描述

②将连接后的表作为临时表，对WAREHOUSE进行ifnull判断

select coalesce(t1w,t2w,t3w),t1Q,t2Q,t3Q from 
(
select t1.WAREHOUSE as t1w,t2.WAREHOUSE as t2w,t3.WAREHOUSE as t3w,t1.QTY as t1Q,t2.QTY as T2Q,t3.QTY as T3Q from T1110A t1 full join T1110B t2 on t1.WAREHOUSE = t2.WAREHOUSE and t1.item = t2.item
full join T1110C t3 on t1.WAREHOUSE = t3.WAREHOUSE and t1.item = t3.item
) tt1
  • 运行结果

在这里插入图片描述

③分别对其余三列进行if判空

select coalesce(t1w,t2w,t3w),if(t1Q is null,0,t1Q),if(t2Q is null,0,t2Q),if(t3Q is null,0,t3Q) from 
(
select t1.WAREHOUSE as t1w,t2.WAREHOUSE as t2w,t3.WAREHOUSE as t3w,t1.QTY as t1Q,t2.QTY as T2Q,t3.QTY as T3Q from T1110A t1 full join T1110B t2 on t1.WAREHOUSE = t2.WAREHOUSE and t1.item = t2.item
full join T1110C t3 on t1.WAREHOUSE = t3.WAREHOUSE and t1.item = t3.item
) tt1
  • 运行结果

在这里插入图片描述

20211123

11、工资部门汇总表

（1）需求

  • 源表
    在这里插入图片描述
  • 目标表
    在这里插入图片描述
  • 建表语句&数据
create table money_sum(
name     string
,day_money    int
,week_money   int
,month_money    int
,department   string
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'

INSERT INTO money_sum VALUES('张三',100,900,2700,1),('李四',120,680,2900,1),('王五',110,850,3000,2),('马六',120,790,2800,2);

（2）代码实现

①先union all获取所有数据

select * from( 
select "总计" as name,sum(day_money) as day_money,sum(week_money) as week_money,sum(month_money) as money_sum, from  money_sum 
union
select concat(department,"组") as name,sum(day_money) as day_money,sum(week_money) as week_money,sum(month_money),department as money_sum from  money_sum group by department
union
select name,day_money,week_money,month_money,department from money_sum) as tmp
  • 运行结果
    在这里插入图片描述
    （2）将数据进行排序
select * from( 
select "总计" as name,sum(day_money) as day_money,sum(week_money) as week_money,sum(month_money) as money_sum,'9999' as department from  money_sum 
union
select concat(department,"组") as name,sum(day_money) as day_money,sum(week_money) as week_money,sum(month_money),department as money_sum from  money_sum group by department
union
select name,day_money,week_money,month_money,department from money_sum) as tmp
order by department,name desc
  • 运行结果
    在这里插入图片描述

最后

以上就是野性灯泡为你收集整理的HQL笔试题总结（持续更新中）日期：202111162021111720211118的全部内容，希望文章能够帮你解决HQL笔试题总结（持续更新中）日期：202111162021111720211118所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(56)

相关文章

hive hql 窗口函数 练习
hive hql 窗口函数 练习
Hql查询案例三 ： 连续销售记录查询案例数据需求实现
Hql查询案例三 ： 连续销售记录查询案例数据需求实现
HQL中常用的函数以下是我最近写sql常用的一些函数（不全），后续会持续更新整理
HQL中常用的函数以下是我最近写sql常用的一些函数（不全），后续会持续更新整理
hql题及答案
hql题及答案
HQL笔试题总结（持续更新中）日期：202111162021111720211118
HQL笔试题总结（持续更新中）日期：202111162021111720211118
hive 每个用户连续登陆的最大天数？
hive 每个用户连续登陆的最大天数？
Hive sql : 查询连续登录天数1.问题描述2.在Hive中建表3.查询最大连续登录天数
Hive sql : 查询连续登录天数1.问题描述2.在Hive中建表3.查询最大连续登录天数
hiveSql 实现计算用户的最大连续登录天数 & 创建断连后的连续序号1.需求一：2.需求二
hiveSql 实现计算用户的最大连续登录天数 & 创建断连后的连续序号1.需求一：2.需求二

评论列表共有 0 条评论

立即
投稿
返回
顶部