场景题目
假定订单表如下,
表名: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有些的差异,尤其在正则的支持方面,写法是不一样的,需要重点关注。
领取专属 10元无门槛券
私享最新 技术干货