目录
一、前言
在大数据开发中有时会有这样的需求:将多条数据中的某个字段通过排序拼接为一个字段。
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