hql练习题不定期更新

hql练习题

习题1: 展示合规产品的开始时间和结束时间

表一
产品 合规 日期
1 1 20201201
1 1 20201202
1 1 20201203
1 0 20201204
1 0 20201205
1 1 20201206
1 1 20201207

表二
产品 合规的产品 开始时间 结束时间
1 1 20201201 20201203
1 1 20201206 20201207

1为合规 0为不合规

思路:
考虑到多行变少行,并且属性有合并的迹象,感觉是是一个分组聚合的表格
1.首先不需要展示为0的数据,可以过滤掉
2.从开始和结束时间,联想到分组的最大值和最小值
3.一段连续相差1的数,想要得到统一的数据,可以引入序号来实现

 1. 建表
create table if not exists lyc_test1_product(
product		string	comment '产品'
complance   string	comment '合规'
datetime    string	comment '日期'
)comment'产品检验日程表'
row format delimited fields terminated by '\001' lines terminated by '\n' stored as rcfile;

 2. 插入数据

insert into  lyc_test1_product values(1,1,20201201);
insert into  lyc_test1_product values(1,1,20201201);
insert into  lyc_test1_product values(1,1,20201201);
insert into  lyc_test1_product values(1,0,20201201);
insert into  lyc_test1_product values(1,0,20201201);
insert into  lyc_test1_product values(1,1,20201201);
insert into  lyc_test1_product values(1,1,20201201);

 3. 解答

select 
	product
	complance
	min(datetime)
	max(datetime)
from
	(
	 select 
			product
			complance
			datetime
			row_number()over (partition by product order by datetime) as pd
	from lyc_test1_product
	where complance <> 0
	)a group by a.product,a.complance,(cast(a.datetime as int) - a.pd)

习题2 将一张工资表里面的每一条以五万为界限拆分成多条数据

famount fname
120000 bb
240000 aa
fname famount fb famount_x
aa 240000 1 50000
aa 240000 2 50000
aa 240000 3 50000
aa 240000 4 50000
aa 240000 5 40000
bb 240000 1 50000
bb 240000 2 50000
bb 240000 3 20000

思路:这感觉是一个行转列的形式,
1.如何划分成一个行转列
2.一个金额可以分成多少份
3.字段第三列明显是一个分组排序
4.根据排序减去相应的金额可以得到结果

1 建表
create table if not exists lyc_test_famount(
	 famount int comment '金额'
	,fname string comment '名称'
)comment '工资表'
row format delimited fields terminated by '\001'
lines terminated by '\n' stored as textfile;

2 插入数据
insert into lyc_test_famount values(240000,'aa');
insert into lyc_test_famount values(120000,'bb');

3 解题:
select 
		 b.nums as fname
		,b.famount
		,b.fb
		,case when (b.famount-(b.fb-1)*50000) > 50000
			 then 50000
			 else (b.famount-(b.fb-1)*50000)
			 end
  from	
	  (
	   select 
			 nums
			,famount
			,row_number()over(partition by nums order by famount) as fb
			from
			    (
				 select
					   concat(fname,repeat(concat(',',fname),cast(if(famount%50000=0,famount/50000,floor(famount/50000)-1)as int))) as fnames
					   famount
					   from lyc_test_famount
				 )a lateral view explode(split(a.fnames,',') num as nums
	  )b

运用到的一些函数
floor(小数) 向下取整,就是不大于整数的最大整数
repeat(‘字符串’,个数) 可以将一个字符串复制成若干个 repeat(aa,4)=aaaaaaaa
lateral view explode(split(字段,分隔符)) num 行转列函数

习题三 单列变多列的列传行

表一：
a
b
c
d
e
f
g
h
i
j
k
l
m
n

表二
gp n1 n2 n3 n4 n5
1 a b c d e
2 f g h i j
3 k l m n

一列变多列的列转行，本来一定会想到concat，但是本次使用的是collect_list,
聚合函数的转化，可以将聚合看成是队列

create table if not exists lyc_test_name(
name string comment '名称'
)comment '字母名称表'
row format delimited fields terminated by '\001'
lines terminated by '\n'
stored as textfile;

insert into lyc_test_name values('a');
insert into lyc_test_name values('b');
insert into lyc_test_name values('c');
insert into lyc_test_name values('d');
insert into lyc_test_name values('e');
insert into lyc_test_name values('f');
insert into lyc_test_name values('g');
insert into lyc_test_name values('h');
insert into lyc_test_name values('i');
insert into lyc_test_name values('j');
insert into lyc_test_name values('k');
insert into lyc_test_name values('l');
insert into lyc_test_name values('m');
insert into lyc_test_name values('n');

select 
	   aa.id
	  ,collect_list(aa.name)[1] as n1
	  ,collect_list(aa.name)[2] as n2
	  ,collect_list(aa.name)[3] as n3
	  ,collect_list(aa.name)[4] as n4
	  ,collect_list(aa.name)[5] as n5
from

	(select 
		ceil(a.id/5) as gp,
		a.name as name
	from
		(
			select row_number() over(order by name) as id,name from lyc_test_name
		)a
	)aa group by aa.gp