Hive HQL面试题(group by和case when)（一）

HQL面试题（一）

1. 数据准备

1. gz.txt 用户工资组成表

用户id（uid）, 基本工资（jb） 奖金（jj） 提成（tc） 部门编号（ deptno）

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

2. bm.txt 部门表

1,销售
2,技术
3,行政

3. yg.txt 员工信息表

uid name gender age

1,zs,M,28
2,ww,F,36
3,zl,F,48
4,pp,M,44
5,wb,M,32
6,TQ,F,32

2. 建表并到导入数据到Hive数据表中

1. 建立数据库db_company

create database db_company;

use db_company;

2. 建表

# 在db_company 创建工资表
create table tb_gz(
uid int,
jb int,
jj int,
tc int,
deptno int
)
row format delimited fields terminated by ",";

# 将linux节点服务器上本地文件加载到hive数据表中
load data local inpath "/root/hive_sql/gz.txt" into table gz;

# 建立部门表
create table bm(
deptno string ,
name string
)
row format delimited fields terminated by ",";

# 将本地文件导入数据表中
load data local inpath "/root/hive_sql/bm.txt" into table bm;

create table yg(
uid int,
name string,
gender string,
age int
)
row format delimited fields terminated by ",";

load data local inpath "/root/hive_sql/yg.txt" into table yg;

3. 表格建立后，数据如下

+---------+--------+--------+--------+------------+
| gz.uid  | gz.jb  | gz.jj  | gz.tc  | gz.deptno  |
+---------+--------+--------+--------+------------+
| 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          |
+---------+--------+--------+--------+------------+


+------------+----------+
| bm.deptno  | bm.name  |
+------------+----------+
| 1          | 销售       |
| 2          | 技术       |
| 3          | 行政       |
+------------+----------+


+---------+----------+------------+---------+
| yg.uid  | yg.name  | yg.gender  | yg.age  |
+---------+----------+------------+---------+
| 1       | zs       | M          | 28      |
| 2       | ww       | F          | 36      |
| 3       | zl       | F          | 48      |
| 4       | pp       | M          | 44      |
| 5       | wb       | M          | 32      |
| 6       | TQ       | F          | 32      |
+---------+----------+------------+---------+

3. 题目

3.1 求出公司中每个员工的姓名 和 三类收入中最高的那种收入的类型

1. 思路，

• 需要员工名字，收入，不涉及部门，所以yg表和gz表join
• 收入最高，也就是需要求出三个收入部分最高的，查阅资料可以知道有一个函数greatest
• 需要知道哪个部分最高，使用case when

2. sql

• 第一步，求出名字以及每个人对应工资中最高的部分信息

select
yg.name,
greatest(gz.jb, gz.jj, gz.tc) as highest
from
gz
join
yg
on
gz.uid=yg.uid;

+----------+----------+
| yg.name  | highest  |
+----------+----------+
| zs       | 3000     |
| ww       | 5000     |
| zl       | 3000     |
| pp       | 8000     |
| wb       | 2000     |
| TQ       | 2500     |
+----------+----------+