select
(
    case when profile like '%,male' then 'male'
    else 'female' end
) as gender,count(device_id)
from user_submit
group by gender;

# 如果count里面是distinct device_id
# 结果的gender会反序，变成female在第一行，male在第二行，不知为何。