需求
在许多业务场景中，需要处理用户的邮箱信息，为了探索不同邮箱的不同特定，便于对用户进行分类处理，需要对邮箱进行处理。
前、后缀分离
邮箱的格式大致是一致的
前缀@后缀
的样式。需要将邮箱进行分类。
明显看到他们前后缀通过
@
复合连接，将他们分别分离成两列即可。
select email_list[0] as pre_email , email_list[1] as pro_email
from (
select split(email,\'@\') email_list
from table a
) a
结果是：
| pre_email | pro_email |
|---|---|
| 123456 | qq.com |
| 123456 | 163.com |
此时可以查看，究竟有那些邮箱的后缀，计数是多少，便于进行分类。
select pro_email, count(*) cnt
from table
group by pro_email
前缀的处理
对于前缀，主要可以为以下几种类型
- 手机号：
13579246810
- 纯数字：
3123443242（
- 纯字母：
qwert
- 字母与数字：
xiaoming1982
- 其他：
xiaoming_19
分类代码如下：
case when pre_email rlike "^(\\+?86)?((13[0-9])|(14[5,7])|(15[0-3,5-9])|(17[0,3,5-8])|(18[0-9])|161|166|198|199|(147))\\d{8}$" then \'手机号\'
when pre_email rlike \'^\\d+$\' then \'纯数字\'
when pre_email rlike \'^[a-zA-Z]*$\' then \'纯字母\'
when pre_email rlike \'^[a-z0-9A-Z]+$\' then \'字母与数字\'
else \'其他\' end as pre_flag
后缀的处理
后缀主要是公司，可以根据使用的场景，查看那些邮箱用的比较多，定义对应的后缀汉语名称。
- 腾讯邮箱：
qq.com
qq.vip.com
foxmail.com
- 网易邮箱：
163.com
126.com
yeah.cn
- 电信邮箱：
139.com
189.com
分类代码如下：
case when pro_email rlike \'.*(163|126|yeah).*\' then \'网易邮箱\'
when pro_email rlike \'.*(sina).*\' then \'新浪邮箱\'
when pro_email rlike \'.*(qq|foxmail).*\' then \'腾讯邮箱\'
when pro_email rlike \'.*(sohu).*\' then \'搜狐邮箱\'
when pro_email rlike \'.*(139|189).*\' then \'电信邮箱\'
else \'others\' end as pro_flag
统计
这样就可以处理成不同的组合。进行单独、交叉统计。
-- 交叉统计
select pre_flag, pro_flag, count(*) cnt
from table
group by pre_flag, pro_flag