SQL 对邮箱数据的处理（分类统计）
需求
在许多业务场景中，需要处理用户的邮箱信息，为了探索不同邮箱的不同特定，便于对用户进行分类处理，需要对邮箱进行处理。
前、后缀分离
邮箱的格式大致是一致的 前缀@后缀 的样式。需要将邮箱进行分类。
| 123456@qq.com |
| xiaoming@163.com |
明显看到他们前后缀通过 @ 复合连接，将他们分别分离成两列即可。
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
版权声明：本文为heenhui2016原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。