挑战100天不停更之hive sql第1天 - 行列转换

🌿挑战100天不停更，刷爆 hive sql🧲

NUM: 第1天 - 行列转换

详情请点击🔗我的专栏🖲,共同学习,一起进步~

🧨不废话，刷题~~🧨

– # a – 年份 b – 部门 c – 绩效得分

-- 建表
CREATE TABLE t1 ( a string,b string,c DOUBLE);

INSERT INTO t1 VALUES ('2014','B',9), ('2015','A',8), ('2014','A',10), ('2015','B',7);

1,多行转多列

思路：年份分组，取出不同部门的绩效 年份（分组） - 部门A - 部门B

SELECT a,
       max(CASE
               WHEN b = 'A' THEN c
           END) col_A,
       max(CASE
               WHEN b = 'B' THEN c
           END) col_B
FROM t1
GROUP BY a;

此处也可通过部门分组,取出不同年份的部门成绩,列名为 部门（分组）-年份(14年)-年份(15年)

SELECT b,
       max(CASE
               WHEN a = '2014' THEN c
           END) col_2014,
       max(CASE
               WHEN a = '2015' THEN c
           END) col_2015
FROM t1
GROUP BY b;

2,多列转多行

基本思路：通过单独查出不同部门的数据,union all不同部门,查abc

CREATE TABLE t1_2 AS
SELECT a,
       max(CASE
               WHEN b = 'A' THEN c
           END) col_A,
          max(CASE
                  WHEN b = 'B' THEN c
              END) col_B
FROM t1
GROUP BY a;


SELECT a,
       b,
       c
FROM
  (SELECT a,
          'A' AS b,
          col_A AS c
   FROM t1_2
   UNION ALL SELECT a,
                    'B' AS b,
                    col_B AS c
   FROM t1_2) tmp;

3,同一部门会有多个绩效,多行转多列 表名t1_3

2014 年公司组织架构调整，导致部门出现多个绩效，业务及人员不同,无法合并算绩效，源表内容如下：

2014 B 9
2015 A 8
2014 A 10
2015 B 7
2014 B 6

– 思路：使用collect_set函数收集为一个集合，concat_ws然后保存为数组
**collect_set 为去重 **
concat_ws 通过逗号连接

CREATE TABLE t1_3 AS SELECT * FROM t1;

INSERT INTO t1_3 VALUES('2015','B',7),('2014','B',6);

select 
    a,
    max(CASE
        WHEN b="A" THEN c
    END) col_A,
    max(CASE
        WHEN b="B" THEN c
    END) col_B
FROM (select a,
      b,
      concat_ws(",",collect_set(cast(c AS string))) AS c
      FROM t1_3
      GROUP BY a,
               b)tmp
GROUP BY a;