hive的sql查询语句和题练习

最新推荐文章于 2023-06-16 00:01:58 发布

一把秀儿

最新推荐文章于 2023-06-16 00:01:58 发布

阅读量229

点赞数

分类专栏： linux和hadoop生态

本文链接：https://blog.csdn.net/m0_52106226/article/details/110449661

版权

linux和hadoop生态专栏收录该内容

7 篇文章 0 订阅

订阅专栏

查询语法

show  functions  查看系统中 支持的函数
desc function  加想要查的函数     查询这个函数怎么用
if(1==1,1,0)    类似于三维运算符   表达式一成立执行1,不成立执行0
nvl(comm,0)   如果comm是null就返回0,如果不是null就返货comm原本的默认值
greatest(jb,jj,tc)    返回里面最大的一个数
case 2
when 1 then 'jb'
when 2 then 'jj'
when 3 then 'tc'  返回跟case后面一样的数的then后面的值   只执行一个 这个就是返回jj
cas 后面不可以写函数   如果有函数直接写写成when 2=1 then 'jb'也可以
select
sal ,
case
when sal <=1000 then 'ds'
when sal >1000 and sal <=2000 then 'pm'
when sal >2000 then 'xk'
else 'boss'
end as tag
也可以像如上不等值的用法

员工表题练习

先创建表3张
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
10 ACCOUNTING 1700
20 RESEARCH 1800
30 SALES 1900
40 OPERATIONS 1700
1700 Beijing
1800 London
1900 Tokyo

-- 员工表 
create  table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string, 
sal double, 
comm double,
deptno int)
row format delimited fields terminated by '\t';
load data local  inpath "/data/emp.txt"  into table emp ;

-- 部门表 
create table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';
load data local  inpath "/data/dept.txt"  into table dept ;

-- 位置
create table if not exists tb_local(
loc int,
loc_name string
)
row format delimited fields terminated by '\t';
load data local  inpath "/data/location.txt"  into table tb_local ;

员工表

+------------+------------+------------+----------+---------------+----------+-----------+-------------+
| emp.empno  | emp.ename  |  emp.job   | emp.mgr  | emp.hiredate  | emp.sal  | emp.comm  | emp.deptno  |
+------------+------------+------------+----------+---------------+----------+-----------+-------------+
| 7369       | SMITH      | CLERK      | 7902     | 1980-12-17    | 800.0    | NULL      | 20          |
| 7499       | ALLEN      | SALESMAN   | 7698     | 1981-2-20     | 1600.0   | 300.0     | 30          |
| 7521       | WARD       | SALESMAN   | 7698     | 1981-2-22     | 1250.0   | 500.0     | 30          |
| 7566       | JONES      | MANAGER    | 7839     | 1981-4-2      | 2975.0   | NULL      | 20          |
| 7654       | MARTIN     | SALESMAN   | 7698     | 1981-9-28     | 1250.0   | 1400.0    | 30          |
| 7698       | BLAKE      | MANAGER    | 7839     | 1981-5-1      | 2850.0   | NULL      | 30          |
| 7782       | CLARK      | MANAGER    | 7839     | 1981-6-9      | 2450.0   | NULL      | 10          |
| 7788       | SCOTT      | ANALYST    | 7566     | 1987-4-19     | 3000.0   | NULL      | 20          |
| 7839       | KING       | PRESIDENT  | NULL     | 1981-11-17    | 5000.0   | NULL      | 10          |
| 7844       | TURNER     | SALESMAN   | 7698     | 1981-9-8      | 1500.0   | 0.0       | 30          |
| 7876       | ADAMS      | CLERK      | 7788     | 1987-5-23     | 1100.0   | NULL      | 20          |
| 7900       | JAMES      | CLERK      | 7698     | 1981-12-3     | 950.0    | NULL      | 30          |
| 7902       | FORD       | ANALYST    | 7566     | 1981-12-3     | 3000.0   | NULL      | 20          |
| 7934       | MILLER     | CLERK      | 7782     | 1982-1-23     | 1300.0   | NULL      | 10          |
+------------+------------+------------+----------+---------------+----------+-----------+-------------+

部门表
+--------------+-------------+-----------+
| dept.deptno  | dept.dname  | dept.loc  |
+--------------+-------------+-----------+
| 10           | ACCOUNTING  | 1700      |
| 20           | RESEARCH    | 1800      |
| 30           | SALES       | 1900      |
| 40           | OPERATIONS  | 1700      |
+--------------+-------------+-----------+

位置
+---------------+--------------------+
| tb_local.loc  | tb_local.loc_name  |
+---------------+--------------------+
| 1700          | Beijing            |
| 1800          | London             |
| 1900          | Tokyo              |
+---------------+--------------------+

1) 按照部门编号分组 聚合工资
select
deptno,
sum(sal+if(comm is null , 0 , comm))  --用sum聚合函数   再用if语句
from
emp
group by --分组
deptno ;

1) 按照部门编号分组 聚合工资  第二种方法
select
deptno,
sum(sal+nvl(comm,0)) as amount     --用nvl方法 
from
emp
group by 
deptno ;

-- 部门名称和总薪资
因为在两个表里  所以要将两个表关联起来
select
t.deptno ,    --返回t表的deptno
dept.dname,   --返回dept表的dname
t.amout       -- 返回t表的amout
from
	(select 
	deptno,                        --内表
	sum(sal-nvl(comm,0))  amout    --先求出部门分组和总薪资   在取个amout别名
	from                           --因为部门名在dept表上所以要关联一下
	emp
	group by
	deptno
	) t    --起的别名
join     --关联dept表  
dept     --表明 join 表名  on   关联条件
on      
t.deptno = dept.deptno ;  --关联条件 t表的deptno等于dept表的 deptno

-- 部门名称和总薪资   的另一种写法
	with t as (
	select
	deptno,
	sum(sal+nvl(comm,0)) as amount     --先把内表拿出来写好了起个别名然后直接用
	from
	emp
	group by 
	deptno
	)

select
t.deptno ,
dept.dname ,
t.amount
from
t          --就是上面写好的内表的别名直接拿过来关联
,
dept
where
t.deptno = dept.deptno;

求  部门名称  地址  岗位  姓名  工资  最高的
因为有重名的所以不能把名字也group by 分组了 所以只能先求前几个在关联一下员工表
注意  分组只能返回分组的东西和聚合函数  返回不了其他分组
select 
dept.dname ,
tb_local.loc_name ,    --返回的数据
t1.job ,
t1.max_sal
from 
	(select
	deptno ,
	job ,
	max(sal)  max_sal     --max函数求最高工资
	from
	emp
	group by deptno , job   --把deptno和job分组提取出来
	)t1
join
dept         --关联dept表
join
tb_local     --关联tb_lacal 表
on 
t1.deptno = dept.deptno   --关联条件
and
dept.loc = tb_local.loc ;   --关联条件

再关联上emp表提取name
select
t.* ,        --返回t表的所有数据  和emp 表的name
emp.ename
from
t       --用上面的表 with t as ()  取个别名叫  t
join    --关联emp表
emp
on
t.deptno = emp.deptno    --把几个关联条件都写上
and
t.job = emp.jobt
and
t.max_sal = emp.sal ;

练习sql题二

先键三张表
1,2000,3000,1500,1
2,5000,500,1000,2
3,1500,1000,3000,2
4,3000,6000,8000,3
5,1500,2000,1800,1
6,2500,1000,1900,1
1,销售
2,技术
3,行政
1,zs,M,28
2,ww,F,36
3,zl,F,48
4,pp,M,44
5,wb,M,32
6,TQ,F,32

gz表
drop  table gz ;
create table gz(
uid int,
jb int,
jj int,
tc int,
deptno int
)
row format delimited fields terminated by ",";
load data local inpath "/data/biao/gz.txt" into table gz;

bm表
drop  table bm ;
create table bm(
deptno string ,
name string
)
row format delimited fields terminated by ",";
load data local inpath "/data/biao/bm.txt" into table bm;

yg表
create table yg(
uid int,
name string,
gender string,
age int
)
row format delimited fields terminated by ",";
load data local inpath "/data/biao/yg.txt" into table yg;

-- 求出公司中每个员工的姓名 和 三类收入中最高的那种收入的类型
select
name,
greatest(jb,jj,tc),  --greatest返回最大的一个值
case 
when greatest(jb,jj,tc)=jb then 'jb'
when greatest(jb,jj,tc)=jj then 'jj'   --三个等于谁就返回谁
when greatest(jb,jj,tc)=tc then 'tc'
end as qian
from(
	select
	yg.name ,
	gz.jb ,
	gz.jj ,    --先返回员工姓名和三类收入
	gz.tc 
	from
	yg
	join
	gz
	on
	yg.uid=gz.uid
)  a;

--求每个部门的薪资总和
select
b.name ,    --返回部门  和薪资和
t.sum_sal
from(
select
deptno ,
sum(jb+jj+tc) sum_sal   --先返回gz表的deptno和工资和
from
gz
group by deptno
) t
join           --再关联bm表
bm
on t.deptno = bm.deptno ;

求公司中每个岗位不同性别员工薪资总和
select
t.* ,
bm.name          --返回岗位 工资和  性别
from(
	select 
	gz.deptno,
	yg.gender,
	sum(jb+jj+tc) sum_sal
	from                  --先关联gz表和yg表返回性别,工资和岗位编号
	gz                     --返回岗位编号是为了能和bm表关联
	join
	yg
	on
	gz.uid=yg.uid
	group by
	gz.deptno ,yg.gender) t
join
bm
on
bm.deptno = t.deptno ;

--求公司中不同性别 不同年龄段(20-30.31-40.41.50)的员工薪资总和
select
gender , age_range , --返回性别 年龄段 工资和
sum(sum_sal)
from(
	select
	yg.gender,
	yg.age,
	gz.jb+gz.jj+gz.tc as sum_sal ,
	case
	when age >=20 and age < 30 then '20-30'
	when age >=30 and age < 40 then '30-40'  --再后面加一列年龄段
	when age >=40 and age < 50 then '40-50'
	else '下岗'
	end as age_range
	from
	yg                  --先关联yg和gz表返回性别 年龄  工资和
	join
	gz
	on
	yg.uid =gz.uid) t
group by gender ,age_range ; --把年龄段再分组 工资就可以sum了

一把秀儿

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
hive的sql查询语句和题练习

查询语法show functions 查看系统中支持的函数desc function 加想要查的函数查询这个函数怎么用if(1==1,1,0) 类似于三维运算符表达式一成立执行1,不成立执行0nvl(comm,0) 如果comm是null就返回0,如果不是null就返货comm原本的默认值greatest(jb,jj,tc) 返回里面最大的一个数case 2when 1 then 'jb'when 2 then 'jj'when 3 then 'tc
复制链接

扫一扫

专栏目录