赞
踩
目录
在大数据开发中有时会有这样的需求:将多条数据中的某个字段通过排序拼接为一个字段。
- name oid
- 赵一 80
- 钱二 190
- 孙三 40
-
- 得到结果:190|80|40
- 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
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。