![e0302e4cab22a276db99c331e0690d1f.png](https://i-blog.csdnimg.cn/blog_migrate/7ff1e8ead94124da5423e1634d31d656.jpeg)
情景说明
假定有一张表,表名:t_ord 订单表
样本数据如下:
![d4f59c963b8c2ae797a27379b7c6b6e8.png](https://i-blog.csdnimg.cn/blog_migrate/33fb29128d984b6268cb7eec4dd46749.png)
其中综合状态码是一个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),结果类似于是这样:
![d55cc60851d8b896671d055d9ff61c72.png](https://i-blog.csdnimg.cn/blog_migrate/cf1935ae2adea60ad9990f0aa1d7b9e6.png)
- 然后和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, 代表匹配到的这个字符,后面加上逗号。
思考
业务方面:
- 业务的数据需求可能千差万别,但总归都有办法支持,兵来将挡,水来土掩。更多的是考验解决问题的思路,要多花时间梳理逻辑,会分解问题,剩下的是使用工具,我认为思路和逻辑占70%,工具占30%。
- 作为产品经理,数据分析方面可能无法投入太多精力,但还是建议产品经理有一定的数据操控技能。
技术方面:
- 大数据平台对sql的支持越来越完善,Hive,Presto,Spark目前经过大量验证可以在企业大力推广。
- 正则在解决问题中有独特的优势,不管是编程,还是数据分析,正则都是强大的工具。
- Hive和Presto有些差异,尤其在正则的支持方面,写法是不一样的,需要重点关注。
后来反思了一下,我这套代码应该不是最优的方法,还请给予指导,共同探讨。
——千文鱼