题解 | #统计每种性别的人数#

select
    case
    when substring_index(profile,',',-1)='male' then 'male'
    when substring_index(profile,',',-1)='female' then 'female'
    end as gender,
    count(device_id) as number
from
    user_submit
group by
    gender;

解题思路：

步骤1：统计每种性别，group by gender

步骤2：gender来自profile的最后一部分，所以用到substring_index()函数

步骤3：利用case when 语句，加标签，如果substring_index(profile,',',-1)='male' then 'male'...