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
