String process in SQL

废话不多说,这里简单记三个常用的 String 处理的函数,简单来说就是把行变成了列。

CONCAT

1
2
select concat('11', '22', '33');
→ 112233

如果有任意一个为 NULL,则返回 NULL。

CONCAT_WS

1
Generic UDF for string function CONCAT_WS(sep, [string | array(string)]+). This mimics the function from MySQL http://dev.mysql.com/doc/refman/5.0/en/string-functions.html# function_concat-ws
1
2
select concat_ws(',', '11', '22', '33');
→ 11,22,33
1
2
3
4
5
6
7
8
9
10
11
12
|id|name|
|1 | 10 |
|1 | 20 |
|1 | 20 |
|2 | 20 |
|3 | 200|
|3 | 500|
select id, concat_ws(',', name) as name group by id;
→ |id|name |
|1 |10,20,20|
|2 |20 |
|3 |200,500 |

如果拼接字段不是 String 类型,转换成 String 即可,

1
select name, concat_ws(',', cast(id as string)) as id from test group by name;

GROUP_CONCAT

GROUP_CONCAT 中可以使用 DISTINCT 和 ORDER BY。

1
2
3
4
5
6
7
8
9
10
11
12
|id|name|
|1 | 10 |
|1 | 20 |
|1 | 20 |
|2 | 20 |
|3 | 200|
|3 | 500|
select id, group_concat(distinct name order by name asc separator ';') from test group by id;
→ |id|name |
|1 |10;20 |
|2 |20 |
|3 |200;500 |

但是,hive 中没有 GROUP_CONCAT,🙄,所以正确的打开方式是这样的,

1
select name, concat_ws(',', collect_set(cast(id as string))) as id from test group by name;

collect_set,利用了集合本身的特性就已经去掉重复啦。

FYI

Hive JavaDoc 2.3.2