Write a query that calculates the difference between the highest salaries found in the marketing and engineering departments. Output just the absolute difference in salaries.
Tables: db_employee, db_dept
--建表
use strata;
create table db_employee
(
id int
,first_name string
,last_name string
,salary int
,department_id int
)
row format delimited fields terminated by ',';
load data local inpath '/tmp/strata/db_employee.txt' overwrite into table db_employee;
create table db_dept
(
id int
,department string
)
row format delimited fields terminated by ',';
load data local inpath '/tmp/strata/db_dept.txt' overwrite into table db_dept;
--表结构
hive> select * from db_employee limit 5;
OK
db_employee.id db_employee.first_name db_employee.last_name db_employee.salary db_employee.department_id
10301 Keith Morgan 27056 2
10302 Tyler Booth 32199 3
10303 Clifford Nguyen 32165 2
10304 Mary Jones 49488 3
10305 Melissa Lucero 27024 3
hive> select * from db_dept;
OK
db_dept.id db_dept.department
1 engineering
2 human resource
3 operation
4 marketing
5 sales
6 customer care
--结果输出
with t as
(select department_id,max(salary) as max_sal
from db_employee
where department_id in
(select id from db_dept where department regexp 'engineering|marketing')
group by department_id)
select abs(collect_set(max_sal)[0]-collect_set(max_sal)[1]) from t;