hive的行列转换

-- 行转列的常规做法是，group by+sum(if())【或count(if())】
-- | year | month | amount |
-- | ---- | ----- | ------ |
-- | 1991 | 1     | 1.1    |
-- | 1991 | 2     | 1.2    |
-- | 1991 | 3     | 1.3    |
-- | 1991 | 4     | 1.4    |
-- | 1992 | 1     | 2.1    |
-- | 1992 | 2     | 2.2    |
-- | 1992 | 3     | 2.3    |
-- | 1992 | 4     | 2.4    |


--
-- -- 查询的结果
-- | year | m1   | m2   | m3   | m4   |
-- | ---- | ---- | ---- | ---- | ---- |
-- | 1991 | 1.1  | 1.2  | 1.3  | 1.4  |
-- | 1992 | 2.1  | 2.2  | 2.3  | 2.4  |

set hive.exec.mode.local.auto=true;
create table table2
(
    year   int,
    month  int,
    amount double
);
insert overwrite table table2
values (1991, 1, 1.1),
       (1991, 2, 1.2),
       (1991, 3, 1.3),
       (1991, 4, 1.4),
       (1992, 1, 2.1),
       (1992, 2, 2.2),
       (1992, 3, 2.3),
       (1992, 4, 2.4);
create table table3
(
    year int,
    m1   double,
    m2   double,
    m3   double,
    m4   double
);
insert overwrite table table3
values (1991, 1.1, 1.2, 1.3, 1.4),
       (1992, 2.1, 2.2, 2.3, 2.4);
select *
from table2;
select *
from table3;
--多行转多列
--case when写法
select year,
       sum(case when month = 1 then amount end) m1,
       sum(case when month = 2 then amount end) m2,
       sum(case when month = 3 then amount end) m3,
       sum(case when month = 4 then amount end) m4
--也可以使用max
from table2
group by year;

--if写法
select year,
       sum(if(month = 1, amount, 0)) m1,
       sum(if(month = 2, amount, 0)) m2,
       sum(if(month = 3, amount, 0)) m3,
       sum(if(month = 4, amount, 0)) m4
--也可以使用max
from table2
group by year;
--方法二
select year,
       arr[0] m1,
       arr[1] m2,
       arr[2] m3,
       arr[3] m4
from (select year,
             collect_list(amount) arr
      from table2
      group by year) a;

--多列转多行
select year, '1' as m1, m1 as amount
from table3
union
select year, '2' as m2, m2 as amount
from table3
union
select year, '3' as m3, m3 as amount
from table3
union
select year, '4' as m4, m4 as amount
from table3
order by year;

--多行转单列
create table table4
(
    year   int,
    month  int,
    amount double
);
insert overwrite table table4
values (1991, 1, 1.1),
       (1991, 1, 1.2),
       (1991, 1, 1.3),
       (1991, 1, 1.4),
       (1992, 2, 2.1),
       (1992, 2, 2.2),
       (1992, 2, 2.3),
       (1992, 2, 2.4);
--concat_ws 用于进行字符的拼接  参数1—指定分隔符 参数2—拼接的内容
--collect_set  它的主要作用是将某字段的值进行去重汇总 collect_list(不去重)
--cast 转换类型
select year, month, concat_ws(",", collect_list(cast(amount as string))) amount
from table4
group by year, month;
--单列转多行
create table table5(year int, month int, amount string);
insert overwrite table table5 values (1991,1,"1.1,1.2,1.3,1.4"),
                                     (1992,2,"2.1,2.2,2.3,2.4");


select year,month,tmp_table.*  from table5 lateral view explode(split(amount,',')) tmp_table;