hivesql 行转列，列转行（分列名和字符串转换）

情况一：列名转换

行转列

select name,

sum(case subject when 'Math' then score else 0 end) as Math,

sum(case subject when 'Chinese' then score else 0 end) as Chinese,

sum(case subject when 'English' then score else 0 end) as English

from tab_scores

group by name

列转行

select name,'Math',Math from tab_scores

union all

select name,'English',English from tab_scores

union all

select name,'Chinese',Chinese from tab_scores

情况二：字符串转换

 

select name,concat_ws(',',collect_set(hobby)) as hobby

from hobbyInfo group by name

Concat()函数用于将多个字符串连接成一个字符串。返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。可以有一个或多个参数。

    hive> select concat('a','b');

    ab

    hive> select concat('a','b',null);

    NULL

concat_ws函数

    是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。

    CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。

    concat_ws函数在连接字符串的时候，只要有一个字符串不是NULL，就不会返回NULL。  

  hive> select concat_ws('-','a','b');

    a-b

    hive> select concat_ws('-','a','b',null);

    a-b

    hive> select concat_ws('','a','b',null);

    ab

group_concat函数
group_concat()会计算哪些行属于同一组，将属于同一组的列显示出来。要返回哪些列，由数参数(就是字段名)决定。分组必须有个标准，就是根据group by指定的列进行分组。

+----------+----+--------------------------+

| locus    | id | day                      |

+----------+----+--------------------------+

| 27       |  1 | Unpublished              |

| 27       |  2 | Submitted (20-11-2020)   |

| 64       | 23 | Unpublished              |

| 64       | 24 | Submitted (31-10-2020)   |

+----------+----+--------------------------+

 SELECT locus,GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') FROM info WHERE locus IN('27','64') GROUP BY locus;

 +----------+----------------------------------------------------------+

| locus    | GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') |

+----------+----------------------------------------------------------+

| 27       | 2_1                                                      |

| 64       | 24_23                                                    |

+----------+----------------------------------------------------------+

collect_list和collect_set

他们都是将分组中的某列转为一个数组返回，不同的是collect_list不去重，而collect_set去重。
最终的返回值：

1. collect_list()：有序不去重

select

  t.id,

  concat_ws(',', collect_list(t.class))

from

  temp t

group by

  t.id

1. collect_set()：无序去重

select

  t.id,

  concat_ws(',', collect_set(t.class))

from

  temp t

group by

  t.id

若是想使用collect_set使结果有序，可以采用：

select

  t.id,

  concat_ws(',', sort_array(collect_set(t.class), false))

from

  temp t

group by

  t.id

列转行

split():

将一个字符串按照指定字符分割，结果为一个array；

split(str,‘,’)，将字符串str按逗号进行分割得到一个数数组

explode():

炸裂函数，将一列复杂的array或者map拆分为多行,它的参数必须为map或array；

explode(arr) ,将数组arr拆分为多行的数据

lateral view:

lateral view udtf(字段名)表别名/表临时名as列别名/列临时名。lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，lateralview再把结果组合，产生一个支持别名表的虚拟表。

select a.name,tabHobby.colHobby as hobby from hobbyInfo a

lateral view explode(split(a.hobby,',')) tabHobby as colHobby

-- tabHobby是侧视图的别名，colHobby是解析出来的字段名，#注意，as不能省略