【SQL 03 统计每种性别的人数】

题目描述

现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个性别的
用户分别有多少参赛者，请取出相应结果

用户申请表 user_submit:

device_id	profile	blog_url
2138	180cm,75kg,27,male	http:/url/bigboy777
3214	165cm,45kg,26,female	http:/url/kittycc
6543	178cm,65kg,25,male	http:/url/tiger
4321	171cm,55kg,23,female	http:/url/uhksd
2131	168cm,45kg,22,female	http:/urlsydney

最终输出样式：

gender	number
male	2
female	3

题目分析

题目在于我们要在profile字段里提取出用户的相关性别信息；在profile数据包括：身高、体重、年龄、性别，且是以英文逗号作为区分，解决方案有：

1. SUBSTRING_INDEX 文本函数
2. LIKE 函数

SQL实现

（1）SUBSTRING_INDEX的写法：

SELECT SUBSTRING_INDEX(profile,",",-1) AS gender,
COUNT(*) AS number
FROM user_submit
GROUP BY gender;

（2）LIKE的写法1：

SELECT 
IF(profile LIKE '%female','female','male') AS gender,
COUNT(*) AS number
FROM user_submit
GROUP BY gender;

这意味找出所有包含female 的数据 认为是 female，其余的都是male；
（3）LIKE的写法2：

SELECT 
	(
	CASE WHEN profile LIKE '%,male' THEN 'male' 
		 WHEN profile LIKE '%,female' THEN 'female' 
		 END
	) AS gender,
	COUNT(device_id) AS number
	FROM user_submit 
	GROUP BY gender;

写法2里LIKE 后面用%,male中的逗号区分，避免female中的male 带来歧义。

Knowledge Point 知识点

1.SUBSTRING_INDEX() 函数

定义： substring_index()函数用来截取字符串
语法：substring_index(str,delim,count)

str 要处理的字符串
delim  分隔符
count  计数

#示例：
str=www.wiki.com
substring_index(str,'.',1) 
# return：www
substring_index(str,'.',-2) 
# wiki.com

• 如果 number 是正数，则返回从str左边开始计数的第 number 个delimiter（不包含delimiter）左边的字符串。
• 如果 number 是负数，则返回从str右边开始计数的第(number的绝对值)个delimiter（不包含delimiter）右边的字符串。
• BUT，如果要中间的的 wiki 怎么办？
  需要从两个方向截取：
  先截取从右数第二个分隔符的右边的全部内容，再截取从左数的第一个分隔符的左边的全部内容：

substring_index(substring_index(str,'.',-2),'.',1);
# return wiki

Reference

牛客网练习