SQL技巧-一个订单综合状态的统计难题

场景题目

假定订单表如下，

表名：t_ord

其中综合状态码是一个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写法-初始版

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 >=sysdate(-1) --昨天

) 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

写法解析：

将ord_flag重新构建成带位置信息的字符串，并用逗号分割。构建完成的ord_flag_deal看起来可能是这样："01-1，02-0，03-1……99-8，100-0"。

将上面的ord_flag_deal拆成数组Array，使用的是split函数，根据逗号来分拆。

使用Hive列转行方法：LATERAL VIEW explode，将上面的数组转成一个表，结果类似于下表，实际就是把一个订单的状态拆成100行：

最后进行group by，即可得出结果。

缺点：手工编写拼凑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'

,'P\d|'), 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，使用'P\d'，表示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'

, 'P\d'),

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, 代表匹配到的这个字符，后面加上逗号。

思考

业务方面：

业务的数据需求可能千差万别，但总归都有办法支持，兵来将挡，水来土掩。更多的是考验我们解决问题的思路，要多花时间梳理逻辑，会分解问题，剩下的是使用工具，我认为思路和逻辑占70%，工具占30%

作为产品经理，数据分析方面可能无法投入太多精力，但还是建议产品经理有一定的数据操控技能。

技术方面：

大数据平台对sql的支持越来越完善，Hive，Presto，目前经过大量验证可以在企业大力推广。

正则在解决问题中有独特的优势，不管是编程，还是数据分析，正则都是强大的工具。

Hive和Presto有些的差异，尤其在正则的支持方面，写法是不一样的，需要重点关注。