问题描述:表中记录了各年份各部门的平均绩效考核成绩。
a – 年份
b – 部门
c – 绩效得分
数据如下(格式自由调整)
a b c
2014 B 9
2015 A 8
2014 A 10
2015 B 7
问题一:多行转多列
问题描述:将上述表内容转为如下输出结果所示:
a col_A col_B
2014 10 9
2015 8 7
问题二:如何将结果转成源表?(多列转多行)
问题描述:将问题一的结果转成源表,问题一结果表名为t1_2。
问题三:同一部门会有多个绩效,求多行转多列结果
问题描述:2014年公司组织架构调整,导致部门出现多个绩效,业务及人员不同,无法合并算绩效,源表内容如下:
2014 B 9
2015 A 8
2014 A 10
2015 B 7
2014 B 6
输出结果如下所示:
```sql
a col_A col_B
2014 10 6,9
2015 8 7
答案如下:
select current_database();
create database Interview;
--问题一: 多行转多列
-- 思路:首先以year进行分组,求每年的每个部分的绩效,
-- 然后使用一个函数但我们知道本门的编号的时候可以通过原表
-- 值之间对应的关系找到对应的绩效评分可以使用:
-- 某个函数名称(case when (条件) then (结果) end) 别名
use Interview;
create table t1(
year string,
dep string,
score int
)row format delimited
fields terminated by ":";
load data local inpath '/root/data/01.txt' into table t1;
select year,
max(case when dep="A" then score end) tol_A,
max(case when dep="B" then score end ) tol_B
from t1 group by year;
-- 问题二: 如何将问题一的结果转换成原表(多列转多行)
-- 思路:转换成原来的表我们需要拿到的字段有三个
-- year dep score
-- yesr 可以直接在问题一的结果表的基础上拿到。
-- dep 字段我们不能从结果的表中拿到,但是我们可以直接给他进行赋值
-- tol_A 就是需要拿到的绩效这个可以直接拿到
-- 使用union all 对查询的表进行拼接
select year,dep,score from
(select year,"A" as dep ,tol_A as score from (select year,
max(case when dep="A" then score end) tol_A,
max(case when dep="B" then score end ) tol_B
from t1 group by year) t1_2
union all
select year,"B" as dep ,tol_B as score from (select year,
max(case when dep="A" then score end) tol_A,
max(case when dep="B" then score end ) tol_B
from t1 group by year) t1_2) tmp;
-- 问题三:同一个部门会有多个的绩效,求多行转多列的结果
-- 思路:先将以时间和部门进行分组
-- 使用concat_ws()函数经score变为集合
-- 查询year ,使用case when then end 进行条件的赛选
-- (后边部分与为问题一的思路一致)
create table t1_2(
year string,
dep string,
score int
)row format delimited
fields terminated by ":";
load data local inpath '/root/data/01_2.txt' into table t1_2;
select year,
max(case when dep = "A" then c end) col_A,
max(case when dep = "B" then c end) col_B
from (select year,
dep,
concat_ws(",", collect_set(cast(score as string))) c
from t1_2
group by year, dep) tmp
group by year;