目录

一、前言

二、样例SQL

三、函数用法

四、思路解析

一、前言

在大数据开发中有时会有这样的需求：将多条数据中的某个字段通过排序拼接为一个字段。

name    oid
赵一    80
钱二    190
孙三    40

得到结果：190|80|40

二、样例SQL

select 
regexp_replace (concat_ws("|",sort_array (collect_set (concat_ws(":",concat(IF (order_id< 10,"000",IF (order_id< 100,"00",IF (order_id< 1000,"0",""))),order_id),oid)))),"\\d+\:","") oids 
from
(select 
oid,
row_number() over (PARTITION BY name ORDER BY oid DESC) order_id 
from a) a;

三、函数用法

1.regexp_replace(source, pattern, replace_string, occurrence)
● source：string类型，要替换的原始字符串。
● pattern：string类型常量，要匹配的正则模式，pattern为空串时抛异常。
● replace_string：string，将匹配的pattern替换成的字符串。
● occurrence：bigint类型常量，必须大于等于0，默认是0，大于0：表示将第几次匹配替换成replace_string，等于0：表示替换掉所有的匹配子串。其它类型或小于0抛异常。

2.concat_ws(separator,str1,str2,…)
concat_ws() 代表concat with separator，是concat()的特殊形式。第一个参数separator是其它参数的分隔符。会忽略所有的NULL值。

3.concat(str1,str2,…)
直接将参数拼接起来，和concat_ws的区别是没有专门放分隔符的参数。

4.sort_array
将列表进行升序排序
排序逻辑为按字母序排序，也就是说10会排在2之前。

5.collect_set
返回一个数组或者说是列转行，去除重复元素，与sort_array搭配可以实现升序排序。

6.collect_list
返回一个数组或者说是列转行，不去除重复元素，与sort_array搭配可以实现升序排序。

7.row_number() over (PARTITION BY X ORDER BY Y DESC) 
具有分组排序的功能
PARTITION BY：用于给结果集分组
ORDER BY：排序

四、思路解析

1.在内层SQL中获取到oid字段以及排序字段order_id。

select 
oid,
row_number() over (PARTITION BY name ORDER BY oid DESC) order_id 
from a

80     2
190    1
40     3

2.但是order_id不能直接用于排序拼接，原因是排序函数sort_array的排序逻辑是字母序升序排序，也就是10会排在2之前，因为10的首位1要比2小。所以这里用到concat将order_id填充成一个四位数字，10填充为0010，2填充为0002。这样我们就有了排序字段。

concat(IF (order_id< 10,"000",IF (order_id< 100,"00",IF (order_id< 1000,"0",""))),order_id)

3.将排序字段和结果字段oid用冒号作为分隔符拼接在一起。

concat_ws(":",concat(IF (order_id< 10,"000",IF (order_id< 100,"00",IF (order_id< 1000,"0",""))),order_id),oid)

0002:80
0001:190
0003:40

4.将排序字段和结果字段拼接之后的字段升序排序。这里的升序排序是按照冒号之前的四位数来排序的。用到函数sort_array、collect_set或者collect_list。

sort_array (collect_set (concat_ws(":",concat(IF (order_id< 10,"000",IF (order_id< 100,"00",IF (order_id< 1000,"0",""))),order_id),oid)))

0001:190
0002:80
0003:40

5.将升序排序之后的数据拼接起来。

concat_ws("|",sort_array (collect_set (concat_ws(":",concat(IF (order_id< 10,"000",IF (order_id< 100,"00",IF (order_id< 1000,"0",""))),order_id),oid))))

0001:190|0002:80|0003:40

6.将四位数和冒号替换为空。到这一步就得到了想要的结果。

regexp_replace (concat_ws("|",sort_array (collect_set (concat_ws(":",concat(IF (order_id< 10,"000",IF (order_id< 100,"00",IF (order_id< 1000,"0",""))),order_id),oid)))),"\\d+\:","")

190|80|40

7.如果想得到40|80|190的结果就把内层SQL中的order_id升序排列。

select 
oid,
row_number() over (PARTITION BY name ORDER BY oid ASC) order_id 
from a

80     2
190    3
40     1

8.需要注意的是oid不能为NULL，否则与四位数的order_id拼接之后就只剩下四位数。得不到最终想要的拼接结果。

concat_ws(':',0001,NULL)

得到结果是0001