sql按逗号分割字符串_SQL技巧--一个订单综合状态的统计难题-CSDN博客

情景说明

假定有一张表，表名：t_ord 订单表

样本数据如下：

其中综合状态码是一个100位长度的定长字符串，为了排版显示方便，状态码每20位手动折行，原本数据是连续的，中间没有换行符。

这100位字符串，其中每一位代表一个业务含义，比如：

第1位：‘0’代表未使用，‘1’代表PC下单，‘2’代表APP下单，

第2位：‘1’代表使用优惠券，‘2’代表未使用优惠券，

...

以此类推，直到100位，每一位代表一个业务在订单上的状态。

声明：以上的表、字段、数据内容是均为虚构。

业务需求来了

业务描述是：统计昨天每个业务所有状态码对应的订单数量。

即统计昨天PC下单的订单数量，APP下单的订单数量，…… ，即一次性算出所有业务模式对应的订单量数据。

解题思路

1. 需求分析

利用【综合状态码】算出所有业务形态对应的订单量。
业务描述翻译到状态码上就是：统计前一天【综合状态码】每一位上不同状态码涉及的订单个数。
举例说明，希望得到的结果是：第1位代码为‘1’相关的订单是__个，第1位代码为‘2’相关的订单为__个, …… ，第100位代码为’7‘的订单是__个，第100位代码为‘8‘的订单是__个。

2. 分析思路

状态码是个长字段，可以拆成100个字符，分别来计算；
对这100个字符进行列传行操作；
需要记录每个字符所在的位置，第1位~第100位；
根据这些位置和字符值，统计订单数量。

3. 用到的sql技巧

列传行
构建字符位置
正则表达式
字符拆分

Hive写法-初始版

先交代思路。当时试了不少方法，只讲hive能支持的方法，代码看起来比较笨拙，不过思路还是统一的。

第一步，将100个字符进行预处理：

将ord_flag重新构建成带位置信息的字符串。根据表中第一行样本数据，构建过程是这样的：

样本数据，共100 个字符：10700202030……301

构建带位置的数据，比如

第一个字符，记位置为01，其值为1，记为01-1

第二个字符，记位置为02，其值为0，记为02-0

第三个字符，记位置为03，其值为7，记为03-7

以此类推，

第一百个字符为‘1’，记位置为100，其值为1，记为100-1

将这构建出来的带有位置的字符串连起来，用逗号分割，就是下面的样子：

‘01-1，02-0，03-7……98-3，99-0，100-1’

第二步，列传行：

将上面构建的长字符串，拆成行。

第一行，值为‘01-1’

第二行，值为‘02-0’

第三行，值为‘03-7’

第一百行，值为‘100-1’

第三步，进行分组计数，计数结果即为业务所需的数据

前两步是拿订单表的一行数据举例，实际上，每行数据都会按这个方法处理。

这样，我们分别计数就会得到所需结果。

最终结果可能如下：

01-1 53 --解读：第一位为1，即pc端下单的订单数量为53

01-2 107 --解读：第一位为2，即app端下单的订单数量为107

03-1 52 --解读：第三位为1，即xx业务代码值为1的订单数量为52

03-7 98 --解读：第三位为7，即xx业务代码值为7的订单数量为98

……

第一步预处理技术点：

concat——连接字符串

substr——取某个位置开始的字符串

第二步列传行技术点：

LATERAL VIEW explode——列传行

split——根据逗号，将字符串拆成数组

第三步分组计数技术点：

count(1)——计数

group by ——分组

SQL如下：

select ord_flag_split,count(1) as num from

(select

concat(

'01-',substr(ord_flag,1,1),',',

'02-',substr(ord_flag,2,1),',',

'03-',substr(ord_flag,3,1),',',

'04-',substr(ord_flag,4,1),',',

'05-',substr(ord_flag,5,1),',',

'06-',substr(ord_flag,6,1),',',

'07-',substr(ord_flag,7,1),',',

'08-',substr(ord_flag,8,1),',',

'09-',substr(ord_flag,9,1),',',

'10-',substr(ord_flag,10,1),',',

'11-',substr(ord_flag,11,1),',','

……省略中间部分……

'99-',substr(ord_flag,99,1),',',

'100-',substr(ord_flag,100,1)

) ord_flag_deal

from t_ord

where dt = '昨天的日期'

) b

LATERAL VIEW explode(split(b.ord_flag_deal,',')) adTable as ord_flag_split

where substr(ord_flag_split,4) <> '0'--排除掉未被使用的

group by ord_flag_split

缺点：手工编写拼凑1-100位的信息，方法比较原始，代码看起来比较臃肿。

Presto写法--改进版

由于集群支持了presto引擎，试着用presto重写这块逻辑，发现presto和Hive还是有一定差异的。

presto列转行不是用LATERAL VIEW explode ，presto列转行需要用cross join unnest。
presto在正则的支持上和Hive很多不一样的地方，这里也是一个坑，好在摸清楚了。

代码修改如下：

select

position,

value，

count(1) as num

from

t_ord

cross join unnest(regexp_extract_all(

'P001P002P003P004P005P006P007P008P009P010P011P012P013P014P015P016P017P018P019P020P021P022P023P024P025P026P027P028P029P030P031P032P033P034P035P036P037P038P039P040P041P042P043P044P045P046P047P048P049P050P051P052P053P054P055P056P057P058P059P060P061P062P063P064P065P066P067P068P069P070P071P072P073P074P075P076P077P078P079P080P081P082P083P084P085P086P087P088P089P090P091P092P093P094P095P096P097P098P099P100'

,'Pd{3}|'), split( regexp_replace(ord_flag,'(w)','$1,'))

) adTable(position,value)

where

dt= sysdate( - 1) --昨天

andvalue <> '0' --排除掉未被使用的

group by

position,

value

写法解析：

依旧是将ord_flag重新构建成带位置信息的字符串，不过这次写法更清晰，使用了正则。
构建位置信息，使用 P001P002P003……P100 这样的方法，P001就代表第1位，P100就代表第100位，这种构建方法比较省时间，用excel的序列直接就能快速拽出来。
正则表达式一：regexp_extract_all，使用'Pd{3}'，表示P开头，后面是三个数字，这样就能把所有位置拆成一个array。
正则表达式二：regexp_replace split( regexp_replace(ord_flag,'(w)','$1,'))，使用w代表任何一位可见字符，这样就能把ord_flag拆成一百位的array。
使用cross join unnest将两个array组合成两个字段的表adTable(position,value)，结果类似于是这样：

然后和t_ord关联，即可得出每个订单的100位订单状态
最后进行group by，即可得出结果。

转到presto上，并优化代码后，执行时间缩短为Hive的1/5，效果还是很明显的。

更难的问题来了

业务对订单状态进行了合并，有可能是多位合起来代表一个业务含义，比如

有以下位置特殊，是多位合起来代表一个业务含义，比如：

5-8位四位合起来是一个业务含义，有可能是 0001，有可能是 1309，这个是用户自定的；

60-62 两位合起来是一个业务含义；

63-64 两位合起来是一个业务含义……

这样我们之前的代码对这些多位码就无法处理了，必须根据业务进行改造。

解决思路

在上面sql的基础上，进行分段处理：

1-4位，每位代表一个业务状态，共4个业务状态，

5-8位，这四位代表一个业务状态，

9-59位，每位代表一个业务状态，共51个业务状态，

60-62位，这三位代表一个业务状态，

63-64位，这两位代表一个业务状态，

65-100位，每位代表一个业务状态，共36个业务状态

代码优化之后如下：

select

position,

value,

count(1) as position_value_count

from

t_ord a

cross join unnest(regexp_extract_all(

'P001P002P003P004P005P009P010P011P012P013P014P015P016P017P018P019P020P021P022P023P024P025P026P027P028P029P030P031P032P033P034P035P036P037P038P039P040P041P042P043P044P045P046P047P048P049P050P051P052P053P054P055P056P057P058P059P060P063P065P066P067P068P069P070P071P072P073P074P075P076P077P078P079P080P081P082P083P084P085P086P087P088P089P090P091P092P093P094P095P096P097P098P099P100'

, 'Pd{3}'),

split( concat(regexp_replace(substr(ord_flag,1,4),'(w)','$1,'),

substr(ord_flag,5,4),',',

regexp_replace(substr(ord_flag,9,51),'(w)','$1,'),

substr(ord_flag,60,3),',',

substr(ord_flag,63,2),',',

regexp_replace(substr(ord_flag,65,36),'(w)','$1,')

),',')

) adTable(position, value)

where

dt >= sysdate( - 1)

and value <> '0' --排除掉未被使用的

group by position,value

写法解析：

在重新构建位置信息的时候，带下划线的部分是特殊处理的。P005代表第5～8位，P060代表第60～62位，P063代表第63～64位。
在ord_flag按位拆分的时候，同样对这几个位置进行了特殊处理。
这里分段使用了正则表达式regexp_replace，这里是一个比较取巧的办法，regexp_replace(substr(ord_flag,1,4),'(w)','$1,')，实现的就是将第1～4位替换成逗号分割的字符串。'(w)'表示匹配任何一个字符，注意小括号。$1, 代表匹配到的这个字符，后面加上逗号。